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.
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.
The INSERT INTO statement is used to insert new records in a table.
The UPDATE statement is used to update records in a table.
The DELETE statement is used to delete records in a table.
The magic continues....
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:
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.
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!