Close SqlConnection while using SqlTransaction

Question

I am using SqlTransaction for a series of insert command-

connection.Open();
transaction = connection.BeginTransaction();
command.Transaction = transaction;
command.CommandText="Insert into ....";
connection.Close();

//Doing some other task and then again open connection.
connection.Open();
command.CommandText="Insert into ....";
connection.Close(); 

And finally,

transaction.Commit();
if (transaction != null) { transaction.Rollback(); }

My question is, can I close that connection while using transaction? I must need RollBack ability.

Any help?

Answer 1

You can close the connection after transaction complete . something like below

class Transaction
 {
     public Transaction()
     {
        string FirstQuery = "INSERT INTO Table1 VALUES('Vineeth',24)";
        string SecondQuery = "INSERT INTO Table2 VALUES('HisAddress')";
        int ErrorVar = 0;
        using (SqlConnection con = new SqlConnection("your connection string"))
        {
            try
            {
                SqlCommand ObjCommand = new SqlCommand(FirstQuery, con);
                SqlTransaction trans;
                con.Open();
                trans = con.BeginTransaction();
                ObjCommand.Transaction = trans;
                //Executing first query
                //What ever operation on your database do here

                ObjCommand.ExecuteNonQuery();  //Exected first query
                ObjCommand.CommandText = SecondQuery;
                ObjCommand.ExecuteNonQuery();  //Exected first query
               //Everything gone fine. So commiting
               ObjCommand.Transaction.Commit();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error but we are rollbacking");
                ObjCommand.Transaction.Rollback();
            }
            con.Close();
        }
    }
}

Or you can use TransactionScope