A quick TransactionScope Example
Ok, I promised I'd have some examples by Monday and I'm late. Anyway, I'm working on a full fledged article where I really test this thing including MessageQueue as well but this gives you the gist of it. Basically it's just a fleshed out example of what Angel posted but it should give you a feel for how easy this guy is to use. That's not to say this is a simplistic class - as Angel pointed out there are 15 overloads for the constructor alone. But it is easy as hell to use in its simplest form. I'm having some trouble getting another chunk of it to work (non-db related) but that's due to me being dumb in all likelihood. Anyway, here it is:
const
string ConnectString = @"Data Source=.\SQLExpress;Integrated Security=True;AttachDBFilename=C:\Visual EmptyDatabase1.mdf";
private void btnTryTransaction_Click(object sender, EventArgs e)
{
TimeSpan ts = new TimeSpan(0, 0, 5);//Didin't do this yet - should have taken it out.
TransactionScope scopeObject = new TransactionScope();
string sql = "INSERT INTO tb_Customers(Customer_ID, Customer_FirstName, Customer_LastName) VALUES (@CustID, @FirstName , @LastName)";
using (scopeObject)
{
using (SqlConnection cn = new SqlConnection(ConnectString))
{
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.Parameters.Add(
"@CustID", SqlDbType.Int, 4).Value = 8;
cmd.Parameters.Add(
"@FirstName", SqlDbType.VarChar, 50).Value = "William";
cmd.Parameters.Add(
"@LastName", SqlDbType.VarChar, 50).Value = "Gates";
cn.Open();
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.CommandText =
"SELECT COUNT(*) FROM tb_Customers";
int i = (int)cmd.ExecuteScalar();//7 Records after Successful Insert
cn.Close();
}
scopeObject.Consistent =
false;
}
scopeObject.Dispose();
MessageBox.Show(GetTotalCount().ToString());
//Now this will equal 6 Proving it was rolled back with
//Absolutely NO effort on my part other than setting consistent
//to false. Now, a better example (forgive me, I'm just trying
//an illustration, I'll have a hardcore example very soon
//you could have a bool Property - in the exception handler
//set it to false, initialize it to true and set .Consistent
//to the property you created
}
private int GetTotalCount()
{
using (SqlConnection cn = new SqlConnection(ConnectString))
{
SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM tb_Customers", cn);
cn.Open();
int i = (int)cmd.ExecuteScalar();
cn.Close();
return i;
}
}
If I don't set .Consistent to false then I keep all seven records. As soon as I get the MessageQueue part to work, I'll have everything up - promise.