Transaction In Asp.net

Transaction In Asp.net

As we seen earlier Transaction in Sql Server.Means If we are Dealing with two or table at same time for insertion and updation of data,if there is any problem in any executable statement then whole transaction is get rollback to its initial value.

Here  i am using some Text-box For talking Input.If there is all condition come true the n data will insert in to its Tables,otherwise it thrown an Exception.

 Design Database tables.

Create table in your Database..

CREATE TABLE [dbo].[GridView](

      [id] [int] IDENTITY(1,1) NOT NULL,

      [name] [varchar](50) NULL,

      [age] [int] NULL,

      [salary] [decimal](18, 0) NULL,

      [country] [varchar](50) NULL,

      [city] [varchar](50) NULL,

 CONSTRAINT [PK_GridView] PRIMARY KEY CLUSTERED

(

      [id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

CREATE TABLE [dbo].[test1](

      [id] [int] IDENTITY(1,1) NOT NULL,

      [city] [varchar](50) NULL,

      [salary] [int] NULL,

}

Here Its .aspx  Part.

   <div>
  
        name<asp:TextBox ID="txt_name" runat="server"></asp:TextBox>
        <br />
        <br />
        age<asp:TextBox ID="txt_age" runat="server"></asp:TextBox>
        <br />
        salary<asp:TextBox ID="txt_sal" runat="server"></asp:TextBox>
        <br />
        <br />
        country<asp:TextBox ID="txt_con" runat="server"></asp:TextBox>
        <br />
        ciry<asp:TextBox ID="txt_city" runat="server"></asp:TextBox>
        <br />
        sal2<asp:TextBox ID="txt_sal2" runat="server"></asp:TextBox>
        <br />
        city2<asp:TextBox ID="txt_city2" runat="server"></asp:TextBox>
    <asp:Button ID="btn" Text="Submit" runat="server" onclick="btn_Click" />
    </div>
    <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>

Here Its .Cs  Part.

 Here i am using two table for inserting Data gridview and test1.

 

Here its code for insert data in tables.

If there is any problem in any one of   its table data then whole transaction get rollback .

 protected void btn_Click(object sender, EventArgs e)
    {
        con.Open();

        SqlTransaction trans = con.BeginTransaction();
        //SqlTransaction trans = con.BeginTransaction(IsolationLevel.ReadCommitted);

        try
        {
           
            string qu1 = "insert into gridview (name,age,salary,country,city)";

            qu1 += "values('" + txt_name.Text + "','" + txt_age.Text + "','" + txt_sal.Text + "','" + txt_con.Text + "','" + txt_city.Text + "');select @@IDENTITY";

            SqlCommand cmd = new SqlCommand(qu1, con,trans);
            cmd.ExecuteNonQuery();


            string query2 = "Select @@Identity";


             cmd.CommandText = query2;
            int idx = Convert.ToInt32(cmd.ExecuteScalar());
            if (idx != 0)
            {
                Response.Write("<script>alert('Successfully saved')</script>");
            }
            else{
                Response.Write("<script>alert('Not saved')</script>");
            }
       
      




            string qu2 = "insert into test1(city,salary)";
            qu2 += "values('"+txt_city2.Text+"','"+txt_sal2.Text+"')";
            SqlCommand cmd1 = new SqlCommand(qu2, con,trans);
            cmd1.ExecuteNonQuery();
            trans.Commit();

            Response.Redirect("https://www.google.co.in");
       
        }

        catch (Exception ex)
        {
            trans.Rollback();
            Label1.Text = "Error: " + ex.Message;
        }
   
    }