如何在c#中使用sqltransaction

how to use sqltransaction in c#

提问by Sonu_Orai

I am using following code to execute two commands at once. I used sqltransaction to assure either all command get executed or rolled back.When I run my program without "transaction" it run properly but when I use "transaction" with them they show error. My code is as follow;

我正在使用以下代码一次执行两个命令。我使用 sqltransaction 来确保所有命令都得到执行或回滚。当我在没有“事务”的情况下运行我的程序时，它运行正常，但是当我对它们使用“事务”时，它们会显示错误。我的代码如下；

SqlTransaction transaction = connectionsql.BeginTransaction();

try
{
    SqlCommand cmd1 = new SqlCommand("select account_name from master_account where NOT account_name = 'BANK' AND NOT account_name = 'LOAN'", connectionsql);
    SqlDataReader dr1 = cmd1.ExecuteReader();
    while (dr1.Read())
    {
        comboBox1.Items.Add(dr1[0].ToString().Trim());
    }
    cmd1.Dispose();
    dr1.Dispose();

    SqlCommand cmd2 = new SqlCommand("select items from rate",connectionsql);
    SqlDataReader dr2 = cmd2.ExecuteReader();
    while (dr2.Read())
    {
        comboBox2.Items.Add(dr2[0].ToString().Trim());
    }
    cmd2.Dispose();
    dr2.Dispose();
    transaction.Commit();

    dateTimePicker4.Value = dateTimePicker3.Value;
}
catch(Exception ex)
{
    transaction.Rollback();
    MessageBox.Show(ex.ToString());
}

and error:

和错误：

回答by LarsTech

You have to tell your SQLCommand objects to use the transaction:

您必须告诉您的 SQLCommand 对象使用该事务：

cmd1.Transaction = transaction;

or in the constructor:

或在构造函数中：

SqlCommand cmd1 = new SqlCommand("select...", connectionsql, transaction);

Make sure to have the connectionsql object open, too.

确保也打开了 connectionsql 对象。

But all you are doing are SELECT statements. Transactions would benefit more when you use INSERT, UPDATE, etc type actions.

但是您所做的只是 SELECT 语句。当您使用 INSERT、UPDATE 等类型的操作时，事务将受益更多。

回答by Snake Eyes

Well, I don't understand why are you used transaction in case when you make a select.

好吧，我不明白为什么您在创建select.

Transaction is useful when you make changes (add, edit or delete) data from database.

当您更改（添加、编辑或删除）数据库中的数据时，事务非常有用。

Remove transaction unless you use insert, updateor deletestatements

除非使用insert, updateordelete语句，否则删除事务

回答by Ramashankar

The following example creates a SqlConnection and a SqlTransaction. It also demonstrates how to use the BeginTransaction, Commit, and Rollback methods. The transaction is rolled back on any error, or if it is disposed without first being committed. Try/Catch error handling is used to handle any errors when attempting to commit or roll back the transaction.

下面的示例创建一个 SqlConnection 和一个 SqlTransaction。它还演示了如何使用 BeginTransaction、Commit 和 Rollback 方法。事务在发生任何错误时回滚，或者在没有首先提交的情况下被处理。Try/Catch 错误处理用于在尝试提交或回滚事务时处理任何错误。

private static void ExecuteSqlTransaction(string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        SqlCommand command = connection.CreateCommand();
        SqlTransaction transaction;

        // Start a local transaction.
        transaction = connection.BeginTransaction("SampleTransaction");

        // Must assign both transaction object and connection 
        // to Command object for a pending local transaction
        command.Connection = connection;
        command.Transaction = transaction;

        try
        {
            command.CommandText =
                "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
            command.ExecuteNonQuery();
            command.CommandText =
                "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
            command.ExecuteNonQuery();

            // Attempt to commit the transaction.
            transaction.Commit();
            Console.WriteLine("Both records are written to database.");
        }
        catch (Exception ex)
        {
            Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
            Console.WriteLine("  Message: {0}", ex.Message);

            // Attempt to roll back the transaction. 
            try
            {
                transaction.Rollback();
            }
            catch (Exception ex2)
            {
                // This catch block will handle any errors that may have occurred 
                // on the server that would cause the rollback to fail, such as 
                // a closed connection.
                Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
                Console.WriteLine("  Message: {0}", ex2.Message);
            }
        }
    }
}

See SqlTransaction Class

参见SqlTransaction 类

回答by Masoud Siahkali

Update or Delete with sql transaction

