Transferring Data Using SqlBulkCopy and Visual Basic

Monday Apr 17th 2017 by Hannes DuPreez
Share:

Learn to use the SqlBulkCopy command within Visual Basic.

In the past, I have written quite a few articles on how to work with SQL Server and Visual Basic. ADO.NET has some built-in features that can make SQL Server work even better with Visual Basic. A prime example would be the SqlBulkCopy command, which you will learn about today. ADO.NET has made our lives so easy!

ADO.NET

ADO.NET provides a rich set of components for creating distributed and data-sharing applications. ADO.NET provides access to relational, XML, and application data and data sources such as SQL Server data sources exposed through OLE DB and ODBC.

ADO.NET separates data access from data manipulation into different components that can be used separately at the same time. ADO.NET includes data providers for connecting to a database, executing commands, and retrieving results. These results can either be processed directly or placed in a DataSet object that gets exposed to the user.

SqlBulkCopy

SqlBulkCopy lets you bulk load a SQL Server table with data from another source. This means that it copies (in bulk) data from another source into an SQL database table. When dealing with large amounts of data, there are always a few options available to insert data quickly. You could write a Stored procedure that encompasses the INSERT logic, you could try to do it manually, or you could use the SqlBulkCopy .NET Class.

The SqlBulkCopy class inserts the data faster because it doesn't have to jump back and forth with the Insert statement requests that keep being sent. Because of less cluttery communication with the SQL Server, SqlBulkCopy helps in entering vast amounts of data quickly.

The SqlBulkCopy class is similar to SQL Server's bcp utility, except for the fact that it is built-in to the ADO.NET language instead of being a separate tool.

Our Project

In the project that you will create next, you will make use of the SqlBulkCopy method in two different ways: One to simply copy a large volume of information to a Table, and another to do the same but do it by using a Transaction. Lastly, you will use the SQL INSERT statement to insert information.

Start a new Visual Basic Windows Forms project and design your form to resemble Figure 1.

Our Design
Figure 1: Our Design

Code

Add the following Imports statement above your Class declaration to import all the necessary SQL capabilities from the SQLClient namespace:

Imports System.Data.SqlClient

Create the Source and Destination connection strings:

   Private strConSrc As New SqlConnection("Data Source=.\sqlexpress; _
      Initial Catalog=TestDatabase;Integrated Security=True")

   Private strConDest As New SqlConnection("Data Source=.\sqlexpress; _
      Initial Catalog=TestDatabase;Integrated Security=True")

Add the following code:

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

      BulkCopy()

   End Sub

   Private Sub BulkCopy()

      Dim strComm As New SqlCommand("SELECT * _
         FROM TableToCopyFrom", strConSrc)

      strConSrc.Open()
      Dim drCopy As SqlDataReader = strComm.ExecuteReader

      Dim bcCopy As New SqlBulkCopy(strConDest)

      strConDest.Open()
      bcCopy.DestinationTableName = "TableToCopyTo"
      bcCopy.WriteToServer(drCopy)

      drCopy.Close()

      strConSrc.Close()
      strConDest.Close()

   End Sub.

The BulkCopy Sub creates a new SQLCommand object that obtains all the records in the desired table to copy from via an SQL query. It opens the connection and creates a dataReader object to read the information obtained. An SqlBulkCopy gets created and the Destination Table name gets specified. The WriteToServer method of the SqlBulkCopy object copies the information from the DataReader into the destination table. Afterwards, all the objects get closed.

Add the next code:

   Private Sub BulkCopyTrans()

      Dim strComm As New SqlCommand("SELECT * _
         FROM TableToCopyFrom", strConSrc)

      strConSrc.Open()
      Dim drCopy As SqlDataReader = strComm.ExecuteReader

      Dim bcCopyTrans As New SqlBulkCopy(strConSrc _
         .ConnectionString, _
         SqlBulkCopyOptions.UseInternalTransaction)
      bcCopyTrans.BatchSize = 50


      bcCopyTrans.DestinationTableName = "TableToCopyTo"

      Try

         bcCopyTrans.WriteToServer(drCopy)

      Catch ex As Exception

         MessageBox.Show(ex.Message)

      Finally

         bcCopyTrans.Close()
         drCopy.Close()
         strConSrc.Close()

      End Try

   End Sub

   Private Sub btnCopyTrans_Click(sender As Object, _
         e As EventArgs) Handles btnCopyTrans.Click

      BulkCopyTrans()

   End Sub

The BulkCopyTrans Sub is very similar to the BulkCopy Sub. The difference is that BulkCopyTrans creates an SqlBulkCopy object with the ability to use a Transaction whilst copying. If you do not know what an SQL Transaction is, this article will be helpful. You could have a look on MSDN for a better understanding of the SqlBulkCopyOptions enumeration.

Add the following code:

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

      Dim strInsert As String = "BULK INSERT TableToCopyTo " & _
         "FROM 'C:\Datasources\DataToCopyFrom.txt'"

      Dim strComm As New SqlCommand(strInsert, strConSrc)

      Try

         strConSrc.Open()
         strComm.ExecuteNonQuery()

      Catch ex As Exception

         MessageBox.Show(ex.Message)

      Finally

         strConSrc.Close()

      End Try

   End Sub

This code in the btnInsert_Click event inserts the data into the desired table through the use of the conventional INSERT FROM SQL statement.

More Reading

Conclusion

Having a decent knowledge of what Visual Basic can do with SQL is very important for any new developer. Knowing how to make use of Transactions quickly can enable you to work with SQL queries more productively in any work environment. Go ahead and see if you can explore more hidden gems like these in ADO.NET.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved