Data access layer + sql connections

[Mark]

I am building my first .NET data access layer but have run in an problem and
would be interested in hearing opinions from more experienced .NET
developers.

I am building a data access layer that encapsulates my business entities.
For simplicity sake, say I have a .Load and a .Store method for each
business entity in each DAL. Each DAL then has it's own ConnectionString
property and therefore each .Load and .Store method opens it's own
SqlConnection and issues the relevant SQL commands. In theory there should
be no performance loss because of connection pooling.

The problem is that in the application layer I sometimes need to store
multiple business entites within a transaction. Since each DAL opens it's
own SqlConnection this can't be done (or can it?)

The way I am thinking of solving this is to create a helper class that
encapsulates the entire database. This class utilizes the DAL components and
passes a SqlConnection object to them which they can use to .Load and
..Store.

Does anyone have an opinions on the pro's and con's of passing an
SqlConnection object to the DAL instead of using a ConnectionString and
getting the DAL components to create their own SqlConnection?

 

[Frans Bouma [C# MVP]]

I am building my first .NET data access layer but have run in an problem and
would be interested in hearing opinions from more experienced .NET
developers.

I am building a data access layer that encapsulates my business entities.
For simplicity sake, say I have a .Load and a .Store method for each
business entity in each DAL. Each DAL then has it's own ConnectionString
property and therefore each .Load and .Store method opens it's own
SqlConnection and issues the relevant SQL commands. In theory there should
be no performance loss because of connection pooling.

The problem is that in the application layer I sometimes need to store
multiple business entites within a transaction. Since each DAL opens it's
own SqlConnection this can't be done (or can it?)

The way I am thinking of solving this is to create a helper class that
encapsulates the entire database. This class utilizes the DAL components and
passes a SqlConnection object to them which they can use to .Load and
.Store.

Does anyone have an opinions on the pro's and con's of passing an
SqlConnection object to the DAL instead of using a ConnectionString and
getting the DAL components to create their own SqlConnection?

You should implement a 'transaction' object to which you can add
entities, and this transaction object should contain the connection. The
entities then should use the connection of the transaction they're added
to (if any) for database activity.

Though, why are you re-inventing this wheel again if there are already
so many frameworks out there solving this for you? It's not as if a
solid DAL is written on an afternoon.

Frans

--

 

[Dumitru Sbenghe]

The simplest way to modify you DAL to solve you problem is to use Thread
Local Session pattern: open the session (connection, transaction) in your
application layer and the DAL will use the same session, without passing any
Connection object around.



In the same time, be careful and also consider Frans's advice; it's hard to
build a data layer.

Dumitru