使用 sql 事务更新或删除

 private void SQLTransaction() {
   try {
     string sConnectionString = "My Connection String";
     string query = "UPDATE [dbo].[MyTable] SET ColumnName = '{0}' WHERE ID = {1}";

     SqlConnection connection = new SqlConnection(sConnectionString);
     SqlCommand command = connection.CreateCommand();
     connection.Open();
     SqlTransaction transaction = connection.BeginTransaction("");
     command.Transaction = transaction;
     try {
       foreach(DataRow row in dt_MyData.Rows) {
         command.CommandText = string.Format(query, row["ColumnName"].ToString(), row["ID"].ToString());
         command.ExecuteNonQuery();
       }
       transaction.Commit();
     } catch (Exception ex) {
       transaction.Rollback();
       MessageBox.Show(ex.Message, "Error");
     }
   } catch (Exception ex) {
     MessageBox.Show("Problem connect to database.", "Error");
   }
 }

回答by Bizhan

You can create a SqlTransactionfrom a SqlConnection.

您可以SqlTransaction从SqlConnection.

And use it to create any number of SqlCommands

并使用它来创建任意数量的 SqlCommands

SqlTransaction transaction = connection.BeginTransaction();
var cmd1 = new SqlCommand(command1Text, connection, transaction);
var cmd2 = new SqlCommand(command2Text, connection, transaction);

Or

或者

var cmd1 = new SqlCommand(command1Text, connection, connection.BeginTransaction());
var cmd2 = new SqlCommand(command2Text, connection, cmd1.Transaction);

---

---

If the failure of commands nevercause unexpected changes don'tuse transaction.

如果命令失败永远不会导致意外更改，请不要使用事务。

if the failure of commands mightcause unexpected changes put them in a Try/Catch block and rollback the operation in anotherTry/Catch block.

如果命令失败可能导致意外更改，则将它们放在 Try/Catch 块中并回滚另一个Try/Catch 块中的操作。

Why another try/catch? According to MSDN:

为什么要再次尝试/捕获？根据MSDN：

Try/Catchexception handling should always be usedwhen rolling backa transaction. A Rollback generates an InvalidOperationExceptionif the connection is terminated or if the transaction has already been rolled back on the server.

try / catch语句的异常处理，应始终使用时回滚事务。InvalidOperationException如果连接终止或事务已在服务器上回滚，则回滚会生成一个。

Here is a sample code:

这是一个示例代码：

string connStr = "[connection string]";
string cmdTxt = "[t-sql command text]";

using (var conn = new SqlConnection(connStr))
{
    conn.Open();
    var cmd = new SqlCommand(cmdTxt, conn, conn.BeginTransaction());


    try
    {
        cmd.ExecuteNonQuery();
        //before this line, nothing has happened yet
        cmd.Transaction.Commit();
    }
    catch(System.Exception ex)
    {
        //You should always use a Try/Catch for transaction's rollback
        try
        {
            cmd.Transaction.Rollback();
        }
        catch(System.Exception ex2)
        {
            throw ex2;
        }
        throw ex;
    }

    conn.Close();
}

The transaction is rolled backin the event it is disposedbefore Commit or Rollback is called.

事务被回滚在它是在事件设置之前提交或回滚调用。

So you don't need to worry about app being closed.

因此您无需担心应用程序被关闭。

回答by lulliezy

First you don't need a transaction since you are just querying select statements and since they are both select statement you can just combine them into one query separated by space and use Dataset to get the all the tables retrieved. Its better this way since you made only one transaction to the database because database transactions are expensive hence your code is faster. Second of you really have to use a transaction, just assign the transaction to the SqlCommand like

首先，您不需要事务，因为您只是在查询 select 语句，并且由于它们都是 select 语句，因此您可以将它们组合成一个由空格分隔的查询，并使用 Dataset 来获取检索到的所有表。这种方式更好，因为您只对数据库进行了一次事务，因为数据库事务很昂贵，因此您的代码速度更快。第二个你真的必须使用事务，只需将事务分配给 SqlCommand 就像

sqlCommand.Transaction = transaction;

And also just use one SqlCommand don't declare more than one, since variables consume space and we are also on the topic of making your code more efficient, do that by assigning commandText to different query string and executing them like

并且只使用一个 SqlCommand 不要声明多个，因为变量消耗空间，我们也在使您的代码更高效的主题，通过将 commandText 分配给不同的查询字符串并执行它们来做到这一点

sqlCommand.CommandText = "select * from table1";
sqlCommand.ExecuteNonQuery();
sqlCommand.CommandText = "select * from table2";
sqlCommand.ExecuteNonQuery();