CODEWITHSUNDEEP
c#sql-server
Alexandros Angelopoulos
Alexandros Angelopoulos

Reputation: 19

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

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?

Upvotes: 2

Views: 705

Answers (2)

Marc Gravell
Marc Gravell

Reputation: 1062484

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.

Upvotes: 3

jason.kaisersmith
jason.kaisersmith

Reputation: 9600

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})";

Upvotes: 0

Related Questions