Data Points

The Enterprise Library Data Access Application Block, Part 2

John Papa

Code download available at:DataPoints0508.exe(197 KB)

Contents

Setting Up Your Project
Post-Build Events
Get Rows via a DataReader
Parameterized Queries and DataSets
Table Names and Exposure
ExecuteDataSet Overloads
Loading an Existing DataSet
Stored Procedures
Hit and Run
Another Hit and Run
Getting XML
Wrapping It Up

Last month I explored the foundation of the Enterprise Library Data Access Application Block (DAAB) including how it all fits into an architecture (see Data Points: The Enterprise Library Data Access Application Block, Part 1). I also discussed the Configuration Tool, how to use the cryptography and configuration features with the DAAB, and how the DAAB offers database abstraction through its database factory pattern and through reflection.

This month, I will continue the exploration by walking through several code examples that demonstrate the different ways data can be retrieved using the DAAB. I'll discuss how to get at features of ADO.NET through the DAAB and will show how to take advantage of the database-specific abstraction that the Enterprise Library DAAB offers through its database factory pattern.

I will start by setting up a project that uses the Enterprise Library DAAB and discussing how a project can benefit from post-build events. Then I will walk through examples that retrieve data using several different approaches which include returning the data in a DataReader, a DataSet, a string value, and XML.

I'll show you how to execute a parameterized query using inline SQL as well as stored procedures. The use of output parameters to retrieve data can be very efficient, as can the use of the ExecuteScalar method of ADO.NET (and of DAAB). I'll demonstrate how both of these can be accomplished, and explain methods to retrieve multiple rowsets at once. The DAAB is like any other tool in that to run efficiently it needs to be used properly. Therefore, along the way, I'll discuss how it can be tuned and how it operates under the covers. (All of the code samples can be downloaded from the MSDN®Magazine Web site.)

The Enterprise Library Application Blocks can be downloaded from the MSDN Web site at Enterprise Library. GotDotNet workspaces also is a good place to go to get the latest on Enterprise Library and any updates: Enterprise Library: Workspace Home.

Setting Up Your Project

For the following examples I will set up a Windows® Forms application that uses the Enterprise Library DAAB to retrieve and manipulate data against the SQL Server™ Northwind database. Figure 1 shows the finished project. (Note that the options to run each procedure that I will discuss here are shown on the left side of the dialog). First, I created a Windows Forms application using Visual Studio® .NET and I named it MSDN200508. I then added a reference to the DAAB and to the Configuration Application Block (since it is used by the DAAB). The Enterprise Library assemblies are located in the folder Program Files\Microsoft Enterprise Library\bin.

Figure 1 Testing DAAB Using a Windows Form

The Enterprise Library uses the Web.config file to point to its configuration files for ASP.NET applications. In a Windows Forms application, the Enterprise Library uses the app.config file to point to its configuration files. After I created my Windows Forms application, I added an app.config file to the project. Next, I opened the Enterprise Library configuration tool, selected File | Open Application from the menu and chose the app.config file of the MSDN200508 Windows Forms project.

From there, I set up my DAAB config file to point to the Northwind database on my local SQL Server instance, and because I am security conscious I also encrypted the dataConfiguration.config file. (For details on how to use the Configuration Tool to set up a DAAB config file to point to the Northwind database and how to encrypt it with cryptography, see my Data Points column in the July 2005 issue.) Once I have my configuration files established, I added the dataConfiguration.config file to my Windows Forms project. Then, just to test things out, I added a button and a DataGrid to a form, added the following code to the button1_Click event, and built and ran it:

private void button1_Click(object sender, System.EventArgs e) { string sSql = "SELECT CustomerID, CompanyName, City, Region FROM Customers"; Database dbNorthwind = DatabaseFactory.CreateDatabase(); DBCommandWrapper cmdCust = dbNorthwind.GetSqlStringCommandWrapper(sSql); DataSet dsCust = dbNorthwind.ExecuteDataSet(cmdCust); dataGrid1.DataSource = dsCust.Tables[0].DefaultView; }

