ADO.NET Transaction

(ADO.NET)


Tools Used: Visual Studio 2013 Express, Sql Server 2008/2012 express

I have written code for insert operation using ado.net transaction. I created two tables  one for bank customer personal info and another one for bank customer address info. The below code try to perform insert operation on two tables simultaneously. If any one insert operation fails, The below code will discard the entire insert operation with the help of rollback method of ado.net transaction.

Code:
 
protected void btn_addcust_Click(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["AdodotnetConn"].ToString());
            conn.Open();
            SqlTransaction dbTran=null;

            try
            {
                    //sql transaction
                    dbTran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted,"BankCustomerTran");

                    //Insert query for bank customer personal info
                    #region PersonalInfo

                    string sqlQuery = "INSERT INTO Customers(CustId,Name,Age,Gender) VALUES(@CustId,@Name,@Age,@Gender)";

                    SqlCommand cmd = new SqlCommand(sqlQuery, conn);

                    cmd.Transaction = dbTran;

                    cmd.Parameters.AddWithValue("@CustId", txt_custid.Text);
                    cmd.Parameters.AddWithValue("@Name", txt_name.Text);
                    cmd.Parameters.AddWithValue("@Age", txt_age.Text);

                    //gender
                    if (rdo_male.Checked)
                        cmd.Parameters.AddWithValue("@Gender", rdo_male.Text);
                    else if (rdo_female.Checked)
                        cmd.Parameters.AddWithValue("@Gender", rdo_female.Text);

                    cmd.ExecuteNonQuery();

                    #endregion


                    //Insert query for bank customer address info

                    #region AddressInfo

                    cmd.CommandText =@"INSERT INTO CustAddress(CustId,Address,City,State,ZipCode,Country) VALUES(@CustId,@Address,@City,@State,
                    @ZipCode,@Country)";


                    cmd.Parameters.Clear();

                    cmd.Parameters.AddWithValue("@CustId", txt_custid.Text);
                    cmd.Parameters.AddWithValue("@Address", txt_address.Text);

                    cmd.Parameters.AddWithValue("@City", txt_city.Text);
                    cmd.Parameters.AddWithValue("@State", txt_state.Text);

                    cmd.Parameters.AddWithValue("@ZipCode", txt_zipcode.Text);
                    cmd.Parameters.AddWithValue("@Country", txt_country.Text);

                    cmd.ExecuteNonQuery();

                    #endregion


                    dbTran.Commit();


                    lbl_result.Text = "Bank customer details added successfully.";
                    lbl_result.CssClass = "successcls";

                  //rebind gridview
                    BindGridView();

               
            }
            catch(Exception)
            {
                dbTran.Rollback();
                lbl_result.Text = "Error Occurred.";
                lbl_result.CssClass = "errorcls";
              
            }
            finally
            {
                conn.Close();
            }
        }


Ratings


Average Rating: 5.00 by 1 users
Giri Prasad
5/17/2014
5/17/2014
Download PDF
Download Project

Comments






giri

useful


prasad

testksadjfsdkaljfklasjflkasjflkasjflkjsalfkjaslkfjasljfalskjflaskjflaskjflasjdflajslfkjasldjfalskjflkasjfdlkasjflkasjldfkjsaljfalskjflaskjflkasjflkasjflkasjdflkasjfldkasjflweirowuoiqwuoiuwqoiqwuoiwqeu


jimmy

good one


john

very useful


green

good...