CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
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.
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
- Making Use of SQL Variables
- Sub Queries and Sub Tables in SQL and VB
- Doing SQL Transactions with Visual Basic
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.
