any ado.net experts please?

  • Thread starter Thread starter trisha
  • Start date Start date
T

trisha

my question is about transactions...

i have a base class which handles executing an specified sp from a sql
database and returns data. it also handles the connection, command objects
etc.

my data layer classes inherit the base class and return necessary data (eg:
AddCustomer, AddOrder, AddOrderItem, etc). There are 3 separate classes in
this case: Customer, Order, and OrderDetail.

my question is: what is the best way to handle transactions in the above
scenario? the transaction examples in msdn all refer to a local transaction
variable, but i want my transaction object to persist across 3 data layer
classes. for example, i what do i do if i want to add order items, add
order and add customer all in one transaction. this will most probably be
logic which exists in my business layer.

does anyone have any working solutions for the above scenario?

thanks a lot.
 
Trisha,

If your transactions span entities more than ONE database, you cannot use
ADO.NET transactions, and you have to use COM+ transactions or
System.Transactions in .NET 2.0. More than one databases can be enlisted in
distributed transactions using the "Begin Distributed Transaction" command,
however that is not .NET specific.

However, most applications are designed to abstract the need for having
multiple classes enlist in COM+ distributed transactions. The reason for
this is because COM+ transactions are expensive and should be used not as
frequently. This is acheived by packaging your datastore access into
transactions and all conflicts being resolved within that period - not over
business layer/data layer/ui layer.

Hope this helped.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
 
I have a similar implementation to you with regards to your base class data component(DAL) which is inherited by multiple Data Layer classes (DALC). I've found that with Manual transactions I cannot escape creating a SQL Connection and a SQL Transaction from my Business Layer. I've written a component that encapsulates bot hthe connection and the transaction. It can be passed to any DALC, which passes it directly to the DAL. Should a connection / transaction already exist my DAL simply uses it. If non exist it will create a New SQL connection and SQL transaction (if needed)

Here is how your business logic might look with this component

