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