Enterprise Library Data Access Blocks Design Decisions

Question

The back story to this is a coworker didn't like the fact we were standardization on Enterprise Library Data Access Blocks because

• It required too many references in every project that needed database access
• We didn't need all the features it provided
• He thought the DbCommand / SqlCommand should be stored internally in the database objects, instead of having to let database build a sqlcommand and requiring the user to manage its state externally
• He didn't like that you had to specify the type when adding a parameter, he thought the overloads should infer the type for you. *Didn't like that Enterprise Library was generic for all databases, when our system was clearly only ever going to be Sql Server compatible anyways

I personally wanted to keep using enterprise library instead of his home grown solution, but it did cause me to ask some questions.

Why doesn't Enterprise Library or other Database Abstraction Layers provide AddParameter overloads for basic types?

Example

   db.AddInParameter(dbCommand, 
     "EmployeeID", DbType.Int32, 1);

What would be the reason, or some of the reasons why they don't just provide overloads for all the datatypes like int, instead of only taking an object and forcing the user to specify the type.

   db.AddInParameter(dbCommand, "EmployeeID", 1);

Some reasons that I can think of are...

If you don't specify every single type mapping as an overload the following situation can happen.

Lets say you have an overload for int but not char

char c = 'R'

db.AddInParameter(dbCommand, "Initial", c);

The compiler will resolve the overload to int instead of char and throw an error because the stored procedure expeected the type to be char.

Another implication is that If I want to mock the Database class I now have a massive interface of overloads I need to implement instead of just one.

My other big question was..

Why does Data Access block require you to retrieve the command object, then pass it back in for subsequent calls instead of just managing its state internally?

using (var cmd = db.GetStoredProcCommand("AddEmployee"))
{
      db.AddInParameter(cmd, "@Name", DbType.String, name);

instead of

using(var db = new Database())
{

      db.CreateStoredProcCommand("AddEmployee")

      db.AddInParameter("@Name", DbType.String, name);

I'm interested in seeing what you guys think.

Thanks

Answer 1

Hi

note that Enterprise Library Data Access Blocks is not a new data access framework, it's a wrapper for normal ADO.NET which does many stuff for you automatically needed in large enterprise applications. So it only saves some lines of code. The technique behind is still simple ADO.NET. So please can you please give some more information about your problem? As far as i can see you simply don't like the API syntax of Enterprise Library Data Access Blocks.

Answer 2

First question :

This is just a guess, but I suspect that it's because ADO.NET doesn't provide that functionality.

Second question :

DbCommands are database-provider-specific, so you need a factory method somewhere to instantiate them.

As for storing the command and its parameters in the Database object, Database objects are designed to not contain command-specific state. This enables them to be reused. A common pattern is to instantiate a Database object once at application startup, and call its GetXXXCommand() methods as required during the application's lifetime. Also, multi-threaded applications (eg web sites) can safely instantiate DbCommands from a single Database object, even when serving many concurrent requests.

If the Database object stored an implicit DbCommand, cross-thread sharing of Database instances would no longer be possible.

Finally, even in single-threaded applications, it's perfectly possible to construct multiple DbCommands, or not have a one-to-one mapping between calls to GetXXXCommand() and calls to ExecuteXXX(). You could for example execute them many times, each time with different parameters.