This code is supposed to get the list of customers from the database and load the DataGrid with them. However, instead I get an exception that tells me it cannot find the dataConfiguration.config file. The reason this occurs is that when you build the Windows Forms application and run it in debug mode, the assembly's executable file (MSDN200508.exe) and its config file (MSDN200508.exe.config) are created in the bin\debug folder of the project. The MSDN200508.exe.config file tells the application to look for the dataConfiguration.config file in the same folder where the app resides. By default, the configuration tool does not include any path information to the dataConfiguration.config file (nor any other of the Enterprise Library config files). Since I did not specifically add the path, when the application was run in debug mode it created the exe and the exe.config files in the bin\debug folder. Then it tried to locate the dataConfiguration.config file in the bin\debug folder and threw an exception when it was not found there.

Note that this is not an issue with ASP.NET Web applications. When ASP.NET applications are built, the Web.config file remains in the root folder of the Web application.

Post-Build Events

The solution I chose is to use a post-build event to make sure that all of my Enterprise Library configuration files are copied to the target folder when the project is built. Visual Studio .NET exposes pre-and post-build events for C# projects but not for Visual Basic® .NET projects. When I develop Visual Basic .NET projects I create a .bat file that copies the configuration files to the target directory. Then I manually run the .bat file after each build. (Yes, this is annoying but the good news is that build events appear in Visual Studio 2005. Moreover, in Visual Studio 2005 you can automatically copy files to the output directory by configuring the "Copy to Output Directory" property for the appropriate files.) For Visual Basic .NET projects, I included a sample .bat file that you can download along with the code from this column. Since the project I created is in C#, I added a post-build event (see Figure 2) which you can also perform by following these four quick steps:

  1. In Solution Explorer, right-click the project and select Properties
  2. Select Common Properties | Build Events
  3. Left-click in post-build event command line
  4. Enter the following command: copy "$(ProjectDir)*.config" "$(TargetDir)", and then click OK

Figure 2 Creating a Post-Build Event

When the project is built, it will copy all files in the project directory with an extension of .config to the target directory. If I make a debug build, the config files are copied to the bin\debug folder. If I make a release build, the config files are copied to the bin\release folder. If the build succeeds, the last message in the output window will be a success message from the post-build event.

As an alternative for Visual Basic .NET projects, pre-and post-build events can be created using an add-in for Visual Studio .NET. It can be downloaded from this Web address: Visual Studio .NET 2003 Automation Samples.

Get Rows via a DataReader

To get started with the Enterprise Library DAAB, I first need to set up the means to connect to a database. The DatabaseFactory class exposes a CreateDatabase method which creates an instance of a Database object (Microsoft.Practices.EnterpriseLibrary.Data.Database, to be exact). CreateDatabase has two signatures. If it is passed an instance name, it uses the matching instance name found in the dataConfiguration.config file. If no instance name is passed (as shown in the first lines of code in Figure 3), it uses the default instance defined in the dataConfiguration.config file.

Figure 3 Calling the DAAB ExecuteReader

