C# 使用 LINQ-to-SQL 从事务内部回滚存储过程调用？

Rollback a stored procedure call from inside a transaction using LINQ-to-SQL?

提问by

I have a C#.net winform program which runs with a SQL Server database. I am using LINQ-to-SQL. Is it possible to rollback the call to one or more stored procedures inside a transaction within my program using LINQ-to-SQL?

我有一个与 SQL Server 数据库一起运行的 C#.net winform 程序。我正在使用 LINQ-to-SQL。是否可以使用 LINQ-to-SQL 回滚对我程序中事务内一个或多个存储过程的调用？

Initially I thought it would make sense to manage the transaction inside the stored procedure but if I need to rollback more than one stored procedure call as part of a single transaction it would need to be done in my C# program.

最初我认为在存储过程中管理事务是有意义的，但是如果我需要将多个存储过程调用作为单个事务的一部分回滚，则需要在我的 C# 程序中完成。

Can someone point me to a code snippet on how to do this or provide some insight into an alternative?

有人可以指出我如何执行此操作的代码片段或提供一些替代方法的见解吗？

回答by Andre Gallo

Although I'm not using stored procs, you coudl have something like that:

虽然我没有使用存储过程，但你可以有这样的东西：

    public Response<SomeObject> SaveSomething(Object yourObject)
    {
        DbTransaction dbTransaction = null;
        try
        {
            using (DataContext context = new DataContext())
            {
                    //Creates a new DB transaction
                    if (context.Connection.State == System.Data.ConnectionState.Closed)
                    {
                        context.Connection.Open();
                    }
                    dbTransaction = context.Connection.BeginTransaction(System.Data.IsolationLevel.Serializable);
                    context.Transaction = dbTransaction;

         context.SaveYourObject(yourObject);
                    //Commit the transaction
                    dbTransaction.Commit();
                    response.ResponseObject = yourObject;
                    response.Messages.AddSuccessfulSave("Saved!");
                }
            }
        }
        catch (ChangeConflictException cex)
        {
            if (dbTransaction != null) dbTransaction.Rollback();
            response.Errors.AddConcurrencyError();
            response.IsSuccessful = false;
        }
        catch (SqlException sqlEx)
        {
            if (dbTransaction != null) dbTransaction.Rollback();
            if (sqlEx.Class == 14 && (sqlEx.Number == 2601 || sqlEx.Number == 2627)) //Duplicated key
            {
                response.Errors.Add(new Error
                {
                    Name = "Duplicate item",
                    Description = "This object already exists."
                });
                ExceptionPolicy.HandleException(sqlEx, SERVICE_EXCEPTION_POLICY);
                response.IsSuccessful = false;
            }
            else //other SQL errors
            {
                response.Errors.AddSavingError("Your object", yourObjectId);
                ExceptionPolicy.HandleException(sqlEx, SERVICE_EXCEPTION_POLICY);
                response.IsSuccessful = false;
            }
        }

回答by Marc Gravell

Another alternative to DbTransactionis TransactionScope- this provides a much simpler programming model, and is extensible to multiple simultaneous databases and other feeds (via DTC) - but at the cost of a small amount of overhead on the connection. It used to be more overhead, but under SQL2005 etc it will use the "LTM" until you start spanning multiple connections - so a single operation is usually very cheap:

另一种替代方法DbTransaction是TransactionScope- 这提供了一个更简单的编程模型，并且可以扩展到多个并发数据库和其他提要（通过 DTC） - 但以连接上的少量开销为代价。它曾经是更多的开销，但在 SQL2005 等下它会使用“LTM”直到你开始跨越多个连接 - 所以单个操作通常非常便宜：

using (TransactionScope tran = new TransactionScope())
using (FooDataContext ctx = new FooDataContext())
{
    // your work with ctx
    // ...
    // other work involving connections etc
    // ...
    tran.Complete();
}

Very simple ;-p You should also be able to make the transaction more granular (over just a few queries) ormore encompassing very simply. Most existing code will automatically enlist in the transaction scope, making it very easy to retro-fit into existing code.

非常简单 ;-p 您还应该能够使事务更精细（仅通过几个查询）或更简单地包含。大多数现有代码将自动加入事务范围，从而非常容易地改造现有代码。

For more info on TransactionScope (and general transactions in .NET), see here.

有关 TransactionScope（以及 .NET 中的一般事务）的更多信息，请参见此处。

回答by Marc Gravell

private string RollBack_fn()
{
    int sal = 0;
    OracleConnection myconn = new OracleConnection(ConfigurationManager.AppSettings["con"].ToString());
    cmd = new OracleCommand("SP_student_MAST", myconn);
    cmd.CommandType = CommandType.StoredProcedure;
    OracleParameter param1 = null, param2 = null, param3 = null, param4 = null, param5 = null;

    try
    {
        myconn.Open();
       trans = myconn.BeginTransaction();
        cmd.Transaction = trans;
        //param1 = new OracleParameter("pSNo", OracleType.VarChar, 5);
        //param1.Value ="";
        //cmd.Parameters.Add(param1);

        param2 = new OracleParameter("pSName", OracleType.VarChar, 10);
       // param2.Value = "Saravanan";
        param2.Value = TextBox1.Text;
        cmd.Parameters.Add(param2);

        param3 = new OracleParameter("pENo", OracleType.VarChar,5);
        param3.Value = TextBox2.Text;
        cmd.Parameters.Add(param3);

        param4 = new OracleParameter("pEName", OracleType.VarChar,10);
       // param4.Value = "Sangeetha";
        param4.Value = TextBox3.Text;
        cmd.Parameters.Add(param4);

        param5 = new OracleParameter("pSENo", OracleType.Char, 5);
        param5.Value = "";
        cmd.Parameters.Add(param5);
        sal = cmd.ExecuteNonQuery();
        trans.Commit();
        Response.Write("Record Saved");
        myconn.Close();
       // rollbackvalue = 0;
    }
    catch (Exception ex)
    {
        Response.Write("Not saved.RollBacked");
        trans.Rollback();
        //rollbackvalue = 1;
    }
    string cs = Convert.ToString(sal);
    return cs;

}