001.Imports System.Data.SqlClient
002.
003.Module Module1
004. Sub Main()
005. Dim connectionString As String = GetConnectionString()
006.
007.
008. Using connection As SqlConnection = _
009. New SqlConnection(connectionString)
010. connection.Open()
011.
012.
013. Dim commandRowCount As New SqlCommand( _
014. "SELECT COUNT(*) FROM dbo.BulkCopyDemoMatchingColumns;", _
015. connection)
016. Dim countStart As Long = _
017. System.Convert.ToInt32(commandRowCount.ExecuteScalar())
018. Console.WriteLine("Starting row count = {0}", countStart)
019.
020.
021. Dim newProducts As DataTable = MakeTable()
022.
023.
024.
025.
026. Using bulkCopy As SqlBulkCopy = _
027. New SqlBulkCopy(connection)
028. bulkCopy.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns"
029.
030. Try
031.
032. bulkCopy.WriteToServer(newProducts)
033.
034. Catch ex As Exception
035. Console.WriteLine(ex.Message)
036. End Try
037. End Using
038.
039.
040.
041. Dim countEnd As Long = _
042. System.Convert.ToInt32(commandRowCount.ExecuteScalar())
043. Console.WriteLine("Ending row count = {0}", countEnd)
044. Console.WriteLine("{0} rows were added.", countEnd - countStart)
045.
046. Console.WriteLine("Press Enter to finish.")
047. Console.ReadLine()
048. End Using
049. End Sub
050.
051. Private Function MakeTable() As DataTable
052.
053. Dim newProducts As DataTable = _
054. New DataTable("NewProducts")
055.
056.
057. Dim productID As DataColumn = New DataColumn()
058. productID.DataType = System.Type.GetType("System.Int32")
059. productID.ColumnName = "ProductID"
060. productID.AutoIncrement = True
061. newProducts.Columns.Add(productID)
062.
063. Dim productName As DataColumn = New DataColumn()
064. productName.DataType = System.Type.GetType("System.String")
065. productName.ColumnName = "Name"
066. newProducts.Columns.Add(productName)
067.
068. Dim productNumber As DataColumn = New DataColumn()
069. productNumber.DataType = System.Type.GetType("System.String")
070. productNumber.ColumnName = "ProductNumber"
071. newProducts.Columns.Add(productNumber)
072.
073.
074. Dim keys(0) As DataColumn
075. keys(0) = productID
076. newProducts.PrimaryKey = keys
077.
078.
079. Dim row As DataRow
080. row = newProducts.NewRow()
081. row("Name") = "CC-101-WH"
082. row("ProductNumber") = "Cyclocomputer - White"
083. newProducts.Rows.Add(row)
084.
085. row = newProducts.NewRow()
086. row("Name") = "CC-101-BK"
087. row("ProductNumber") = "Cyclocomputer - Black"
088. newProducts.Rows.Add(row)
089.
090. row = newProducts.NewRow()
091. row("Name") = "CC-101-ST"
092. row("ProductNumber") = "Cyclocomputer - Stainless"
093. newProducts.Rows.Add(row)
094. newProducts.AcceptChanges()
095.
096.
097. Return newProducts
098. End Function
099.
100. Private Function GetConnectionString() As String
101.
102.
103. Return "Data Source=(local);" & _
104. "Integrated Security=true;" & _
105. "Initial Catalog=AdventureWorks;"
106. End Function
107.End Module