public string GetCustomersViaExecuteReader() { // Create the Database object using the default database as // indicated in dataConfiguration.config. Database db = DatabaseFactory.CreateDatabase(); // Set the SQL statement string sSql = "SELECT CustomerID, CompanyName, " + " City, Country FROM Customers"; // Create the DBCommandWrapper and execute the SQL DBCommandWrapper cmd = db.GetSqlStringCommandWrapper(sSql); // Using "using" will cause both the DataReader and connection to be // closed. (ExecuteReader will close the connection when the // DataReader is closed.) StringBuilder sbData = new StringBuilder(); using (IDataReader dataReader = db.ExecuteReader(cmd)) { while (dataReader.Read()) { sbData.Append(dataReader["CompanyName"]); sbData.Append(Environment.NewLine); } } return sbData.ToString(); }

The instance of the Database class that is created can be used to access a variety of data providers including SQL Server and Oracle. The type of provider defined in the dataConfiguration.config file determines the type of database that the instance of the Database class will reference. By using this factory pattern, you can better abstract the data provider than if you use the specific namespaces, such as System.Data.SqlClient.

After I create an instance of the Database class, I use its GetSqlStringCommandWrapper method to create an instance of a DBCommandWrapper. The GetSqlStringCommandWrapper method accepts command text such as a SQL statement and creates an instance of a DBCommandWrapper. In this project, the DBCommandWrapper wraps the functionality of the ADO.NET SqlCommand object (or whatever provider-specific Command object is relevant to the data provider). Now I pass the DBCommandWrapper to the Database.ExecuteReader method to return a DataReader. I then loop through the rows using the DataReader and build a string containing the list of customers using a StringBuilder. (Since string variables are immutable, using StringBuilder is more efficient than using a string variable when strings are continually concatenated together.)

The connection is opened when the ExecuteReader method is invoked. If I used ADO.NET code directly, I would first have to open the connection and then I could issue the ExecuteReader method of the Command object to get a DataReader. With the DAAB, the ExecuteReader method opens the connection for me and then gets the DataReader. Whether I use ADO.NET code directly or the DAAB, the connection is left open after the ExecuteReader method is invoked. It is very important to make sure that the connection is closed to conserve resources.

In ADO.NET, I can either explicitly close the connection when I'm finished using the DataReader or I can tell the DataReader to close the connection when the DataReader closes. The code to do this in ADO.NET is:

SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

As a nice cleanup feature, under the covers the DAAB Database.ExecuteReader method invokes the ADO.NET Command object's ExecuteReader method with the CommandBehavior.CloseConnection enumerator value. In the code in Figure 3, instead of closing the DataReader explicitly, I used the using statement to close the DataReader. When the using statement's block completes, the DataReader is closed, closing the connection with it. Figure 1 was the result of running the code in Figure 3.

Parameterized Queries and DataSets

When using a DataReader, the DAAB opens the connection for me, then leaves it open until the DataReader is closed. However, when invoking the ExecuteDataSet or LoadDataSet methods to fill a DataSet, the DAAB opens and closes connections for me. The Database.ExecuteDataSet method creates a new DataSet and fills it with one or more DataTable objects from a database. The following code sample creates a DBCommandWrapper object that represents a parameterized SELECT statement:

public DataSet GetCustomersViaExecuteDataSet(string sCountry) { Database db = DatabaseFactory.CreateDatabase(); string sSql = "SELECT CustomerID, CompanyName, City, Country" + " FROM Customers WHERE Country = @sCountry"; DBCommandWrapper cmd = db.GetSqlStringCommandWrapper(sSql); cmd.AddInParameter("@sCountry", DbType.String, sCountry); return db.ExecuteDataSet(cmd); }

The DBCommandWrapper exposes a few methods to add parameters: AddParameter, which adds a parameter (no direction specified); AddInParameter, which adds an input parameter; and AddOutParameter, which adds an output parameter.

The code uses the AddInParameter method to add the @sCountry parameter and its value to the DBCommandWrapper. (I could have used the AddParameter method and then separately set the direction to be an input parameter, as well.) Then I call the ExecuteDataSet method of the Database object, which executes the DBCommandWrapper's command, retrieves the results, creates a new DataSet, and throws the results into the new DataSet. The DataSet will contain a single DataTable containing the rowset of customers. The DBCommandWrapper code can be run from the sample project by choosing the listbox item #2.

Table Names and Exposure

One caveat with the ExecuteDataSet is that it names the first DataTable "Table". If the DBCommandWrapper's command returned two rowsets, the second rowset would have been put inside of a second DataTable named "Table1" (the third DataTable will be named "Table2", and so on).

One ingredient that I demand of any data layer or data access component is the ability to communicate with the underlying data access technology (ADO.NET, in this case). While DAAB makes it easier to create a command and add parameters to it, if I need to make an adjustment that the DAAB interface does not expose, I can always go right to the ADO.NET objects. For example, if I want to set a property of the ADO.NET SqlParameter object I can get to it by going through the following line of code:

SqlParameter param = (SqlParameter)cmd.Command.Parameters["@sCountry"];

Concatenating user-supplied parameters into SQL strings and then executing the SQL makes an application vulnerable to SQL injection attacks. It is a good practice to use parameterized queries or stored procedures with parameters whether you use inline SQL or stored procedures. One nice feature of the Enterprise Library DAAB is that if it detects that the parameter token (the @ character for SQL Server or the : character for Oracle) is not the first character of the parameter name, it will append the appropriate data provider parameter token automatically.

ExecuteDataSet Overloads

The ExecuteDataSet also has other overloaded methods that allow for a variety of behaviors. For example, some of the overloaded versions of ExecuteDataSet allow you to pass in an IDBTransaction object that the DBCommandWrapper should enlist in. Others allow command text and the type of command to be passed in instead of an explicit DBCommandWrapper object. In this case the ExecuteDataSet method will generate the DBCommandWrapper object under the covers from the Database object using the command text and command type parameters. Another set of overloads for ExecuteDataSet allows for the name of a stored procedure to be passed in as well as a parameter array for the values of the parameters for the stored procedure.

Loading an Existing DataSet

While the Database.ExecuteDataSet method fills a new DataSet with data, the Database.LoadDataSet method fills an existing DataSet. As shown in the following code, the LoadDataSet method accepts as parameters the DBCommandWrapper to execute, an existing DataSet, and the name to use for the DataTable that it will create and fill with the results from the DBCommandWrapper:

string sSql = "SELECT CustomerID, CompanyName, City, Country" + " FROM Customers WHERE Country = @sCountry"; Database db = DatabaseFactory.CreateDatabase(); DBCommandWrapper cmd = db.GetSqlStringCommandWrapper(sSql); cmd.AddInParameter("@sCountry", DbType.String, sCountry); DataSet ds = new DataSet(); db.LoadDataSet(cmd, ds, "Customers");

In this example, the DataSet called ds is created prior to the invocation of the Database.LoadDataSet method. This comes in handy when I want to fill an existing DataSet that already contains one or more DataTable objects or when I want to use a typed DataSet. The code in Figure 4 can be run from the sample project by choosing the listbox item #3.

Figure 4 Loading a DataSet with Multiple DataTables

public DataSet GetCustomersViaLoadDataSetMultipleTables(string sCountry) { Database db = DatabaseFactory.CreateDatabase(); string sSql = "SELECT CustomerID, CompanyName, City, Country" + " FROM Customers WHERE Country = @sCountry;" + "SELECT OrderID, CustomerID, OrderDate FROM Orders" + " WHERE CustomerID IN (SELECT CustomerID" + " FROM Customers WHERE Country = @sCountry)"); DBCommandWrapper cmd = db.GetSqlStringCommandWrapper(sSql); cmd.AddInParameter("@sCountry", DbType.String, sCountry); DataSet ds = new DataSet(); db.LoadDataSet(cmd, ds, new string[] {"Customers", "Orders"}); ds.Relations.Add("Customers2Orders", ds.Tables["Customers"].Columns["CustomerID"], ds.Tables["Orders"].Columns["CustomerID"]); return ds; }

