SQL Server 从 C# 中选择查询执行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14120799/
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
SQL Server select query execution from c#
提问by Nithish Inpursuit Ofhappiness
string user = "1234";
string strSQL = string.Format("Select * From User where UserId = '{0}'",user);
SqlCommand myCommand = new SqlCommand(strSQL, cnn);
reader = myCommand.ExecuteReader();
My Usertable consists of UserIdand Passwordcolumns. The UserIdcolumn type is ncharand so I've used the single quotes. I get an error saying that
我的User表由UserId和Password列组成。该UserId列类型是nchar，所以我已经使用了单引号。我收到一个错误说
incorrect syntax near the keyword User"
关键字 User 附近的语法不正确"
(I guess the table name Useris being referred to here).
（我猜User这里引用了表名）。
I have the connection string and other database environment related things correctly for I've checked the database connection status and it is open(during program execution).
我有正确的连接字符串和其他数据库环境相关的东西，因为我已经检查了数据库连接状态并且它是打开的（在程序执行期间）。
What is the error in the syntax? I'm unable to retrieve the rows from my table.
语法错误是什么？我无法从我的表中检索行。
采纳答案by codingbiz
Useris a Keyword. Use square bracket around it to avoid the error. Select * from [User]
User是一个关键字。在它周围使用方括号以避免错误。Select * from [User]
string strSQL = string.Format("Select * From [User] where UserId = '{0}'",user);
Also, you should always use parameterized query like below to prevent SQL Injection attack:
此外，您应该始终使用如下参数化查询来防止 SQL 注入攻击：
string strSQL = string.Format("Select * From [User] where UserId = @UserId");
回答by John Woo
you should wrap userwith brackets []
你应该user用括号括起来[]
string strSQL = string.Format("Select * From [User] where UserId = '{0}'",user);
The query above is vulnerable to SQL Injection. It should be parameterized to avoid this. The following is an example:
上面的查询容易受到SQL Injection. 应该对其进行参数化以避免这种情况。下面是一个例子：
string user = "1234";
string strSQL = "Select * From [User] where UserId = @userID";
SqlCommand myCommand = new SqlCommand(strSQL, cnn);
myCommand.AddWithValue("@userID", user);
reader = myCommand.ExecuteReader();
use the following
使用以下
Try-Catchblock for proper catching of errorsusingstatement for proper object disposal
Try-Catch用于正确捕获错误的块using正确处置物品的声明
snippet:
片段：
string user = "1234";
string strSQL = "Select * From [User] where UserId = @userID";
using (SqlConnection cnn = new SqlConnection("connection string here"))
{
using (SqlCommand myCommand = new SqlCommand(strSQL, cnn))
{
myCommand.Parameters.AddWithValue("@userID", user);
using (SqlDataReader reader = myCommand.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(reader["columnName"].ToString());
}
}
}
}
回答by Soner G?nül
Wrap with []. It is a keyword. Read Reserved Keywordsarticle from MSDN.
用 包裹[]。它是一个关键字。阅读Reserved KeywordsMSDN 上的文章。
string strSQL = string.Format("Select * From [User] where UserId = '{0}'",user);
But more important part, your query is open for an SQL Injectionattack. You should always use parameterized queries.
但更重要的是，您的查询很容易受到SQL Injection攻击。您应该始终使用参数化查询。
string strSQL = "Select * From [User] where UserId = @userID";
SqlCommand myCommand = new SqlCommand(strSQL, cnn);
myCommand.Parameters.AddWithValue("@userID", user);
回答by Paul Aldred-Bann
You should really use parameters for this:
你真的应该为此使用参数：
string user = "1234";
using (SqlCommand command = new SqlCommand("select * from [User] where UserId = @userid", cnn))
{
command.Parameters.AddWithValue("@userid", user);
using (SqlDataReader reader = myCommand.ExecuteReader())
{
// iterate your results here
}
}
Well spotted by other posters, I never caught the reserved word thing with your table name. I've amended my answer - but can't take credit for missing the obvious!
被其他海报很好地发现，我从来没有用你的表名看到保留字。我已经修改了我的答案 - 但不能因为错过了显而易见的事情而受到赞扬！
