What is wrong with my ADO.NET code?

Question

Can any one help me to identify the error in this program using ADO.NET...

Code:

using System; 
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;   // the code is showing an error in this line

class Program
{
  static void Main(string[] args)
  {
    try
    {
       string connectString = "Initial Catalog=AdventureWorks; 
                               Data Source=SQLSERVER01;
                               User id =user;password=password";
       SqlConnection con = new SqlConnection();
       con.ConnectionString = connectString;

       con.Open();

       SqlTransaction tr = null;
       tr = con.BeginTransaction();

       SqlCommand cmd = new SqlCommand("INSERT INTO sunny1(name, id, city, phone, pincode) VALUES ('sandy', 01441, 'abc', 'phone', 122001)", con, tr); 
       cmd.ExecuteNonQuery();
       tr.Commit();

       Console.WriteLine("transaction completed ");
    }
    catch (SqlException e)
    {
       tr.Rollback();
       Console.WriteLine("teansaction not completed " + e.Message);
    }
    catch (System.Exception ex)
    {
       Console.WriteLine("system error " + ex.Message);
    }
    finally
    {
       cn.Close();
    }

    Console.ReadLine();
 }
}

The code also shows an error message related to some missing assembly.

Answer 1

You have to add an assembly reference to System.Transactions before you can reference it in your code:

1. Right click on the "References" folder in your project
2. Select "Add Reference..."
3. Choose "System.Transactions" from the list of .NET assemblies

Answer 2

In addition what John answered, I would also strongly recommend you start putting your SqlConnection and SqlCommand in using() blocks - this will guaranteed they'll be disposed of at the end of their lifetime and help avoid bugs.

So instead of

   SqlConnection con = new SqlConnection();
   con.ConnectionString = connectString;

   con.Open();

   SqlTransaction tr = null;
   tr = con.BeginTransaction();

   SqlCommand cmd = new SqlCommand("INSERT INTO sunny1(name, id, city, phone, pincode) VALUES ('sandy', 01441, 'abc', 'phone', 122001)", con, tr); 
   cmd.ExecuteNonQuery();
   tr.Commit();

you should use:

   using(SqlConnection con = new SqlConnection(connectString))
   { 
      SqlTransaction tr = con.BeginTransaction();

      using(SqlCommand cmd = new SqlCommand("....", con, tr))
      {
         con.Open();
         cmd.ExecuteNonQuery();
         tr.Commit();
         con.Close();
      }     
   }

Also remember: always open your connection as late as possible (no need to open it when you still need to create other objects - it just needs to be open just before the ExecuteNonQuery() call) and close it explicitly as soon as possible (don't just leave it open and wait for the finally {} block to happen).

Marc

Answer 3

You have to remove the offending line:

using System.Transactions;

you don't use anything from System.Transactions in your code.

Or if you want to keep the using directive add a reference to System.Transactions assembly.

Answer 4

in this example there is no need to wrap one single command in a transaction. Instead of this:

   con.Open();

   SqlTransaction tr = null;
   tr = con.BeginTransaction();

   SqlCommand cmd = new SqlCommand("INSERT INTO sunny1(name, id, city, phone, pincode) VALUES ('sandy', 01441, 'abc', 'phone', 122001)", con, tr); 
   cmd.ExecuteNonQuery();
   tr.Commit();

you do the same with less lines of code:

   con.Open();

   SqlCommand cmd = new SqlCommand("INSERT INTO sunny1(name, id, city, phone, pincode) VALUES ('sandy', 01441, 'abc', 'phone', 122001)", con); 
   cmd.ExecuteNonQuery();