Radical Development

Technical Without the Technicalities

Microsoft Enterprise Library: Data Access Application Block

For those of you who have been using the Enterprise Library from Microsoft then I tip my hat to you. I admit that I have not used this library for a number of years and in most cases the reason is because I have honestly not been in a position to do so. It is a long story so don’t ask. There are a number of reason why you should seriously consider the use of the Enterprise Library and I cannot think of any better reason than those provided directly from Microsoft.

The goals of Enterprise Library are the following:

  • Consistency. All Enterprise Library application blocks feature consistent design patterns and implementation approaches.
  • Extensibility. All application blocks include defined extensibility points that allow developers to customize the behavior of the application blocks by adding their own code.
  • Ease of use. Enterprise Library offers numerous usability improvements, including a graphical configuration tool, a simpler installation procedure, and clearer and more complete documentation and samples.
  • Integration. Enterprise Library application blocks are designed to work well together or individually.

Now that the groundwork has been laid let us get started.

Introduction to the Data Access Library

The Data Access Application Block includes a small number of methods that simplify the most common techniques for accessing a database. Each method encapsulates the logic required to retrieve the data and manage the connection to the database. The methods exposed by the block allow you to execute queries, return data in a range of different formats, populate a DataSet, update the database from a DataSet, perform data access asynchronously (against SQL Server databases), and return data as objects in a suitable format for use with client-side query technologies such as LINQ.

As with anything good there is always its counterpart. For example, if you are going to work with data in a a mechanism that is unique to your application or if your are going to work with a particular database that provides unique features then you may not want to use the Data Access Library. It is also important to understand the level of support. For example, if you are targeting an Oracle database you should not use this library since Microsoft has depreciated the Oracle Client. In this case you should turn to Oracle’s ODP.NET which is native to an Oracle Database.

The Database Connection

The beauty of the Data Access Library is the simplicity of its use. For example, to establish a connection to a database, SQL Server in this case all it takes is a single line of code. First things first is to add the appropriate references to the Enterprise Library Assemblies.

//Enterprise Library
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;

Then comes the database object creation.

Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>(“MyDatabase”);

The string value MyDatabase represents the connection to a SQL Server. Yes, it actually is that simple. In fact the instance of database could in fact be defined in a single class so your development team would never have to waste any time in defining a database object again. It is also worth saying that this database object is not yet in an open state. This is good news because so many developers often forget to close out their database connections. Another important benefit is the Data Access Library also will log events to the application event log, but this is a subject that I will go into in a future article.

Inline SQL Statement Example

I want to first say that you should exercise extreme caution when using inline SQL because this type of database interaction is vulnerable to SQL Injection. For the purpose of this example, I will utilize inline SQL.

In this example we will employ two SQL statements. One will retrieve the contact details to include first, middle, and last names. The second statement will return the total number of contacts.

private const string sqlOne = "SELECT FirstName," +
" MiddleName, LastName FROM Person.Contact WHERE (LastName = N'adams') AND" +
" (FirstName LIKE N'r%') ORDER BY LastName";

private const string sqlTwo = "SELECT COUNT(*) AS TotalContacts FROM Person.Contact WHERE (LastName = N'adams') AND" +
" (FirstName LIKE N'r%')";

Build the WebForm

Now turning our attention to the web form we will need to server controls. First is a Gridview and second is a Label.

<asp:GridView ID="ContactsGridView" runat="server"></asp:GridView>
<asp:Label ID="TotalContactsLabel" runat="server" Text=""></asp:Label>

At this point if you have been working with ADO.NET you will find the code behind the webform very familiar. The only thing we are doing different in this example is working with the database object that was covered earlier in this article.

/// <summary>
/// Gets the contacts.
/// </summary>
/// <returns>DataSet of contacts</returns>
private DataSet GetContacts()
{
DataSet dsContacts = db.ExecuteDataSet(CommandType.Text, sqlOne);
return dsContacts;
}

/// <summary>
/// Gets the total contacts.
/// </summary>
/// <returns>Total number of contacts</returns>
private int GetTotalContacts()
{
//because ExecuteScalar returns an object we must cast to an int
int totalContacts = (int)db.ExecuteScalar(CommandType.Text, sqlTwo);
return totalContacts;
}
  1. Internal is for assembly scope (i.e. only accessible from code in the same .exe or .dll)
  2. Private is for class scope (i.e. accessible only from code in the same class)

Now that we have the necessary methods established the next step is to bind the data to the server controls back on the webform. We will do this in the page load event.

/// <summary>
/// Handles the Load event of the Page control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
protected void Page_Load(object sender, EventArgs e)
{
ContactsGridView.DataSource = GetContacts();
ContactsGridView.DataBind();

TotalContactsLabel.Text = string.Format("There are a total of {0} contacts", GetTotalContacts().ToString());
}

Now upon execution of the web application you will be presented the following:

Contacts Data Displayed

Conclusion

As you can see, using the Data Access Library component of the Enterprise Library is not complex. Of course, this article has just begun to scratch the surface in the hopes that your interest is perked.

Examples Via MSDN

The following examples are from the Enterprise Library 5.0, Chapter 2 – Much ADO about Data Access.

Reading Rows Using a Query with No Parameters

Simple queries consisting of an inline SQL statement or a stored procedure, which take no parameters, can be executed using the ExecuteReader method overload that accepts a CommandType value and a SQL statement or stored procedure name as a string.

// Call the ExecuteReader method by specifying just the stored procedure name.
using (IDataReader reader = namedDB.ExecuteReader("MyStoredProcName"))
{
// Use the values in the rows as required.
}

Reading Rows Using an Array of Parameter Values

While you may use simple no-parameter stored procedures and SQL statements in some scenarios, it’s far more common to use queries that accept input parameters that select rows or specify how the query will execute within the database server. If you use only input parameters, you can wrap the values up as an Object array and pass them to the stored procedure or SQL statement. Note that this means you must add them to the array in the same order as they are expected by the query, because you are not using names for these parameters—you are only supplying the actual values. The following code shows how you can execute a stored procedure that takes a single string parameter.

// Call the ExecuteReader method with the stored procedure
// name and an Object array containing the parameter values.
using (IDataReader reader = defaultDB.ExecuteReader("ListOrdersByState",
new object[] { "Colorado" }))
{
// Use the values in the rows as required - here we are just displaying them.
DisplayRowValues(reader);
}

Retrieving XML Data

The Data Access block provides the ExecuteXmlReader method for querying data as XML. It takes a SQL statement that contains the FOR XML statement and executes it against the database, returning the result as an XmlReader. You can iterate through the resulting XML elements or work with them in any of the ways supported by the XML classes in the .NET Framework. However, as SQLXML is limited to SQL Server (the implementations of this type of query differ in other database systems), it is only available when you specifically use the SqlDatabase class (rather than the Database class).

The following code shows how you can obtain a SqlDatabase instance, specify a suitable SQLXML query, and execute it using the ExecuteXmlReader method.

// Resolve a SqlDatabase object from the container using the default database.
SqlDatabase sqlServerDB
= EnterpriseLibraryContainer.Current.GetInstance<Database>() as SqlDatabase;

// Specify a SQL query that returns XML data.
string xmlQuery = "SELECT * FROM OrderList WHERE State = @state FOR XML AUTO";

// Create a suitable command type and add the required parameter
// NB: ExecuteXmlReader is only available for SQL Server databases
using (DbCommand xmlCmd = sqlServerDB.GetSqlStringCommand(xmlQuery))
{
xmlCmd.Parameters.Add(new SqlParameter("state", "Colorado"));
using (XmlReader reader = sqlServerDB.ExecuteXmlReader(xmlCmd))
{
// Iterate through the elements in the XmlReader
while (!reader.EOF)
{
if (reader.IsStartElement())
{
Console.WriteLine(reader.ReadOuterXml());
}
}
}
}

Retrieving Single Scalar Values

A common requirement when working with a database is to extract a single scalar value based on a query that selects either a single row or a single value. This is typically the case when using lookup tables or checking for the presence of a specific entity in the database. The Data Access block provides the ExecuteScalar method to handle this requirement. It executes the query you specify, and then returns the value of the first column of the first row of the result set as an Object type. This means that it provides much better performance than the ExecuteReader method, because there is no need to create a DataReader and stream the results to the client as a row set. To maximize this efficiency, you should aim to use a query that returns a single value or a single row.

The ExecuteScalar method has a set of overloads similar to the ExecuteReader method we used earlier in this chapter. You can specify a CommandType (the default is StoredProcedure) and either a SQL statement or a stored procedure name. You can also pass in an array of Object instances that represent the parameters for the query. Alternatively, you can pass to the method a Command object that contains any parameters you require.

The following code demonstrates passing a Command object to the method to execute both an inline SQL statement and a stored procedure. It obtains a suitable Command instance from the current Database instance using the GetSqlStringCommand and GetStoredProcCommand methods. You can add parameters to the command before calling the ExecuteScalar method if required. However, to demonstrate the way the method works, the code here simply extracts the complete row set. The result is a single Object that you must cast to the appropriate type before displaying or consuming it in your code.

// Create a suitable command type for a SQL statement.
// NB: For efficiency, aim to return only a single value or a single row.
using (DbCommand sqlCmd
= defaultDB.GetSqlStringCommand("SELECT [Name] FROM States"))
{
// Call the ExecuteScalar method of the command.
Console.WriteLine("Result using a SQL statement: {0}",
defaultDB.ExecuteScalar(sqlCmd).ToString());
}

// Create a suitable command type for a stored procedure.
// NB: For efficiency, aim to return only a single value or a single row.
using (DbCommand sprocCmd = defaultDB.GetStoredProcCommand("GetStatesList"))
{
// Call the ExecuteScalar method of the command.
Console.WriteLine("Result using a stored procedure: {0}",
defaultDB.ExecuteScalar(sprocCmd).ToString());
}

Updating Data

The following code from the example application for this chapter shows how you can use the ExecuteNonQuery method to update a row in a table in the database. It updates the Description column of a single row in the Products table, checks that the update succeeded, and then updates it again to return it to the original value (so that you can run the example again). The first step is to create the command and add the required parameters, as you’ve seen in earlier examples, and then call the ExecuteNonQuery method with the command as the single parameter. Next, the code changes the value of the command parameter named description to the original value in the database, and then executes the compensating update.

string oldDescription = "Carries 4 bikes securely; steel construction, fits 2" receiver hitch.";
string newDescription = "Bikes tend to fall off after a few miles.";

// Create command to execute the stored procedure and add the parameters.
DbCommand cmd = defaultDB.GetStoredProcCommand("UpdateProductsTable");
defaultDB.AddInParameter(cmd, "productID", DbType.Int32, 84);
defaultDB.AddInParameter(cmd, "description", DbType.String, newDescription);

// Execute the query and check if one row was updated.
if (defaultDB.ExecuteNonQuery(cmd) == 1)
{
// Update succeeded.
}
else
{
Console.WriteLine("ERROR: Could not update just one row.");
}

// Change the value of the second parameter
defaultDB.SetParameterValue(cmd, "description", oldDescription);

// Execute query and check if one row was updated
if (defaultDB.ExecuteNonQuery(cmd) == 1)
{
// Update succeeded.
}
else
{
Console.WriteLine("ERROR: Could not update just one row.");
}

Be sure to check out Chapter 2 – Much ADO about Data Access for much more details and examples.