c＃ado、netを使用してSQLサーバーデータベースに行を挿入してみてください

Question

コンソールアプリケーションでAdo.Netを使用してSQLServerデータベースに行を挿入しようとしています。キーボードからの入力を読み取っています。

これが私のコードです：

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

私の問題は、コンパイラが補間された文字列を文字列として読み取るが、列を変数として認識しないことです...行全体を単一の文字列として読み取ります。何が問題ですか？

Answer 1

これは非常に重要です。SQLで補間文字列を使用しないでください。それはあらゆる点で悪いです：

• セキュリティ：SQLインジェクション
• 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.

Answer 2

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