Introduction
One, often overlooked feature of ADO.NET with SQL Server, is its capability to execute multiple SQL statements using a single SqlCommand.
Very often programs execute statements separately and/or call a Stored Procedure which executes a bigger bunch of statements. Of course using a Stored Procedure
is a preferred way but there are situations when it's beneficial to execute more than one statement with a single call. This can be done using a batch,
which basically means a set of SQL or T-SQL statements together.
The setup
To test the functionality, let's have a small table.
CREATE TABLE MultiStatementTest (
id int not null identity(1,1),
somevalue int not null
);
And populate it with a few rows.
DECLARE @counter int = 1
BEGIN
WHILE (@counter <= 5) BEGIN
INSERT INTO MultiStatementTest (somevalue) VALUES (RAND() * 1000);
SET @counter = @counter + 1;
END;
END;
Now the data looks something like:
SELECT * FROM MultiStatementTest;
id somevalue
--- ---------
1 854
2 73
3 732
4 546
5 267
The test program
The test program is simple to use. Just define the correct connection string to the database where you created the test table and you're ready to run the tests.
Executing multiple SQL statements
The first variation uses SqlCommand.ExecuteNonQuery to execute two separate SQL statements against the test table. The first one updates the field
somevalue by one and the second by two. The method looks like:
public static bool ExecuteMultipleUpdates(string connectionString, bool generateError = false) {
System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection();
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
int rowsAffected;
connection.ConnectionString = connectionString;
command.CommandText = @"
UPDATE MultiStatementTest SET somevalue = somevalue + 1;
UPDATE MultiStatementTest SET" + (generateError ? "WONTWORK" : "") +
" somevalue = somevalue + 2;";
command.CommandType = System.Data.CommandType.Text;
command.Connection = connection;
try {
connection.Open();
rowsAffected = command.ExecuteNonQuery();
} catch (System.Exception exception) {
System.Windows.MessageBox.Show(exception.Message, "Error occurred");
return false;
} finally {
command.Dispose();
connection.Dispose();
}
System.Windows.MessageBox.Show(string.Format("{0} rows updated",
rowsAffected, "Operation succesful"));
return true;
}
So the CommandText property contains all the statements that are going to be executed in this batch. The statements are separated by a semicolon.
After the batch has been executed, the rows have been updated twice so the contents of the table look something like:
id somevalue
--- ---------
1 857
2 76
3 735
4 549
5 270
One important thing to notice is that the amount of affected rows returned by ExecuteNonQuery is 10. There were five rows in the table and each one of them
got updated twice so the total amount of updates is 10. So even with batches, it's possible to check that the correct amount of rows get updated regardless
of which statement makes the update.
Executing two SELECT statements using a data reader
The next test is to execute two different SELECT statements and read the results using a SqlDataReader class. The method is:
public static bool ExecuteReader(string connectionString, bool generateError = false) {
System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection();
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlDataReader dataReader;
System.Text.StringBuilder stringBuilder;
bool loopResult = true;
connection.ConnectionString = connectionString;
command = new System.Data.SqlClient.SqlCommand();
command.CommandText = @"
SELECT somevalue FROM MultiStatementTest WHERE somevalue%2 = 1;
SELECT somevalue FROM MultiStatementTest " + (generateError ? "WONTWORK" : "WHERE") +
" somevalue%2 = 0;";
command.CommandType = System.Data.CommandType.Text;
command.Connection = connection;
try {
connection.Open();
dataReader = command.ExecuteReader();
while (loopResult) {
stringBuilder = new System.Text.StringBuilder();
while (dataReader.Read()) {
stringBuilder.AppendLine(dataReader.GetInt32(0).ToString());
}
System.Windows.MessageBox.Show(stringBuilder.ToString(), "Data from the result set");
loopResult = dataReader.NextResult();
}
} catch (System.Exception exception) {
System.Windows.MessageBox.Show(exception.Message, "Error occurred");
return false;
} finally {
command.Dispose();
connection.Dispose();
}
return true;
}
The idea in the batch is the same, two statements separated by a semicolon. In this example, the rows are divided into two result sets depending if the number is odd or even.
When the ExecuteReader is called, the first result set is automatically usable. The method loops through the rows and shows the results:
857
735
549
In order to get the next results, the reader has to be instructed to advance to the next result set using the NextResult method. After this, the second set
of values can again be looped through. Results for the second set:
76
270
Using SqlDataAdapter for multiple SELECT statements
Often using a SqlDataReader is quite clumsy if the results are to be stored in a DataSet. For the next test, let's use a SqlDataAdapter
to fill a data set. The code looks like:
public static bool ExecuteMultipleSelects(string connectionString, bool generateError = false) {
System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection();
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter();
System.Data.DataSet dataset = new System.Data.DataSet();
connection.ConnectionString = connectionString;
command = new System.Data.SqlClient.SqlCommand();
command.CommandText = @"
SELECT * FROM MultiStatementTest WHERE somevalue%2 = 1;
SELECT " + (generateError ? "WONTWORK" : "*") +
" FROM MultiStatementTest WHERE somevalue%2 = 0;";
command.CommandType = System.Data.CommandType.Text;
command.Connection = connection;
try {
connection.Open();
adapter.SelectCommand = command;
adapter.Fill(dataset);
} catch (System.Exception exception) {
System.Windows.MessageBox.Show(exception.Message, "Error occurred");
return false;
} finally {
command.Dispose();
connection.Dispose();
}
System.Windows.MessageBox.Show(string.Format(
"Dataset contains {0} tables, {1} rows in table 1 and {2} rows in table 2",
dataset.Tables.Count,
dataset.Tables[0].Rows.Count,
dataset.Tables[1].Rows.Count,
"Operation succesful"));
return true;
}
Now fetching data this way is really easy. The code just calls the Fill method of the adapter, passing the DataSet as an argument.
The adapter automatically creates two separate DataTable objects in the data set and populates them. In my test scenario, the first table contains three rows
and the second two rows.
Since in this example the tables were created on-the-fly, they are automatically named Table1 and Table2 so if names
are used to reference the tables, changing names to something more descriptive is sensible.
Executing an anonymous T-SQL block
While Stored Procedures are excellent, sometimes T-SQL code may be, for example, very dynamic in nature. In this kind of situation, it may be hard to create the Stored Procedure.
Batches can be used to execute a bunch of T-SQL statements also. In this approach, there is no named object in the database but the batch is executed like it would have
been executed, for example, from SQL Server Management Studio.
The test code looks like:
public static bool ExecuteAnonymousTSql(string connectionString, bool generateError = false) {
System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection();
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();
int rowsAffected;
connection.ConnectionString = connectionString;
command.CommandText = @"
DECLARE @counter int = 1
BEGIN
WHILE (@counter <= 5) BEGIN
INSERT INTO MultiStatementTest (somevalue) VALUES (RAND() * 100000);
SET @counter = @counter + 1;
" + (generateError ? "WONTWORK" : "") + @"
END;
END;";
command.CommandType = System.Data.CommandType.Text;
command.Connection = connection;
try {
connection.Open();
rowsAffected = command.ExecuteNonQuery();
} catch (System.Exception exception) {
System.Windows.MessageBox.Show(exception.Message, "Error occurred");
return false;
} finally {
command.Dispose();
connection.Dispose();
}
System.Windows.MessageBox.Show(string.Format("{0} rows inserted",
rowsAffected,
"Operation succesful"));
return true;
}
Now, in this example, the same piece of script is used that was used in the beginning to create a few test rows. As you can see, variable declarations, loops, etc.,
are perfectly valid statements to include in the batch.
When this is run, five more rows are added to the table. Also note that since NOCOUNT is off (by default), the ExecuteNonQuery method
returns the correct amount of rows inserted in the batch. If NOCOUNT is set on, the number of rows affected would be -1.
What about error handling?
What if an error occurs when executing a single statement inside a batch? I've used some syntactical errors to test this. The batch is parsed as a whole so even
if later statements contain a syntactical error, the batch won't have any effect. For example, if an erroneous
UPDATE statement is included in the batch, the state of the table won't change.
The situation is different if the error isn't syntactical but occurs during execution. Consider for example foreign key errors which are detected when incorrect values occur.
In this case, the previous statements may have already changed the database state (depending on the statements) so using proper transactions is advisable, as always.
Conclusion
While batches won't (and shouldn't) replace good old Stored Procedures etc., they are useful when used properly. They can be used to create, for example, very dynamic operations
without having to make several round trips as long as no client side logic is needed between calls.
History
- December 27, 2011: Article created.
