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?

Everything looks fine. Are you sure that `WHERE PlanID = @PlanID` matches something in Fonts & Sizes? — James Curran, Aug 04 '14 at 18:43
@JamesCurran I realize now that the PlanID is not being added properly elsewhere in the code. Thank you very much. — jwolf, Aug 04 '14 at 18:50
A side note I recommend you read [How Data Access Code Affects Database Performance](http://msdn.microsoft.com/en-us/magazine/ee236412.aspx). `AddWithValue` is pretty much an anti-pattern. — Remus Rusanu, Aug 04 '14 at 18:58
@RemusRusanu I'll give it a look. That was an issue I'm most likely going to address after everything is functional. Thank you — jwolf, Aug 04 '14 at 19:03

score 1 · Answer 1 · edited May 23 '17 at 12:05

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();
        }
    }

I appreciate the suggestion. I'll be sure to check out your links and make some edits. — jwolf, Aug 04 '14 at 19:00

Using an SQL Transaction in ASP.NET to Update Data

1 Answers1