Using Enterprise Library DAAB and Microsoft Access 2007 via ODBC and OLEDB

Using Enterprise Library DAAB and Microsoft Access 2007 via ODBC and OLEDB - Connection Strings and ADO.NET

by David Hayden ( Microsoft MVP C# ), Filed: Enterprise Library 2.0, Enterprise Library 3.0, ADO.NET 2.0

Most developers don't realize that the Enterprise Library Data Access Application Block ( DAAB ) can connect to any database that has a .NET Data Provider. One of the common questions is - “Does Enterprise Library support Microsoft Access?“ The answer is yes! You can use OLEDB or ODBC to connect to the Microsoft Access Database using the Data Access Application Block.

In this example I will be using the DAAB to connect to a Microsoft Access 2007 Database. The connection strings for Microsoft Access 2007 are as follows for my example:

OLEDB - "Provider=Microsoft.ACE.OLEDB.12.0;
         Data Source=MyDatabase.accdb;"

ODBC - "Driver={Microsoft Access Driver (*.mdb, *.accdb)};
        DBQ=MyDatabase.accdb"

There are additional options to add a user id and password, etc. In my example, I just created the database, called MyDatabase.accb, in the same directory as my executable and didn't add any security.

DAAB and Microsoft Access 2007 Using OLEDB

Let's first use the Enterprise Library Data Access Application Block with Microsoft Access 2007 using OLEDB. Here is the app.config to support such a configuration:

<xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="dataConfiguration"
          type="Microsoft.Practices.EnterpriseLibrary.
               Data.Configuration.DatabaseSettings,
               Microsoft.Practices.EnterpriseLibrary.Data,
               Version=2.0.0.0, Culture=neutral,
               PublicKeyToken=null" />
  </configSections>
  <dataConfiguration defaultDatabase="MicrosoftAccess" />
  <connectionStrings>
    <add name="MicrosoftAccess"
         connectionString="Provider=Microsoft.ACE.OLEDB.12.0;
                           Data Source=MyDatabase.accdb"
         providerName="System.Data.OleDb" />
  </connectionStrings>
</configuration>

I can now use the Database Class to grab all the Customers from the Customers Table as follows:

Database database =
   DatabaseFactory.CreateDatabase();

DbCommand command =
   database.GetSqlStringCommand("SELECT * FROM Customers");

DataSet customer = database.ExecuteDataSet(command);

DAAB and Microsoft Access 2007 Using ODBC

Now let' use the Enterprise Library Data Access Application Block with Microsoft Access 2007 using ODBC. No programming changes necessary! Just change the app.config file to specify ODBC:

<xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="dataConfiguration"
        type="Microsoft.Practices.EnterpriseLibrary.
              Data.Configuration.DatabaseSettings,
              Microsoft.Practices.EnterpriseLibrary.Data,
              Version=2.0.0.0, Culture=neutral,
              PublicKeyToken=null" />
  </configSections>
  <dataConfiguration defaultDatabase="MicrosoftAccess" />
  <connectionStrings>
    <add name="MicrosoftAccess"
         connectionString="Driver={Microsoft Access Driver
                           (*.mdb, *.accdb)};
                           DBQ=MyDatabase.accdb"
         providerName="System.Data.Odbc" />
  </connectionStrings>
</configuration>

The code will stay exactly the same as above:

Database database = DatabaseFactory.CreateDatabase();

DbCommand command =
   database.GetSqlStringCommand("SELECT * FROM Customers");

DataSet customer = database.ExecuteDataSet(command);

Conclusion

As much as I love programming with ADO.NET 2.0, the Enterprise Library Data Access Application Block is a great time saver to help you avoid having to deal with all the ADO.NET plumbing. The same is certainly true when using the DAAB with Microsoft Access 2007 using OLEDB or ODBC.

Source: David Hayden ( Microsoft MVP C# )

Filed: Enterprise Library 2.0, Enterprise Library 3.0, ADO.NET 2.0

posted on Sunday, January 28, 2007 12:16 PM