Doing SQL Transactions with Visual Basic

CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Introduction

SQL Transactions are magical little creatures. Before you think that you are reading a fantasy novel, I’ll stop; but really, SQL Transactions are quite powerful. With today’s article, I will explain what SQL Transactions are and how to use them properly in your Visual Basic applications.

A Quick Beginner’s Guide to SQL

Structured Query Language is the programming language you use to build databases, extract information from databases, and store information into databases. In an earlier article I spoke about the term database, so if you haven’t read it yet, have a read here. If this is your first time hearing the term SQL, have a look here.

To extract any information from any database, we need to write an SQL query. A query can be explained as a request for information that it then presents ultimately. Now, as I have mentioned, all database information gets stored inside tables. These tables contain all the information that has been stored. We must write a query to get this information and present it to the user.

Queries

The simplest form of a query would look like this:

SELECT * FROM TableName

This will give you all the data stored in that particular table. Remember now, that some tables can have millions of records inside them, so, in this case, this simple query can give you all the millions of records. Usually, this is not really needed. Usually, there is some sort of condition involved when extracting information. A query with a condition will look like the following:

SELECT * FROM TableName WHERE Field = Value

A query similar to the above now has the capability to return only certain information. Let me use a bank as an example. With any bank, they deal with millions of customers. Many of these customers may have the same last names, or even the exact same names. Any customer of the bank may have more than one type of account. Now, taking all of this into consideration, a query to return all the information to a specific person may be troublesome. If conditions did not exist, this would have caused you stand in the banking queue for a very long time while the poor teller sifts through all the information just to find the correct person with the correct information.

We can take conditions further, and write a query like the following:

SELECT * FROM TableName WHERE Field1 = Value1
   AND Field2 > Value2 OR Field3 < Value3

It gets trickier now, as here we deal with more than one field’s value and more than one condition. The above query can be translated into layman’s terms to mean:

Select all the data from the table named TableName where Column1 is equal to Value1 AND Column2 is greater than Value2 OR Column3 is less than Value3.

It can get more complicated…

SELECT * FROM Table1, Table2, Table3
   WHERE Table1.Field1 = Value1
   AND Table2.Field2 > Value2
   OR Table3.Field3 < Value3

This SQL code extracts certain data from three different tables, based on various conditions set.

SQL is not only just about extracting data; it can work with data in various other ways, such as inserting information, editing information, and deleting information. Here is a quick overview of all these.

INSERT INTO

The INSERT INTO statement is used to insert new records in a table.

UPDATE

The UPDATE statement is used to update records in a table.

DELETE

The DELETE statement is used to delete records in a table.

The magic continues….

SQL Transactions

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, all of the data modifications are erased. Here is more information:

https://msdn.microsoft.com/en-us/library/ms174377.aspx

Our Project

Create a New Visual Basic Windows Forms project. You may name it anything you like. There is only one button that you have to add onto the form, so we can jump straight into the code.

Add the following Namespaces:

Imports System.Data
Imports System.Data.SqlClient

These namespaces enable you to communicate with an SQL server database and give us the ability to manipulate any data in any way in an SQL Server database.

Add a modular Variable to hold your connection string:

   Private sqlCon As New SqlConnection("Data Source= _
      .sqlexpress;Initial Catalog=Students; _
      Integrated Security=True")

The above piece of code creates the object that contains all the necessary information to connect to an SQL Server database.

Add the code for your button. Note, my object naming might differ from yours.

   Private Sub PerformTransactionButton_Click(ByVal _
      sender As System.Object, ByVal e As System.EventArgs) _
      Handles PerformTransactionButton.Click
         PerformTransaction()
   End Sub

The button simply calls a procedure named PerformTransaction. You will add this procedure now:

   Private Sub PerformTransaction()

      DeleteRecords()

      ' Create the transaction
      Dim tTransaction As SqlTransaction

      ' Create 2 commands to execute in the transaction
      Dim sqlComm1 As New SqlCommand("INSERT INTO Students" & _
                                     "(Age, Name)" & _
                                     "VALUES (37,'Hannes')", sqlCon)
      Dim sqlCom2 As New SqlCommand("INSERT INTO Courses" & _
                                    "(CourseName, Duration)" & _
                                    "VALUES ('Programming', '1 Year')", _
                                    sqlCon)


      ' Open the connection and begin the transaction
      sqlCon.Open()
      tTransaction = sqlCon.BeginTransaction()


      ' Set the commands to execute within the transaction
      sqlComm1.Transaction = tTransaction
      sqlCom2.Transaction = tTransaction

      ' Execute the commands
      qlComm1.ExecuteNonQuery()
      sqlCom2.ExecuteNonQuery()


      Dim response As DialogResult = MessageBox.Show("Commands have _
         already been executed." & Environment.NewLine & _
         "Proceed with transaction?", "Performing Transaction", _
         MessageBoxButtons.YesNo)

      Select Case response
         Case Windows.Forms.DialogResult.Yes
            tTransaction.Commit()
         Case Windows.Forms.DialogResult.No
            tTransaction.Rollback()
      End Select

      sqlCon.Close()
   End Sub

The first line of the PerformTransaction procedure calls another (I know, it is getting a bit boring…) procedure named DeleteRecords, which I will elaborate on later.

Then, you create a Transaction object that will be used to start and abort the SQL transactions. Two SQL commands are created next. The insert commands will form part of the ultimate transaction.

Lastly, a choice is given to commit the transaction, or to abort it and, in doing so, roll back all the affected information.

Add the DeleteRecords sub procedure:

   Private Sub DeleteRecords()
      ' Clear these records if they exist.
      Dim sqlDelComm As New SqlCommand("DELETE FROM Students _
         WHERE Name = 'Hannes'; DELETE FROM Courses _
         WHERE CourseName = 'Programming'", sqlCon)
      sqlCon.Open()
      sqlDelComm.ExecuteNonQuery()
      sqlCon.Close()
   End Sub

An SQL command is created that deletes information. Afterwards, the SQL command gets executed and the data is deleted.

Conclusion

Today, you have learned how to work SQL transactions in Visual Basic. As you can see, there is not much to it. Until next time, cheers!