Transferring Data Using SqlBulkCopy and Visual Basic

WEBINAR: On-demand webcast

How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >

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.



About the Author

Hannes DuPreez

Hannes du Preez has been a Microsoft MVP for Visual Basic from 2008 to 2017. He loves technology and loves Visual Basic. He loves writing articles and proving that Visual Basic is more powerful than what most believe. You are most welcome to reach him at: ojdupreez1978@gmail.com

Related Articles

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • As all sorts of data becomes available for storage, analysis and retrieval - so called 'Big Data' - there are potentially huge benefits, but equally huge challenges...
  • The agile organization needs knowledge to act on, quickly and effectively. Though many organizations are clamouring for "Big Data", not nearly as many know what to do with it...
  • Cloud-based integration solutions can be confusing. Adding to the confusion are the multiple ways IT departments can deliver such integration...

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date