Transaction(Commit or Rollback) To Button_Click Handling Two Stored Procedure

Refer:

1. How to begin and end sql transaction in asp.net code

I hope this will help you out.

Yea thanks Shashikant for your post. This does not meet my requirement. As you can see in btnSave_Click I joined two things together which are SaveCustomer() and SaveCustomerOrders(id As Integer).

My requirement is how do I add transaction to SaveCustomer() and SaveCustomerOrders(id As Integer) in btnSave_Click so that both of them execute as one unit of fail ?

Hi,

Please refer below code.

C#

public void SaveCustomerData()
{
    string constr = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
    SqlTransaction transaction;
    using (SqlConnection con = new SqlConnection(constr))
    {
        con.Open();
        transaction = con.BeginTransaction();
        try
        {
            int id = 0;
            using (SqlCommand cmd = new SqlCommand("PR", con, transaction))
            {
                cmd.Parameters.AddWithValue("@GRNumber", "1233");
                cmd.Parameters.Add("@GRID", SqlDbType.BigInt).Direction = ParameterDirection.Output;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.ExecuteNonQuery();
                id = Convert.ToInt32(cmd.Parameters["@GRID"].Value);
            }
            foreach (GridViewRow row in GView.Rows)
            {
                string ponum = ((Label)row.Cells(1).FindControl("lblPONumber")).Text;
                string partyname = ((Label)row.Cells(2).FindControl("lblPartyName")).Text;
                string itemname = ((Label)row.Cells(3).FindControl("lblItemName")).Text;
                string qty = ((Label)row.Cells(4).FindControl("lblQtyOrdered")).Text;
                string qtydelivered = ((Label)row.Cells(5).FindControl("lblQtyDelivered")).Text;
                using (SqlCommand cmd = new SqlCommand("PR_Det", con, transaction))
                {
                    cmd.Parameters.AddWithValue("@GRID", id);
                    cmd.Parameters.Add("@PONumber", SqlDbType.VarChar).Value = ponum;
                    cmd.Parameters.Add("@PartyName", SqlDbType.VarChar).Value = partyname;
                    cmd.Parameters.Add("@ItemName", SqlDbType.VarChar).Value = itemname;
                    cmd.Parameters.AddWithValue("@QTYOrdered", qty).ToString();
                    cmd.Parameters.AddWithValue("@QTYReceived", qtydelivered).ToString();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.ExecuteNonQuery();
                }
            }
            transaction.Commit();
        }
        catch (SqlException sqlError)
        {
            transaction.Rollback();
        }
        finally
        {
            con.Close();
        }
    }
}

I hope this will help you out.

Thank you thank you so much Shashikant. It worked. I added transaction to the following line:

From

using (SqlCommand cmd = new SqlCommand("PR", con))

To

using (SqlCommand cmd = new SqlCommand("PR", con, transaction))