SQL Bulk Stored Procedure call C#

Question

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.

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

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.

Answer 2

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

Answer 3

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.