.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> ADO.NET >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

ADO.NET Manual Transactions

Posted By:Shashi Ray       Posted Date: November 30, 2009    Points: 10    Category: ADO.NET    URL: http://www.dotnetspark.com  

The following code shows how to take advantage of the transaction support offered by the SQL Server..NET Data Provider to protect a funds transfer operation with a transaction
 

The following code shows how to take advantage of the transaction support offered by the SQL Server .NET Data Provider to protect a funds transfer operation with a transaction. This operation transfers money between two accounts located in the same database.

public void TransferMoney( string toAccount, string fromAccount, decimal amount )
{
  using ( SqlConnection conn = new SqlConnection(
            "server=(local);Integrated Security=SSPI;database=SimpleBank" ) )
  {
    SqlCommand cmdCredit = new SqlCommand("Credit", conn );
    cmdCredit.CommandType = CommandType.StoredProcedure;
    cmdCredit.Parameters.Add( new SqlParameter("@AccountNo", toAccount) );
    cmdCredit.Parameters.Add( new SqlParameter("@Amount", amount ));
    SqlCommand cmdDebit = new SqlCommand("Debit", conn );
    cmdDebit.CommandType = CommandType.StoredProcedure;
    cmdDebit.Parameters.Add( new SqlParameter("@AccountNo", fromAccount) );
    cmdDebit.Parameters.Add( new SqlParameter("@Amount", amount ));

    conn.Open();
    // Start a new transaction
    using ( SqlTransaction trans = conn.BeginTransaction() )
    {
      // Associate the two command objects with the same transaction
      cmdCredit.Transaction = trans;
      cmdDebit.Transaction = trans;
      try
      {
        cmdCredit.ExecuteNonQuery();
        cmdDebit.ExecuteNonQuery();
        // Both commands (credit and debit) were successful
        trans.Commit();
      }
      catch( Exception ex )
      {
        // transaction failed
        trans.Rollback();
        // log exception details . . .
        throw ex;
      }
    }
  }
}

Shashi Ray


     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend