Bulk Load ADO.net DataTable to SQL Server Table
This code snippet will make your life super easy if you have similar need what I had.
Here is simple problem… If you have DataTable(s) or You have DataSet with Multiple DataTables and You want to Insert all records to some SQL Server Table(s) How would you do that ?
Use below VB.net/C# code snippet
VB.net Code
Sub TestTableLoad() Dim myDataTable As System.Data.DataTable '= LoadData(XXXX) BulkLoadDataSetToSQLDB("Data Source=(local);Initial Catalog=WFX_REPORT;Integrated Security=SSPI;", myDataTable, "your_target_tablename", 10000) End Sub Sub BulkLoadDataSetToSQLDB(connString As String _ , dt As System.Data.DataTable _ , destTableName As String _ , Optional insertBatchSize As Integer = 10000) If String.IsNullOrEmpty(destTableName) Then destTableName = dt.TableName End If Using bulkCopy As System.Data.SqlClient.SqlBulkCopy = New System.Data.SqlClient.SqlBulkCopy(connString) If dt.Rows.Count > 0 Then bulkCopy.DestinationTableName = destTableName 'bulkCopy.BulkCopyTimeout = (timeoutvalue) bulkCopy.BatchSize = insertBatchSize bulkCopy.WriteToServer(dt) End If End Using End Sub
C# Code
public void TestTableLoad() { System.Data.DataTable myDataTable = default(System.Data.DataTable); //= LoadData(XXXX) BulkLoadDataSetToSQLDB("Data Source=(local);Initial Catalog=WFX_REPORT;Integrated Security=SSPI;", myDataTable, "your_target_tablename", 10000); } public void BulkLoadDataSetToSQLDB(string connString, System.Data.DataTable dt, string destTableName, int insertBatchSize = 10000) { if (string.IsNullOrEmpty(destTableName)) { destTableName = dt.TableName; } using (System.Data.SqlClient.SqlBulkCopy bulkCopy = new System.Data.SqlClient.SqlBulkCopy(connString)) { if (dt.Rows.Count > 0) { bulkCopy.DestinationTableName = destTableName; //bulkCopy.BulkCopyTimeout = (timeoutvalue) bulkCopy.BatchSize = insertBatchSize; bulkCopy.WriteToServer(dt); } } }
Leave a Reply
You must be logged in to post a comment.