Use SqlCommand to process multiple T-SQL statements at once in Visual Basic .NET
Introduction
In this very concise article, we'll see a little know method of using the SqlCommand class, which is normally employed to execute a single operation against a database. The programming language of the examples below will be Visual Basic .NET, but the concept itself applies to C# as well.
Query a Table
The most common way of using a SqlCommand is by executing a single instruction, to retrieve records, or to insert, update, delete them. In the following snippet of code, i'll show a simple method through which we can open a connection, and select from an hypothetical Products table those record which Status field is equal to zero. Those records will serve, utilizing a DataAdapter, to populate a DataTable for further use.
Dim dt as DataTable
Using conn As New SqlConnection("<YOUR_CONNECTION_STRING>")
conn.Open()
Dim _sqlCommand As New SqlCommand("SELECT ProdId FROM Products WHERE Status = 0", conn)
Dim ta As New SqlDataAdapter(_sqlCommand)
ta.Fill(dt)
End Using
Multiple instructions
But what happens when we need to execute multiple instructions? When a query becomes too complex, or requires more than a single step to be accomplished, what are the options at stake for a developer? Many situations could be handled through a single well-written query, but there are times in which a query must be "unpacked" in sub-elements, maybe desiring to mantain the whole script as readable as possible, or to avoid overloads on our database engine, due to mammoth-sized not-so-well-written queries.
A first, more expensive method, could be to create as many SqlCommands as we need. Suppose we need to generate a temporary table, to fill it with those Products records with Status field being zero, to further update another field to a given value, then joining our current recordset with another table, retrieving the resultant recordset but deleting the temporary table afterwards.
A developer could be tempted to use a first SqlCommand to create the temp table, a second one to insert on it those records which match our selections, a third for updating the records, and so on. While this can be done (and some situations could require it), the result can be a code harder to mantain, and more resource-expending.
A concise solution
A not-so-known use of SqlCommand, is its possibility of executing entire SQL scripts. That means a SQL script which we've successfully tested on, say, Management Studio, can be feed to a SqlCommand for a complete execution. Consider the following pseudo-code:
Dim sqlCmd as String = "CREATE TABLE #mytemp (ProdId INT, ProdCode VARCHAR(25), MyColumn INT) " & Environment.NewLine & _
"INSERT INTO #mytemp (ProdId, ProdCode) SELECT ProdId, ProdCode FROM Products " & Environment.NewLine & _
"UPDATE #mytemp SET MyColumn = 2 " & Environment.NewLine & _
"SELECT * FROM #mytemp " & Environment.NewLine & _
"DROP TABLE #mytemp "
Dim dt as DataTable
Using conn As New SqlConnection("<YOUR_CONNECTION_STRING>")
conn.Open()
Dim _sqlCommand As New SqlCommand(sqlCmd, conn)
Dim ta As New SqlDataAdapter(_sqlCommand)
ta.Fill(dt)
End Using
As you can see, we haven't used SqlCommand as a single-instruction runner. Instead, we've passed to it a small script, which does several operations. When we execute our command, to fill the DataTable, the whole instruction set will be runned. The DataTable will be filled with the last SELECT resultant recordset, but the other instructions will be executed too. So, in running our code, a single instance of SqlCommand will handle the temp table creation, and the subsequent insert/update/select/drop functions.
Conclusion
An approach like this can be very useful to have a neater VB/C# code, but - also importantly - what shown above represent a clear proof of the System.Data.SqlClient namespace potentialities, which makes it on of the most apt and smart choices when it comes to database handling.
Other languages
The present artice is available in the following localizations: