Use ADO.net Transaction Object

Sometimes we need to use ADO.net Transaction object because if a sql command execute, it may fail and we don't want the application or database changes with error in halfway, and we want to set to the initial state before our sql command execute.

There are famous ACID properties for Transaction object.(Atomicity, Consistency, Isolation, Durability).

Here is a paper which use SqlTransaction dealing with SqlBulkCopy, it is a good article:
http://www.aspdotnetcodes.com/SqlBulkCopy_With_SqlTransaction.aspx

I make notes how to use SqlTransation object below, we can write code like:

string constr=@"...."

using (SqlConnection con=....)
{
con.Open();
using (SqlTransaction tran=con.BeginTransation())
{
try
{
using (SqlCommand cmd=con.CreateCommand())
{
cmd.Transaction=tran;
cmd.Execute....;
}
tran.Commit();
}
catch
{
tran.Rollback();
}
}
}

That is, if a command execute sucessfully, we use SqlTransation Commit() method, and if it failed, we use its Rollback() method to rollback the transaction.