的SqlCommand的与IN参数的问题 [英] An issue of SqlCommand with parameters for IN
问题描述
显然,下面的代码不会打印任何东西了,因为它是预期..我相信这是我试图把项目列表的事实做 @namelist
。显然,它不只是一个文本替换。
我怎样才能解决这个问题?谢谢
使用(VAR连接=新的SqlConnection(_connectionString))
{
connection.Open() ;使用(VAR CMD = connection.CreateCommand())
{
cmd.CommandText = @
选择列名,table_name的information_schema.columns在中(@namelist)表格名 ;
cmd.Parameters.AddWithValue(@名称列表,'tableOne','tableTwo');
VAR读卡器= cmd.ExecuteReader();
而(reader.Read())
{
变种一个=读者[0];
Console.WriteLine(一);
}
}
}
不幸的是,SQL参数不是的解决的这种方式,换句话说,后端不只是建立一个安全的字符串,其值替换每个参数。相反,你必须动态地构建一个参数列表:
cmd.CommandText = @选择列名,从INFORMATION_SCHEMA TABLE_NAME。列在表格名中(@ P1,P2 @,@ P3); //这可以建立动态
然后添加每个参数:
cmd.Parameters.AddWithValue(@ P1,tableOne);
cmd.Parameters.AddWithValue(@ P2,tableTwo);
cmd.Parameters.AddWithValue(@ P3,tableThree);
您当然可以在一个循环中添加这些参数,如果该号码是未知的,直到运行时:
为(VAR I = 0; I< myParams.length;我++)
{
cmd.Parameters。 AddWithValue(@ p的+ i.ToString(),myParams [I]);
}
如果您的表的列表存储在一个枚举
,或者你可以逃脱他们或用正则表达式验证它们,这也将是相当安全的,只是建立在原始的SQL自己,而不是使用这些参数。
这是当然的大原因之一,我使用 PostgreSQL的;为阵列的本地支持。
Apparently, the following code does not print anything out as it is expected.. I am sure it is to do with the fact that I tried to put a list of items in to @namelist
. Clearly, it is not just a text replacement.
How can I solve this problem? Thanks
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = @"select column_name, table_name from information_schema.columns where table_name in (@namelist)";
cmd.Parameters.AddWithValue("@namelist", "'tableOne', 'tableTwo'");
var reader = cmd.ExecuteReader();
while (reader.Read())
{
var a = reader[0];
Console.WriteLine(a);
}
}
}
Unfortunately, SQL parameters aren't resolved that way, in other words, the backend doesn't just build a safe-string replacing each parameter with its value. Instead, you'll have to dynamically build a parameter list:
cmd.CommandText = @"select column_name, table_name from information_schema.columns where table_name in (@p1, @p2, @p3)"; // This can be built dynamically
And then add each parameter:
cmd.Parameters.AddWithValue("@p1", "tableOne");
cmd.Parameters.AddWithValue("@p2", "tableTwo");
cmd.Parameters.AddWithValue("@p3", "tableThree");
You could of course add these parameters in a loop if the number was unknown until runtime:
for(var i = 0; i < myParams.length; i++)
{
cmd.Parameters.AddWithValue("@p" + i.ToString(), myParams[i]);
}
If your list of tables were stored in an enum
, or you could escape them or validate them with a regular expression, it would also be fairly safe to just build the raw SQL yourself and not use parameters at all.
This is, of course, one of the big reasons I use PostgreSQL; native support for arrays.
这篇关于的SqlCommand的与IN参数的问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!