Click here to Skip to main content
13,699,870 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
how can i use transaction code in below code for multiple query

private SqlConnection DBCon
    {
        get { return new SqlConnection(ConfigurationManager.ConnectionStrings[" "].ConnectionString); 
}
    }
  protected DataTable ExecuteQry(string Qry)
    {
        DataTable dt = new DataTable();
        try {
           
            using (SqlConnection myCon = DBCon)
            {
                SqlCommand myCommand = new SqlCommand(Qry, myCon);
                myCommand.CommandType = CommandType.Text;
                //Set Parameters       
                foreach (SqlParameter Parameter in parameter)
                {
                    myCommand.Parameters.AddWithValue(Parameter.ParameterName, Parameter.Value);
                }
                //Execute the command
                SqlDataAdapter da = new SqlDataAdapter(myCommand);
                // Execute the command
                myCon.Open();
                da.Fill(dt);
                myCon.Close();

            }
        }
        catch(Exception ex)
        {
            ex.ToString();
        }
        return dt;
    }


What I have tried:

i have tried the given code

protected boolExecuteQry(string Qry,string Qry1)
   {

       try {

                  using (SqlConnection myCon = DBCon)
       {
           myCon.Open();
           using (SqlTransaction tr = myCon.BeginTransaction())
           {
               try
               {
                   if (Qry != "")
                   {
                       SqlCommand myCommand = new SqlCommand(Qry, myCon, tr);
                       myCommand.CommandType = CommandType.Text;
                       foreach (SqlParameter Parameter in parameter)
                       {
                           myCommand.Parameters.AddWithValue(Parameter.ParameterName, Parameter.Value);
                       }
                       myCommand.ExecuteNonQuery();

                   }
if (Qry1 != "")
                   {
                       SqlCommand myCommand = new SqlCommand(Qry1, myCon, tr);
                       myCommand.CommandType = CommandType.Text;
                       foreach (SqlParameter Parameter in parameter)
                       {
                           myCommand.Parameters.AddWithValue(Parameter.ParameterName, Parameter.Value);
                       }
                       myCommand.ExecuteNonQuery();

                   }
           }
       }
       catch(Exception ex)
       {
           ex.ToString();
       }

   }
Posted 3-Jan-18 8:51am
Updated 3-Jan-18 9:54am

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

I'm, not even going to ask what this code is being used for. It looks wrong for whatever purpose you intend.
1) You've got two Qry variables, neither of which are properly named to describe what they are used for.

2) You can seemingly execute BOTH queries, of which they will both use the same set of parameter objects.

3) Your parameters are coming from somewhere else in the class and not from being passed in via the method arguments.

4) Your "parameter" variable is also not properly named as it's not describing a collection of parameter objects.

5) Your outside try/catch block, well, has no catch block!

But, on to the question at hand. You never committed the transaction upon successful completion of the query.

Oh, and you're missing a few closing curly braces near the bottom of the code.

Your modified code should look more like this:
protected boolExecuteQry(string Qry,string Qry1)
{
    try
    {
        using (SqlConnection myCon = DBCon)
        {
            myCon.Open();
            SqlTransaction tr = myCon.BeginTransaction();

            try
            {
                if (Qry != "")
                {
                    SqlCommand myCommand = new SqlCommand(Qry, myCon, tr);
                    myCommand.CommandType = CommandType.Text;
                    foreach (SqlParameter Parameter in parameter)
                    {
                        myCommand.Parameters.AddWithValue(Parameter.ParameterName, Parameter.Value);
                    }
                    myCommand.ExecuteNonQuery();
                }

                if (Qry1 != "")
                {
                    SqlCommand myCommand = new SqlCommand(Qry1, myCon, tr);
                    myCommand.CommandType = CommandType.Text;
                    foreach (SqlParameter Parameter in parameter)
                    {
                        myCommand.Parameters.AddWithValue(Parameter.ParameterName, Parameter.Value);
                    }
                    myCommand.ExecuteNonQuery();
                }

                tr.Commit();
            }
            catch(Exception ex)
            {
                // Something failed so rollback the transaction.
                tr.Rollback();

                // This line of code does absolutely NOTHING! Why is it here?
                ex.ToString();  
            }
        }
    }
    catch (Exception ex)
    {
        // WHat do you do here if the connection fails to open?
    }
}
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web05-2016 | 2.8.180910.1 | Last Updated 3 Jan 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100