SQL Bulk Stored Procedure call C#

How do I call stored procedures in bulk? I would l开发者_Python百科ike 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.

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.

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.

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

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

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>:

// 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();

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

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.

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.

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

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...

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