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);
		}
	}
}

 

Binary World is a Software Development company located in Atlanta, USA (since 2007). Binary World specialized in Business Intelligence, mobile, cloud computing and .Net Application Development.

Leave a Reply