SQL批量存储过程调用C＃

How do I call stored procedures in bulk? I would like to do something like a bulk copy.

如何批量调用存储过程？我想做一些像批量复制的东西。

All that the stored procedure does is 8 selects for unique constraint and 8 inserts. With no returning value.

存储过程所做的全部是8选择唯一约束和8个插入。没有回报价值。

5 个解决方案

#1

You cannot do that.

你不能这样做。

Bulk copy is a firehose dump of data into a table, you cannot call sprocs or anything else instead of just dumping it into an existing table.

批量复制是将数据转储到表中，不能调用sprocs或其他任何内容，而只是将其转储到现有表中。

What you can do, however, is dump the data using bulk copy into a temporary table with the right structure, and then afterwards call your sproc that moves that data into the real tables, possibly by modifying existing data instead of inserting it, or whatnot.

但是，您可以做的是使用批量复制将数据转储到具有正确结构的临时表中，然后调用您的sproc将该数据移动到真实表中，可能通过修改现有数据而不是插入它，或者诸如此类。

#2

If you are using SQL Server 2008, then Table-Valued Parameters is a viable option.

如果您使用的是SQL Server 2008，则表值参数是可行的选项。

First, you create a user-defined table type containing all of your expected columns and data types on the SQL Server side:

首先，您创建一个用户定义的表类型，其中包含SQL Server端的所有预期列和数据类型：

create type dbo.MyTableType as table
( 
    foo int,
    bar varchar(100)
);

then use the above as the table type parameter for your stored procedure:

然后使用以上作为存储过程的表类型参数：

create procedure uspInsertMyBulkData
(
    @myTable dbo.MyTableType readonly
)
as
    /* now in here you can use the multi-row data from the passed-in table 
       parameter, @myTable, to do your selects and inserts*/

Then, on the C#/.NET client side, call this stored procedure via ADO.NET and pass in either a DataTable, an object that inherits from DbDataReader (such as DataTableReader), or an object of type IEnumerable<SqlDataRecord>:

然后，在C＃/ .NET客户端，通过ADO.NET调用此存储过程，并传入DataTable，一个继承自DbDataReader的对象（如DataTableReader），或一个IEnumerable 类型的对象：

// create my source DataTable
object [] row1 = {1, "a"};
object [] row2 = {2, "b"};
var myDataTable = new DataTable();
myDataTable.Columns.Add(new DataColumn("foo"));
myDataTable.Columns.Add(new DataColumn("bar"));
myDataTable.LoadDataRow(row1, true);
myDataTable.LoadDataRow(row2, true);

// bulk send data to database
var conn = new SqlConnection(connectionString);
var cmd = new SqlCommand("uspInsertMyBulkData", conn)
    {
        CommandType = CommandType.StoredProcedure
    };
SqlParameter param = cmd.Parameters.AddWithValue("@myTable", myDataTable);
param.SqlDbType = SqlDbType.Structured;
cmd.ExecuteNonQuery();

#3

If you want to bulk load data into a table (inserts), the SqlBulkCopy class is the way to go.

如果要将数据批量加载到表（插入）中，可以使用SqlBulkCopy类。

Alternatively, you can use the SqlDataAdapter. Set the InsertCommand to the stored procedure that will perform an insert, and map the datatable fields to the sproc parameters. If you have updated records in the datatable, you can also specify an UpdateCommand which will be fired for each updated row. Then call the Update method on the SqlDataAdapter passing it the datatable. You can set the UpdateBatchSize property to define how many records to send to the db in each roundtrip.

或者，您可以使用SqlDataAdapter。将InsertCommand设置为将执行插入的存储过程，并将数据表字段映射到sproc参数。如果已更新数据表中的记录，还可以指定将为每个更新的行触发的UpdateCommand。然后在SqlDataAdapter上调用Update方法，将数据表传递给它。您可以设置UpdateBatchSize属性以定义每次往返中要发送到数据库的记录数。

#4

SqlServer stored procedures can accept xml, so you could prepare your bulk data as an xml file and pass it to a special-purpose stored procedure which would then call your original stored procedure for each row. You'd need the OPENXML function.

SqlServer存储过程可以接受xml，因此您可以将批量数据准备为xml文件，并将其传递给专用存储过程，然后为每行调用原始存储过程。你需要OPENXML功能。

I hesitate to recommend the xml features of SqlServer, but this may be a case where they are appropriate.

我不推荐推荐SqlServer的xml功能，但这可能是适合的情况。

#5

I'm not saying that I recommend it, but you could put an insert trigger on the table you are bulk copying into that inserts into those 8 separate tables instead of the original one. You may need to have a tempdb big enough to store all the data though...

我不是说我推荐它，但是你可以在你正在批量复制的表上插入一个插入触发器插入到那些8个单独的表而不是原始的表中。您可能需要一个足够大的tempdb来存储所有数据，但...

CREATE TRIGGER TRG_REPLACETRIGGER
    ON BULK_TABLE
    INSTEAD OF INSERT
AS BEGIN
    INSERT TABLE1 (ID, VALUE) SELECT ID, VALUE1 FROM INSERTED
    INSERT TABLE2 (ID, VALUE) SELECT ID, VALUE2 FROM INSERTED
    -- ... TABLE3-7
    INSERT TABLE8 (ID, VALUE) SELECT ID, VALUE8 FROM INSERTED
END

#1