none
Optimized Update C# Code and sql transaction has completed.error. RRS feed

  • Question

  • with reference to the coding below.. every time there's an error, roll back shoud start , but it started with an error, sql transaction has completed. It's no longer usable. 

    i debugged and find it that when conn is used outside the using tag, the conn is disposed. 

    how do i optimized this piece of code ?

     public virtual bool Update(DataTable dt)
            {
                bool isOk=false;
    
                SqlConnection conn = null;
    
                SqlTransaction trans = null;
    
                try
                {
    
                    using (conn = new SqlConnection(DataAccess.DBConn.ConnString))
                    {
                        conn.Open();
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            trans = conn.BeginTransaction();
    
                            SqlCommand cmd = new SqlCommand("UpdateTitle", conn);
    
                            cmd.Transaction = trans;
    
                            cmd.Parameters.AddWithValue("@ttl_name", dt.Rows[i]["Title"].ToString());
                            cmd.Parameters.AddWithValue("@ttl_desc", dt.Rows[i]["Description"].ToString());
                            cmd.Parameters.AddWithValue("@ttl_enable", dt.Rows[i]["Enable"].ToString());
                            cmd.Parameters.AddWithValue("@id", decimal.Parse(dt.Rows[i]["id"].ToString()));
                            cmd.CommandType = CommandType.StoredProcedure;
    
                            cmd.ExecuteNonQuery();
    
                        }
    
                    }
                    isOk = true;
                }
                catch (Exception ex)
                {
    
                    isOk = false;
                    Err.Msg.Prompt(ex);
    
                }
                finally
                {
                    if (isOk)
                        trans.Commit();
                    else
                        try
                        {
                            trans.Rollback();
                        }
                        catch
                        {
                            throw;
                        }
                
                }
                return isOk;
    
            }
    


    Ohhh. boy..
    Thursday, February 11, 2010 8:02 AM

All replies

  • i slightly revised my coding abit.
       public virtual bool Update(DataTable dt)
            {
                bool isOk = false;
                if (dt==null) return true;
                SqlConnection conn = null;
    
                SqlTransaction trans = null;
    
                try
                {
                    conn = new SqlConnection(DataAccess.DBConn.ConnString);
                    conn.Open();
                    trans = conn.BeginTransaction("TS");
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                       
    
                        SqlCommand cmd = new SqlCommand("UpdateTitle", conn);
    
                        cmd.Transaction = trans;
    
                        cmd.Parameters.AddWithValue("@ttl_name", dt.Rows[i]["Title"].ToString());
                        cmd.Parameters.AddWithValue("@ttl_desc", dt.Rows[i]["Description"].ToString());
                        cmd.Parameters.AddWithValue("@ttl_enable", dt.Rows[i]["Enable"].ToString());
                        cmd.Parameters.AddWithValue("@id", decimal.Parse(dt.Rows[i]["id"].ToString()));
                        cmd.CommandType = CommandType.StoredProcedure;
    
                        cmd.ExecuteNonQuery();
    
                    }
    
                    isOk = true;
                }
                catch (Exception ex)
                {
    
                    isOk = false;
                    Err.Msg.Prompt(ex);
    
                }
                finally
                {
                    if (isOk)
                        trans.Commit();
                    else
                        try
                        {
                            if (trans != null)
                                trans.Rollback();
                        }
                        catch
                        {
                            throw;
                        }
                    conn.Close();
                }
                return isOk;
    
            }
    Now im not sure if i missed out anything or any performance or possible error issue i may have missed out.

    can anyone here evaluate me ?
    Ohhh. boy..
    Thursday, February 11, 2010 8:48 AM
  • Hi unrealweapon,

    I'm curious about what this method is for. If you are just updating the database to reflect changes to the datatable, you could instead just use the DataAdapter.Update() method.

    If that doesn't work, one possible optimisation that I see in this code is to have just one SqlCommand object that you create and set parameters, and then change the parameters for each row, instead of rebuilding the SqlCommand for each row.

    Cheers,
    Jared
    Postings are provided "As Is" with no warranties and confer no rights.
    Monday, March 15, 2010 10:53 PM
  • You need to open connection and start transaction outside of the try block and  your code should look like


            public virtual bool Update(DataTable dt)
            {

                if (dt == null) return true;
                SqlConnection conn = null;
                SqlTransaction trans = null;

                conn = new SqlConnection(DataAccess.DBConn.ConnString);
                conn.Open();
                trans = conn.BeginTransaction("TS");

                try
                {
                   
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        SqlCommand cmd = new SqlCommand("UpdateTitle", conn);

                        cmd.Transaction = trans;

                        cmd.Parameters.AddWithValue("@ttl_name", dt.Rows[i]["Title"].ToString());
                        cmd.Parameters.AddWithValue("@ttl_desc", dt.Rows[i]["Description"].ToString());
                        cmd.Parameters.AddWithValue("@ttl_enable", dt.Rows[i]["Enable"].ToString());
                        cmd.Parameters.AddWithValue("@id", decimal.Parse(dt.Rows[i]["id"].ToString()));
                        cmd.CommandType = CommandType.StoredProcedure;

                        cmd.ExecuteNonQuery();

                    }

                    isOk = true;

                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    Err.Msg.Prompt(ex);

                }
                finally
                {
                    trans.Commit();
                    conn.Close();
                }

                return isOk;

            }


    Val Mazur (MVP) http://www.xporttools.net
    Wednesday, March 17, 2010 10:25 AM
    Moderator