带参数的 SelectCommand 提供空结果
目前，我将稍微清理一下我的代码，VS 告诉我，最好对 sql 命令使用 SqlParameter 而不是复合 string。 所以我决定更改我的代码，不幸的是现在我没有得到结果，我也不知道为什么。 这是我的代码片段：
...
DataTable dt = new DataTable();
SqlConnection connection = new SqlConnection(GetSQLConnectionString());
SqlDataAdapter sqlSelect = new SqlDataAdapter();
try
{
connection.Open();
sqlSelect.SelectCommand = connection.CreateCommand();
sqlSelect.SelectCommand.CommandText = "SELECT id, @FROM AS \"from\", @TO AS \"to\" FROM Dictionary WHERE @FROM LIKE @SEARCHSTRING";
sqlSelect.SelectCommand.Parameters.Add(new SqlParameter("@FROM", this.from));
sqlSelect.SelectCommand.Parameters.Add(new SqlParameter("@TO", this.to));
sqlSelect.SelectCommand.Parameters.Add(new SqlParameter("@SEARCHSTRING", "'%" + this.SearchField.Text + "%'"));
sqlSelect.Fill(dt);
connection.Close();
}
catch(SqlException e)
...
我没有得到任何异常。为什么搜索后dt为空？ （对于复合字符串，选择有效。）出了什么问题？
格瑞兹
Currently I will clean my code a little bit and VS told me, it is better to use the SqlParameter for the sql commands instead a compound string.
So I decided to change my code, unfortunately now I don’t get a result and I don’t know why.
Here is the piece of my code:
...
DataTable dt = new DataTable();
SqlConnection connection = new SqlConnection(GetSQLConnectionString());
SqlDataAdapter sqlSelect = new SqlDataAdapter();
try
{
connection.Open();
sqlSelect.SelectCommand = connection.CreateCommand();
sqlSelect.SelectCommand.CommandText = "SELECT id, @FROM AS \"from\", @TO AS \"to\" FROM Dictionary WHERE @FROM LIKE @SEARCHSTRING";
sqlSelect.SelectCommand.Parameters.Add(new SqlParameter("@FROM", this.from));
sqlSelect.SelectCommand.Parameters.Add(new SqlParameter("@TO", this.to));
sqlSelect.SelectCommand.Parameters.Add(new SqlParameter("@SEARCHSTRING", "'%" + this.SearchField.Text + "%'"));
sqlSelect.Fill(dt);
connection.Close();
}
catch(SqlException e)
...
I don’t get any exception. Why is dt empty after the search? (With a compound string, the select works.) What went wrong?
Greetz
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您不能使用这样的参数指定字段名称。在 where 子句
WHERE @FROM LIKE @SEARCHSTRING中，它将参数@FROM的值与参数@SEARCHSTRING的值进行比较。如果 where 子句的计算结果为 true，您将获得字典表中的每条记录，如果其计算结果为 false，您将不会获得任何记录。它永远不会将@from的内容视为字典表中的字段名称。
You can't specify field names using parameters like that. In your where clause
WHERE @FROM LIKE @SEARCHSTRINGit is comparing the value of the parameter@FROMwith the value of the parameter@SEARCHSTRING.If the where clause evaluates to true you will get every record in the dictionary table, if it evaluates to false you will get no records. It will never treat the contents of @from as a field name in the dictionary table.
正如人们在这里所说的，问题是你不能将字段名称作为参数传递。
由于多种原因，您采用的方法是一个坏主意，首先，当您以这种方式传递 sql 命令时，服务器必须在每次执行该查询时重新编译它，这会给服务器带来额外的负载并降低性能。其次，像这样传输 select 语句的安全性存在风险，因为它会让拦截它的任何人都可以查看您的表结构。第三，使用这样的 select 语句意味着如果您想重用代码，则无需复制粘贴就无法重用。
我建议切换到存储过程。您仍然可以传入参数等，但它将改进您的代码，因为它将 SQL 从 C# 中取出，只留下相关的内容。
如果您确实需要传入要在这样的 select 语句中使用的字段名，您可以在 SQL 中执行此操作并构建查询字符串，然后使用 sp_executesql 执行它。
基本上，您所做的就是声明一个查询字符串
，然后只需使用 sp_executesql 来执行 @queryString
您可能需要将参数转换为 Varchar，但如果在构建查询字符串时遇到任何错误
As people have said here the issue is that you cant pass field names as parameters.
The approach you are taking is a bad idea for a couple of reasons, firstly when you pass a sql command in this way the server has to recompile it every time you execute that query, this puts extra load on the server and slows down performance. Secondly it is a risk to security transmitting your select statements like this as it gives anyone who intercepts it a look at your table structure. Thirdly using select statements like this means if you ever want to reuse the code you cant without a copy paste.
What I would reccomend is switching to a stored procedure. you can still pass in your parameters etc but it will improve your code as it takes the SQL out of the c# and leaves only what is relevant.
If you REALLY need to pass in fieldnames to be used within the select statement like this you can do this in SQL and build up a query string then execute it using sp_executesql.
Basically what you do is declare a query string like
then just use sp_executesql to execute the @queryString
You may need to cast the parameters as Varchar though if you get any errors whilst building up the querystring
为什么你要写这样的查询？
您正在尝试从表中获取 @FROM 并尝试将其作为参数传递，这应该如何工作？另外为什么要包含斜杠？它们只会让事情变得混乱，删除它们。 Select 查询仅使用“WHERE”子句获取输入参数，而不使用其他子句。
尝试将其替换为此
Also remove all but the last栖身的:
另请注意“FROM”也是一个 SQL 关键字，因此请通过将其括在“[]”中来确保以正确的方式解释它。
希望这有帮助...
Why you have written query like this?
you are trying to fetch @FROM from the table and also trying to pass it as a parameter, hows that supposed to work? Also why have you included slashes? they just make things messy, remove them. A Select query takes input parameters only with "WHERE" clause and nowhere else.
Try replacing it with this
Also remove all but the last occurrences of:
Also take care that "FROM" is an SQL keyword as well, so make sure its being interpreted the right way by enclosing it in "[]".
Hope this helps...
这：
假设是：
This:
suppose to be: