Sponsored By Aspose - File Format APIs for .NET

Aspose are the market leader of .NET APIs for file business formats – natively work with DOCX, XLSX, PPT, PDF, MSG, MPP, images formats and many more!

Enterprise Library – Data Access Application Block (DAAB) – Patterns and Practices

I have mentioned a couple of other Enterprise Library Application Blocks so far:



Probably the most popular application block is the Data Access Application Block (DAAB).  For detailed information on this block, you can read the documentation.  However, in general, DAAB simplifies the task of reading and writing information to a database by providing a simple API and a library that provides access to the most frequently used features of ADO.NET.  It essentially handles the details of connection management, etc. and allows you to read and write data to the database with fewer lines of code and in a consistent manner.


As with all the application blocks in the Enterprise Library (download), you need to really familiarize yourself with the Enterprise Library Configuration Tool that is part of the download.  It helps create the necessary configuration files that allow each block to run accordingly.  Shown below is a snippet of the configuration tool allowing me to customize the configuration files for my local Sql Server and the Northwind Database.


Data Access Application Block


As I mentioned before, it looks involved and outputs a rather involved configuration file, but it took me all of about 5 minutes to create the necessary configuration files.  Other than the name of the database and server, you can accept most of the defaults which pop-up automatically as soon as you say you want to add the Data Access Application Block.  Aside from the information that needs to go in your app.config or web.config, here is the configuration file for the DAAB:


 


dataConfiguration.config
<?xml version=1.0 encoding=utf-8?>
<dataConfiguration>
<xmlSerializerSection type=Microsoft.Practices.EnterpriseLibrary.Data.
Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data,
Version
=1.0.0.0, Culture=neutral, PublicKeyToken=null>
<enterpriseLibrary.databaseSettings xmlns:xsd=http://www.w3.org/2001/XMLSchema
xmlns:xsi
=http://www.w3.org/2001/XMLSchema-instance defaultInstance=Northwind
xmlns
=http://www.microsoft.com/practices/enterpriselibrary/08-31-2004/data>
<databaseTypes>
<databaseType name=Sql Server type=Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase,
Microsoft.Practices.EnterpriseLibrary.Data, Version=1.0.0.0, Culture=neutral,
PublicKeyToken
=null />
</databaseTypes>
<instances>
<instance name=Northwind type=Sql Server connectionString=Northwind />
</instances>
<connectionStrings>
<connectionString name=Northwind>
<parameters>
<parameter name=database value=Northwind isSensitive=false />
<parameter name=Integrated Security value=True isSensitive=false />
<parameter name=server value=local isSensitive=false />
</parameters>
</connectionString>
</connectionStrings>
</enterpriseLibrary.databaseSettings>
</xmlSerializerSection>
</dataConfiguration>

 


The file basically just specifies that I am using Sql Server and the connection string to get to the server and the Northwind Database.


So that is really not that interesting.  The key to the block is simplifying the use of ADO.NET.  Let’s grab a customer from the Customer Table in Northwind.  I will use the DataReader, but you could certainly pull this off with a DataSet as well.  I will also use a querystring, but you could also use a Stored Procedure as well.  To each his own on how you prefer to access data from the database.  Shown below is a rough outline of the specific commands to pull this off using the DAAB:


 


Data Access Application Block
Database db = DatabaseFactory.CreateDatabase();

string query = Select * from Customers Where CustomerID = @CustomerID;

DBCommandWrapper command = db.GetSqlStringCommandWrapper(query);

command.AddInParameter(@CustomerID, DbType.String, id);

using (IDataReader reader = db.ExecuteReader(command))
{
// Do Something…
}


 


Everything starts out with DatabaseFactory.CreateDatabase().  If you don’t specify a named instance of a database (as I did not), it just grabs the default database in the configuration file.  The DAAB comes with this DBCommandWrapper class that is essentially a wrapper for SqlCommand for Sql Server.  I pass it my querystring as well as add the value of the @CustomerID parameter.


I wanted to use a DataReader, so I call ExecuteReader on the database (db), which passes back an IDataReader.  At this point, I do whatever I want with the reader.  Realize at this point the connection to the database is open, so you want to do your business and get the connection closed.  In my case, I wrapped the reader in a using block, which essentially mimics a try/finally block and calls dispose on the reader in finally.  Calling dispose on the reader will close the reader which causes the open connection to close due to its use of the CommandBehavior.CloseConnection.


That is it.  You don’t have to open the connections or create the command objects and manage it all.  DAAB does it for you.  Let’s complete the code to grab a particular customer from the Customers Table.


We don’t have to do this, but let’s create a Customer Class.  Rather than passing the DataReader through another layer, we are going to pass up a Customer Object to the “UI” layer.  Here is a bare bones Customer Class with only a few pieces of information:


 


