Mega Code Archive
Bulk load rows in a DataTable that match a specified state
<![CDATA[
Imports System.Data
Imports System.Data.SqlClient
Module Module1
Sub Main()
Using connection As SqlConnection = New SqlConnection("Data Source=(local);Integrated Security=true;Initial Catalog=AdventureWorks;")
connection.Open()
Dim commandRowCount As New SqlCommand("SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;", connection)
Dim countStart As Long = System.Convert.ToInt32(commandRowCount.ExecuteScalar())
Console.WriteLine("Starting row count = {0}", countStart)
Dim newProducts As DataTable = New DataTable("NewProducts")
Dim productID As DataColumn = New DataColumn()
productID.DataType = System.Type.GetType("System.Int32")
productID.ColumnName = "ProductID"
productID.AutoIncrement = True
newProducts.Columns.Add(productID)
Dim productName As DataColumn = New DataColumn()
productName.DataType = System.Type.GetType("System.String")
productName.ColumnName = "Name"
newProducts.Columns.Add(productName)
Dim productNumber As DataColumn = New DataColumn()
productNumber.DataType = System.Type.GetType("System.String")
productNumber.ColumnName = "ProductNumber"
newProducts.Columns.Add(productNumber)
Dim keys(0) As DataColumn
keys(0) = productID
newProducts.PrimaryKey = keys
Dim row As DataRow
row = newProducts.NewRow()
row("Name") = "A"
row("ProductNumber") = "A1"
newProducts.Rows.Add(row)
row = newProducts.NewRow()
row("Name") = "B"
row("ProductNumber") = "B1"
newProducts.Rows.Add(row)
row = newProducts.NewRow()
row("Name") = "C"
row("ProductNumber") = "C1"
newProducts.Rows.Add(row)
newProducts.AcceptChanges()
row = newProducts.Rows(0)
row.BeginEdit()
row("Name") = "AAA"
row.EndEdit()
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(connection)
bulkCopy.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns"
Try
bulkCopy.WriteToServer(newProducts, DataRowState.Unchanged)
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Using
Dim countEnd As Long = System.Convert.ToInt32(commandRowCount.ExecuteScalar())
Console.WriteLine("Ending row count = {0}", countEnd)
Console.WriteLine("{0} rows were added.", countEnd - countStart)
End Using
End Sub
End Module
]]>