locked
SqlTransaction in scope problem RRS feed

  • Question

  • User-1993844047 posted
     protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
            {
                if (Page.IsPostBack)
                {
                    if (e.CommandName == "Insert")
                    {
                        Int32 newItemNo = -1;
                        using (var Conn = new SqlConnection(_ConnectionString))
                        {
                            try
                            {
                                Conn.Open();
                                using (var ts = new System.Transactions.TransactionScope()) 
                                {
                                     
                                    InsertIssueLog(ref newItemNo, ts, e);
    
                                    InsertSupportDoc(newItemNo, ts, e);
    
                                    //InsertApprovals(newItemNo, e);
                                    Approvals app = new Approvals();
                                    app.ItemNo = newItemNo;
                                    app.AppType = ApprovalType.CREATE_ISSUE;
                                    app.CreatedBy = new Guid(Session["userId"].ToString());
                                    app.ProposedPersonnelID = Guid.Empty;
                                    app.ProposedDate = (DateTime?)null;
                                    app.ApprovePersonID = Guid.Empty;
                                    app.ApprovalDate = (DateTime?)null;
                                    app.ApprovalReason = "N/A";
                                    app.ReasonAcceptReject = "";
                                    Helpers.InsertApprovals(app, con, ts);
    
                                    String ClerkEmail = "";
                                    String SupervisorEmail = "";
    
                                    Helpers.GetEmailAddresses(new Guid(Session["userId"].ToString()), ref ClerkEmail, ref SupervisorEmail);
    
                                    // 2) Send an Email notification to the followup person
                                    Helpers.SendingMail(ClerkEmail, SupervisorEmail, "New Issue Log Created", "New Issue Log Created");
    
                                    GridView1.EditIndex = -1;
                                    
                                    ts.Complete();
                                    con.Close();                                
                                } 
                            }
                             
                        } 
    
                        Response.Redirect("~/clerk/ManageIssueLog.aspx");
                    }                                    
      
                }
            }

    Do I have to pass the TransactionScope to InsertIssueLog and put something like

    SqlCommand comm = new SqlCommand(commText, conn, ts) inside InsertIssueLog?

    Or is it a common practice to have standalone SqlTransaction and SqlConnection in each method?

    Thanks

    Jack

    Thursday, April 9, 2015 11:34 PM

Answers

  • User842257015 posted

    Hi,

    Yeha, we can pass transaction scope object to another method, then used like standalone transaction object. But my best advice to use stand alone Transaction scope object for every Db interaction.

     public void CityInsert()
            {
                using (ScriptLearning.EDMX.SCRIPTLEARNINGEntities dbContext = new EDMX.SCRIPTLEARNINGEntities())
                {               
                    using (var dbContextTransaction = new System.Transactions.TransactionScope())
                    {
                        try
                        {
                            this.CityCollectionInsert(dbContextTransaction);
                            dbContext.CITies.Add(new EDMX.CITY { ID = 105, NAME = "----" });
                            dbContext.SaveChanges();
                            dbContextTransaction.Complete();
                        }
                        catch (Exception ex)
                        {
                            dbContextTransaction.Dispose();
                        }
                    }
                }
            }
    
            private void CityCollectionInsert(System.Transactions.TransactionScope dbContextTransaction)
            {
                using (ScriptLearning.EDMX.SCRIPTLEARNINGEntities dbContext = new EDMX.SCRIPTLEARNINGEntities())
                {
                    //using (var dbContextTransaction1 = new System.Transactions.TransactionScope())
                    //{
    
                        try
                        {
                            dbContext.CITYCOLLECTIONs.Add(new EDMX.CITYCOLLECTION { CITYNO1 = 103, CITYNO2 = 102, CITYNO3 = 103, CITYNO4 = 105, SNO = 1003 });
                            dbContext.SaveChanges();
                            dbContextTransaction.Complete();
                        }
                        catch (Exception ex)
                        {
                            dbContextTransaction.Dispose();
                        }
    
                    //}
                }
            }

    ref : https://msdn.microsoft.com/en-us/library/vstudio/bb738523(v=vs.100).aspx

    Thanks,

    Jai

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 10, 2015 3:12 AM
  • User1644755831 posted

    Hello lucky7456969,

    TransactionScope works over multiple connections also so as long as your connection are inside the scope all the things will be executed in that transaction scope. weather its single connections or multiple connections.

    for example.

    using (var ts = new System.Transactions.TransactionScope()) 
    {
        using (var Conn = new SqlConnection(_ConnectionString))
    {
    Conn.Open();
    InsertIssueLog(ref newItemNo, e); //I don't think you need to pass transaction scope here because

           InsertSupportDoc(newItemNo, e); //now it does not matter if this method use same connection or other connection it will be included in scope
    //rest of the code
    ts.Complete();
    //con.Close(); connection will be closed automatically because you are of the using scope.
    } }

    You can follow below article to understand more.

    https://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.complete.aspx

    Hope this helps.

    With Regards,

    Krunal Parekh

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 13, 2015 1:11 AM

