Visual Basic 1002005 [A Developers Notebook] [Electronic resources] نسخه متنی

5.8. Bulk-Copy Rows from One Table to Another

Most SQL Server gurus are familiar with
the BCP command-line utility, which allows
you to move vast amounts of information from one SQL Server database
to another. BCP comes in handy any time you need to load a large
number of records at once, but it's particularly
useful when you need to transfer data between servers. In .NET 2.0,
the SqlClient namespace includes a new
SqlBulkCopy class that allows you to perform a
bulk-copy operation programmatically.

Note: The new SqlBulkCopy class gives you the most efficient way
to copy large amounts of data between tables or databases.

5.8.1. How do I do that?

The key ingredient in a bulk-copy operation is the new
SqlBulkCopy class. It performs all of its work
when you call the WriteToServer() method, which can be used in two ways:

You can submit your data as a DataTable or an
array of DataRow objects. This makes sense if you
want to insert a batch of records from a file you created earlier. It
also works well if you're creating a server-side
component (like a web service) that receives a disconnected
DataSet with the records that need to be loaded
into a table.

You can submit your data as an open DataReader
that draws records from another SqlConnection.
This approach is ideal if you want to transfer records from one
database server to another.

Before you call WriteToServer( ), you need to
create the connections and commands you need and set up
mapping between the destination and source
table. If your source and destination tables match exactly, no
mapping is required. However, if the table names differ, you need to
set the SqlBulkCopy.DestinationTableName
property to the name of the target table. Additionally, if the column
names don't match or if there are fewer columns in
the target table than there are in the source data, you also need to
configure column mapping. To set column mapping, you add one mapping
object for each column to the SqlBulkCopy.ColumnMappings
collection. Each mapping object specifies the name of the source
column and the name of the corresponding target column.

Figure 5-1. Creating a CustomersShort table

To try this out, create a new SQL Server database named
NorthwindCopy and a table named
CustomersShort. The
CustomersShort table is designed to offer a
subset of the information in the Customers
table. You can create it by using a tool like SQL Server Enterprise
Manager (see the column settings in Figure 5-1), or
you can use the script included with the downloadable content for
this chapter to create it automatically (look for the file
GenerateNorthwindCopy.sql).

Once you've created
CustomersShort, you have a perfect table for
testing a SQL Server bulk-copy operation. All you need to do is
create two connections, define the mapping, and start the process.
Example 5-8 has the code you need.

Example 5-8. Using SQLBulkCopy

Imports System.Data.SqlClient
Module Module1
Private ConnectSource As String = _
"Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"
Private ConnectTarget As String = _
"Data Source=localhost;Initial Catalog=NorthwindCopy;" &_
Public Sub Main( )
' Create the source and target connections.
Dim conSource As New SqlConnection(ConnectSource)
Dim conTarget As New SqlConnection(ConnectTarget)
' Create a command for counting the number of rows in a table.
Dim cmdCount As New SqlCommand("SELECT COUNT(*) FROM CustomersShort", _
conTarget)
' Initialize the SqlBulkCopy class with mapping information.
Dim BCP As New SqlClient.SqlBulkCopy(conTarget)
BCP.DestinationTableName = "CustomersShort"
BCP.ColumnMappings.Add("CustomerID", "ID")
BCP.ColumnMappings.Add("CompanyName", "Company")
BCP.ColumnMappings.Add("ContactName", "Contact")
' Count the rows in CustomersShort.
conTarget.Open( )
Dim Rows As Integer = CInt(cmdCount.ExecuteScalar( ))
Console.WriteLine("CustomersShort has " & Rows & " rows.")
Console.WriteLine("Starting bulk copy...")
' Retrieve the rows you want to transfer.
conSource.Open( )
Dim cmd As New SqlCommand( _
"SELECT CustomerID,CompanyName,ContactName FROM Customers", conSource)
Dim reader As SqlDataReader = cmd.ExecuteReader( )
' Write the data to the destination table.
BCP.WriteToServer(reader)
' Clean up.
BCP.Close( )
reader.Close( )
conSource.Close( )
' Count the rows in CustomersShort again.
conSource.Open( )
Rows = CInt(cmdCount.ExecuteScalar( ))
Console.WriteLine("Finished bulk copy.")
Console.WriteLine("CustomersShort has " & Rows & " rows.")
conTarget.Close( )
Console.ReadLine( )
End Sub
End Module

When you run the code, you'll see output like this,
indicating that the bulk-copy operation completed successfully:

CustomersShort has 0 rows.
Starting bulk copy...
Finished bulk copy.
CustomersShort has 91 rows.

5.8.2. What about...

...other SqlBulkCopy properties?
SqlBulkCopy provides two useful properties:
BulkCopyTimeout (which
allows you to set how long you'll wait for an
unresponsive server) and BatchSize
(which allows you to set how many operations are batched together, as
described in the lab "Batch DataAdapter Commands for
Better Performance"). Errors are handled in the same
way as when you directly execute a SqlCommand. In
other words, if an error happens on the server side (like a unique
value conflict), the process will be interrupted immediately, and
you'll receive a SqlClient
exception with the full details.

5.8.3. Where can I learn more?

For a complete list of class members, look up the
SqlBulkCopy class in the MSDN help library
reference. Or, for information about the original BCP utility, look
for the index entry "bcp utility"
in the SQL Server Books Online help.