Click here to Skip to main content
15,868,164 members
Articles / Database Development / SQL Server

Executing multiple SQL statements as one against SQL Server

Rate me:
Please Sign up or sign in to vote.
4.91/5 (16 votes)
27 Dec 2011CPOL4 min read 272.4K   1.3K   27   19
This article describes a few techniques for how multiple SQL statements can be executed with one SqlCommand.

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.

SQL
---------------------------------
-- Create the test table
---------------------------------
CREATE TABLE MultiStatementTest (
   id        int not null identity(1,1),
   somevalue int not null
); 

And populate it with a few rows.

SQL
---------------------------------
-- Add 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:

SQL
---------------------------------
-- Select the initial data
---------------------------------
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:

C#
/// <summary>
/// Executes two separate updates against the the connection
/// </summary>
/// <param name="connectionString">Connection string to use</param>
/// <param name="generateError">Should the statement generate an error</param>
/// <returns>True if succesful</returns>
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:

C#
/// <summary>
/// Executes two separate select statements against the the connection using data reader
/// </summary>
/// <param name="connectionString">Connection string to use</param>
/// <param name="generateError">Should the statement generate an error</param>
/// <returns>True if succesful</returns>
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:

C#
/// <summary>
/// Executes two separate select statements against the the connection
/// </summary>
/// <param name="connectionString">Connection string to use</param>
/// <param name="generateError">Should the statement generate an error</param>
/// <returns>True if succesful</returns>
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:

C#
/// <summary>
/// Executes an anonymous T-SQL batch against the the connection
/// </summary>
/// <param name="connectionString">Connection string to use</param>
/// <param name="generateError">Should the statement generate an error</param>
/// <returns>True if succesful</returns>
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
Europe Europe
Biography provided

Comments and Discussions

 
GeneralMy vote of 5 Pin
Thomas Maierhofer (Tom)14-Dec-14 10:25
Thomas Maierhofer (Tom)14-Dec-14 10:25 
GeneralRe: My vote of 5 Pin
Wendelius14-Dec-14 10:39
mentorWendelius14-Dec-14 10:39 
GeneralMy vote of 4 Pin
tienuit2-Sep-14 22:32
tienuit2-Sep-14 22:32 
GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 6:15
professionalKanasz Robert24-Sep-12 6:15 
GeneralRe: My vote of 5 Pin
Wendelius25-Sep-12 8:11
mentorWendelius25-Sep-12 8:11 
GeneralMy vote of 3 Pin
Ryan Giggs28-Aug-12 15:31
Ryan Giggs28-Aug-12 15:31 
GeneralRe: My vote of 3 Pin
Wendelius25-Sep-12 8:12
mentorWendelius25-Sep-12 8:12 
QuestionQuestion Pin
l709823-Apr-12 10:15
l709823-Apr-12 10:15 
AnswerRe: Question Pin
Wendelius17-May-12 8:44
mentorWendelius17-May-12 8:44 
GeneralUsing DataReader for DataSet Pin
Earth163-Jan-12 6:00
Earth163-Jan-12 6:00 
GeneralRe: Using DataReader for DataSet Pin
Wendelius3-Jan-12 10:58
mentorWendelius3-Jan-12 10:58 
GeneralMy vote of 5 Pin
User 48220332-Jan-12 20:20
User 48220332-Jan-12 20:20 
GeneralRe: My vote of 5 Pin
Wendelius3-Jan-12 3:13
mentorWendelius3-Jan-12 3:13 
Questionmy vote of 5 Pin
Luc Pattyn2-Jan-12 15:47
sitebuilderLuc Pattyn2-Jan-12 15:47 
AnswerRe: my vote of 5 Pin
Wendelius2-Jan-12 18:27
mentorWendelius2-Jan-12 18:27 
QuestionThoughts Pin
PIEBALDconsult27-Dec-11 13:08
mvePIEBALDconsult27-Dec-11 13:08 
AnswerRe: Thoughts Pin
Wendelius27-Dec-11 19:22
mentorWendelius27-Dec-11 19:22 
QuestionSqlCommandSet Pin
deathgore27-Dec-11 12:37
deathgore27-Dec-11 12:37 
AnswerRe: SqlCommandSet Pin
Wendelius27-Dec-11 19:16
mentorWendelius27-Dec-11 19:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.