try to insert row to sql server database using c# ado,net

Question

I am trying to insert a row in a SQL Server database using Ado.Net in a Console Application I am reading input from keyboard....

here is my code:

private void InsertStudents(string con)
{      
   SqlConnection Connection = new SqlConnection(con);
   SqlDataAdapter adapter = new SqlDataAdapter();

   int id               = Convert.ToInt32(Console.ReadLine());
   string     firstName = Console.ReadLine();
   string      lastName = Console.ReadLine();
   DateTime dateOfBirth = Convert.ToDateTime(Console.ReadLine());
   double   tuitionFees = Convert.ToDouble(Console.ReadLine());

   string sql = "$insert into product (ID,FirstName,LastName,DateOfBirth,TuitionFees) values {id}, {firstName}, {lastName}, {dateOfBirth}, {tuitionFees})";
   try
   {
        Connection.Open();
        adapter.InsertCommand = new SqlCommand(sql, Connection);
        adapter.InsertCommand.ExecuteNonQuery();
        Console.WriteLine(" 1 Row inserted!");
   }
   catch (Exception ex)
   {
        Console.WriteLine(ex.ToString());
   }
}

my problem is that the compiler reads my interpolated string as a string but not recognising the columns as variables...it reads the whole line as a single string. what is the problem?

Marc Gravell · Accepted Answer · 2020-11-24 10:47:45Z

This is very important: do not use interpolated strings with SQL; it is bad in every way:

security: SQL injection
performance: query plan reuse
reliability: brittleness with reserved symbols like '
correctness: i18n/l10n (i.e. formatting) issues - especially relevant for the DateTime value (date of birth; is "07/11/2020" the 7th of November? or the 11th of July?) - but also the tuition fees amount (in "123,456", is the comma a decimal separator (France et al)? or a group separator?)

The correct approach is parameters. Always.

So, SQL like:

insert into product (ID,FirstName,LastName,DateOfBirth,TuitionFees)
values (@id, @firstName, @lastName, @dateOfBirth, @tuitionFees)

And to do that, either learn about ADO.NET parameters, or: use a tool like Dapper that simplifies it:

int id = ...
string firstName = ...
string lastName =  ...
DateTime dateOfBirth = ...
double   tuitionFees = ...

using var conn = new SqlConnection(con); // Dapper will deal with the open/close
conn.Execute(@"
insert into product (ID,FirstName,LastName,DateOfBirth,TuitionFees)
values (@id, @firstName, @lastName, @dateOfBirth, @tuitionFees)",
    new { id, firstName, lastName, dateOfBirth, tuitionFees });

Also, final note: do not use double for currency; use decimal. double is not suitable for currency amounts.

jason.kaisersmith · Accepted Answer · 2020-11-24 09:49:15Z

0

You have placed the dollar sign inside the string, instead of in front of it. It should be:

string sql = $"insert into product (ID,FirstName,LastName,DateOfBirth,TuitionFees) values {id}, {firstName}, {lastName}, {dateOfBirth}, {tuitionFees})";

answered Nov 24, 2020 at 9:49

jason.kaisersmith

9,7233 gold badges35 silver badges61 bronze badges

1 Comment

Marc Gravell Over a year ago

this is actively harmful advice - exactly the same as I wrote here

Collectives™ on Stack Overflow

try to insert row to sql server database using c# ado,net

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Collectives™ on Stack Overflow

try to insert row to sql server database using c# ado,net

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related