您可以将值作为逗号分隔的字符串发送到存储过程参数中,然后使用 Split()
SQL 函数拆分它们。
尝试一下
StringBuilder Par1 = new StringBuilder("");
StringBuilder Par2 = new StringBuilder("");
for (var i = 0; i < MyObject.Length; i++)
{
if (i > 0)
{
Par1.Append(",");
Par2.Append(",");
}
Par1.Append(MyObject[i].Prop1);
Par2.Append(MyObject[i].Prop2);
}
myCommand.Parameters.Add(new SqlParameter("@Par1", Par1.ToString()));
myCommand.Parameters.Add(new SqlParameter("@Par2", Par2.ToString()));
myCommand.CommandText = "MyStoredProcedure";
myCommand.CommandType = System.Data.CommandType.StoredProcedure;
myCommand.ExecuteNonQuery();
你的存储过程将会长成这个样子
CREATE Procedure MyStoredProcedure
(
@Par1 as varchar(max),
@Par2 as varchar(Max)
)
AS
create table #AllValues
(
value1 varchar(50),
value2 varchar(50)
);
create table #Par1s
(
id integer,
data varchar(50)
);
create table #Par2s
(
id integer,
data varchar(50)
);
insert into #Par1s select * from dbo.Split (@Par1,',')
insert into #Par2s select * from dbo.Split (@Par2,',')
Insert into #AllValues(value1,value2)
Select #Par1s.data,#Par2s.data From #Par1s Inner Join #Par2s On #Par1s.ID = #Par2s.ID
Insert into myTable(Col1,Col2)
Select value1,value2 from #AllValues
您可以使用相同的方法在Insert
或Update
中传递任意数量的参数。