如果在c＃asp.net中有任何失败，如何添加回滚sql事务？ [英] how to add rollback sql transactions if any failed in c# asp.net?

问题描述

how to add a sql transaction if I have the procedure, and in one of these procedures, failed to execute sql ..

if one fails in execution of the sql sql in another procedure is not executed

ex :

 in procedure 1 :     string sql = "delete from column where id='" + id+ "' and column_name='" + columnname + "'"; mda.execSQL(sql1, connStr);
in procedure 2 : mda.execSQL(sql2, connStr);   string sql = "delete from columntype where idtype='" + id+ "' and column_name='" + columnname + "'"; mda.execSQL(sql, connStr);
in procedure 3 : mda.execSQL(sql2, connStr);   string sql = "delete from columntype where idtype='" + id+ "' and column_name='" + columnname + "'"; mda.execSQL(sql, connStr);

please check also my DataAccess.cs 

public string execSQL(String queryString, String ConnectionString)
    {

        // Retrieve the connection string stored in the Web.config file.

        SqlConnection connection = new SqlConnection(ConnectionString);
        SqlCommand  cmd = new SqlCommand( queryString,connection  );

        try
        {
            // Connect to the database and run the query.
            
            connection.Open();            
            cmd.ExecuteNonQuery ();

            // Fill the DataSet.
            return "sukses";

        }
        catch (Exception ex)
        {

            // The connection failed. Display an error message.
            return  ex.Message;

        }

        

    }

推荐答案

在您的C＃代码中添加一个事务：

Add a Transaction to your C# code:

using (SqlConnection connection = new SqlConnection(ConnectionString))
{
    using (SqlCommand  cmd = new SqlCommand( queryString,connection  ))
    { 
        SqlTransaction trans;
        try
        {
            connection.Open();  
            trans = connection.BeginTransaction("Trans");
            cmd.Transaction = trans;
            cmd.ExecuteNonQuery ();
            trans.Commit();
            return "sukses";
        }
        catch (Exception ex)
        {
            if trans != null) trans.Rollback();
            return  ex.Message;
        }
    }
}

：doh：忘记将交易连接到命令......：O [/ edit]

Griff，给自己一杯咖啡 - 你需要它...... [/ edit]

[edit]:doh: forgot to connect the transaction to the command... :O [/edit]
[edit]Griff, get yourself a coffee - you need it...[/edit]

这篇关于如果在c＃asp.net中有任何失败，如何添加回滚sql事务？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！