来自 C# 的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9966301/
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 query from C#
提问by Csharp_learner
I am trying to query SQL Server database from C#
我正在尝试从 C# 查询 SQL Server 数据库
I have class
我有课
Class_A
{
public fetch((string name, string last_name))
{
SqlConnection conn = null;
double val = 0;
string server = "123.444.22.sss";
string dbase = "xyz";
string userid = "cnsk";
string password = "xxxxxx";
string connection = "Data Source=" + server + ";Initial Catalog=" + dbase
+ ";User ID=" + userid + ";Password=" + password;
conn = new SqlConnection(connection);
try
{
conn.Open();
}
catch(Exception)
{
string e = "Database error contact administrator";
MessageBox.Show(e, "Error!");
}
try
{
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand("select * from table where NAME"
+ " = name and LAST_NAME = last_name", conn);
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
//do something
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
return (0);
}
}
There is a problem in my query.
我的查询有问题。
When I give normal query "select * from table" --- this gives me perfect results.
当我给出正常查询“select * from table”时——这给了我完美的结果。
But when I try to give where condition it gives me error. Any suggestions, to fix this? Thanks.
但是当我尝试给出 where 条件时，它给了我错误。有什么建议可以解决这个问题吗？谢谢。
采纳答案by Robbie
?? WARNINGThis answer contains a SQL injection security vulnerability. Do not use it. Consider using a parameterized query instead, as described in some of the other answers to this question (e.g. Tony Hopkinson's answer).
?? 警告此答案包含 SQL 注入安全漏洞。不要使用它。考虑使用参数化查询，如该问题的其他一些答案中所述（例如 Tony Hopkinson 的答案）。
Try adding quotes around the values in the where clause like this:
尝试在 where 子句中的值周围添加引号，如下所示：
select * from table where NAME = 'name' and LAST_NAME = 'last_name'
In your case where you are using variables you need to add the quotes and then concatenate the values of the variables into the string. Or you could use String.Formatlike this:
在您使用变量的情况下，您需要添加引号，然后将变量的值连接到字符串中。或者你可以这样使用String.Format：
var sql = String.Format("select * from table where [NAME] = '{0}' and LAST_NAME = '{1}'", name, last_name);
SqlCommand myCommand = new SqlCommand(sql);
回答by Wiktor Zychla
Try
尝试
select * from table where NAME = 'name' and LAST_NAME = 'last_name'
instead of
代替
select * from table where NAME = name and LAST_NAME = last_name
Edit:
编辑：
If nameand last_nameare your parameters then try this:
如果name和last_name是您的参数，请尝试以下操作：
SqlCommand myCommand = new SqlCommand("select * from table where NAME = @name and LAST_NAME = @last_name", conn);
myCommand.Parameters.AddWithValue( "@name", name );
myCommand.Parameters.AddWithValue( "@last_name", last_name );
Using parameterized commands means that you are invulnerable to a potential huge security hole - sql injection which ispossible when command text is manually concatenated.
使用参数化命令意味着您不会受到潜在的巨大安全漏洞 - sql 注入的影响，这在手动连接命令文本时是可能的。
回答by Loren Pechtel
The text needs to be quoted as others have said--but that's not really the right answer here. Even without malice you're going to run into trouble with the Irish here, look what happens when you try to look for Mr. O'Neill. Use parameters instead.
文本需要像其他人所说的那样引用——但这在这里并不是真正的正确答案。即使没有恶意，你也会在这里遇到爱尔兰人的麻烦，看看当你试图寻找奥尼尔先生时会发生什么。改用参数。
回答by Tony Hopkinson
Use a parameterised query, and more usings, and stop with the generic exceptions.
使用参数化查询和更多用途，并停止使用通用异常。
something like this where somName and SomeLastName are the values that you wan t to query for.
像这样的东西，其中 somName 和 SomeLastName 是您想要查询的值。
String sql = "Select * From SomeTable Where [Name] = @Name and [Last_Name] = @LastName";
try
{
using(SqlConnection conn = new SqlConnection(connection))
{
conn.Open();
using( SqlCommand command = new SqlCommand(sql,conn))
{
command.Parameters.Add(new SqlParameter("Name", DbType.String,someName));
command.Parameters.Add(new SqlParameter("LastName", DbType.String,someLastName));
using(IDataReader myReader = command.ExecuteReader())
{
while (myReader.Read())
{
//do something
}
}
}
}
return 0; // Huh?
}
catch(SqlException sex)
{
Console.Writeline(String.Format("Error - {0}\r\n{1}",sex.Message, sex.StackTace))
}
NB not checked might be a silly in it
NB 未检查可能是愚蠢的
