C# 将字符串或 NULL 插入 SQL Server 数据库时出现问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/374522/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Problem inserting string or NULL into SQL Server database
提问by beardog
I'm having trouble coming up with the proper syntax for allowing either a string or a NULL to be passed to the database. Here's my code:
我在想出允许将字符串或 NULL 传递给数据库的正确语法时遇到了麻烦。这是我的代码：
string insertString = String.Format(
@"INSERT INTO upload_history (field1, field2, field3)
VALUES ('{0}', '{1}', '{2}')",
varField1, varField2, varField3);
I used single quotes around the variable placeholders so that the database would properly accept a string value. However, if NULL is passed, it ends up going into the database as the string "NULL".
我在变量占位符周围使用了单引号，以便数据库正确接受字符串值。但是，如果传递了 NULL，它最终会作为字符串“NULL”进入数据库。
Is there a way I can leave the single quotes out of the InsertCommand string and conditionally add single quotes to my variables?
有没有办法可以将单引号排除在 InsertCommand 字符串之外，并有条件地将单引号添加到我的变量中？
采纳答案by Marc Gravell
Don't concatenate the string (string.Format) - use parameters (@p1etc) - then you can pass DBNull.Valueto mean null to SQL Server
不要连接字符串 ( string.Format) - 使用参数 (@p1等) - 然后您可以将DBNull.Value意思传递给 SQL Server
SqlCommand cmd = new SqlCommand();
cmd.CommandText = @"INSERT INTO upload_history (field1, field2, field3)
VALUES (@p1, @p2, @p3)";
cmd.Parameters.AddWithValue("@p1", (object)someVar ?? DBNull.Value);
//...
This also protects you from SQL injection
这也可以保护您免受 SQL 注入
回答by Stefan Schultze
Concentating the string with String.Format might be a big security risk (SQL Injection), and also problematic if you want to insert the ' character.
将字符串与 String.Format 集中在一起可能会带来很大的安全风险（SQL 注入），如果您想插入 ' 字符，也会有问题。
Solution:
解决方案：
cmd.CommandText = "INSERT INTO upload_history (field1, field2, field3) " +
"VALUES (@p1, @p2, @p3)";
cmd.Parameters.AddWithValue("@p1", varField1);
cmd.Parameters.AddWithValue("@p2", varField2);
cmd.Parameters.AddWithValue("@p3", varField3);
cmd.ExecuteNonQuery();
回答by rjrapson
In the spirit of answering the question as it was asked, and being fully aware that refactoring the code to paramaterizing the queries is the correct solution, you could write a function that returns either a single-quoted string or a non-quoted NULL string value, then remove the single-quotes from the query string.
本着回答问题的精神，并充分意识到重构代码以对查询进行参数化是正确的解决方案，您可以编写一个函数来返回单引号字符串或非引号 NULL 字符串值，然后从查询字符串中删除单引号。
string insertString = String.Format( @"INSERT INTO upload_history (field1, field2, field3) VALUES ({0}, {1}, {2})", ToStringorNull(varField1), ToStringorNull(varField2), ToStringorNull(varField3));
If you are using VS 2008 you could even implement it as an extension method.
如果您使用的是 VS 2008，您甚至可以将其作为扩展方法来实现。
string insertString = String.Format( @"INSERT INTO upload_history (field1, field2, field3) VALUES ({0}, {1}, {2})", varField1.ToStringorNull, varField2.ToStringorNull, varField3.ToStringorNull);
I'll leave creating the ToStringorNull function to you - it isn't hard :-)
我将把创建 ToStringorNull 函数留给你——这并不难:-)