If I want to execute a command that returns multiple rowsets, I would use the overloaded signature for LoadDataSet that accepts the DBCommandWrapper, the existing DataSet, and an array of the table names (one for each expected resultset). The code in Figure 4 shows how to do this by first creating a string containing two SELECT statements concatenated with a semicolon. Then it creates a DBCommandWrapper out of the SQL string and passes in a value for the @sCountry parameter. Finally, the code invokes the LoadDataSet method, passing to it the DBCommandWrapper, the DataSet, and the string array containing the values to use as the names of the two DataTables to be created within the DataSet. The code sample in Figure 4 can be run from the sample project by choosing the listbox item #4.

One difference between ExecuteDataSet and LoadDataSet is that LoadDataSet has a parameter for the name(s) of the DataTable(s) it will create. As I mentioned earlier, the ExecuteDataSet method names each DataTable starting with Table, Table1, Table2, and so on. Another difference between the two methods is that LoadDataSet expects to load an existing DataSet while ExecuteDataSet expects to generate a new one.

Stored Procedures

The code samples shown so far can all be modified to execute stored procedures instead of inline SQL code. In the following code snippet, which can be run by executing listbox item #5 from the sample project, the DBCommandWrapper's GetStoredProcCommandWrapper method is passed the name of the stored procedure to execute:

