views:

188

answers:

2

Hi,

Is there anyway to insert in bulk a System.Data.DataTable in C# into a SQL Server table using a store procedure and passing this table as parameter?

The table won't have a fixed number of records, Thanks

+3  A: 

Yes there is a way:

        DataTable dataTable = null; // your data needs to be here
        try
        {
            ConnectionStringSettings mConString = ConfigurationManager.ConnectionStrings["SiteSqlServer"];

            // Optional truncating old table
            using (SqlConnection connection = new SqlConnection(mConString.ConnectionString))
            {
                connection.Open();
                // Delete old entries
                SqlCommand truncate = new SqlCommand("TRUNCATE TABLE MYTABLE", connection);
                truncate.ExecuteNonQuery();
            }

            SqlBulkCopy bulkCopy = new SqlBulkCopy(mConString.ConnectionString, SqlBulkCopyOptions.TableLock)
                                          {
                                              DestinationTableName = "dbo.MYTABLE",
                                              BatchSize = 100000,
                                              BulkCopyTimeout = 360
                                          };
            bulkCopy.WriteToServer(dataTable);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex);
        }

Please experiement with the BatchSize - 100000 was good for me - it should not be larger than that - speed migth decrease otherwise. BatchSize does not limit your data - it's just the size of each "packet" that will be sent to the sql server.

SiteSQLServer should be inside your app.config or web.config. You need to change the parameters here if not.

Please change MYTABLE to your table name.

Andreas Rehm
+2  A: 

In SQL Server versions before 2008, the only way to push an entire DataTable to SQL Server was SqlBulkCopy. You have to push all data into (probably temporary) staging table and call the procedure to process the staged data.

In SQL Server 2008, table-valued user-defined types have been introduced. They provide some great new options to work with sets of data.

Have a look at MSDN:

and probably a second at two of my blog posts:

Greets Flo

Florian Reischl
Nice - but slower than bulkcopy...
Andreas Rehm
Sure, slower (however, not too much) but more powerful, since no staging table needed
Florian Reischl
hi Florian,Just trying to run something like this but it doesn't work...USE AdventureWorks;GO/* Create a user-defined table type */CREATE TYPE LocationTableType AS TABLE ( LocationName VARCHAR(50), CostRate INT );GOI have the AdventureWorks db in my SQL Server, shouldn't I be able to create the new type?I just get this error...Msg 156, Level 15, State 1, Line 3Incorrect syntax near the keyword 'AS'.Thanks
Dave
Please try: "PRINT @@VERSION;". Does it return SQL Server 2008 or another version? If 2008, check database properties for compatibility mode version "10"
Florian Reischl
Ok, we are using 2005... so I will have to use the other solution, right? SQLBulkCopy...
Dave
So SqlBulkCopy is the best way to go
Florian Reischl