DAAB in Enterprise Library for .NET 2.0


Here we go again – another Data Access Application Block (DAAB). Those of you who have been following our team and using our stuff for a while probably know that DAAB was the first code-based deliverable that we produced, and was also the first ever “application block”. And while many of our later blocks offer more functionality and address more glamorous scenarios, DAAB still remains our most popular application block today. Why? First, data access is something everybody spends a lot of time on. Second, the block has always been simple. Sure, it’s got a lot more to it that the original DAAB (which had just one class, SqlHelper), but it’s still very easy to learn and use.


Design Goals


The overall rationale behind the DAAB has always been the same: to cut down the amount of code you need to write for the most common data access scenarios. It does not attempt to hide ADO.NET or implement all ADO.NET scenarios, and all of the power of ADO.NET is always available to use from the DAAB. However we’ve found that most developers, most of the time, are doing the same basic data access tasks. The DAAB cuts down the amount of code you need for these tasks, and in the process cuts down on common mistakes such as failing to close the connection, or not storing and protecting connection strings correctly.


The upcoming release in Enterprise Library for .NET Framework 2.0 is a relatively minor update to the existing Enterprise Library DAAB. It will support the same key scenarios, but it’s been redesigned to leverage some new features in .NET Framework 2.0, and to sport a simpler architecture. In addition, we’ve made some minor changes to the block based on your feedback, in particular around being able to use the block without configuration files. Most of these changes are already in place in the August CTP release (available on the EntLib Community site), but we still have a bit of work to do, especially around the configuration design-time and instrumentation.


Key Changes


So what’s new in the upcoming release? Here are some of the bigger changes:


Use with or without configuration


The first two versions of DAAB didn’t provide any support for connection string management; this was left as an ‘exercise for the reader’. The first Enterprise Library releases addressed this potential problem by using the Configuration Application Block to manage the connection strings, requiring developers to use logical instance names from their code. While this solution worked well for most developers, we’ve been told we swung the pendulum too far in the other direction by not providing an easy way for people to manage their own connection strings, for example where these are created dynamically.


So the great news is that the new DAAB will support the best of both worlds. You’ll still be able to create Database references using either a default or named instance, using the same familiar syntax:


Database db = DatabaseFactory.CreateDatabase();
Database db = DatabaseFactory.CreateDatabase(“Northwind”);



However, if you know the type of database you want, and the connection string, you will be able to ‘new up’ the Database objects directly, without using configuration at all:



SqlDatabase sqlDatabase = new SqlDatabase(myConnectionString);
GenericDatabase db = new GenericDatabase(connectionString, OdbcFactory.Instance);



(More on the GenericDatabase later). Once you have your Database instance, you can use it the same way, not matter how you got it.


Integration with the <connectionStrings> section


.NET Framework 2.0 includes a new <connectionStrings> section in the configuration files, which looks like this:



<connectionStrings>
  <add name=”Sales”
       providerName=”System.Data.SqlClient”
       connectionString= “server=myserver;database=Products;uid=salesUser;pwd=sellMoreProducts” />


  <add name=”NorthWind” 
       providerName=”System.Data.SqlClient” 
       connectionString=”server=.;database=NorthWind;Integrated Security=SSPI” />
</connectionStrings>



This information can also be easily accessed programmatically using ConfigurationManager.ConnectionStrings. It doesn’t take a genius to see that this configuration information is pretty similar to what we already have in Enterprise Library today. So rather than give you two different ways of managing database connections, the new DAAB will make use of this section. DAAB will still have some optional configuration sections for some advanced functionality like Oracle package mapping, but for most scenarios this simple section is all that you need. (Note: this feature isn’t implemented in the August CTP release, which still uses its own section for the connections, but this will be changed in the final release).


Updated design with ADO.NET 2.0 features


ADO.NET 2.0 provides some important new features that have allowed us to simplify the design of the DAAB. For example, the new DbProviderFactory and its provider-specific subclasses are now used to create the commands and connections used by the block. In addition, to simplify the architecture we have eliminated DbCommandWrapper class in the previous Enterprise Library DAABs. As a result we have moved the methods that were on DbCommandWrapper have been ‘promoted’ to the Database class. This should be the only breaking change from the previous Enterprise Library DAAB.


GenericDatabase


In the previous Enterprise Library DAAB versions, it was necessary to have an EntLib Database-derived class for every ADO.NET managed providers. We included Database classes for SQL Server, Oracle and DB2, but if you used any other managed providers, you were out of luck (or at least needed to find or build your own Database class).



