Using an SQL Transaction in ASP.NET to Update Data

Question

I'm attempting to update multiple tables using an SQL transaction. Right now, the transaction is only affecting one of the tables (dbo.Colors). However, the code is pretty much the same for all three, so I'm wondering where I'm running into an issue. The key difference between the tables is that one is getting information from asp:Textbox(es) while the other two are getting their data from drop down lists. They are all related through a Session variable named "PlanID". Here is my code:

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConversionConnect2"].ConnectionString);
    string query = "UPDATE dbo.Colors SET HeaderBackground = @HeaderBackground, HeaderText = @HeaderText, FooterBackground = @FooterBackground, FooterText = @FooterText,  ButtonText = @ButtonText, ButtonHover = @ButtonHover WHERE PlanID = @PlanID";
    string query2 = "UPDATE dbo.Fonts SET HeaderFont = @HeaderFont, FooterFont = @FooterFont, ButtonFont = @ButtonFont WHERE PlanID = @PlanID";
    string query3 = "UPDATE dbo.Sizes SET HeaderSize = @HeaderSize, FooterSize = @FooterSize, ButtonSize = @ButtonSize WHERE PlanID = @PlanID";

using (conn)
        {
            SqlTransaction trans = null;
            try
            {
                conn.Open();
                trans = conn.BeginTransaction();
                using (SqlCommand transCom = new SqlCommand(query, conn, trans))
                {
                    transCom.Parameters.AddWithValue("@PlanID", Session["planid"].ToString());
                    transCom.Parameters.AddWithValue("@HeaderBackground", txtheadercolor.Text);
                    transCom.Parameters.AddWithValue("@HeaderText", headertext.Text);
                    transCom.Parameters.AddWithValue("@FooterBackground", txtfootercolor.Text);
                    transCom.Parameters.AddWithValue("@FooterText", footertext.Text);
                    transCom.Parameters.AddWithValue("@ButtonText", txtbuttoncolor.Text);
                    transCom.Parameters.AddWithValue("@ButtonHover", txthovercolor.Text);
                    transCom.ExecuteNonQuery();
                }
                using (SqlCommand transCom2 = new SqlCommand(query2, conn, trans))
                {
                    transCom2.Parameters.AddWithValue("@PlanID", Session["planid"].ToString());
                    transCom2.Parameters.AddWithValue("@HeaderFont", headerfont.SelectedValue.ToString());
                    transCom2.Parameters.AddWithValue("@FooterFont", footerfont.SelectedValue.ToString());
                    transCom2.Parameters.AddWithValue("@ButtonFont", ddButtonFont.SelectedValue.ToString());
                    transCom2.ExecuteNonQuery();
                }
                using (SqlCommand transCom3 = new SqlCommand(query3, conn, trans))
                {
                    transCom3.Parameters.AddWithValue("@PlanID", Session["planid"].ToString());
                    transCom3.Parameters.AddWithValue("@HeaderSize", ddheadersize.SelectedValue);
                    transCom3.Parameters.AddWithValue("@FooterSize", ddfootersize.SelectedValue);
                    transCom3.Parameters.AddWithValue("@ButtonSize", ddButtonSize.SelectedValue);
                    transCom3.ExecuteNonQuery();
                }
                trans.Commit();
            }
            catch (Exception Ex)
            {
                if (trans != null)
                {
                    trans.Rollback();
                }
                else
                {
                    return;
                }
            }
            conn.Close();
        }

This code runs without errors, however, when I check dbo.Fonts and dbo.Sizes, I see that both tables were not updated. Any suggestions?

Answer 1

Check this MSDN sample out.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue(v=vs.110).aspx

The .AddWithValue will try to do a type conversion. It may or maynot be what you want.

Here is a good debate on the subject.

http://forums.asp.net/t/1200255.aspx

Also, string will be converted to nvarchar() which might have performance issues on the DBMS. In short, use .Add and select the datatype if possible.

Carve out a simple example like MSDN and test it first. Then modify it to your particular example.

Without the actual table definition, more time, etc ... I can not give you an exact answer.

Good luck C# coding!

...

I think you need another using clause for the transaction itself. Again, test with simple example then expand.

Why use a using statement with a SqlTransaction?

    /* Code from Stack Overflow Answer */

using (SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["T3"])) {
        cn.Open();
        using (SqlTransaction tr = cn.BeginTransaction()) {
            //some code
            tr.Commit();
        }
    }