To pass bulk rows of Data to stored Procedure

Question

I am reading an spreadsheet file via C# ... and my spreadsheet has more than 1000 rows. I need to send each row of data to stored procedure to perform some data base side logic and update records. I need some help to send all those 1000 rows of data at a single round trip to save time. What is the technique need to group all these 1000 rows of data.

Answer 1

Guessing that you are using SQL Server 2008 or better you have a few options. All of these options were covered in great detail at Tech-Ed 2010 in New Orleans and video of the session is available online. Below is a summary of the options presented.

Option #1 - Bulk Insert (not really covered in video)

This is a great option if you just need to "dump" the data into a table and you don't need to do much with the data except get it into the database. This is also supported in ADO.NET using the SqlBulkCopy object. I have also written a lightweight wrapper you can find on CodePlex to make working with SQL Server and ADO.NET easier

Option #2 - Pass a delimited list

Take all of your data and build a big string and pass the whole string into a stored procedure. This is surprisingly fast but comes with a lot of baggage. You have to have a split function to get the data out and to get the best performance you will need to do the splitting with SQL-CLR and that can be showstopper if you don't own the database.

Option #3 - Pass as XML

This is almost identical to Option #2 because you are once again passing in a giant string into one parameter. This also has reasonable performance but also comes with much of the same baggage that Option #2 has but without the split function because Sql Server knows how to parse XML.

Option #4 - Use a Table Valued Function (SQL Server 2008)

This option is very cool and gives the best performance. You start by creating a value type in SQL Server of type "Table" then you create a stored procedure that takes that value type in as a parameter. In C# you can now create a SqlCommand and add a parameter with a type of SqlDbType.Structured.

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Test.spTVP";
var p = cmd.Parameters.Add("@Values", SqlDbType.Structured);
p.TypeName = "Test.OrderTableType";
p.Value = dataTable;
cmd.Execute…;

When the stored procedure is executed all of the data is available in the table variable of the stored procedure. It can be used just like any other table variable so moving the data around is very simple.

Option #5 - Use a Streaming Table Valued Function (SQL Server 2008)

A little more work then option #4 because you have to setup an iterator but you get some crazy performance out of it because you don't have to load all of the data on the client before passing it into the stored procedure. The .NET Runtime will actually stream the data into the database and the implementation of the stored procedure is the same.

class MyStreamingTvp : IEnumerable<SqlDataRecord> { …
}
…
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Test.spTVP";
var p = cmd.Parameters.Add("@Values", SqlDbType.Structured);
p.TypeName = "Test.OrderTableType";
p.Value = new MyStreamingTvp(…);
cmd.Execute…;

All of these options are covered with great detail and a little humor in the video I mentioned at the beginning. It was one of my favorite sessions at Tech-Ed this year.

Answer 2

Ryan's answer is very thorough and covers the various options. For a relatively small number of rows (1000-5000 is pretty small all things considered), I would use what is outlined as option #3, passing XML as a stored procedure parameter. We do this often in my shop and the following is the code associated with it:

I am assuming your spreadsheet data is simple and you already have it readily available within your code as something like a List that you've created or a DataTable. For this simple example, I will assume that your data is a DataTable for simplicity sake.

I, like Ryan, am also assuming SQL 2008.

1 - Prepare the data in C# by transforming the data into XML that will be passed to the stored procedure. This is just a string of XML. We use a method in our Base Data class. You pass in your DataTable and it will convert it to a simple XML string that you can pass as a parameter to your stored procedure.

    public string ConvertToXMLDataString(DataTable table) {
        StringBuilder XMLString = new StringBuilder();
        if (string.IsNullOrEmpty(table.TableName))
            table.TableName = "DataTable";
        XMLString.AppendFormat("<{0}>", table.TableName);
        DataColumnCollection tableColumns = table.Columns;
        foreach (DataRow row in table.Rows) {
            XMLString.AppendFormat("<RowData>");
            foreach (DataColumn column in tableColumns) {
                XMLString.AppendFormat("<{1}>{0}</{1}>", row[column].ToString(), column.ColumnName);
            }
            XMLString.AppendFormat("</RowData>");
        }
        XMLString.AppendFormat("</{0}>", table.TableName);
        return XMLString.ToString();
    }

2 - I've created a simple example DataTable that will contain 1000 rows of data, all integers, 10 columns

    DataTable table = new DataTable("DataTable");
            for(int i = 1; i < 11; i++){
                table.Columns.Add(new DataColumn("Column" + i.ToString()));
            }
            int j = 0;
            for (int i = 0; i < 1000; i++) {
                DataRow newRow = table.NewRow();
                for (int k = 0; k < table.Columns.Count; k++) {
                    newRow[k] = j++;
                }               
                table.Rows.Add(newRow);
            }

The end result of passing the DataTable to ConvertToXMLDataString is a nicely formatted XML representation of the DataTable that can be passed into the stored procedure and easily selected from:

<DataTable>
    <RowData>
        <Column1>0</Column1>
        <Column2>1</Column2>
        <Column3>2</Column3>
        <Column4>3</Column4>
        <Column5>4</Column5>
        <Column6>5</Column6>
        <Column7>6</Column7>
        <Column8>7</Column8>
        <Column9>8</Column9>
        <Column10>9</Column10>
    </RowData>
    <RowData>
        <Column1>10</Column1>
        <Column2>11</Column2>
        <Column3>12</Column3>
        <Column4>13</Column4>
        <Column5>14</Column5>
        <Column6>15</Column6>
        <Column7>16</Column7>
        <Column8>17</Column8>
        <Column9>18</Column9>
        <Column10>19</Column10>
    </RowData>
</DataTable>

3 - Now, create a stored procedure that will handle that XML data string that has been passed into it.

    CREATE PROCEDURE [dbo].[pr_Test_ConvertTable] 
@TableData XML
AS
BEGIN
    SET NOCOUNT ON
    SET ANSI_NULLS ON
    SET ARITHABORT ON

    DECLARE @TempTable TABLE (
        Column1 int, Column2 int, Column3 int, Column4 int, Column5 int,
        Column6 int, Column7 int, Column8 int, Column9 int, Column10 int
    )

    INSERT INTO @TempTable (Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9, Column10)
    SELECT XmlTable.Data.value('(./Column1)[1]','int'), XmlTable.Data.value('(./Column2)[1]','int'), 
    XmlTable.Data.value('(./Column3)[1]','int'), XmlTable.Data.value('(./Column4)[1]','int'),
    XmlTable.Data.value('(./Column5)[1]','int'), XmlTable.Data.value('(./Column6)[1]','int'),
    XmlTable.Data.value('(./Column7)[1]','int'), XmlTable.Data.value('(./Column8)[1]','int'),
    XmlTable.Data.value('(./Column9)[1]','int'), XmlTable.Data.value('(./Column10)[1]','int')
    FROM @TableData.nodes('//DataTable/RowData') AS XmlTable(Data)

    SELECT * FROM @TempTable
END
GO

4 - The procedure accepts the XML variable of @TableData and inserts it into a newly created table variable called @TempTable.

The final step is to now create your database call with the proper XML parameter. Call the SP as you would normally, just use this as the parameter.

cmd.Parameters.Add("@TableData", SqlDbType.Xml).Value = ConvertToXMLDataString(table);

There you have it. You should be able to adjust accordingly to handle your data. I typically hate passing DataTables around, would much rather pass an Object, or a List around, but in this situation, you probably have your data already in the DataTable.

If this is a once and done, or a not often type of thing, the performance hit you take in exchange for the convenience of using XML is minimal. If this is something that happens often by many users, use the more efficient approach.