In the new version, we still have the SqlDatabase and OracleDatabase class – but we also have a new GenericDatabase class. The GenericDatabase can be used with any .NET managed provider (including the ODBC and OLE-DB providers that ship with .NET) – but the catch is that it doesn’t support all of the DAAB functionality. Most significantly, the overloads that use parameter discovery do not work (since these methods aren’t supported on .NET’s DbConnection or DbCommand classes). There’s still the option to build your own Database-derived classes to support this advanced functionality and provide improved database transparency – this is why we built SqlDatabase and OracleDatabase – but at least you’ll be able to use most of the block against any type of database.



And in case you were wondering what happened to DB2, at the moment we are not aware of any ADO.NET 2.0-compatible managed providers. Hopefully these will exist soon, but in the meantime OLE-DB should be an option.


Design Diagram


The following diagram shows the key classes in the new Data Access Application Block.

DAAB Design

I hope this description has been helpful. Feel free to provide feedback on this post or on the EntLib Community site.

Comments (25)
  1. El Bruno says:

    Great post !!!

    mmm … great idea to create cnn and db from connection strings, and I like the Generic DataBase to implement my own provider 😀

    Bye from Spain 😀

  2. Julio Casal says:

    Project LINQ has just been released. As it is "and a unified programming model that extends the .NET Framework to offer integrated querying for objects, databases and XML", has you considered it for these or future releases of the Data Access Application Block?

  3. Great news!

    Unfortunately, I used dbCommandWrapper a lot, so that will change my code. Sounds like a S&R on dbCommandWrapper for database might work though … can you post example code that would replicate this:

    dbCommandWrapper = eligibilityDB.GetStoredProcCommandWrapper("usp_LookupCandidateSummaryViewSort", department,sort,order);

    (And hopefully, we can still insert parameters in directly).

  4. Julio – We are definitely aware of project LINQ and excited by its potential. We don’t know exactly how this will impact the DAAB yet, but we’re starting to think about this now.

    Adam – we’ll still have these methods, but they will return DbCommands instead of DbCommandWrappers.

    Tom

  5. Scott Rudy says:

    Oracle seems to be more and more supportive of .NET lately (more proof that it’s enterprise enabled). Will you be supporting Microsoft’s Oracle Provider, Oracle’s Provider or both?

  6. Hi Scott –

    We’re only testing against Microsoft’s Oracle Provider, and our OracleDatabase class uses the MS provider. It should be possible to use Oracle’s provider with the GenericDatabase, but we haven’t tested this.

    Tom

  7. angel says:

    Since you guys already provide a way to instantiate a DB wrapper by name using:

    Database db = DatabaseFactory.CreateDatabase("Northwind");

    Have you considered providing the same sort of functionality for Commands? I mean, having commands described in an xml config file, and instantiating them in code by name using a similar syntax?

  8. Marc van Balen says:

    Excellent improvement over the "old" DAAB.

    I have been using (testing) the Data Access Block since the first release and found the configuration a bit cumbersome, specially in our case where we had to "implemented" the Role/membership/Personalization…. etc providers for 2.0 using the DAAB.

    Migrating from old to new using dbCommand in stead of dbCommandWrappers went quite smoothly.

    Thanks very much for all your effort on this!!!

  9. John McMillion says:

    Thank you so much for the improved Conn Str handling. I am really interested in seeing if there will be any cool functions added to DAAB for the new TransactionScope functionality in 2.0. Right now, I’m passing a hashtable of sql statements to a function that saves a distributed transaction with nested database updates within a TransactionScope block.

    Thanks for all of your efforts!

  10. With the previous configuration format; the EntLib allowed to encrypt parameters. This was very useful to encrypt the database password in the configuration file. Especially in my environment, where the password is only know by DBAs for the Production database.

    It seems that with the 2.0 config format, we loose that capability. I am missing something?

    On a different topic, an IBM sell rep confirms that a DB2 dotnet manage provider for 2.0 will be available in November.

  11. Tom says:

    Hi Emmanuel –

    Thanks for raising the encryption question – I should have discussed this in my post. .NET now natively supports encrypted configuration. There’s an example from the .NET docs showing how you can encrypt your connection strings (look up "Protected Configuration" in the index). The same approach will work for all EntLib config sections, since they are just "regular" .NET configuration sections now.

    Thanks for the heads-up on IBM’s DB2 provider. We won’t be able to do any testing on this before we ship, but hopefully it will work with our GenericDatabase (and if not, it should be easiy to wrap it with a new DB2Database class).

    Tom

  12. draduc says:

    there is any way to setup the command time out in one single place and to use it allover the project?

  13. Tom says:

    draduc – this is something on our list to look at, but we may not get to it. It’s more complicated than you might expect, since the logical place to store this would be on the connection string definition, but the configuration schema for this section isn’t extensible.

    Tom

  14. Jim Kirch says:

    Unfortunately, I see that I will have some real difficulty with the removal of the DBCommandWrapper class.

    I have taken to creating methods on my DataAccess classes that return DbCommandWrappers so that the client can have the option of getting results as an IDbReader or a DataSet.

    ie. dao method

    DBCommandWrappr FindByIdCommand(int id)

    client code then looks something like this:

    Database.ExecuteReader(dao.FindByIdCommand(1))

    or

    Database.ExecuteDataset(dao.FindByIdCommand(1))

    Not only are my data access classes impacted, but is all the code that uses them. Ugh!

  15. Bob says:

    Is IBM DB2 still supported?

  16. Bob – we won’t directly support DB2 out of the box, since we aren’t aware of any ADO.NET 2.0 managed providers from either Microsoft or IBM (although I’m sure they will ship before too long). In the meantime it will be possible to connect to DB2 via OLE-DB.

    Tom

  17. Hi Tom,

    Sounds good, but I have a query. ASP.NET 2.0 introduces Membership, are we able to use an enterprise library "Database" as a provider for the membership? Or will membership have to be completely separate from the usual business DB transactions?

    Cheers

  18. Hi Husain –

    The ASP.NET Membership system works with a provider model similar to the way that Enterprise Library works. Out of the box the platform supplies a SqlMembershipProvider and an ActiveDirectoryMembershipProvider. As its name suggests, the SqlMembershipProvider uses SQL Server, rather than using the database-agnostic Database class from EntLib (although it does use the <connectionStrings> section that the DAAB uses, so all of your connections are stored in one place and you can access the same database from DAAB).

    Anyway, since Membership uses a provider model, it would be possible for you to build your own MembershipProvider-derived class that uses the DAAB if you want to support other databases.

    Tom

  19. frank says:

    Hi Tom

    I know it’s not realted to DAAB but it’s EntLib realted.

    I added another config section in the app.config file using the config tool. Everything works fine.

    When I set the Encrypt=true for this section in the config tool and save it, I expect the config file for that section will be encrypted just like dataConfiguration.config file. But it’s not. When I run my app to retrieve the config setting, I got "length of data to decrypt is invalid".

    Am I missing something. BTW, the encryption key file is generated and dataConfiguration is encrypt/decrypt fine.

  20. matt says:

    Would also be nice if you could instantiate a Database object by providing a provider name and connection string.

  21. Hi Matt –

    Yes you’ll be able to do this. If you have an EntLib Database class for your chosen Database provider, you just new up the right Database and specify the connection string, eg

    SqlDatabase db = new SqlDatabase(connectionString);

    If there isn’t an EntLib Database class, you can use the GenericDatabase and give it the provider as well as the connection string:

    Database db = new GenericDatabase(SomeStrangeDbProviderFactory.Instance, connectionString);

    Tom

  22. Lagear says:

    I had a little problem when I tried to connect to my oracle database (9iR2), there is the error:

    Unable to cast object of type ‘System.Collections.Generic.List`1’ to type ‘System.Collections.Generic.IList`1’.

    there is a part of my code:

    ////

    IList<IOraclePackage> oraclePackages = (IList<IOraclePackage>)(new List<OraclePackage>());

    Database db = new OracleDatabase("Data Source=MYSERVER.MYDOMAIN.COM;User ID=xxxx;Pasword=yyyyy", oraclePackages);

    /////

    there is a implement of IOraclePackage:

    /////

    class OraclePackage : IOraclePackage

    {

    string _name, _prefix;

    OraclePackage()

    {

    _name = string.Empty;

    _prefix = string.Empty;

    }

    public string Name

    {

    get { return _name; }

    set { _name = value; }

    }

    public string Prefix

    {

    get { return _prefix; }

    set { _prefix = value; }

    }

    }

    /////

    How I most to implement an instance of IOraclePackage?

    regards.

  23. What about using the new TableAdapter (ADO.NET 2.0) within the new ENTERPRISE LIBRARY for .NET 2.0?

    CESAR DE LA TORRE

    ctorre@renacimiento.com

Comments are closed.