Public void AddOrder(doube customerId, XmlDocument Order

Tr
 
I have a similar implementation to you with regards to your base class data component(DAL) which is inherited by multiple Data Layer classes (DALC). I've found that with Manual transactions I cannot escape creating a SQL Connection and a SQL Transaction from my Business Layer. I've written a component that encapsulates bot hthe connection and the transaction. It can be passed to any DALC, which passes it directly to the DAL. Should a connection / transaction already exist my DAL simply uses it. If non exist it will create a New SQL connection and SQL transaction (if needed).

Here is how your business logic might look with this component:


Public void AddOrder(doube customerId, XmlDocument orderLineItems)
{

//Your Transaction object should Implement IDisposable
using(MyTransactionObject oTran = new MyTransactionObject ("RequiresTransaction"))
{

Try
{
//Create and Order Object and pass your Transaction to it
OrderDalc orderDalc = New OrderDalc(oTran);
double orderId = orderDalc.AddNewOrder(customerId);

//Create an Order LineItem object and pass your transaction
to it...
LineItemDalc lineItem = New LineItemDalc(oTran);

foreach(XmlElement LineItemXml in orderLitenItems.SelectNodes
(LineItems/LineItem))
{
lineItem.AddNewLineItem(orderId,
LineItemXml.getAttribute("ItemId"),
LineItemXml.getAttribute("Quantity"));
}

oTran.CommitTransaction();
}
catch
{
oTran.RollBackTransaction();
}
}
}


Hope this helps. We have chosen not to use Enterprise services / COM+ as we do not connect to more than one Physical Database.
 
Hi Mike,

Your idea looks pretty interesting as I also have this kind of needs.
Could you give us the source code of the MyTransactionObject class? I am not
very familiar with IDisposable and i do not know how to implement it.
If you cannot give us the code for that helper class then maybe a few hints?
Also what is the string "RequiresTransaction" that you pass in the
constructor for?

Tx a lot,

Francois.

michaeltours said:
I have a similar implementation to you with regards to your base class
Click to expand...
data component(DAL) which is inherited by multiple Data Layer classes
(DALC). I've found that with Manual transactions I cannot escape creating a
SQL Connection and a SQL Transaction from my Business Layer. I've written a
component that encapsulates bot hthe connection and the transaction. It can
be passed to any DALC, which passes it directly to the DAL. Should a
connection / transaction already exist my DAL simply uses it. If non exist
it will create a New SQL connection and SQL transaction (if needed).
Here is how your business logic might look with this component:


Public void AddOrder(doube customerId, XmlDocument orderLineItems)
{

//Your Transaction object should Implement IDisposable
using(MyTransactionObject oTran = new MyTransactionObject ("RequiresTransaction"))
{

Try
{
//Create and Order Object and pass your Transaction to it
OrderDalc orderDalc = New OrderDalc(oTran);
double orderId = orderDalc.AddNewOrder(customerId);

//Create an Order LineItem object and pass your transaction
to it...
LineItemDalc lineItem = New LineItemDalc(oTran);

foreach(XmlElement LineItemXml in orderLitenItems.SelectNodes
(LineItems/LineItem))
{
lineItem.AddNewLineItem(orderId,
LineItemXml.getAttribute("ItemId"),
LineItemXml.getAttribute("Quantity"));
}

oTran.CommitTransaction();
}
catch
{
oTran.RollBackTransaction();
}
}
}


Hope this helps. We have chosen not to use Enterprise services / COM+ as
Click to expand...
we do not connect to more than one Physical Database.
 
It's not fully functional yet but here's something to get you going

Create a public enum called TransactionMode in your DataTier with 2 Values: Non Transactional / Transactional (call them what you like

Create a Class called TransactionContext (Implement Idisposable

public class TransactionContext : IDisposabl

private SQLConnection _conn
private SQLTransaction _tran
private eTransactionMode _transactionMode
private string_Database



The class Constructor should take a Parameter of TransactionMode (best to set the mode during construction. Can also setup a Default Database at this point as most transactions persist over 1 Database. I created an overloaded constructor so that Database was not required but possible at construction.

//Constructo
public TransactionContext(eTransactionMode transactionMode, string database

_transactionmode = transactionmode
_database = database


The class should encapsulate a SQL connection and a SQL Transaction, and be able to return references to both. (However in my class they can only be created internally. There is no property SET for either of these members.

The Interface should be able to Connect(and begin transaction if the mode is required). It should understand that if a connection / transaction exists it does not need to start new ones, it can just use the existing ones

You should also have COMMIT Method and a ROLLBACK Method. Both are straight forward

The DISPOSE method (required since you're implementing IDisposable) just needs to check if either the Connection is not NUll or the Transaction is not null and call the dispose method on each

public dispose(

if(_conn!=null
_conn.dispose()

if(_trans!=null
_trans.dispose()


Following the example I gave you earlier, the Business logic is really easy. You just create a Transaction context object (NOTE: that if you dont contain it within a "using" statement you MUST call the dispose method manually as this will release the connection and transaction objects internally. If you don't then those unmanged resources might stay open very long !!

Then, all you gotta do is pass the context to your Data Layer classes (I do it during construction so that for each Datalayer class it only need to be passed once.) As each Data layer class inherits your Base Class, you can just pass that transactioncContext to the base class at construction

Your base class should have no need for a SQL connection or a SQL Transaction now as these are inside your Context (Transaction Conext should be a private member inside your DAL). If you are making a Non transactional call and you did not need the Transaction context and did not pass it in the your DAL can just create a New one for the call, anddispose of it directly after. If you specify a non transaction mode then only a connection should be created and not a transaction object too

Inside your DAL you'll most likely have a method to execute a stored procedure. Inside that method you can simple pass the SQL Command object the connection from the transactionConext and do the same for the transaction

Then, just call Commit or Rollback in your business logic

Thats about it .... I was really bored at work so that's why I typed all this out .... It's better for you to figure out yourself how this all fits together !!!!! hope this helps..
 
Thanks a million for that Mike, now I understand most of it. But I still
don't understand why the TransactionContext class must implement the
IDisposable interface...
As a normal user of transactions and connections, I never need to call
connection.dispose() manually neither transaction.dipose()

Then why in this case we need to do that? I had a look at the IDisposable
interface in the MSDN doc but they just say: Defines a method to release
allocated unmanaged resources.
But as far as i know Transactions and connection are managed resource as
they are part of the framework.
Also when does the framework call the Dispose() method?

Sorry for having so many questions !

Thanks in advance,

Francois.

michaeltours said:
It's not fully functional yet but here's something to get you going:

Create a public enum called TransactionMode in your DataTier with 2
Click to expand...
Values: Non Transactional / Transactional (call them what you like)
Create a Class called TransactionContext (Implement Idisposable)

public class TransactionContext : IDisposable
{
private SQLConnection _conn;
private SQLTransaction _tran;
private eTransactionMode _transactionMode;
private string_Database;

}


The class Constructor should take a Parameter of TransactionMode (best to
Click to expand...
set the mode during construction. Can also setup a Default Database at this
point as most transactions persist over 1 Database. I created an overloaded
constructor so that Database was not required but possible at
construction. )
//Constructor
public TransactionContext(eTransactionMode transactionMode, string database)
{
_transactionmode = transactionmode;
_database = database;
}


The class should encapsulate a SQL connection and a SQL Transaction, and
Click to expand...
be able to return references to both. (However in my class they can only be
created internally. There is no property SET for either of these members.)
The Interface should be able to Connect(and begin transaction if the mode
Click to expand...
is required). It should understand that if a connection / transaction exists
it does not need to start new ones, it can just use the existing ones.
You should also have COMMIT Method and a ROLLBACK Method. Both are straight forward.

The DISPOSE method (required since you're implementing IDisposable) just
Click to expand...
needs to check if either the Connection is not NUll or the Transaction is
not null and call the dispose method on each.
public dispose()
{
if(_conn!=null)
_conn.dispose();

if(_trans!=null)
_trans.dispose();
}

Following the example I gave you earlier, the Business logic is really
Click to expand...
easy. You just create a Transaction context object (NOTE: that if you dont
contain it within a "using" statement you MUST call the dispose method
manually as this will release the connection and transaction objects
internally. If you don't then those unmanged resources might stay open very
long !!!
Then, all you gotta do is pass the context to your Data Layer classes (I
Click to expand...
do it during construction so that for each Datalayer class it only need to
be passed once.) As each Data layer class inherits your Base Class, you can
just pass that transactioncContext to the base class at construction.
Your base class should have no need for a SQL connection or a SQL
Click to expand...
Transaction now as these are inside your Context (Transaction Conext should
be a private member inside your DAL). If you are making a Non transactional
call and you did not need the Transaction context and did not pass it in the
your DAL can just create a New one for the call, anddispose of it directly
after. If you specify a non transaction mode then only a connection should
be created and not a transaction object too.
Inside your DAL you'll most likely have a method to execute a stored
Click to expand...
procedure. Inside that method you can simple pass the SQL Command object the
connection from the transactionConext and do the same for the transaction.
Then, just call Commit or Rollback in your business logic.


Thats about it .... I was really bored at work so that's why I typed all
Click to expand...
this out .... It's better for you to figure out yourself how this all fits
together !!!!! hope this helps...
 
H

The IDisposable interface is just there to Force you to have a Dispose method. It is always recommended that if any object uses un-manged recources such as SQL server connections or File streams it should implement IDisposable.

Do a Test with SQL Connection. Open a Connection, and run something like sp_who2 on Query analyser. Notice the open connection. If you run .close() on your connection it should not display in query analyser anymore. Same should happen if you open it, and then call .dispose(). However, if you open the connection and let the object that made the call run out of scope , the connection will still be held on the database because you did NOT specifically close or dispose of the connection. It is always recommended that if any object has a disppose method it should be called. That is what the "using" statement is there for, because it garuntees to call an objects dispose method (providing that object implements IDisposable!)

So, without question any SQLClient object with a dispose method are using unmanaged resources(Connection, transaction etc etc ...), and dispose should always be called. My Transacton Context object just encapsulates SQL Transaction and SQL Connection so it's dispose method doesn't need to do anything other than called the dispose methods on both these objects

Hope that helps !
 
Ok I got it now, and I saw that the implementation Connection.Dispose() is
not doing much more than calling Connection.Close(). Then as you say you can
have a more elegant code by calling the TransactionContext.Dispose() method
instead of calling the method TransactionContext.Connection.Close() as the
class TransactionContext is an encapsulation of a connection object.

About the transaction object, did you know that all Transaction.Dispose() is
doing is to call Transaction.Rollback()?

Tx a lot again for your explanation.

Francois.


michaeltours said:
Hi

The IDisposable interface is just there to Force you to have a Dispose
Click to expand...
method. It is always recommended that if any object uses un-manged recources
such as SQL server connections or File streams it should implement
IDisposable.
Do a Test with SQL Connection. Open a Connection, and run something like
Click to expand...
sp_who2 on Query analyser. Notice the open connection. If you run .close()
on your connection it should not display in query analyser anymore. Same
should happen if you open it, and then call .dispose(). However, if you open
the connection and let the object that made the call run out of scope , the
connection will still be held on the database because you did NOT
specifically close or dispose of the connection. It is always recommended
that if any object has a disppose method it should be called. That is what
the "using" statement is there for, because it garuntees to call an objects
dispose method (providing that object implements IDisposable!).
So, without question any SQLClient object with a dispose method are using
Click to expand...
unmanaged resources(Connection, transaction etc etc ...), and dispose should
always be called. My Transacton Context object just encapsulates SQL
Transaction and SQL Connection so it's dispose method doesn't need to do
anything other than called the dispose methods on both these objects.
 
Hi didin't know that about it, but as dispose is called after connect I presume the implementation of the transaction object knows this too. As a practice I always call an objects dispose method if it has one !!!
 
Back
Top