如何使用在.NET多SqlConnections单一的SqlTransaction？ [英] How to use a single SqlTransaction for multiple SqlConnections in .NET?

问题描述

1. 在我的SQL Server 2000，它不支持MultipleActiveResults
2. 在我所要做的多个刀片，它的每个插入一个连接完成。
3. 我希望所有的插入前就可以开始交易，所有插入后完成它。
4. 我该怎么办呢？

解决方案

什么是你不使用一个连接和多个命令的原因（实际上是重新创建循环一个命令）？ 也许这个解决方案会为你工作：

 公共静态无效CommandExecNonQuery（CMD的SqlCommand，
       查询字符串，的SqlParameter [] PRMS）
    {
        cmd.CommandText =查询;
        cmd.Parameters.AddRange（PRMS）;
        cmd.ExecuteNonQuery（）;
        cmd.Parameters.Clear（）;
    }

    静态无效的主要（字串[] args）
    {
        串insertQuery =
           @INSERT TestTable的（COLUMN1，COLUMN2）+
             VALUES（@ ParamCol1，@ ParamCol2）;
        使用（SqlConnection的连接=
          新的SqlConnection（的connectionString））
        {
            使用（SqlCommand的命令=
              connection.CreateCommand（））
            {
                的SqlTransaction事务= NULL;
                尝试
                {
                    //调用BeginTransaction（）需要打开连接
                    connection.Open（）;

                    交易= connection.BeginTransaction（）;

                    //指定交易到命令
                    command.Transaction =交易;
                    的for（int i = 0; I＆LT; 100;我++）
                        CommandExecNonQuery（命令，insertQuery，
                          新的SqlParameter [] {
                            新的SqlParameter（@ ParamCol1，I），
                            新的SqlParameter（@ ParamCol2，i.ToString（））}）;
                    器transaction.commit（）;
                }
                抓住
                {
                    transaction.Rollback（）;
                    扔;
                }
                最后
                {
                    的Connection.close（）;
                }
            }
        }
    }
 

另见
Sql Server的交易 - ADO.NET 2.0 - 提交和回滚 - 使用声明 - IDisposable的

1. I have SQL Server 2000, it doesn't support MultipleActiveResults.
2. I have to do multiple inserts, and it's done with one connection per insertion.
3. I want to begin a transaction before all insertions and finish it after all insertions.
4. How do I do it?

解决方案

What is the reason you don't use one connection and multiple commands (actually one command recreated in loop)? Maybe this solution will work for you:

   public static void CommandExecNonQuery(SqlCommand cmd, 
       string query, SqlParameter[] prms)
    {
        cmd.CommandText = query;
        cmd.Parameters.AddRange(prms);
        cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();
    }

    static void Main(string[] args)
    {
        string insertQuery = 
           @"INSERT TESTTABLE (COLUMN1, COLUMN2) " + 
             "VALUES(@ParamCol1, @ParamCol2)";
        using (SqlConnection connection = 
          new SqlConnection(connectionString))
        {
            using (SqlCommand command = 
              connection.CreateCommand())
            {
                SqlTransaction transaction = null;
                try
                {
                    // BeginTransaction() Requires Open Connection
                    connection.Open();

                    transaction = connection.BeginTransaction();

                    // Assign Transaction to Command
                    command.Transaction = transaction;
                    for (int i = 0; i < 100; i++)
                        CommandExecNonQuery(command, insertQuery, 
                          new SqlParameter[] { 
                            new SqlParameter("@ParamCol1", i), 
                            new SqlParameter("@ParamCol2", i.ToString()) });
                    transaction.Commit();
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
        }
    }

Also see
Sql Server Transactions - ADO.NET 2.0 - Commit and Rollback - Using Statement - IDisposable

这篇关于如何使用在.NET多SqlConnections单一的SqlTransaction？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！