Enterprise Library, Data Access Application Block (DAAB), Best Practicises Question

[Mr Brown Shoes]

Hi, From an architectural perspective is it best to use the DatabaseFactory.CreateDatabase() method once at the top of a method call stack, and pass its reference to each method in the stack that needs database access, or should each method in the data-layer use the DatabaseFactory.CreateDatabase() method internally. Clearly the first approach uses less resources, as quite a lot of code is utilised by the DatabaseFactory.CreateDatabase() method, including reading the web.config file, however the second approach keeps the Data Layer tightly cohesive and loosely coupled from other sections. For example let’s examine a web service solution. Assuming that the service document has a web method, which in-turn calls a business facade. The facade then forwards necessary information to one or more 'information managers'. These then utilise 'data managers' which provide scalar values, Datasets etc required for their calculations. Should each method in the data managers use DatabaseFactory.CreateDatabase() to get a connection to the DB for executing various commands, or should this be called once at the business facade level and a reference passed to each Info manager which in-turn pass to respective data managers? Could anyone make a recommendation based on experience? Thanks in advance.

[AhClem]

First, you can use the Application State or the Caching Application Block to avoid going back to the Web.config file repeatedly. Depends somewhat on whether you're doing WinForms or ASP.Net. Since you're talking about Web.config, it's obviously the latter. If you have a long chain of user controls as I do, you sure don't want every one of them creating and destroying their own database objects. I think Microsofts intention was pretty obvious when they modeled the database objects on the toolbar as something that you'd drag onto your page. Connections aren't cheap and your database server probably has a limit on the number of concurrent connections permitted so you don't want any more open at one time then necessary. Also, you stand a really good chance of deadlocking against yourself. Will

[Mr Brown Shoes]

Thanks for the reply Will, The application in question is a web services project that returns data to both an ASP.NET and Windows Forms App so your recommendation won't really apply. At first I tried dragging and dropping connections and data adapters etc onto my web services file but I found the code generated to be very untidy. I instead opted to have my web service class contact a business facade, which then contacts other classes in a business layer, which finally contact a database layer where I have utilised the DAAB. So to further define my question; do I create the Database object in the global.asax session start method, and then use the reference in each class required in the data layer? Do I create the connection each time a service is requested and pass it down to each class required in the data layer? Or do I just use the DatabaseFactory.CreateDatabase() method when it is required within each method of my data layer classes? I notice that when using a Database object supplied by the DAAB that you do not specify the connection string when you use a method such as 'ExecuteNonQuery', this suggest to me that the connection can be managed at this lower level.

[AhClem]

First, you'd probably have better luck with this question in Web Development. . I haven't done any Web Services so I don't know the environment. However in general, if you know you're going to need the connection for five calls to the database, it's a lot cheaper if you don't build and destroy the database objects and connect and disconnect five times (although I don't have a good feel for what connection pooling buys). On the other hand, if you have any sort of volume then you don't want to tie up the database resources a moment longer than necessary. So, if a Session lasts until it times out for a Web Service(?), I wouldn't allocate the connection at the Session level. Will

[Mr Brown Shoes]

K, I had a similar architecture to what you’re advocating before I moved to use the DAAB. I'm looking for advice more specific to the DAAB. For example, putting the Database object in the session will not put the connection in the session, the database object provides a method to create and return a connection based on settings in the web config file. And when you call a method on the Database object, such as ExecuteNonQuery, you don't explicitly give a connection. I assume that it creates one internally based on the config file. So given the examples on using the DAAB that I have seen, there's no way to share a single connection between multiple queries without the use of a transaction or additional verbose code. Based on this I guess the best thing to do is put the database object in the session and then call methods such as LoadData and ExecuteNonQuery where necessary, Let the DAAB handle the connections completely.

[AhClem]

M'Kay, I see your point. I always assumed that there was a Connection object that was embedded within the Database object. That's NOT the case. If you RTFC then you'll see that each invocation of one of the wrappers "new"s its own connection (at least for SQL Server). Personally, I was happier in my deluded ignorance. I guess that means that the only way to economize is to exploit connection pooling. The various ADO.Net books seem to do a good job of covering that subject in detail. Of course, there's nothing stopping you from altering or building upon the Library except having to maintain your enhancements in future versions. In a sort of poor man's DAAB I worked on at one client site, I changed a "DatabaseHelper" class (which did have an embedded Connection object) so that it left the connection in the state it found it. If an explicit Open was performed, it stayed open until an explicit Close was performed. This runs the risk of Connection leaks if you aren't careful. Perhaps that's why the authors of the Library chose the approach that they did? Will