Caution in Font

Printer Code 39 in Font Caution

You will throw an Exception object if you invoke the WriteToServer method before specifying the DestinationTableName property for a SqlBulkCopy class instance.

This section s sample for the SqlBulkCopy class reads a short .csv file named Book1.csv. The file resides in the project folder (the one with the form files) for the WinCh12 project. Our form for this example, Form9, has three Click event procedures for Button1 through Button3 and a Load event procedure for the form. The sample relies on a copy of the Northwind SQL Server database whose database files reside in the project folder. The Load event procedure points the cnn1 SqlConnection object at the Northwind database files in the project folder. See the Connecting to SQL Server Express Database Files section in 11 for commentary on how to accomplish this. The cnn1 variable is declared at the module level for its use with multiple procedures. The Button1_Click procedure creates a database table named FromExcel that has columns matching the columns of the Book1.csv file. The Button2_Click procedure drops the FromExcel database table.

The Button3_Click procedure is the workhorse component of the sample. It performs four tasks, including one that uses a SqlBulkCopy instance to copy data from a client application to an SSE database.

Figure 12-8 shows the Book1.csv file open in Excel. The file has a single worksheet named Book1. The worksheet has three columns for first name, last name, and an identification number. The worksheet has five rows. The SqlBulkCopy class is ideal for cases in which there are many rows, but it is easy enough to use that you may want to consider it for small worksheet files, such as the one in this demonstration. In any event, the syntax for using a SqlBulkCopy instance is the same whether you are dealing with 5 rows or 50,000 rows.

Figure 12-8. You can export a .csv file from Excel for populating a database table via a SqlCopyBulk instance. Although the Book1.csv file can be opened with Excel in a spreadsheet, it is actually a text file with the column values on each row separated by commas. Figure 12-9 shows the same Book1.csv file open as a text file within Visual Basic Express.

Figure 12-9. Although a .csv file can open as a worksheet from within Excel, its underlying format is that of a simple text file with commas separating the values on a row.

The following excerpt from the Button1_Click procedure includes a CREATE TABLE statement for the FromExcel database table. The procedure creates the table in the Northwind database files inside the WinCh12 project, because the cnn1 SqlConnection object points at these files. The procedure generates a message confirming success or a reminder that the attempt to create the table did not succeed. One reason an attempt to generate the table might fail is because it already exists. In this case, you can click Button2 to drop the existing version of the FromExcel database table. Dim cmd1 As New SqlCommand cmd1.CommandText = "CREATE TABLE FromExcel (" & _ "FirstName nvarchar(15), " & _ "LastName nvarchar(20), " & _ "PersonID int Not Null)" cmd1.Connection = cnn1 Try cnn1.Open() cmd1.ExecuteNonQuery() MessageBox.Show("Command succeeded.", "Outcome", _ MessageBoxButtons.OK, MessageBoxIcon.Information) Catch ex As Exception MessageBox.Show(ex.Message) Finally cnn1.Close() End Try

The Button3_Click procedure does nearly all the work for the sample application. It is the only procedure within the sample to actually use a SqlBulkCopy instance. There are four parts to this procedure. This section reviews the code for each part separately. It creates a DataTable for storing the data from the Book1.csv file. It reads and parses the Book.csv file as it copies a worksheet s column values to the DataTable created in the first step. It sends the DataTable contents to the database table by invoking the WriteToServer method for a SqlBulkCopy instance. Finally, it reads the values from the FromExcel database table and displays the result in a message box to confirm the success of the bulk copy operation. The code from the top of the procedure declares a DataTable named FromExcel. This DataTable will accept data from the Book1.csv file before its contents are sent to a database table with the same name by the SqlBulkCopy instance. Three successive Add methods for the Columns collection of the FromExcel DataTable adds three columns that correspond to the FromExcel database table. The SqlString data type is a variable length stream of characters. The RowForExcel DataRow variable helps to populate the FromExcel DataTable from the Book1.csv file in the procedure s second part. Dim FromExcel As New DataTable Dim RowForExcel As DataRow FromExcel.Columns.Add("FirstName", GetType(SqlTypes.SqlString)) FromExcel.Columns.Add("LastName", GetType(SqlTypes.SqlString)) FromExcel.Columns.Add("PersonID", GetType(SqlTypes.SqlInt32))