65.9K
CodeProject 正在变化。 阅读更多。
Home

一次执行多个 SQL 语句到 SQL Server

Wendelius
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.91/5 (15投票s)

2011 年 12 月 27 日

CPOL

4分钟阅读

viewsIcon

278100

downloadIcon

1377

本文介绍了一些技术，说明如何使用单个 SqlCommand 执行多个 SQL 语句。

引言

ADO.NET 在 SQL Server 中一个经常被忽视的功能是它能够使用单个 SqlCommand 执行多个 SQL 语句。很多时候，程序会单独执行语句，或者调用执行大量语句的存储过程。当然，使用存储过程是首选方式，但有时通过一次调用执行多个语句会更有益。这可以通过“批处理”来实现，批处理基本上是将一组 SQL 或 T-SQL 语句放在一起。

设置

为了测试该功能，让我们创建一个小型表。

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

并向其中插入几行数据。

---------------------------------
-- 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;

现在数据看起来大概是这样

---------------------------------
-- Select the initial data
---------------------------------
SELECT * FROM MultiStatementTest;
id      somevalue
---     ---------
1       854
2       73
3       732
4       546
5       267

测试程序

测试程序非常易于使用。只需为创建了测试表的数据库定义正确的连接字符串，即可运行测试。

执行多个 SQL 语句

第一个方法使用 SqlCommand.ExecuteNonQuery 对测试表执行两个单独的 SQL 语句。第一个语句将 `somevalue` 字段加一，第二个语句加二。该方法如下所示：

/// <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;
}

因此，CommandText 属性包含将在该批处理中执行的所有语句。语句之间用分号分隔。

批处理执行完毕后，行已更新两次，因此表的内容看起来大概是这样：

id      somevalue
---     ---------
1       857
2       76
3       735
4       549
5       270

需要注意的一个重要事项是，ExecuteNonQuery 返回的影响行数为 10。表中共有五行，每行都更新了两次，因此总更新次数为 10。因此，即使使用批处理，也可以检查是否正确地更新了行数，无论哪个语句执行了更新。

使用数据读取器执行两个 SELECT 语句

下一个测试是执行两个不同的 SELECT 语句，并使用 SqlDataReader 类读取结果。方法如下：

/// <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;
}

批处理中的思路相同，两个语句用分号分隔。在此示例中，行根据数字是奇数还是偶数被分为两个结果集。当调用 ExecuteReader 时，第一个结果集是自动可用的。该方法遍历行并显示结果：

857
735
549

要获取下一个结果，必须指示读取器使用 NextResult 方法前进到下一个结果集。之后，可以再次遍历第二组值。第二组的值的结果：

76
270

使用 SqlDataAdapter 处理多个 SELECT 语句

如果结果需要存储在 DataSet 中，那么使用 SqlDataReader 通常会很麻烦。在下一个测试中，我们将使用 SqlDataAdapter 来填充数据集。代码如下：

/// <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;
}

现在这样获取数据确实很简单。代码只需调用适配器的 Fill 方法，并将 DataSet 作为参数传递。适配器会自动在数据集中创建两个单独的 DataTable 对象并填充它们。在我的测试场景中，第一个表包含三行，第二个表包含两行。

由于在此示例中表是即时创建的，因此它们被自动命名为 Table1 和 Table2，所以如果使用名称引用表，则更改为更具描述性的名称是明智的。

执行匿名 T-SQL 块

尽管存储过程非常出色，但有时 T-SQL 代码可能具有高度动态性。在这种情况下，创建存储过程可能很困难。批处理也可以用来执行一组 T-SQL 语句。在这种方法中，数据库中没有命名对象，但批处理会像从 SQL Server Management Studio 执行一样被执行。

测试代码如下：

/// <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;
}

现在，在此示例中，使用的是与开始时创建测试行相同的脚本。您可以看到，变量声明、循环等都是可以包含在批处理中的有效语句。

运行此代码后，表中将添加五行。另外请注意，由于 NOCOUNT 处于关闭状态（默认），ExecuteNonQuery 方法将返回批处理中插入的正确行数。如果 NOCOUNT 设置为开启，则受影响的行数将为 -1。

错误处理怎么样？

如果在批处理中执行单个语句时发生错误怎么办？我使用了一些语法错误来测试这一点。批处理被解析为一个整体，因此即使后面的语句包含语法错误，批处理也不会产生任何影响。例如，如果批处理中包含错误的 UPDATE 语句，则表的状态不会改变。

如果错误不是语法错误而是执行过程中发生的错误，情况则不同。例如，考虑外键错误，当出现不正确的值时会检测到。在这种情况下，前面的语句可能已经更改了数据库状态（取决于语句），因此像往常一样，使用适当的事务是明智的。

结论

虽然批处理不能（也不应该）取代旧的存储过程等，但正确使用它们非常有用。它们可用于创建非常动态的操作，例如，而无需进行多次往返，只要在调用之间不需要客户端逻辑。

历史

  • 2011 年 12 月 27 日：创建文章。
© . All rights reserved.