使用In子句使用ADO.net代码 [英] Using In clause using ADO.net code
本文介绍了使用In子句使用ADO.net代码的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试使用'where in(a,b,c)'子句使用ado.net和存储过程。下面是代码。
SqlConnection sqlConnection1 = 新 SqlConnection( connectionstring);
sqlConnection1.Open();
var sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection1;
sqlCommand.CommandType = CommandType.StoredProcedure;
var idList = new List< int> { 17 , 55 };
var idParameterList = new List< string>();
var index = 0 ;
foreach ( var id in idList)
{
var paramName = @ idParam + index;
sqlCommand.Parameters.AddWithValue(paramName,id);
idParameterList.Add(paramName);
index ++;
}
sqlCommand.CommandText = 字符串 .Format( displayInfo, string .Join( ,,idParameterList)); // displayinfo是存储过程名称
使用(SqlDataReader sqlReader = sqlCommand.ExecuteReader())
{
var resultTable = new DataTable();
resultTable.Load(sqlReader);
// 对结果表执行某些操作
}
sqlConnection1.Close();
存储过程
alter PROCEDURE displayInfo
@ idParam varchar (max)
AS
BEGIN
SELECT * FROM 表 WHERE idParam IN ( @ idParam );
END
GO
并得到以下错误 - :
过程或函数displayInfo指定的参数太多。
请告知正确的陈述应该是什么
解决方案
< span class =code-leadattribute>您 使用 在 语法 之后 调用 store procedure
sqlCommand 。 CommandType = CommandType .StoredProcedure;
sqlCommand 。 CommandText = displayInfo;
// 在这里你可以使用你的列表和循环
sqlCommand .Parameters [idParam0]。 Value = 17;
sqlCommand .Parameters [idParam1]。 Value = 55;
您应该只在CommandText中提供过程名称
喜欢...
sqlCommand.CommandText = displayInfo跨度>;
I am trying to use 'where in (a,b,c)' clause using ado.net and stored procedure. Below is the code.
SqlConnection sqlConnection1 = new SqlConnection("connectionstring");
sqlConnection1.Open();
var sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection1;
sqlCommand.CommandType = CommandType.StoredProcedure;
var idList = new List<int> { 17, 55};
var idParameterList = new List<string>();
var index = 0;
foreach (var id in idList)
{
var paramName = "@idParam" + index;
sqlCommand.Parameters.AddWithValue(paramName, id);
idParameterList.Add(paramName);
index++;
}
sqlCommand.CommandText = String.Format("displayInfo",string.Join(",", idParameterList)); //displayinfo is Stored Procedeure Name
using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
{
var resultTable = new DataTable();
resultTable.Load(sqlReader);
// Do something with the result table
}
sqlConnection1.Close();
Stored Procedure
alter PROCEDURE displayInfo
@idParam varchar(max)
AS
BEGIN
SELECT * FROM Table WHERE idParam IN (@idParam);
END
GO
And get below error-:
Procedure or function displayInfo has too many arguments specified.
Please Advise what should be the correct statement
解决方案
You should use the following syntax for calling store procedure sqlCommand.CommandType = CommandType.StoredProcedure; sqlCommand.CommandText = "displayInfo"; // Here you could use your list and loop sqlCommand.Parameters["idParam0"].Value = "17"; sqlCommand.Parameters["idParam1"].Value = "55";
You should only provide the Procedure Name inCommandText
like...
sqlCommand.CommandText = "displayInfo";
这篇关于使用In子句使用ADO.net代码的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文