Sql transaction in using code

Question

0.00/5 (No votes)

See more:

how can i use transaction code in below code for multiple query

C#

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

C#

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

$ultaNn

Updated 3-Jan-18 9:54am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Dave Kreskowiak · Answer 1 · 2018-01-03T09:54:00

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:

C#

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?
    }
}