none
SQL Transactions in C# RRS feed

  • Question

  • I am exploring using SqlTransaction for wraping many sql inserts and updates in my program, but I havent seen an exact solution for my problem in the forums.  This is a nice tutorial, but its still not what I want.

    I have an application that processes an electronic file sent by insurance companies containing massive amount of payment data. After parsing the electronic file I have a Check object that has a List<> of Payment objects.  The Payment objects have properties like account number, a dollar amount, reason for dollar amount, etc. To post Payment back to the database, and update any original charge data, there are about 3 dollar amounts that get inserted into different tables and a total of 3 to 5 SQL inserts or updates.  I post the payments from the Check object with Check.Post(), which in turn
    iteratively goes through all the Payment objects and calls Payment.Post() where the all the sql inserts/updates occur.

    public void Post()
    {

    foreach (Payment p in this.Payments)

         {

    //id is the unique Check ID

    p.Post(this.id);

         }

    }


    Problem:
    Yesterday two people were using my app and they went to post at the same time and the payment table became locked and they both posted half the payments.

    What I want:
    To post all of the payments in a check or post NONE.  I want to wrap all the SQL update/inserts in one transactions so I can do the comit() and rollback() goodness.

    Please help.
    Tuesday, January 29, 2008 4:02 PM

Answers

  • something like below:

    Code Snippet

    SqlConnection myConnection = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;uid=sa;pwd=sa;");
    myConnection.Open();


    // Start a local transaction
    SqlTransaction myTrans = myConnection.BeginTransaction();

    SqlCommand myCommand = new SqlCommand();
    myCommand.Connection = myConnection;
    myCommand.Transaction = myTrans;
    try
    {
    myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
    myCommand.ExecuteNonQuery();
    myCommand.CommandText = "delete * from Region where RegionID=101";


    // Attempt to commit the transaction.

    myCommand.ExecuteNonQuery();
    myTrans.Commit();
    Response.Write("Both records are written to database.");
    }
    catch (Exception ep)
    {
    // Attempt to roll back the transaction.

    myTrans.Rollback();
    Response.Write(ep.ToString());
    Response.Write("Neither record was written to database.");
    }
    finally
    {
    myConnection.Close();
    }

    Tuesday, January 29, 2008 4:41 PM

All replies

  • something like below:

    Code Snippet

    SqlConnection myConnection = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;uid=sa;pwd=sa;");
    myConnection.Open();


    // Start a local transaction
    SqlTransaction myTrans = myConnection.BeginTransaction();

    SqlCommand myCommand = new SqlCommand();
    myCommand.Connection = myConnection;
    myCommand.Transaction = myTrans;
    try
    {
    myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
    myCommand.ExecuteNonQuery();
    myCommand.CommandText = "delete * from Region where RegionID=101";


    // Attempt to commit the transaction.

    myCommand.ExecuteNonQuery();
    myTrans.Commit();
    Response.Write("Both records are written to database.");
    }
    catch (Exception ep)
    {
    // Attempt to roll back the transaction.

    myTrans.Rollback();
    Response.Write(ep.ToString());
    Response.Write("Neither record was written to database.");
    }
    finally
    {
    myConnection.Close();
    }

    Tuesday, January 29, 2008 4:41 PM
  • If I want to keep my code from the previous post would I...

     

    Pass the SQLConnection and Transaction objects around, and use it for all queries like this:

    Code Snippet

    public void Post()

    {

    SqlConnection myConnection = new SqlConnection("TheString");

    SqlTransaction myTransaction = null;

    try

    {

    myConnection.Open();

    myTransaction = myConnection.BeginTransaction();

     

    foreach (Payment p in this.Payments)

    {

    p.Post(this.id, myConnection, myTransaction);

    }

     

    //More SQL Stuff

    EnterPostHistory(myConnection, myTransaction);

     

    myTransaction.Commit();

    }

    catch

    {

    myTransaction.Rollback();

    throw;

    }

    finally

    {

    myConnection.Close();

    myTransaction.Dispose();

    }

    }

     

    UPDATE:  Just tested using this idea and everything worked great.  I induced an error in the middle of the transaction and all was rolled back.

     

    Thanks for your help

    Tuesday, January 29, 2008 5:02 PM