none
Using DataContext with "Using" statement -- Does connection get cleaned up? RRS feed

  • Question

  •  

    Hey Folks.

     

    I have a two-part Master/Detail Table where the child Table actually has multiple parents.  Something along the lines of this:

     

    Child Table:
    Transactions: [ code, partNumber, quantity, etc, etc]

     

    PurchasingTransaction [ code, creator, receiver, dateCreated, etc, etc]

     

    ReceivingTransaction [ code, creator, stubNumber, dateReceived, receivingClerk, etc...]

     

    So that I have one child table that contains references to multiple parent tables. This is a requirement of the customer.

     

    To implement this I have created a stored procedure as the only insert method for the Parent tables.

     

    This takes a sequence value in for example PurchasingTransaction and adds a prefix to denote it is a "Purchase" eg takes sequence 30 and adds to it PR30. Child records in Transactions  are then added with a "PR30" column value and the view joins it with the proper table as no other parent table can have a key with "PRXXXX"...

     

     

    Aaaaaaanyways. That brings me to the point of my quesrtion.

     

    Because when I enter a master/detail record and line items in my code in a transaction, the "PR30" or whatever newly created transaction number is returned from my stored procedure and used to populate the "Transactions" table. Because of this I am calling SqlCommand.ExecuteScalar() instead of using DataContext.table.InsertOnSubmit(myPurchaseObject), so that I can get the value returned to me within the transaction, without having to make an additional query.

     

     

    My code looks something like this

     

    private void InsertPurchaseRequestTransaction(PDC_LINQ.PurchasingTransaction newPurchase)

    {

    using (PDC_LINQ.PDCDataContext myPDC = new PDC_LINQ.PDCDataContext())

    {

    myPDC.Connection.Open();

    using (SqlTransaction ts = myPDC.Connection.BeginTransaction() as SqlTransaction)

    {

     

    newPurchase.dateCreated = DateTime.Now;

     

    //attach Transaction to the DataContext

    myPDC.Transaction = ts;

     

    //SProc for inserting new Parent record of Transaction

    SqlCommand myCmd = new SqlCommand("spInsertPurchasingTransaction", ts.Connection, ts);

    myCmd.CommandType = CommandType.StoredProcedure;

    myCmd.Parameters.AddWithValue("@pRNumber", newPurchase.pRNumber);

    myCmd.Parameters.AddWithValue("@creatorEmployeeCode", newPurchase.creatorEmployeeCode);

    myCmd.Parameters.AddWithValue("@dateCreated", newPurchase.dateCreated);

    myCmd.Parameters.AddWithValue("@revision", newPurchase.revision);

     

    //Output returns "code" field generated by Sproc

    SqlParameter outParam = new SqlParameter("@PRKEY", SqlDbType.VarChar);

    outParam.Direction = ParameterDirection.Output;

    outParam.Size = 30;

    myCmd.Parameters.Add(outParam);

     

    myCmd.Parameters.AddWithValue("@processCode", newPurchase.processCode);

    string myResult = myCmd.ExecuteScalar().ToString();

     

    foreach(LineItem in my LIneitem collection)

    {

       //Create new child LINQ object

     

      //insert new child linq object
    }

    //insert child items into database

    myPDC.SubmitChanges();

     

    //transaction finished

    ts.Commit();

    }

    myPDC.Connection.Close();

              }
    }

     

    Any exceptions thrown in this are handled in the calling function

     

     

    Because my DataContext is being disposed regardless of any exceptions, will the connection be cleaned up as well(and closed), or do I need to plan against the possibility of something in the middle using block failing, and a connection left hanging open?

    Friday, March 28, 2008 1:55 PM

Answers