CODEWITHSUNDEEP
c#sqlasp.netsql-servertransactions
siddaramesh
siddaramesh

Reputation: 241

How to delete previous table transaction if next table transaction fails

I'm inserting some data to tables one by one. I've two tables adjustment_header and adjustment_grid.

First I'll insert data to adjustment_header table then I'll insert data to adjustment_grid table. If adjustment insertion fails, previously inserted data in adjustment_header table should be delete automatically.

Is there any query for this kind of problem?

SqlCommand sqlcmd1 = new SqlCommand("INSERT INTO adjustment_header values('"+TextBox1.Text+"','"+TextBox2.Text+"','"+TextBox3.Text+"','"+TextBox4.Text+"')",conn);
conn.Open();
sqlcmd1.ExecuteNonQuery();
conn.Close();
//adjustment grid row 1
if (itemno1.SelectedItem.Text != "please select")
{
    SqlCommand cmd1 = new SqlCommand("INSERT INTO adjustment_grid values('"+TextBox1.Text+"','" + itemno1.SelectedItem.Text + "','" + adj1.SelectedItem.Text + "','" + store1.SelectedItem.Text + "','" + qty1.Text + "','" + cost1.Text + "')", conn);
    conn.Open();
    cmd1.ExecuteNonQuery();
    conn.Close();
}
//adjustment grid row 2
if (itemno2.SelectedItem.Text != "please select")
{
    SqlCommand cmd2 = new SqlCommand("INSERT INTO adjustment_grid values('" + TextBox1.Text + "','" + itemno2.SelectedItem.Text + "','" + adj2.SelectedItem.Text + "','" + store2.SelectedItem.Text + "','" + qty2.Text + "','" + cost2.Text + "')", conn);
    conn.Open();
    cmd2.ExecuteNonQuery();
    conn.Close();
}
//adjustment grid row 3
if (itemno3.SelectedItem.Text != "please select")
{
    SqlCommand cmd3 = new SqlCommand("INSERT INTO adjustment_grid values('" + TextBox1.Text + "','" + itemno3.SelectedItem.Text + "','" + adj3.SelectedItem.Text + "','" + store3.SelectedItem.Text + "','" + qty3.Text + "','" + cost3.Text + "')", conn);
    conn.Open();
    cmd3.ExecuteNonQuery();
    conn.Close();
}

In this code first I'm inserting data into adjustment_header table then I'm inserting into adjustment_grid table 3 times, in 3 transactions in adjustment_grid table any of one fails previously inserted data should be delete automatically.

Upvotes: 1

Views: 223

Answers (1)

D Stanley
D Stanley

Reputation: 152596

Wrap the entire block in a SqlTransaction, and don't open/close your connection for each statement:

conn.Open();
using(SqlTransaction tran = conn.BeginTransaction("Adjustment"))
{
    SqlCommand sqlcmd1 = new SqlCommand("INSERT INTO adjustment_header values('"+TextBox1.Text+"','"+TextBox2.Text+"','"+TextBox3.Text+"','"+TextBox4.Text+"')",conn, tran);
    sqlcmd1.ExecuteNonQuery();

    //adjustment grid row 1
    if (itemno1.SelectedItem.Text != "please select")
    {
        SqlCommand cmd1 = new SqlCommand("INSERT INTO adjustment_grid values('"+TextBox1.Text+"','" + itemno1.SelectedItem.Text + "','" + adj1.SelectedItem.Text + "','" + store1.SelectedItem.Text + "','" + qty1.Text + "','" + cost1.Text + "')", conn, tran);
        cmd1.ExecuteNonQuery();
    }
    //adjustment grid row 2
    if (itemno2.SelectedItem.Text != "please select")
    {
        SqlCommand cmd2 = new SqlCommand("INSERT INTO adjustment_grid values('" + TextBox1.Text + "','" + itemno2.SelectedItem.Text + "','" + adj2.SelectedItem.Text + "','" + store2.SelectedItem.Text + "','" + qty2.Text + "','" + cost2.Text + "')", conn, tran);
        cmd2.ExecuteNonQuery();
    }
    //adjustment grid row 3
    if (itemno3.SelectedItem.Text != "please select")
    {
        SqlCommand cmd3 = new SqlCommand("INSERT INTO adjustment_grid values('" + TextBox1.Text + "','" + itemno3.SelectedItem.Text + "','" + adj3.SelectedItem.Text + "','" + store3.SelectedItem.Text + "','" + qty3.Text + "','" + cost3.Text + "')", conn, tran);
        cmd3.ExecuteNonQuery();
    }
    tran.Commit();
}

You should also use parameters instead of string concatenation, but that's a separate issue...

I would ALSO not reference your controls directly. Put this type of logic in a separate class in a function that has parameters for the various options. That way you can decouple it from the UI and reuse it later if necessary.

Upvotes: 3

Related Questions