DataSet ds = new DataSet(); string sProc = "prGetCustomersAndOrders"; Database db = DatabaseFactory.CreateDatabase(); DBCommandWrapper cmd = db.GetStoredProcCommandWrapper(sProc); cmd.AddInParameter("@sCountry", DbType.String, sCountry); db.LoadDataSet(cmd, ds, new string[] {"Customers", "Orders"}); ds.Relations.Add("Customers2Orders", ds.Tables["Customers"].Columns["CustomerID"], ds.Tables["Orders"].Columns["CustomerID"]);

Under the covers this method creates a command, sets its command type to be a stored procedure and assigns the name of the stored procedure to the command. This example produces the same results as the previous example that used inline SQL. The two SELECT statements that are in the inline SQL in the previous example are now contained within the stored procedure in this example. Figure 5 shows the results of running listbox item #5.

Figure 5 Loading Two Data Tables into a DataSet

Hit and Run

When I want to retrieve a handful of values from a single row, returning a rowset and creating a DataSet from it can be overkill. If I do not need a DataSet, but just the values from a few columns, calling a stored procedure and having it return the values as output parameters is a good option. I call this the hit-and-run approach to getting a row's data since it hits the database, gets the values it needs, and returns without creating a data store such as a DataSet. For the next example I will use the stored procedure prGetCustomerViaOutputParameters, as shown in Figure 6.

Figure 6 Stored Procedure Returning Output Parameters

ALTER PROCEDURE dbo.prGetCustomerViaOutputParameters ( @sCustomerID NCHAR(5), @sCompanyName NVARCHAR(40) OUTPUT, @sCity NVARCHAR(15) OUTPUT, @sCountry NVARCHAR(15) OUTPUT ) AS SELECT @sCompanyName = CompanyName, @sCity = City, @sCountry = Country FROM Customers WHERE CustomerID = @sCustomerID RETURN

The Database.ExecuteNonQuery method will execute a command (such as a stored procedure or an inline SQL statement) and not return a DataSet or a DataReader. ExecuteNonQuery is great for executing action queries when there is no rowset to return or when only the output parameters of a stored procedure need to be accessed. The code in Figure 7 sets up a DBCommandWrapper to represent the stored procedure shown in Figure 6 and assigns it its input parameter. It then assigns three output parameters (using the AddOutParameter method) to the DBCommandWrapper, one for each of the output parameter values that the stored procedure will return. When the Database.ExecuteNonQuery method is invoked, the stored procedure is executed and the output parameters are filled with values. At this point I can retrieve the values from the output parameters and return them to the Windows Form. (The Figure 7 code sample can be run from the sample project by running listbox item 6.)

Figure 7 Retrieving Output Parameters

public string GetCustomerViaOutputParameters() { Database db = DatabaseFactory.CreateDatabase(); string sProc = "prGetCustomerViaOutputParameters"; DBCommandWrapper cmd = db.GetStoredProcCommandWrapper(sProc); cmd.AddInParameter("@sCustomerID", DbType.String, "ALFKI"); cmd.AddOutParameter("@sCompanyName", DbType.String, 40); cmd.AddOutParameter("@sCity", DbType.String, 15); cmd.AddOutParameter("@sCountry", DbType.String, 15); db.ExecuteNonQuery(cmd); StringBuilder sbData = new StringBuilder(); sbData.Append(cmd.GetParameterValue("@sCompanyName").ToString()); sbData.Append(Environment.NewLine); sbData.Append(cmd.GetParameterValue("@sCity").ToString()); sbData.Append(Environment.NewLine); sbData.Append(cmd.GetParameterValue("@sCountry").ToString()); sbData.Append(Environment.NewLine); return sbData.ToString(); }

Another Hit and Run

Sometimes all that is needed from a stored procedure or an inline SQL statement is a single value. In this case using the Database.ExecuteScalar method is very efficient. ExecuteScalar can be performed on a DBCommandWrapper's command whether it is a SQL statement or a stored procedure. Ideally, the command would return a single row and column from which ExecuteScalar would extract the single value. This code sample can be run from the sample project by running listbox item 6:

