How to handle connections in data access layer ( .net )

Question

Hi I am writing a data access layer. I am confused about managing connections in the system. I know that .net uses connection pooling. But I do not want to open and close database connections in all dml operations or in all sql query's. How can I handle this? Where and when ( maybe in global asax which uses data access layer or in the data access layer ) the connections should be managed?

Answer 1

I want to open connection once. But I am confused about how can I do this? ( Data access layer can be used in windows forms but generally it will be used in asp .net web applications. )

Answer 2

Why don't you want to open/close the connection for every discrete logical operation? Most existing DALs behave that way. Usually it's not a good idea to try to outsmart what the runtime will do for you automatically, such as intelligently manage your connections. You should have a strong, demonstrable technical need before putting in the time and effort to add that complexity to your app.

Answer 3

You should open and close sql connections for each query, unless you are running a batch of statements.

"Open late, close early" is how you should always handle database connections.

If you are going about it in the traditional manner (making your own queries), MS has already written a nice data access interface. The enterprise library (application blocks) for data have all of the nice well formed bells and whistles.

If you dont want to bother with writing queries, I suggest you look at linq2Sql or linq2EF (preferred). They will greatly simplify your coding.

Answer 4

Connection management should not be managed by the DAL.

The one and only layer that can be responsible / can decide whether a new connection should be opened, or a connection should be closed, is the service layer or application layer which uses the DAL. That layer is the only layer that is aware of the context, and so this layer is where you can decide whether you should close a connection, or leave it open since there aer other DB communications that should use the same connection.

Answer 5

You should indeed open/close for every operation. Do not look at Connection use as a high-cost operation because it simply isn't. As you open connections on your site, they will be created in a connection pool. When you "close" the connection, the connection pool does not release the connection: it keeps it on-hand, ready for reuse. Thus, while the very first call resulting in a new connection takes a bit longer, the subsequent connections are very fast.

Update: this is particularly true in a web application! Do not attempt to open a connection just once in your global object and re-use it across all threads or your site simply won't work.

Answer 6

My advice after writing several datalayers in .Net (and a good few more in VB6 previously) is:

1. use datasets not readers if at all possible.
2. make and break the connections (you can't have >1 reader open on any one connection anyway).
3. do the work with paramaterised sps on the back end. 3.5 make sure all tables have a unique one-field primary key!

slightly OT?...

1. use code generation (yours or bought in) to produce ORM classes - but be aware that they are not the be-all and end all (the one table at a time stuff is handy - but can make you write ugly inefficent code where one query on the back end with joins or a cunning SP or view will do the job MUCH more nicely).
2. read up on the Transaction method of the Connection object - very very handy (though some purely db things that needs a transaction (e.g. a delete where there are relationships) should be on thcan be a transaction on the back end.

My own latest basic DAL (no ORM) took me half an hour to write and is compact and pretty efficient. The MS enterprisey stuff is HUGE!!!

One last thing - I personally think that strongly-typed datasets generated from xsds have a high annoyance (and bloat) to gain ratio - and the way they make you handle NULL sux big time. All the code you write to use them is bloaty too... or you end up downcasting them to DataSet to actually get a library of efficient, non-repetitive code.

Answer 7

ok I will open and close connection in every dml or select query run. kpollock can you send me your code? my aim is not copy all of them :) I have started to write something. and why microsoft do not use command pooling? mail : [email protected]

Answer 8

This will be updated.

Answer 9

I wrote my code. Can you write about my code? Errors or which features can I add it?

This is the last thing which is written in this topic but it is not seen at the bottom:(

Answer 10

What about operations that has to occur as a transaction?

It is your BL that does the operation and logic/ validation right?

Let's say you have a BL layer that

1. updates your customer account information. (DAL -> update customer record)
2. insert an adress record. (DAL -> insert adres)
3. validates your customer against an third object. (DAL -> get client & adres & validation object)

the outcome: customer not valid. So you have to roll back your transaction.

How would one go about solving this issue?

Answer 11

I'm not clear on the etiquette for answering old questions, and I couldn't figure out how to comment on another answer (I'm brand new to SO, and I'm not quite through my first cup of coffee today, so cut me some slack =]).

I always write my DALs to open/close connections with every query and let the driver's connection pooling do the work of connection management.

However, I have a multi-user desktop app that uses a shared MS Access DB (SQL Express wasn't around in a really usable form when this app was made), and I've occasionally seen errors indicative of corruption. This MS article recommends using only a single connection for the entire app:

"Repeatedly opening and closing a Microsoft Access database is not recommended. Open the database once at the beginning of the application, and then close the database at the end of the application."

My app is multi-threaded, so I'm assuming I'd have to revise the recommendation to "open one connection PER THREAD" to avoid collisions. Does anyone have any experience using shared Access databases with OLEDB and similar corruption issues?