Tutorial :Insert C# table data into a SQL Server table



Question:

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.


Solution:1

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.


Solution:2

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:


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »