如何将SQL参数中的字符串数组传递给SQL中的in子句
本文关键字:SQL 中的 in 数组 子句 字符串 参数 | 更新日期: 2023-09-27 18:14:07
我用一种复杂的方式做的逻辑。
我只需要在存储过程中执行这个查询:select Sizes, SUM(Quantity)
from tbl_SizeBreakup
where (Brand=@brand)
and (Combo in ('1','2')) ...
我必须在SQL参数中传递的组合在c#中使用是
DataSet dt = new DataSet();
cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "sp_Accessories";
cmd.Connection = con;
cmd.Parameters.AddRange(
new SqlParameter[] {
new SqlParameter("@Mode",mode),
new SqlParameter("@Combo",combo),
}}
所以如果我传递1个参数,按预期工作。我应该传递的组合是一个string[]
(字符串数组)。数组长度可以是任何值,这取决于用户在UI中选择的内容。
我的问题是,如何将string[]
传递到new SqlParameter("@Combo",combo)
?
My storage procedure.
ALTER proc [dbo].[sp_Accessories]
(
@Mode varchar(50)=null,
@id int=null,
@brand varchar(50)=null,
@department varchar(MAX)=null,
@season varchar(50)=null,
@groupname varchar(MAX)=null,
@styles varchar(50)=null,
@combo varchar(50)=null,
@combo_color nvarchar(max)=null,
)
as
if @Mode='getsizewise'
begin
select Sizes,SUM(Quantity) from tbl_SizeBreakup where (Brand=@brand) and (Department=@department) and (Season=@season) and (Group_Name=@groupname) and (Style=@styles)
and (Combo_Color=@color) and (Total_Add_Qty='Total Quantity') Group By Sizes
end
引言:尽管OP已经接受了一个答案,但我认为最好还是分享一下我的经验,因为我相信我即将展示的方法比接受的方法要好。
我发现传递数组到sql server数据库的最好方法是使用user defined table type
和c# DataTable
。在您的例子中,由于您想要传递一维的字符串数组,因此非常简单:
首先需要在数据库中创建一个用户定义的表类型:
CREATE TYPE dbo.StringArray As Table (
StringItem varchar(50) -- you can use any length suited for your needs
)
然后你需要在c#代码中创建一个数据表:
DataTable dt = new DataTable();
dt.Columns.Add("StringItem", typeof(System.String));
然后更改存储过程以接受此数据类型作为参数:
ALTER proc [dbo].[sp_Accessories]
(
@Mode varchar(50)=null,
@id int=null,
@brand varchar(50)=null,
@department varchar(MAX)=null,
@season varchar(50)=null,
@groupname varchar(MAX)=null,
@styles varchar(50)=null,
@combo dbo.StringArray Readonly=null, -- NOTE THIS CHANGE
@combo_color nvarchar(max)=null,
)
as
if @Mode='getsizewise'
begin
select Sizes,SUM(Quantity) from tbl_SizeBreakup where (Brand=@brand) and
(Department=@department) and (Season=@season) and (Group_Name=@groupname) and (Style=@styles)
and (Combo_Color=@color) and (Total_Add_Qty='Total Quantity')
and comboColumn in(select StringItem from @Combo) -- NOTE THIS CHANGE
Group By Sizes
end
然后需要在c#代码中将字符串数组转换为dataTable。
foreach (string s in YourStringArray) {
string[] temp = {s};
dt.Rows.Add(temp);
}
将DataTable作为参数添加到存储过程中:
System.Data.SqlClient.SqlParameter sp = new Data.SqlClient.SqlParameter();
sp.SqlDbType = SqlDbType.Structured;
sp.Value = dt;
sp.ParameterName = "@Combo";
cmd.Parameters.Add(sp);
构建并运行。
这种方法应该比使用sql用户定义函数有更好的性能,也可以用于不同的数据类型。这是使用它的最佳理由之一:考虑一个需要传递日期数组的场景:csv方法需要sql将每个字符串转换为日期,而使用这种方法可以简单地按原样传递日期,而不需要将它们转换为字符串,然后再转换回日期。此外,您可以传递二维数组或字典,您所要做的就是在sql数据库中创建适当的用户定义数据类型。
注意:这里直接写的代码,可能会有一些拼写错误
您可以在csv(逗号分隔值)中传递列表:'1,2,3',此字符串在SP中是无用的,但您可以转换为表,首先创建此函数:
CREATE FUNCTION fun_CSV_to_Table
(
@pcsvList varchar(max)
)
RETURNS @tableWithValues table(theColumn varchar(100))
AS
BEGIN
DECLARE @pos INT
WHILE CHARINDEX(',', @pcsvList) > 0
BEGIN
SELECT @pos = CHARINDEX(',', @pcsvList)
INSERT INTO @tableWithValues
SELECT LTRIM(RTRIM(SUBSTRING(@pcsvList, 1, @pos-1)))
SELECT @pcsvList = SUBSTRING(@pcsvList, @pos+1, LEN(@pcsvList)-@pos)
END
--Insert the last value.
INSERT INTO @tableWithValues SELECT @pcsvList
RETURN
END
GO
然后你可以用它:
SELECT SIZES, SUM(QUANTITY)
FROM TBL_SIZEBREAKUP
WHERE BRAND=@BRAND
AND COMBO IN (select theColumn FROM dbo.fun_CSV_to_Table(@aList))
/* @aList must be a csv: '1,2,3,...,n' */
您可以使用Parameters.AddWithValues
(Updated):
DataSet dt = new DataSet();
cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "sp_Accessories";
cmd.Connection = con;
var parameterList = new StringBuilder();
for (int i = 0; i < items.Length; i++)
{
parameterList.Append(items[i] + ",");
}
var parameters= parameters.ToString().TrimEnd(',');
cmd.Parameters.AddWithValue("@Combo", parameters);
cmd.Parameters.AddRange(
new SqlParameter[] {
new SqlParameter("@Mode",mode),
}}