Customer Class
public class Customer
{
private readonly string _id;
private string _name;
private string _title;

public string ID
{
get { return _id; }
}

public string Name
{
get { return _name; }
set { _name = value; }
}

public string Title
{
get { return _title; }
set { _title = value; }
}

public Customer (string id, string name, string title)
{
_id
= id;
_name
= name;
_title
= title;
}

public override string ToString()
{
return String.Format({0} {1}, {2}, _id, _name, _title);
}
}


 


Now we need to create a class, called CustomerData, that will essentially read in our customer using DAAB and simply fill and return the Customer Class.  Here is something quick:


 


CustomerData Class
public class CustomerData
{
public CustomerData() {}

public Customer FetchByID(string id)
{
Database db
= DatabaseFactory.CreateDatabase();

string query = Select * from Customers Where CustomerID = @CustomerID;

DBCommandWrapper command = db.GetSqlStringCommandWrapper(query);

command.AddInParameter(@CustomerID, DbType.String, id);

using (IDataReader reader = db.ExecuteReader(command))
{
if (reader.Read())
{
return new Customer(reader.GetString(0), reader.GetString(2), reader.GetString(3));
}
else
return null;
}
}
}


 


And here is the code that tests it out:


 


Test Class
public class TestClass
{
public static void Main()
{
CustomerData customerData
= new CustomerData();
Customer customer
= customerData.FetchByID(ALFKI);

if (customer != null)
{
Console.WriteLine(customer);
Console.ReadLine();
}
}
}


 


If you don’t like the idea of using a DataReader or a custom class, you can certainly pass a DataSet.  I created this just for the sake of showing something a bit different from the examples provided as part of the Enterprise Library.  The key here is the little amount of code you have to write as well as how there is nothing specific to Sql Server.


For more information on the DAAB, click here.

This entry was posted in Application Blocks. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

8 Responses to Enterprise Library – Data Access Application Block (DAAB) – Patterns and Practices

  1. David Hayden says:

    You have to open the connection first :)

    There is a sample in the documentation that walks you through the use of the Data Access Application Block with transactions. Here is a snapshot of the code:

    Database db = DatabaseFactory.CreateDatabase();

    using (IDbConnection connection = db.GetConnection())

    {

    connection.Open();

    IDbTransaction transaction = connection.BeginTransaction();

    try

    {

    // Credit the first account

    db.ExecuteNonQuery(creditCommandWrapper, transaction);

    // Debit the second account

    db.ExecuteNonQuery(debitCommandWrapper, transaction);

    // Commit the transaction

    transaction.Commit();

    }

    catch

    {

    // Rollback transaction

    transaction.Rollback();

    return false;

    }

    return true;

    }

    }

  2. Oded says:

    How do i use Transactions ?

    i tried using the db.GetConnection.BeginTransaction().

    and got: "The connection is closed."

  3. David Hayden says:

    I think you’re right, Josh. If you want true abstraction, you will need to go the stored procedure route.

    The config file does not have the flexibility now of allowing you to specify the number and types of parameters for string queries.

    I took a brief look at the code last night and there is a ParameterToken field defined as "@" for SqlServer and ":" for Oracle just as you mentioned. You could probably override it and do some clever coding to make these string queries more flexible and less dependant on the database, but I don’t think we should go there :)

    In most cases, people probably tend to use the DAAB to write less code and for API consistency as opposed to database abstraction, but you bring up a good point about how stored procedures appear to be more ideal if you really want the ultimate abstraction.

  4. Josh Flanagan says:

    After I made the comment, I started to think that maybe EL *does* do the translation for you – thinking it might be pretty easy with some regex replace calls. But as I started to think about the different scenarios, I realized it would be pretty difficult. The killer is the OLEDB case which doesn’t allow for named parameters. If you had a query that only took a single parameter, but used it twice within the query: for Oracle and Sql, you only add a single Parameter to the Command object. For OleDB, you would have to add 2 Parameters to the Command object – both with the same value.

    Even storing the SQL query in a configurable location (as opposed to hardcoded) wouldn’t help, since the number of parameters differs based on provider, so your code would have to change accordingly. Does EL allow you to define the number & type of parameters for a string query in your config file?

    I wonder if you have to use stored procedures if you want true abstraction from the database provider.

  5. David Hayden says:

    Josh,

    You’re absolutely right.

    If you end up hardcoding sql you won’t be as abstract as you might like. I would have to peek at the source code to be sure, but I doubt very much that Enterprise Library does any sort of translation. Great comment.

  6. David Hayden says:

    Thanks, Jeffrey.

  7. Josh Flanagan says:

    It is a nice abstraction, no question.

    Unfortunately, the query and the parameter syntax in your example ARE specific to SQL Server.

    For example, if you change your configuration to use the OracleClient provider, you will still have to change the query in your code to something like:

    Select * from Customers Where CustomerID = :CustomerID

    If you use an OLEDB provider, it becomes:

    Select * from Customers Where CustomerID = ?

    I am assuming the Enterprise library does not do this translation of the query for you. You still have to change the query yourself. If EL does handle it, then cool.

  8. Awesome. Sample code that could actually be used. Thanks for your post. It illustrates, also, how to use a data class to populate business objects.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>