Database db = DatabaseFactory.CreateDatabase(); string sProc = "prGetCustomerName"; DBCommandWrapper cmd = db.GetStoredProcCommandWrapper(sProc); cmd.AddInParameter("@sCustomerID", DbType.String, "ALFKI"); object test = db.ExecuteScalar(cmd); return test == null ? String.Empty : test.ToString();

The ExecuteScalar method returns an object; therefore it must be converted to the datatype that you expect it to return. In this particular case, I knew it was going to return a string. However I could have used the System.Convert method in order to convert the return value to Int32, for example, if I expected to get back a quantity or a row count.

Getting XML

The DAAB can also be used to retrieve XML via an XmlReader object. Figure 8 shows how the SqlDatabase.ExecuteXmlReader method can be used to execute a SQL Server stored procedure that returns XML. The stored procedure uses the FOR XML AUTO clause to return the data in XML format as opposed to the standard set-based rowset. (See the Data Points column in the June issue of MSDN Magazine for more information on SQL Server FOR XML clause.)

Figure 8 Getting XML from a Stored Procedure

public string GetCustomersViaExecuteXmlReader() { SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase(); string sProc = "prGetCustomersXml"; SqlCommandWrapper cmd = (SqlCommandWrapper) db.GetStoredProcCommandWrapper(sProc); cmd.AddInParameter("@sCountry", DbType.String, "USA"); XmlReader xmlRdr = null; StringBuilder sbData = new StringBuilder(); try { xmlRdr = db.ExecuteXmlReader(cmd); while (!xmlRdr.EOF) { if (xmlRdr.IsStartElement()) { sbData.Append(xmlRdr.ReadOuterXml()); sbData.Append(Environment.NewLine); } } } finally { if (xmlRdr != null) { xmlRdr.Close(); } // Explicitly close the connection. The connection is not closed // when the XmlReader is closed. if (cmd.Command.Connection != null) { cmd.Command.Connection.Close(); } } return sbData.ToString(); }

It is important to note that the code in Figure 8 uses SQL Server provider-specific classes. Since the FOR XML clause is not available in all data providers (or an equivalent means to retrieve XML directly), I used the SqlDatabase and SqlCommandWrapper objects. (This code sample can be run from the sample project by executing listbox item #8.)

Notice that I explicitly close the XmlReader and the Connection when I am finished with the XmlReader. This implementation differs from how I used the DataReader in Figure 3. The DataReader implements the IDispose interface, so I can use it in a using block; when the using block ends, the DataReader is automatically closed. The DAAB closes the Connection when the DataReader is closed, so the code in Figure 3 tidies itself up nicely. The XmlReader does not implement the IDispose interface so I cannot use it in a using block and must close the XmlReader explicitly. Further, the ExecuteXmlReader method will not close the Connection when the XmlReader is closed, so I have to close the Connection explicitly.

Wrapping It Up

The Enterprise Library DAAB offer a variety of ways to retrieve data from a data provider using ADO.NET. It also exposes several different overloaded methods for each of the four main data retrieval methods on the Database object. Whether using the DAAB, a custom data access layer, or straight ADO.NET, it is important to make sure to close all connections. The Enterprise Library DAAB helps open and close connections in many cases but when using the XmlReader and DataReader objects it pays to check that connections are closed.

In the first part of this series in the July issue, I discussed how the Enterprise Library DAAB works, how it can be configured, and how it uses cryptography. In this second part I reviewed several ways in which it can be used to retrieve data. In the final installment of this series I will demonstrate several ways the Enterprise Library DAAB can modify data, how to implement transactions with it, how it can use parameter caching, and how to set up NUnit to test the data access code.

Send your questions and comments for John to  mmdata@microsoft.com.

John Papa is a Senior .NET Consultant with ASPSOFT and a baseball fanatic who spends most of his summer nights rooting for the Yankees with his family and his faithful dog, Kadi. He has authored several books on ADO, XML, and SQL Server and can often be found speaking at industry conferences such as VSLive or blogging at codebetter.com/blogs/john.papa.