Lab Bulk Copying

In this lab you will bulk copy data from one table to another. Creating Tables to Copy Data into

To demonstrate how to perform a bulk copy operation, you need tables to copy data into. A quick way to create the tables is to use Server Explorer and the Visual Database Tools to do some cutting and pasting! Use the following steps to create CustomerHis-tory and OrderHistory tables, which you will use to bulk copy the Customer and Order table data into.

1. In Server Explorer, expand the Tables node for the Northwind database.

2. Right-click the Customers table and select Open Table Definition.

3. Select the first row by clicking the box with the key icon.

4. Press Ctrl+A to select all the rows.

5. Press Ctrl+C to copy them to the clipboard.

6. Right-click the Tables node in Server Explorer and select Add New Table.

7. Select the empty row (not a cell but the entire row) and press Ctrl+V to paste the table definition into the row.

8. Select only the CustomerID row. Right-click the CustomerID row and select Set Primary Key.

9. Save the table and name it CustomerHistory.

10. Repeat these steps with the Orders table, set the OrderID as the primary key, and save the table with the name OrderHistory.

► Exercise 1: Perform Bulk Copy Operations to Copy Data to SQL Server

In this first bulk copy exercise, you will load all the records from the Customers table into a DataReader and then copy them into the CustomerHistory table using the Sql-BulkCopy object.

1. Create a new Windows application and name it BulkCopySample.

2. Add a button to the form and set the following properties:

□ Name = CopyCustomersButton

3. Add a second button to the form and set the following properties:

□ Name = CopyOrdersButton

4. Double-click the Copy Customers button to create an event handler.

5. Add references to the System.Data and System.Data.SqlClient namespaces.

6. Add code to create two connections on the form, a SourceConnection and a DestinationConnection. For this example, you can use two connections to the same Northwind database.

At this point, your form code should look like the following (substitute a valid connection string for the SourceConnection and DestinationConnection objects):

Imports System.Data Imports System.Data.SqlClient

Public Class Form1

Private SourceConnection As New SqlConnection _

("Data Source=<ServerName>;Initial Catalog=Northwind;Integrated Security=True") Private DestinationConnection As New SqlConnection _

