Data Access
Implement a Data Access Layer for Your App with ADO.NET
Dan Fox
This article assumes you're familiar with ADO.NET, Visual Basic .NET, and C#
Level of Difficulty123
SUMMARY
Implementing data access functionality is a core activity of most developers working with the .NET Framework, and the data access layers they build are an essential part of their applications. This article outlines five ideas to consider when building a data access layer with Visual Studio .NET and the .NET Framework. The tips include taking advantage of object-oriented techniques and the .NET Framework infrastructure by using base classes, making classes easily inheritable by following guidelines, and carefully examining your needs before deciding on a presentation method and external interface.
Contents
Rule 1: Use Object-oriented Features
Rule 2: Adhere to the Design Guidelines
Rule 3: Take Advantage of the Infrastructure
Rule 4: Choose Your External Interface Carefully
Rule 5: Abstract .NET Framework Data Providers
Conclusion
If you're developing a data-centric application targeting the Microsoft® .NET Framework, you'll eventually need to create a data access layer (DAL). You probably know that there are benefits of building your code in the .NET Framework. Because it supports both implementation and interface inheritance, your code can be more reusable, especially by developers across your organization using different Framework-compliant languages. In this article, I'll present five rules for developing a DAL for your .NET Framework-based applications.
Before I begin, I should note that any DAL you build based on the rules discussed in this article will be compatible to the traditional multitier or n-tier application favored by developers on the Windows® platform. In this architecture, the presentation layer consists of Web Forms, Windows Forms, or XML Web Services code that makes calls to a business layer that coordinates the work of the data access layer. This layer consists of multiple data access classes. Alternatively, the presentation layer may make calls directly to the DAL in cases where business process coordination is not required. This architecture is a variant of the traditional Model-View-Controller (MVC) pattern and in many ways is assumed by Visual Studio® .NET and the controls that it exposes.
Rule 1: Use Object-oriented Features
The most fundamental object-oriented task is creating an abstract base class using implementation inheritance. This base class can include services that all your data access classes can use through inheritance. If the services are generic enough, they can be reused through the distribution of your base class throughout your organization. For example, in the simplest case, your base class can handle the creation of a connection object for your derived classes, as shown in Figure 1.
Figure 1 Simple Base Class
Imports System.Data.SqlClient Namespace ACME.Data Public MustInherit Class DALBase : Implements IDisposable Private _connection As SqlConnection Protected Sub New(ByVal connect As String) _connection = New SqlConnection(connect) End Sub Protected ReadOnly Property Connection() As SqlConnection Get Return _connection End Get End Property Public Sub Dispose() Implements IDisposable.Dispose _connection.Dispose() End Sub End Class End Namespace
As you can see in this figure, the DALBase class is marked as MustInherit (abstract in C#) to ensure that it is used in an inheritance relationship. The class then includes a private SqlConnection object instantiated in the public constructor, which accepts the connection string as a parameter. The protected Connection property then allows derived classes to access the connection object, while the Dispose method from the IDisposable interface ensures that the connection object is disposed.
Even in the following simplified example you can begin to see the usefulness of abstract base classes:
Public Class WebData : Inherits DALBase Public Sub New() MyBase.New(ConfigurationSettings.AppSettings("ConnectString")) End Sub Public Function GetOrders() As DataSet Dim da As New SqlDataAdapter("usp_GetOrders", Me.Connection) da.SelectCommand.CommandType = CommandType.StoredProcedure Dim ds As New DataSet() da.Fill(ds) Return ds End Function End Class
In this case, the WebData class inherits from DALBase and, as a result, does not need to worry about instantiating the SqlConnection object, but simply passes the connection string to the base class via the MyBase keyword (or the base keyword in C#). The WebData class's GetOrders method can access the protected property using Me.Connection (this.Connection in C#). While this example is relatively simple, as you'll see in Rules 2 and 3, the base class can provide other services as well.
An abstract base class is especially useful when your DAL needs to run in a COM+ environment. In this case, since the code required to allow the component to use COM+ is more complicated, it makes sense to create a serviced component base class like the one shown in Figure 2.
Figure 2 Serviced Component Base Class
<ConstructionEnabled(True), _ Transaction(TransactionOption.Supported), _ EventTrackingEnabled(True)> _ Public MustInherit Class DALServicedBase : Inherits ServicedComponent Private _connection As SqlConnection Protected Overrides Sub Construct(ByVal s As String) _connection = New SqlConnection(s) End Sub Protected ReadOnly Property Connection() As SqlConnection Get Return _connection End Get End Property End Class
In this code, the DALServicedBase class includes essentially the same functionality as in Figure 1, but additionally inherits from ServicedComponent in the System.EnterpriseServices namespace and includes attributes to indicate that the component supports object construction, transactions, and statistics tracking. The base class then takes care of catching the construction string configured in the Component Services Manager and once again creating and exposing the SqlConnection object. It's important to note that when a class inherits from DALServicedBase, it also inherits the settings for the attributes. In other words, a derived class will also have its transaction option set to Supported. If the derived class wants to override this behavior, it has the option of redefining the attribute at the class level.
In addition, your derived classes should avail themselves of both overloaded and shared methods where appropriate. There are essentially two scenarios where overloaded methods (a single method with multiple signatures) are used. First, they can be used when a method needs to accept arguments that vary in their types. The canonical examples of this type in the Framework are the methods of the System.Convert class. For example, the ToString method includes 18 overloaded methods that accept one argument, each of which is of a different type. Second, overloaded methods can be used to expose signatures with increasing numbers of arguments, but not necessarily arguments of different types. This type of overloading turns out to be very effective in a DAL since it can be used to expose alternate signatures for data retrieval and modification. For example, a GetOrders method can be overloaded so that one signature accepts no arguments and returns all orders, while an additional signature accepts an argument that indicates that the caller wants to retrieve only specific customer orders, as shown in the following code:
Public Overloads Function GetOrders() As DataSet Public Overloads Function GetOrders(ByVal customerId As Integer) As DataSet
A good implementation tip in this case is to abstract the functionality of the GetOrders method into a private or protected method that can be called by each of the overloaded signatures.
Shared methods (static in C#) can also be used to expose fields, properties, and methods that are accessible to all instances of your data access classes. Although shared members cannot be used with classes that use Component Services, they can be useful for read-only data that can be retrieved in the shared constructor of your data access class and then read by all instances. Be careful when using shared members for read/write data since multiple threads of execution may be vying for access to the shared data.
Rule 2: Adhere to the Design Guidelines
In the online documentation that ships with Visual Studio .NET there is a topic titled "Design Guidelines for Class Library Developers", which covers the naming conventions for classes, properties, and methods in addition to patterns for overloaded members, constructors, and events that you should follow. One of the primary reasons you should follow the naming conventions is the cross-language inheritance provided by the .NET Framework. If you are building a DAL base class in Visual Basic® .NET, you'll want to ensure that developers using other languages compatible with the .NET Framework can inherit from it and easily understand how it works. By adhering to the guidelines I've outlined, your naming conventions and constructs won't be language specific. For example, you'll notice in the code samples in this article that camel casing (first word lowercased, plus intercaps) is used for the arguments to the methods, Pascal casing (each word uppercased) is used for the methods, and the base class has the Base suffix to denote that it is an abstract class.
A corollary to the .NET Framework design guidelines are the general design patterns like those documented in the book Design Patterns by the Gang of Four (Addison-Wesley, 1995). For example, the .NET Framework uses a variant of the Observer pattern called the Event pattern that you should follow when exposing events in your classes.
Rule 3: Take Advantage of the Infrastructure
The .NET Framework includes classes and constructs that can assist in handling generic infrastructure-related tasks such as instrumentation and exception handling. Combining these concepts with inheritance through your base class can be quite powerful. For example, consider the tracing functionality exposed in the System.Diagnostics namespace. In addition to the Trace and Debug classes, this namespace also includes classes derived from Switch and TraceListener. The Switch classes, BooleanSwitch and TraceSwitch, can be configured to be turned on and off both programmatically and through the application's configuration file. In the case of TraceSwitch, multiple levels of tracing can be exposed. The TraceListener classes, TextWriterTraceListener and EventLogTraceListener, direct the output of the Trace and Debug methods to a text file and event log, respectively.
As a result, you can add tracing functionality to your base class to make it simple for the derived classes to log messages. In turn, the application can control whether tracing is enabled using the application configuration file. You can add this functionality to the DALBase class shown in Figure 1 by including a private variable of type BooleanSwitch and instantiating it in the constructor:
Public Sub New(ByVal connect As String) _connection = New SqlConnection(connect) _dalSwitch = New BooleanSwitch("DAL", "Data Access Code") End Sub
The arguments to BooleanSwitch include its name and description. You can then add a protected property to turn the switch on and off and a protected property to format and write a trace message using the WriteLineIf method of the Trace object:
Protected Property TracingEnabled() As Boolean Get Return _dalSwitch.Enabled End Get Set(ByVal Value As Boolean) _dalSwitch.Enabled = Value End Set End Property Protected Sub WriteTrace(ByVal message As String) Trace.WriteLineIf(Me.TracingEnabled, Now & ": " & message) End Sub
In this way, derived classes don't have to know about the switch and listener classes themselves and can simply call the WriteTrace method when a significant event occurs in the data access class.
To create a listener and turn it on, the application configuration file can also be used. Figure 3 shows a simple configuration file that turns on the DAL switch just shown and directs its output to the file DALLog.txt through a TextWriterTraceListener called myListener. Of course, you can also programmatically create your own listener by deriving from the TraceListener class and including the listener directly in your DAL.
Figure 3 Tracing Configuration
<?xml version="1.0" encoding="utf-8" ?> <configuration> <system.diagnostics> <switches> <add name="DAL" value="1" /> </switches> <trace autoflush="true" indentsize="4"> <listeners> <add name="myListener" type="System.Diagnostics.TextWriterTraceListener" initializeData="DALLog.txt" /> </listeners> </trace> </system.diagnostics> </configuration>
A second bit of infrastructure that you should certainly be taking advantage of is structured exception handling (SEH). At the most basic level, your DAL can expose its own Exception object inherited from System.ApplicationException and can additionally expose custom members. For example, the DALException object shown in Figure 4 can be used to wrap exceptions thrown by code in the data access classes. The base class can then expose a protected method to wrap the exception, populate the custom members, and throw it back to the caller, as follows:
Protected Sub ThrowDALException(ByVal message As String, _ ByVal innerException As Exception) Dim newMine As New DALException(message, innerException) newMine.ConnectString = Me.Connection.ConnectionString Me.WriteTrace(message & "{" & innerException.Message & "}") Throw newMine End Sub
Figure 4 Custom Exception Class
Public Class DALException : Inherits ApplicationException Public Sub New() MyBase.New() End Sub Public Sub New(ByVal message As String) MyBase.New(message) End Sub Public Sub New(ByVal message As String, ByVal innerException As Exception) MyBase.New(message, innerException) End Sub ' Add custom members here Public ConnectString As String End Class
In this way, derived classes can simply call the protected method, passing in the data-specific exception (typically a SqlException or OleDbException) that was intercepted and adding a message that pertains to the particular data domain. The base class wraps the exception in a DALException and throws it back to the caller. This allows the caller to easily capture all exceptions from the DAL using a single Catch statement.
As an alternative, take a look at the Exception Management Application Block Overview published on MSDN®. This framework couples the publishing of exceptions with application logging through a set of objects. In fact, you can plug your own custom exception classes into this framework by inheriting them from the BaseApplicationException class provided in the .NET Framework.
Rule 4: Choose Your External Interface Carefully
As you design the methods of your data access classes, you need to consider how they will accept and return data. For most developers, there are three primary choices: using ADO.NET objects directly, using XML, and using custom classes.
If you're going to expose ADO.NET objects directly, you can employ one of two programming models. The first includes the DataSet and DataTable objects, which are useful for disconnected data access. Much has been written about the DataSet and its associated DataTables, but this is most useful when you need to work with data that's been disconnected from the underlying data store. In other words, a DataSet can be passed between the tiers of your application, even when the tiers are physically distributed, as is the case when the business and data services tiers are placed on a cluster of servers that are separate from the presentation services. Additionally, DataSet objects are ideal for returning data through XML-based Web Services because they're serializable and thus can be returned in a SOAP response message.
This is different from accessing data using classes that implement the IDataReader interface, such as SqlDataReader and OleDbDataReader. These data readers are used to access data in a forward-only, read-only manner. The big difference between the two is that the DataSet and DataTable objects can be passed between application domains (and thus between processes on the same or separate machines) by value, whereas the data reader can be passed around, but is always done so by reference. Take a look at Figure 5, in which Read and GetValues are executed in the server process and their return values copied to the client.
Figure 5** Remoting Data Readers **
This diagram highlights how a data reader lives in the app domain in which it was created and all access to it results in a round-trip between the client and server application domain. This means that your data access methods should return data readers only when they are executing in the same app domain as the caller.
When using data readers, there are two other issues to consider. First, when you return a data reader from a method in your data access class, you need to think about the lifetime of the connection object associated with the data reader. By default, the connection remains busy while the caller is iterating through the data reader. Unfortunately, when the caller is finished, the connection remains open and is therefore not returned to a connection pool (if connection pooling is enabled). You can, however, instruct the data reader to close its connection when its Close method is called by passing the CommandBehavior.CloseConnection enumerated value to the ExecuteReader method of command objects.
Second, in order to decouple your presentation layer from a particular Framework data provider (such as SqlClient or OleDb), the calling code should reference the return value using the IDataReader interface rather than the concrete type, such as SqlDataReader. In this way, the presentation layer won't have to change if your application moves from an Oracle to a SQL Server™ back end and the return type of a method in the data access class changes.
If you want your data access classes to return XML, you can choose from the XmlDocument and XmlReader classes in the System.Xml namespace, which are analogous to the DataSet and IDataReader. In other words, your methods should return an XmlDocument (or XmlDataDocument) when the data is to be disconnected from its source, whereas XmlReader can be used for streamed access to XML data.
Finally, you can also decide to return custom classes with public properties. These classes can be marked with the Serialization attribute so they can be copied across application domains. Plus, you'll likely need strongly typed collection classes if you want to return multiple objects from your methods.
Figure 6 contains an example of a simple Book class and its associated collection class. You'll notice that the Book class is marked with Serializable to enable "by value" semantics across application domains. This class implements the IComparable interface so that when it is included in a collection class, it will be sorted by Title by default. The BookCollection class derives from ArrayList in the System.Collections namespace and shadows the Item property and Add method in order to restrict the collection to only Book objects.
Figure 6 Using a Custom Class
Imports System.Xml.Serialization <Serializable()> _ Public Class Book : Implements IComparable <XmlAttributeAttribute()> Public ProductID As Integer Public ISBN As String Public Title As String Public Author As String Public UnitCost As Decimal Public Description As String Public PubDate As Date Public Function CompareTo(ByVal o As Object) As Integer _ Implements IComparable.CompareTo Dim b As Book = CType(o, Book) Return Me.Title.CompareTo(b.Title) End Function End Class Public NotInheritable Class BookCollection : Inherits ArrayList Default Public Shadows Property Item(ByVal productId As Integer) _ As Book Get Return Me(IndexOf(productId)) End Get Set(ByVal Value As Book) Me(IndexOf(productId)) = Value End Set End Property Public Overloads Function Contains(ByVal productId As Integer) As _ Boolean Return (-1 <> IndexOf(productId)) End Function Public Overloads Function IndexOf(ByVal productId As Integer) As _ Integer Dim index As Integer = 0 Dim item As Book For Each item In Me If item.ProductID = productId Then Return index End If index = index + 1 Next Return -1 End Function Public Overloads Sub RemoveAt(ByVal productId As Integer) RemoveAt(IndexOf(productId)) End Sub Public Shadows Function Add(ByVal value As Book) As Integer Return MyBase.Add(value) End Function End Class
By using custom classes you get complete control of how the data is represented, developer productivity through strong typing and IntelliSense®, and no caller dependency on ADO.NET. However, this approach requires more code since the .NET Framework does not contain any object-relational mapping technology (other than typed DataSet objects that are essentially derived DataSet classes). In these cases, you'll typically create a data reader in your data access class and use it to populate the custom class.
Rule 5: Abstract .NET Framework Data Providers
The final rule specifies why and how you should abstract the .NET Framework data provider used internally in your DAL. As I've mentioned, the ADO.NET programming model exposes distinct .NET Framework data providers including SqlClient, OleDb, and others available on the MSDN Online Web site. While this design results in improved performance and the ability for providers to expose data-source-specific functionality (such as the ExecuteXmlReader method of the SqlCommand object), it forces you to decide which provider to code against. In other words, a developer typically chooses to use SqlClient or OleDb and then writes code directly against the classes in the respective namespace.
If you want to change the .NET Framework data provider, you'll need to recode your data access methods. To avoid this, you can use a design pattern known as the Abstract Factory. Using this pattern, you can build a simple class that exposes methods to create the primary .NET Framework data provider objects (command, connection, data adapter, and parameter) based on information identifying the .NET Framework data provider passed into the constructor. The code in Figure 7 shows a simple C# version of this class.
Figure 7 ProviderFactory
public enum ProviderType :int {SqlClient = 0, OLEDB = 1} public class ProviderFactory { public ProviderFactory(ProviderType provider) { _pType = provider; _initClass(); } public ProviderFactory() { _initClass(); } private ProviderType _pType = ProviderType.SqlClient; private bool _pTypeSet = false; private Type[] _conType, _comType, _parmType, _daType; private void _initClass() { _conType = new Type[2]; _comType = new Type[2]; _parmType = new Type[2]; _daType = new Type[2]; // Initialize the types for the providers _conType[(int)ProviderType.SqlClient] = typeof(SqlConnection); _conType[(int)ProviderType.OLEDB] = typeof(OleDbConnection); _comType[(int)ProviderType.SqlClient] = typeof(SqlCommand); _comType[(int)ProviderType.OLEDB] = typeof(OleDbCommand); _parmType[(int)ProviderType.SqlClient] = typeof(SqlParameter); _parmType[(int)ProviderType.OLEDB] = typeof(OleDbParameter); _daType[(int)ProviderType.SqlClient] = typeof(SqlDataAdapter); _daType[(int)ProviderType.OLEDB] = typeof(OleDbDataAdapter); } public ProviderType Provider { get { return _pType; } set { if (_pTypeSet) { throw new ReadOnlyException("Provider already set to " + _pType.ToString()); } else { _pType = value; _pTypeSet = true; } } } public IDataAdapter CreateDataAdapter(string commandText,IDbConnection connection) { IDataAdapter d; IDbDataAdapter da; d = (IDataAdapter)Activator.CreateInstance(_daType[(int)_pType], false); da = (IDbDataAdapter)d; da.SelectCommand = this.CreateCommand(commandText, connection); return d; } public IDataParameter CreateParameter(string paramName, DbType paramType) { IDataParameter p; p = (IDataParameter)Activator.CreateInstance(_parmType[(int)_pType], false); p.ParameterName = paramName; p.DbType = paramType; return p; } public IDataParameter CreateParameter(string paramName, DbType paramType, Object value) { IDataParameter p; p = (IDataParameter)Activator.CreateInstance(_parmType[(int)_pType], false); p.ParameterName = paramName; p.DbType = paramType; p.Value = value; return p; } public IDbConnection CreateConnection(string connect) { IDbConnection c; c = (IDbConnection)Activator.CreateInstance(_conType[(int)_pType], false); c.ConnectionString = connect; return c; } public IDbCommand CreateCommand(string cmdText, IDbConnection connection) { IDbCommand c; c = (IDbCommand)Activator.CreateInstance(_comType[(int)_pType], false); c.CommandText = cmdText; c.Connection = connection; return c; } }
In order to use this class, the code in your data access classes would need to program against the various interfaces that the .NET Framework data providers implement, including IDbCommand, IDbConnection, IDataAdapter, and IDataParameter. For example, in order to fill a DataSet with results from a parameterized stored procedure, you could use the following code inside a method of your data access class:
Dim _pf As New ProviderFactory(ProviderType.SqlClient) Dim cn As IDbConnection = _pf.CreateConnection(_connect) Dim da As IDataAdapter = _pf.CreateDataAdapter("usp_GetBook", cn) Dim db As IDbDataAdapter = CType(da, IDbDataAdapter) db.SelectCommand.CommandType = CommandType.StoredProcedure db.SelectCommand.Parameters.Add(_pf.CreateParameter("@productId", _ DbType.Int32, id)) Dim ds As New DataSet("Books") da.Fill(ds)
Typically, you would declare the ProviderFactory variable at the class level and instantiate it in the constructor of the data access class. Additionally, its constructor would be populated with the provider read from a configuration file, rather than hardcoded, as shown here. As you can imagine, the ProviderFactory would be a great addition to your DAL base class and can then be included in the assembly and distributed to other developers.
You can choose to take it a step further and encapsulate common ADO.NET code that developers write over and over. In fact, Microsoft has released a Data Access Application Block that performs this function for SQL Server (see Data Access Application Block Overview).
Conclusion
In the era of Web Services, more and more applications will be built to manipulate data from an isolated application layer. If you follow some basic rules and commit them to habit, writing data-access code will be faster, easier, and more reusable, save you trips to the server, and allow you to keep your data separate.
For related articles see:
Data Access Application Block Overview
For background information see:
Design Patterns by Erich Gamma, Richard Helm, Ralph Johnson, John Vlissides, (Addison-Wesley, 1995)
Teach Yourself ADO.NET in 21 Days by Dan Fox (SAMS, 2002)
Exception Management Application Block Overview
Dan Foxis a Technical Director for Quilogy (https://www.quilogy.com) in Overland Park, Kansas where he evangelizes technology. Dan is the author of Teach Yourself ADO.NET in 21 Days and Building Distributed Applications with Visual Basic .NET (both from SAMS). Dan can be reached at dfox@quilogy.com.