All replies

  • User842257015 posted

    Hi,

    Yeha, we can pass transaction scope object to another method, then used like standalone transaction object. But my best advice to use stand alone Transaction scope object for every Db interaction.

     public void CityInsert()
            {
                using (ScriptLearning.EDMX.SCRIPTLEARNINGEntities dbContext = new EDMX.SCRIPTLEARNINGEntities())
                {               
                    using (var dbContextTransaction = new System.Transactions.TransactionScope())
                    {
                        try
                        {
                            this.CityCollectionInsert(dbContextTransaction);
                            dbContext.CITies.Add(new EDMX.CITY { ID = 105, NAME = "----" });
                            dbContext.SaveChanges();
                            dbContextTransaction.Complete();
                        }
                        catch (Exception ex)
                        {
                            dbContextTransaction.Dispose();
                        }
                    }
                }
            }
    
            private void CityCollectionInsert(System.Transactions.TransactionScope dbContextTransaction)
            {
                using (ScriptLearning.EDMX.SCRIPTLEARNINGEntities dbContext = new EDMX.SCRIPTLEARNINGEntities())
                {
                    //using (var dbContextTransaction1 = new System.Transactions.TransactionScope())
                    //{
    
                        try
                        {
                            dbContext.CITYCOLLECTIONs.Add(new EDMX.CITYCOLLECTION { CITYNO1 = 103, CITYNO2 = 102, CITYNO3 = 103, CITYNO4 = 105, SNO = 1003 });
                            dbContext.SaveChanges();
                            dbContextTransaction.Complete();
                        }
                        catch (Exception ex)
                        {
                            dbContextTransaction.Dispose();
                        }
    
                    //}
                }
            }

    ref : https://msdn.microsoft.com/en-us/library/vstudio/bb738523(v=vs.100).aspx

    Thanks,

    Jai

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, April 10, 2015 3:12 AM
  • User1644755831 posted

    Hello lucky7456969,

    TransactionScope works over multiple connections also so as long as your connection are inside the scope all the things will be executed in that transaction scope. weather its single connections or multiple connections.

    for example.

    using (var ts = new System.Transactions.TransactionScope()) 
    {
        using (var Conn = new SqlConnection(_ConnectionString))
    {
    Conn.Open();
    InsertIssueLog(ref newItemNo, e); //I don't think you need to pass transaction scope here because

           InsertSupportDoc(newItemNo, e); //now it does not matter if this method use same connection or other connection it will be included in scope
    //rest of the code
    ts.Complete();
    //con.Close(); connection will be closed automatically because you are of the using scope.
    } }

    You can follow below article to understand more.

    https://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.complete.aspx

    Hope this helps.

    With Regards,

    Krunal Parekh

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 13, 2015 1:11 AM