Big Data

Simplify .NET SQL queries with the Data Access Application Block

Microsoft's Data Access Application Block makes connecting .NET applications to SQL databases easier--provided you know how to use its methods efficiently. Here's a quick best practices guide for getting the most out of the DAAB.


Although Microsoft's .NET Application Block is well documented, the practical tips and tricks that make it more usable on a day-to-day basis are harder to find. This article gathers some code that will make the Data Access Application Block easier to use in everyday .NET/SQL Server projects.

Getting started
You can download the Data Access Application Block from MSDN. You'll have to install the package and actually build the components using Visual Studio .NET 2002 or 2003 before loading a project. You must also reference the created DLL in the /bin/ directory in the References folder of the project in which you intend to use the components. Finally, you'll need an assembly reference on each file or form, as shown below:
using Microsoft.ApplicationBlocks.Data;

Basic usage
The principal class of the Data Access Application Block is SQLHelper, which is a sealed class with four common (and several less used) methods:
  • ExecuteDataset generates a DataSet from a SQL query.
  • ExecuteReader generates a SqlDataReader from a SQL query.
  • ExecuteScalar generates a single value object from a SQL query.
  • ExecuteNonQuery runs a SQL query with no return value.

Let's look at each in detail.

ExecuteDataset
ExecuteDataset will run your basic SELECT query and generate a DataSet, which can then be bound to a server object or used to create a DataView. As with all of the methods, there are a number of overloads, but the most common version looks something like this:
DataSet dataSet = SqlHelper.ExecuteDataset(connString,
 "usp_GetStuffProcedure", parameter)

Usually, you are filling a DataSet from a SELECT query with a RowId or a search parameter. The parameter bucket above will take a large number of parameters, so even if you were passing in three search parameters, for instance, you would just need:
DataSet dataSet = SqlHelper.ExecuteDataset(connString,
 "usp_SearchStuffProcedure", parameter1, parameter2, parameter3)

ExecuteReader
ExecuteReader is also for a SELECT statement, but it's usually reserved for situations where performance really matters. SqlDataReaders are like forward-only, read-only recordsets from ADO classic. They are good for filling ListBoxes and CheckBoxLists. But before you go whole-hog and call 300 SqlDataReaders to fill your dynamic controls, read my final comment in the "General pointers" section below.

The call to an ExecuteReader looks just like an ExecuteDataset. Remember that you need an assembly reference to System.Data.SqlClient to use a SqlDataReader with a SQL Server database:
using System.Data.SqlClient;
SqlDataReader sqlDataReader = SqlHelper.ExecuteReader(connString,
 "usp_GetStuffProcedure", parameter);

ExecuteScalar
The ExecuteScalar method has several uses, like returning a SELECT query with only one value such as a COUNT. But the most common usage will be to run an INSERT statement that returns the new row ID. This is a fairly common trick in Transact SQL, but it requires a CAST in the stored procedure to make sure that the resultant row ID is returned in the easiest format for .NET.
INSERT (ColumnName1, ColumnName2) VALUES (@parameter1, @parameter2)
SELECT CAST(@@Identity AS INTEGER)

To keep things simple, the value is returned to .NET as an Object. To get an integer row ID back, use the Convert statement.
int newRowId = Convert.ToInt32(SqlHelper.ExecuteScalar(connString,
 "usp_InsertStuffProcedure",
 parameter1,
 parameter2));

ExecuteNonQuery
Use the ExecuteNonQuery method to run everything else—UPDATE, DELETE, and functional or housekeeping queries—that return no values. Once again, it works just like the other methods, with the connection string, stored procedure, and parameter(s) as arguments.
SqlHelper.ExecuteNonQuery(connString,"usp_DeleteStuffProcedure",
 rowIdToDelete);

General pointers
Aside from using the right method for the right job, a few basic best practices will make using the Data Application Block even simpler in everyday programming.

Get your connection string from the Web.config
Notice that I just used a string variable called connString in all of the above examples. This is because I have done two things in the code above the call to SqlHelper. First, in the Web.config, I created an appSettings section, accessible via the System.Configuration classes:
<appSettings>
 <add  key="mainConnectionString"
value="Data Source=myDb;Initial Catalog=database;
user id=user1; password=pass1"/>
</appSettings>

Second, I initialized the connString variable with the key from the configuration file. Remember to reference the System.Configuration namespace in your assembly.
using System.Configuration;
string connString =
ConfigurationSettings.AppSettings["mainConnectionString"];

Use stored procedures
Although anyone can use inline SQL with the Data Access Application Block, please don't. The parameter overload is slightly different, because you need to specify a command type. Stored procedures are default for the components, so you can ignore the type parameter if you use them, but not for inline SQL.
SqlHelper.ExecuteNonQuery(connString, CommandType.Text,
 "DELETE FROM table1 WHERE rowId = " + rowId);

Why shouldn't you use this? Well, aside from the bad design considerations, you have potentially left yourself open to a SQL injection attack. With the Data Access Application Block, we don't need to ever concatenate SQL strings again, so let's not.

If you are going to use DataSets, really use them
The benefit of DataSets is that they are an in-memory representation of relational data for stateless systems. If you have a system that is storing the eye color and hair color of employees in related tables, we can get all of that information using one stored procedure and then bind the ListBoxes to the auxiliary tables created by the SELECT. The aggregated procedure looks like this:
—Table 0
SELECT HairColorId, EyeColorId
FROM Employees
WHERE rowId = @RowId;
—Table 1
SELECT HairColorId, HairColor
FROM HairColors
—Table 2
SELECT EyeColorId, EyeColor
FROM EyeColors

Use the Data Application Block to get one DataSet:
DataSet dataSet = SqlHelper.ExecuteDataset(connString,
 "usp_GetEmployeeColors", rowId)

Then, you can bind the ListBoxes to the second and third tables. Here's the Eye Color ListBox:
EyeColorListBox.DataSource = dataSet.Tables[2].DefaultView;
EyeColorListBox.DataTextField = "EyeColor";
EyeColorListBox.DataValueField = "EyeColorId";
EyeColorListBox.DataBind();
EyeColorListBox.Items.FindByValue(
dataSet.Tables[0].Rows[0]["EyeColorId"].ToString())
.Selected = true;

Wrapping up
We didn't cover all of the functionality of the Data Access Application Block, and for the purpose of the article, this is a good thing. Lots of developers who should be using the Data Access Application Block are not because of its perceived complexity. After reading this overview of the most useful features, everyone should be able to get started with this great free code from Microsoft.

Editor's Picks