insert....
end
这是从C#中完成的。数据库中没有存储过程。
SqlCommand command = new SqlCommand();
// Set connection, etc.
for(int i=0; i< items.length; i++) {
command.CommandText += string.Format("update mytable set s_id=@s_id{0} where id = @id{0};", i);
command.Parameters.Add("@s_id" + i, items[i].SId);
command.Parameters.Add("@id" + i, items[i].Id);
}
command.ExecuteNonQuery();
编辑警告：尽管该答案在一定程度上正确，但并未解决所提出的问题，实际上ExecuteNonQuery会将工作负载提交到数据库（可以通过编写不正确的查询来证明：异常会在ExecuteNonQuery而不是Commit上引发）。
仅将所有CommandTexts追加到一个大的批处理命令中并不像看起来那么有用。
C＃中准备好的语句的主要好处是，在创建命令时完成数据库中的工作负载。而不是在执行它时（例如使用ExecuteNonQuery()执行命令，仅当您没有创建事务对象时才执行它）。
为了避免这种情况，并且只在所有语句中为数据库创建工作负载一次，更好的方法是创建一个Transaction对象并提交此事务。然后，所有命令都将被执行，而无需在数据库中再进行任何工作负载。
这将是一个更好的方法：
// Try to create the Command as early as possible with a valid Connection object
string commandString = "UPDATE Mytable SET s_id=@s_id where id = @id;";
var command = new SqlCommand(commandString, connection);
// Then define a Transaction object with your Connection
var transaction = connection.BeginTransaction();
command.Transaction = transaction;
// Now iterate through your array
for(int i=0; i<array.Length; i++)
{
command.Parameters.Add("@s_id", SqlDbType.YourType).Value = items[i].SId;
command.Parameters.Add("@id", SqlDbType.YourType).Value = items[i].Id;
command.ExecuteNonQuery(); // Not executed at this point
}
// And now execute it with the possibility to rollback all commands when it fails
try { transaction.Commit(); } // Here the execution is committed to the DB
catch (Exception)
{
transaction.Rollback();
throw;
}
command.ExecuteNonQuery(); // Not executed at this point 这是不正确的。即使使用具有事务的连接执行，它也会被执行并发送到服务器。已使用 SQL Profiler 进行测试。 - Eakan Gopalakrishnan using (var conn = new SqlConnection(GetConnectionStringFromSecret(args)))
{
conn.Open();
stopwatch.Start();
long counter = 0;
var tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted);
SqlCommand cmd = new SqlCommand("", conn, tran);
cmd.CommandType = System.Data.CommandType.Text;
int batch_param_counter = 0;
foreach (var chars_table in table_of_table_of_chars)
{
var key = string.Concat(chars_table);//get 1st param
var hash = BitConverter.ToString(hasher.ComputeHash(Encoding.UTF8.GetBytes(key))).Replace("-", "").ToLowerInvariant();//get 2nd param
cmd.CommandText += $"insert into hash_table([key], hash) values(@key{batch_param_counter}, @hash{batch_param_counter});{Environment.NewLine}";
var param_key = new SqlParameter("@key" + batch_param_counter, System.Data.SqlDbType.VarChar, 20);
param_key.Value = key;
cmd.Parameters.Add(param_key);
var hash_key = new SqlParameter("@hash" + batch_param_counter, System.Data.SqlDbType.VarChar, 32);
hash_key.Value = hash;
cmd.Parameters.Add(hash_key);
batch_param_counter++;
if (counter % 200 == 0)
{
cmd.Prepare();
cmd.ExecuteNonQuery();
cmd.Dispose();
cmd = new SqlCommand("", conn, tran);
cmd.CommandType = System.Data.CommandType.Text;
batch_param_counter = 0;
}
if (counter % 20000 == 0)
{
if (cmd != null && !string.IsNullOrEmpty(cmd.CommandText))
{
cmd.Prepare();
cmd.ExecuteNonQuery();
cmd.Dispose();
cmd = new SqlCommand("", conn, tran);
cmd.CommandType = System.Data.CommandType.Text;
batch_param_counter = 0;
}
tran.Commit();
tran = null;
if (Console.KeyAvailable)
break;
cmd.Transaction = tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted);
}
counter++;
}
if (cmd != null && !string.IsNullOrEmpty(cmd.CommandText))
{
cmd.Prepare();
cmd.ExecuteNonQuery();
cmd.Dispose();
}
if (tran != null)
tran.Commit();
stopwatch.Stop();
}
您可以将值作为逗号分隔的字符串发送到存储过程参数中，然后使用 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中传递任意数量的参数。