IS it right code for C# SQL Transaction ?

Question

0.00/5 (No votes)

See more:

Dear Experts,
Pl see my code. Is it wright Sql & C# Transcation code? Pl give the advice for more fine tune code.

C#

private void btnsave_Click(object sender, EventArgs e)
        {
            string sq = null;
            try
            {
                sq = "Insert into unpw (username,password)values(@username,@password)";
                cmd = new System.Data.SqlClient.SqlCommand(sq, con);


                Trans = con.BeginTransaction();
                    cmd.Parameters.AddWithValue("@username", txtun.Text);
                    cmd.Parameters.AddWithValue("@password", txtpwd.Text);
                    cmd.Transaction = Trans;
                    cmd.ExecuteNonQuery();
                    Trans.Commit();

                    sq = "Insert into unpw1 (username,password)values(@username,@password)";
                    cmd = new System.Data.SqlClient.SqlCommand(sq, con);
                    cmd.Parameters.AddWithValue("@UserName", txtun.Text);
                    cmd.Parameters.AddWithValue("@password", txtpwd.Text);
                    cmd.Transaction = Trans;
                    cmd.ExecuteNonQuery();
                   //Trans.Commit();
                    MessageBox.Show("Save");
                    cmd.Dispose();
                    con.Close();


            }
            catch (Exception ex)
            {

                Trans.Rollback();

                MessageBox.Show(ex.Message);
            }


        }

Thanks & Advance
sreeni.

Posted 15-Jul-14 21:23pm

sreenivashan

Updated 16-Jul-14 1:08am

Pravuprasad

v2

Add a Solution

Comments

jo.him1988 16-Jul-14 3:50am

if you disable //Trans.Commit(); record will not insert in Database and if you forgot somewhere to write cmd.Dispose();and con.Close(); will take your sql server to busy

so always best to open transaction commit it and if any error rollback it
so fst insertion is work fine

sreenivashan 16-Jul-14 3:55am

okay i thankyou.

2 solutions

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)

Madhu Nair · Accepted Answer · 2014-07-15T21:31:00

Change your code with the below changes. I assume that you have declare Trans variable somewhere in your code

C#

private void btnsave_Click(object sender, EventArgs e)
{
    string sq = null;
    try
    {
        Trans = con.BeginTransaction();
        sq = "Insert into unpw (username,password)values(@username,@password)";
        cmd = new System.Data.SqlClient.SqlCommand(sq, con,Trans);


        cmd.Parameters.AddWithValue("@username", txtun.Text);
        cmd.Parameters.AddWithValue("@password", txtpwd.Text);
        cmd.Transaction = Trans;
        cmd.ExecuteNonQuery();


        sq = "Insert into unpw1 (username,password)values(@username,@password)";
        cmd = new System.Data.SqlClient.SqlCommand(sq, con,Trans);
        cmd.Parameters.AddWithValue("@UserName", txtun.Text);
        cmd.Parameters.AddWithValue("@password", txtpwd.Text);
        cmd.Transaction = Trans;
        cmd.ExecuteNonQuery();
        Trans.Commit();
        MessageBox.Show("Save");
        cmd.Dispose();
        con.Close();


    }
    catch (Exception ex)
    {

        Trans.Rollback();

        MessageBox.Show(ex.Message);
    }


}

somendratiwari · Accepted Answer · 2014-07-15T22:43:00

protected void btnsave_Click(object sender, EventArgs e)
   {
       SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCon"].ConnectionString);
       con.Open();
       SqlTransaction Trans =  con.BeginTransaction();
       string sq = null;
       try
       {
           sq = "Insert into unpw (username,password)values(@username,@password)";
           System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sq, con, Trans);
           cmd.Parameters.AddWithValue("@username", txtun.Text);
           cmd.Parameters.AddWithValue("@password", txtpwd.Text);
           cmd.Transaction = Trans;
           int i= cmd.ExecuteNonQuery();
           sq = "Insert into unpw1 (username,password)values(@username,@password)";
           //cmd = new System.Data.SqlClient.SqlCommand(sq, con, Trans);
           //cmd.Parameters.AddWithValue("@UserName", txtun.Text);
           //cmd.Parameters.AddWithValue("@password", txtpwd.Text);
           //cmd.Transaction = Trans;
           cmd.CommandText = sq;
           cmd.ExecuteNonQuery();
           Trans.Commit();
           MessageBox.Show("Save");
           cmd.Dispose();
           con.Close();
       }
       catch (Exception ex)
       {
           Trans.Rollback();
           MessageBox.Show(ex.Message);
       }
   }

No need to write once again the following line (if the statement is same):

//cmd = new System.Data.SqlClient.SqlCommand(sq, con, Trans);
           //cmd.Parameters.AddWithValue("@UserName", txtun.Text);
           //cmd.Parameters.AddWithValue("@password", txtpwd.Text);
           //cmd.Transaction = Trans;

IS it right code for C# SQL Transaction ?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0