Implement a Data Access Layer for Your App with ADO.NET

原创 2004年07月23日 09:59:00

<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

   MSDN Home >  MSDN Library >  .NET Development >  ADO.NET > 


Page Options






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 Difficulty 1 2 3

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.


<?xml:namespace prefix = w ns = "urn:schemas-microsoft-com:office:word" />f 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

Figure 2 Serviced Component Base Class

Figure 3 Tracing Configuration

Figure 4 Custom Exception Class

Figure 6 Using a Custom Class

Figure 7 ProviderFactory

  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()


     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()



         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.
  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


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


        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.
  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

  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.
  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.
  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")


  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).

  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.





拒绝理由1.获取用户信息2.审核人对IAP商店的质疑—————–1.获取用户信息———————苹果认为用户信息涉及隐私,如果未经用户允许获取用户信息,会被reject。 但是,审核人员有可能过度使用...
  • imanapple
  • imanapple
  • 2015-08-11 19:47:07
  • 3019


July 8, 2015 at 7:06 AM 发件人 Apple 17.1 - Apps cannot transmit data about a user without obtainin...
  • tubiebutu
  • tubiebutu
  • 2015-07-08 09:47:39
  • 3121

Data Collection and Storage We noticed that your app requests the user’s consent to access the ....

  • FScyj1
  • FScyj1
  • 2018-01-02 09:55:36
  • 3505

neural networks deep learning Planar data classification with a hidden layer Homework

coursera neural networks deep learning编程参考答案
  • qq_29300341
  • qq_29300341
  • 2017-08-27 19:23:44
  • 7148

第三周编程作业-Planar data classification with one hidden layer

Planar data classification with one hidden layer Welcome to your week 3 programming assignment. It's...
  • yanqianglifei
  • yanqianglifei
  • 2017-09-19 19:49:25
  • 2436

Spring 异常Does your handler implement a supported interface like Controller?] with root cause

今天在做项目的过程中,将Json转换成DTO对象出现问题,尝试了多种方法都不对,异常如下: 2012-11-21 15:16:03 org.apache.catalina.core.Standard...
  • tangyajun_168
  • tangyajun_168
  • 2012-11-21 15:20:45
  • 4946

Xcode8使用出现This app has attempted to access privacy-sensitive data without a usage description.

意思就是说,你需要在 info.plist 文件添加一个 NSLocationAlwaysUsageDescription的 key,然后添加一个描述。 解决方案 1.在项目中找到info.plis...
  • yuhao309
  • yuhao309
  • 2016-11-22 13:45:42
  • 2103

iOS10权限privacy-sensitive data崩溃

[access] This app has crashed because it attempted to access privacy-sensitive data without a usage ...
  • qq_27247497
  • qq_27247497
  • 2016-09-19 09:42:55
  • 1874

This app attempts to access privacy-sensitive data without a usage description

This app attempts to access privacy-sensitive data without a usage description. The app's Info.plist...
  • chaochso
  • chaochso
  • 2016-09-21 11:28:02
  • 2608

No adapter for handler Does your handler implement a supported interface like Controller

 看到关于spring一本书上例子时,出现了错误:No adapter for handler Does your handler implement a supported interface l...
  • kangjian39
  • kangjian39
  • 2008-03-30 20:49:00
  • 5354
您举报文章:Implement a Data Access Layer for Your App with ADO.NET