("Data Source=<ServerName>;Initial Catalog=Northwind;Integrated Security=True") Private Sub CopyCustomersButton_Click(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles CopyCustomersButton.Click End Sub End Class

using System;

usi ng System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace BulkCopySampleCS {

public partial class Form1 : Form {

InitializeComponent() ;

private SqlConnection SourceConnection = new SqlConnection _

("Data Source=.\\sqlexpress;Initial Catalog=Northwind;Integrated Security=True");

private SqlConnection DestinationConnection = new SqlConnection _ ("Data Source=.\\sqlexpress;Initial Catalog=Northwind;Integrated Security=True");

private void CopyCustomersButton_Click(object sender, EventArgs e) {

7. Add code to bulk copy the data from the Customers table into the CustomerHis-tory table. Add the following code below the CopyCustomerButton_Click event handler:

Private Sub BulkCopyCustomers()

Dim GetCustomersCommand As New SqlCommand("SELECT * FROM Customers", _ SourceConnection)

SourceConnection.Open()

Dim reader As SqlDataReader = GetCustomersCommand.ExecuteReader Dim BulkCopier As New SqlBulkCopy(DestinationConnection) DestinationConnection.Open()

BulkCopier.DestinationTableName = "CustomerHistory" BulkCopier.WriteToServer(reader)

reader.C1ose() SourceConnection.C1ose() DestinationConnection.C1ose() End Sub

Iprivate void Bu1kCopyCustomers() {

SqlCommand GetCustomersCommand = new SqlCommand _ ("SELECT * FROM Customers", SourceConnection);

SourceConnection.Open();

SqlDataReader reader = GetCustomersCommand.ExecuteReader(); SqlBulkCopy BulkCopier = new SqlBulkCopy(DestinationConnection); DestinationConnection.Open();

BulkCopier.DestinationTableName = "CustomerHistory"; BulkCopier.WriteToServer(reader);

reader.C1ose();

SourceConnection.C1ose();

DestinationConnection.C1ose();

8. Add a line to the CopyCustomerButton_Click event handler to call the BulkCopy-Customers method.

9. Add code to bulk copy the data from the Orders table into the OrderHistory table. Add the following code below the BulkCopyCustomers method:

Private Sub Bu1kCopyOrders()

Dim GetOrdersCommand As New Sq1Command("SELECT * FROM Orders", SourceConnection)

SourceConnection.Open()

Dim reader As SqlDataReader = GetOrdersCommand.ExecuteReader Dim BulkCopier As New SqlBulkCopy(DestinationConnection) DestinationConnection.Open()

BulkCopier.DestinationTableName = "OrderHistory" BulkCopier.WriteToServer(reader)

reader.C1ose()

SourceConnection .Close() DestinationConnection.Close() End Sub

private void BulkCopyOrders() {

SqlCommand GetOrdersCommand = new SqlCommand _ ("SELECT * FROM Orders", SourceConnection);

SourceConnection.Open();

SqlDataReader reader = GetOrdersCommand.ExecuteReader(); SqlBulkCopy BulkCopier = new SqlBulkCopy(DestinationConnection); DestinationConnection.Open();

BulkCopier.DestinationTableName = "OrderHistory"; BulkCopier.WriteToServer(reader);

reader.Close(); SourceConnection .Close(); DestinationConnection.Close();

10. Double-click the Copy Orders button and add a line to the CopyOrdersButton_Click event handler to call the BulkCopyOrders method.

11. Run the application.

12. Click the Copy Customers button.

13. Click the Copy Orders button.

14. Close the form and navigate to Server Explorer.

15. Right-click the CustomerHistory and OrderHistory tables and select Show Table Data.

Verify that the data from the Customers and Orders tables were successfully copied into the CustomerHistory and OrderHistory tables.

IMPORTANT Save the application!

The next section builds on this example, so do not discard the application.

Add Functionality to Demonstrate Executing the SQL BULK INSERT Statement

In addition to copying a large amount of data with the SqlBulkCopy object, you can also configure Command objects to take advantage of SQL Server features that perform efficient bulk copying. SQL Server provides a BULK INSERT statement for copying data from data files into SQL tables. Create data files with the .bcp utility provided by SQL Server. Although creating data and format files is beyond the scope of this book, they are included on the CD for this example.

Copy the Customers.fmt and NorthwindCustomers.txt files from the CD to the C:\DataSources directory. (You can actually copy these anywhere on your hard drive as long as you point to the correct path in the code example.)

NOTE Data file location

When executing the BulkInsertStatement in the example below, keep in mind the path being passed to the statement is relative to the database server so be sure the file exists at the indicated path on the server before executing the command.

The NorthwindCustomers.txt file contains all 91 records from the Customers table and the SQL BULK INSERT statement will populate the CustomerHistory table created in the previous section.

1. Delete all the records from the CustomerHistory table so that it is empty.

2. Add a button to the form (below the Copy Orders button) and set the following properties:

□ Name = ExecuteBulkInsertButton

3. Double-click the BULK INSERT button and add the following code into the button click event handler (modify the path if necessary):

Dim BulkInsertStatement As String = "BULK INSERT CustomerHistory " & _ "FROM 'C:\Datasources\NorthwindCustomers. txt'"

Dim BulkInsertCommand As New SqlCommand(BulkInsertStatement, SourceConnection)

SourceConnection.Open() BulkInsertCommand.ExecuteNonQueryO Catch ex As Exception

MessageBox.Show(ex.Message) Finally

SourceConnection.Close() End Try

string BulkInsertStatement = "BULK INSERT CustomerHistory " +

"FROM 'C:\\Datasources\\NorthwindCustomers.txt'"; SqlCommand BulkInsertCommand = new SqlCommand(BulkInsertStatement, SourceConnection);

SourceConnection.Open(); BulkInsertCommand.ExecuteNonQuery();

catch (Exception ex) {

MessageBox.Show(ex.Message);

finally {

SourceConnection .Close();

4. Run the application.

5. Click the BULK INSERT button.

6. Inspect the CustomerHistory table's data and verify that the data was successfully inserted. You may need to refresh the table if it is already opened in the document window.

► Exercise 2: Perform Bulk Copy Operations in a Transaction

In this next bulk copy exercise, you will perform basically the same bulk copy action as you did in the last exercise, using only the Customers data, but this time, you will wrap the copy operation in a transaction. You will set the batch size to copy 50 records at a time, causing two separate sets of rows to copy because the Customers table contains 91 records.

1. Create a new Windows application and name it BulkCopyTransaction.

2. Add a button to the form and set the following properties:

□ Name = PrepareTableButton

3. Add a second button to the form and set the following properties:

□ Name = BulkCopyButton

4. Double-click the Execute Bulk Copy button to create an event handler.

5. Add references to the System.Data and System.Data.SqlClient namespaces.

6. This example needs only one connection, so add a SourceConnection to the form. When using an internal transaction, the SqlBulkCopy object creates the destination connection when it is instantiated.

At this point, your form code should look like the following (substitute a valid connection string for the SourceConnection object):

Imports System.Data Imports System.Data.SqlClient

Public Class Form1

Private SourceConnection As New SqlConnection _

("Data Source=.\sqlexpress;Initial Catalog=Northwind;Integrated Security=True")

Private Sub BulkCopyButton _Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles BulkCopyButton.Click

End Sub End Class

ng System;

ng System.Collections.Generic; ng System.ComponentModel; ng System.Data; ng System.Drawing; ng System.Text; ng System.Windows.Forms;

usi usi usi usi usi usi usi using System.Data.SqlClient;

namespace BulkCopyTransactionCS {

public partial class Form1 : Form {

Initiali zeComponent() ;

private SqlConnection SourceConnection = new SqlConnection _

("Data Source=.\\SqlExpress;Initial Catalog=Northwind;Integrated Security=True");

private void BulkCopyButton_Click(object sender, EventArgs e) {

To verify that the transaction performs as expected, you actually want part of the bulk copy operation to fail. To accomplish this, leave a single record in the CustomerHistory table to force a primary key violation and cause the transaction to roll back the active batch and without updating any records. To leave this "spoiler" record, you'll create a PrepareTable method that deletes all records in the CustomerHistory table except for the record for White Clover Markets (WHITC).

7. Double-click the Prepare Table button and add the following code to the event handler:

Dim GetCustomersCommand As New SqlCommand _

("DELETE FROM CustomerHistory WHERE CustomerID <> 'WHITC'", SourceConnection) SourceConnection.Open() GetCustomersCommand.ExecuteNonQuery() SourceConnection.Close()

SqlCommand GetCustomersCommand = new SqlCommand _

("DELETE FROM CustomerHistory WHERE CustomerID <> 'WHITC'", SourceConnection); SourceConnection.Open(); GetCustomersCommand.ExecuteNonQuery(); SourceConnection.Close();

8. Add the following code to the form:

These methods are the code that performs the actual Bulk Copy operation using the SqlBulkCopy object. The source and destination connections are used to transfer the data, 50 records at a time (BatchSize property). The DestinationTableName is where the data is copied to.

Private Sub BulkCopyCustomers()

Dim GetCustomersCommand As New SqlCommand _

("SELECT * FROM Customers", SourceConnection)

SourceConnection.Open()

Dim reader As SqlDataReader = GetCustomersCommand.ExecuteReader

Dim BulkCopier As New SqlBulkCopy _

(SourceConnection.ConnectionString, SqlBulkCopyOptions.UseInternalTransaction) BulkCopier.BatchSize = 50

BulkCopier.DestinationTableName = "CustomerHistory" Try

BulkCopier.WriteToServer(reader) Catch ex As Exception

MessageBox.Show(ex.Message) Finally

BulkCopier.Close() reader.Close() SourceConnection.Close() End Try End Sub

private void BulkCopyCustomers() {

SqlCommand GetCustomersCommand = new SqlCommand _ ("SELECT * FROM Customers", SourceConnection);

SourceConnection.Open();

SqlDataReader reader = GetCustomersCommand.ExecuteReader();

SqlBulkCopy BulkCopier = new SqlBulkCopy _ (SourceConnection.ConnectionString, SqlBulkCopyOptions.UseInternalTransaction); BulkCopier.BatchSize = 50;

BulkCopier.DestinationTableName = "CustomerHistory";

BulkCopier.WriteToServer(reader);

catch (Exception ex) {

MessageBox.Show(ex.Message);

finally {

BulkCopier.Close(); reader.Close(); SourceConnection.Close();

9. Add the BulkCopyCustomers method call into the BulkCopyButton.Click event handler.

10. Run the application and click the Prepare Table button.

11. With the application running, navigate to Server Explorer, right-click the Cus-tomerHistory table, and select Show Table Data.

12. If the CustomerHistory table was filled with data from the last exercise, you should now see only the record for White Clover Markets. Select this record and delete it! (If you didn't complete the last exercise and your table is already empty, that is fine.)

13. Navigate back to the running application and click the Execute Bulk Copy button. The operation should complete successfully and copy the entire Customers table into the CustomerHistory table.

14. Go back to Server Explorer and look at the data in the CustomerHistory table; it should contain all 91 records.

15. Go back to the running form and, once again, click the Prepare Table button.

16. Inspect the table data; now there should be only the White Clover Markets record.

17. Go back to the running form and click the Execute Bulk Copy button.

This time, you should get a message box to appear, indicating a primary key violation. This is due to the attempted insertion of the existing White Clover Markets record.

18. Click OK in the message box and inspect the table data again.

What you see are 51 records—the first bulk copy batch of 50 (the batch size you set in code) and the original WHITC record. Notice that the records between the fiftieth and WHITC (which is actually the eighty-ninth record in the table) did not get inserted; the transaction rolled those inserts back because they were all part of the same batch that was part of the internal transaction.

0 0

Post a comment