A Data Access Logic Component provides methods to perform the following tasks upon a database, on behalf of the caller:

• Create records in the database
• Read records in the database, and return business entity data to the caller
• Update records in the database, by using revised business entity data supplied by the caller
• Delete records in the database

The methods that perform the preceding tasks are often called “CRUD” methods, where CRUD is an acronym based on the first letter of each task.

Each Data Access Logic Component deals with a specific type of business entity. For example, the Customer Data Access Logic Component deals with Customer business entities. There are many different ways to represent business entities, depending on factors such as the following:

• Do you need to bind business entity data to controls in a Microsoft Windows® form or on an ASP.NET page?
• Do you need to perform sorting or searching operations on the business entity data?
• Does your application deal with business entities one at a time, or does it typically deal with sets of business entities?
• Will you deploy your application locally or remotely?
• Will the business entity be used by XML Web services?
• How important are nonfunctional requirements, such as performance, scalability, maintainability, and programming convenience?

This document outlines the advantages and disadvantages of the following implementation options:

• XML. You use an XML string or an XML Document Object Model (DOM) object to represent business entity data. XML is an open and flexible data representation format that can be used to integrate diverse types of applications.
• DataSet. A DataSet is an in-memory cache of tables, obtained from a relational database or an XML document. A Data Access Logic Component can use a DataSet to represent business entity data retrieved from the database, and you can use the DataSet in your application. For an introduction to DataSets, see “Introducing ADO.NET” in the .NET Data Access Architecture Guide.
• Typed DataSet. A typed DataSet is a class that inherits from the ADO.NET DataSet class and provides strongly typed methods, events and properties to access the tables and columns in a DataSet.
• Business Entity Component. This is a custom class to represent each type of business entity. You define fields to hold the business entity data, and you define properties to expose this data to the client application. You define methods to encapsulate simple business logic, making use of the fields defined in the class. This option does not implement CRUD methods as pass-through methods to the underlying Data Access Logic Component; the client application communicates directly with the Data Access Logic Component to perform CRUD operations.
• Business Entity Component with CRUD behaviors. You define a custom entity class as described previously, and you implement the CRUD methods that call the underlying Data Access Logic Component associated with this business entity.
  

  Note   If you prefer to work with your data in a more object-oriented fashion, you can use the alternate approach of defining an object persistence layer based on the reflection capabilities of the common language runtime. You can create a framework that uses reflection to read the properties of the objects and use a mapping file to describe the mapping between objects and tables. However, to implement this effectively would constitute a major investment in infrastructure code. This outlay might be viable for ISVs and solution providers, but not for the majority of organizations, and it is beyond the scope of this document.

 A DataSet represents a collection of DataTable objects, together with the relationships and constraints that bind the various tables together. In effect, the DataSet is an in-memory relational structure with built-in XML support.

ADO.NET relies on the services of .NET data providers. These provide access to the underlying data source, and they comprise four key objects (Connection, Command, DataReader, and DataAdapter).

The types (classes, structs, enums, and so on) associated with each .NET data provider are located in their own namespaces:

• System.Data.SqlClient. Contains the SQL Server .NET Data Provider types.
• System.Data.OracleClient. Contains the Oracle .NET Data Provider
• System.Data.OleDb. Contains the OLE DB .NET Data Provider types.
• System.Data.Odbc. Contains the ODBC .NET Data Provider types.
• System.Data. Contains provider-independent types such as the DataSet and DataTable.

If you are likely to target different data sources and want to move your code from one to the other, consider programming to the IDbConnection, IDbCommand, IDataReader, and IDbDataAdapter interfaces located within the System.Data namespace. All implementations of the Connection, Command, DataReader, and DataAdapter objects must support these interfaces.

A data provider in the .NET Framework enables you to connect to a data source in order to retrieve and modify data from the data source. A .NET Framework data provider also serves as a bridge between a data source and an ADO.NET DataSet.

Most code fragments shown in this document use SqlCommand objects to call stored procedures to perform database manipulation. In some cases, you will not see the SqlCommand object because the stored procedure name is passed directly to a SqlDataAdapter object. Internally, this still results in the creation of a SqlCommand object.

You should use stored procedures instead of embedded SQL statements for a number of reasons:

• Stored procedures generally result in improved performance because the database can optimize the data access plan used by the procedure and cache it for subsequent reuse.
• Stored procedures can be individually secured within the database. A client can be granted permissions to execute a stored procedure without having any permissions on the underlying tables.
• Stored procedures result in easier maintenance because it is generally easier to modify a stored procedure than it is to change a hard-coded SQL statement within a deployed component.
• Stored procedures add an extra level of abstraction from the underlying database schema. The client of the stored procedure is isolated from the implementation details of the stored procedure and from the underlying schema.
• Stored procedures can reduce network traffic, because SQL statements can be executed in batches rather than sending multiple requests from the client.

The SQL Server online documentation strongly recommends that you do not create any stored procedures using “sp_” as a name prefix because such names have been designated for system stored procedures. SQL Server always looks for stored procedures beginning with sp_ in this order:

1. Look for the stored procedure in the master database.
2. Look for the stored procedure based on any qualifiers provided (database name or owner).
3. Look for the stored procedure, using dbo as the owner if an owner is not specified.

Pooling is available in two forms for applications that use the Microsoft Data Access Components: ODBC offers connection pooling through the ODBC Data Source Administrator, and OLE DB core components provide resource pooling as well as additional services such as shaping and the client-side cursor.

You cannot use both ODBC connection pooling and OLE DB resource pooling for a given connection to the database. You should choose which form of pooling you want to use and stick with it exclusively within a given application. However, you can disable OLE DB resource pooling and use connection pooling if you are using the OLE DB Provider for ODBC.

To take advantage of connection pooling, a driver must be threadsafe.

Connection pooling can be enabled or disabled from the following locations:

• The ODBC Data Source Administrator—At the Data Source Administrator level, pooling can be enabled or disabled for the entire driver. This includes features such as whether pooling is on, the time-out value, and the Wait Retry setting. Unless you are writing a native ODBC application, you need be concerned only with the Data Source Administrator.
• The registry—Before the Data Source Administrator offered support for connection pooling in ODBC 3.5 (MDAC 1.5), you needed to use the registry to configure ODBC connection pooling.
• The calling application—From an application level (native ODBC application only), your application can make calls to the Data Source Administrator to tell it whether you want pooling and the type of pooling you want.

This article has repeatedly emphasized the need for developers who use OLE DB resource pooling to keep at least one connection open for each set of user credentials used to access the data store. This is not to be confused with the mistake of opening up multiple and unnecessary Connection objects, or even multiple Recordset objects. It’s also not to be confused with using just one Connection object for several hundred Active Server Pages (ASP pages).

ASP developers should open one connection per set of unique user credentials. However, an ASP developer can eliminate the benefits of pooling in this scenario in the following ways:

• Instantiating dozens or even hundreds of instances of ADO objects at once.
• Dim cnn(200) As ADODB.Connection
  Dim rst(200) As ADODB.Recordset Presently, these two lines of code appear to be frequently and incorrectly used on individual ASP pages. The overhead of instantiating, opening, and manipulating this many objects will eliminate any benefit of pooling, not to mention that it will swell the pool to unnecessary size just to hold each of those connections. If you use this technique on multiple ASP pages, which in turn are hit by multiple users, the amount of memory needed just to hold all of those ADO objects in memory can soon reach into the gigabytes.
• Using just one Connection object for multiple ASP pages.If you don’t want to circumvent the benefits of pooling, however, you can do this: Within a given ASP page, open the Connection object and one or more Recordset objects that you need. Then close and delete them.This also applies for non-ASP developers who are developing an application with multiple threads. Do not try to share a single Connection object between all threads. Instead, use one thread, one Connection object, and one persistent connection in your main application to keep the pool alive.For Microsoft Transaction Server developers, this is not an issue. Microsoft Transaction Server itself enables the pool, whether you implement a single Connection object or not. Because Microsoft Transaction Server is inherently stateless, trying to keep that persistent Connection object around, let alone actually use it, is redundant.

While pooling is a valuable tool that can give applications increased performance and scalability, it can also be sabotaged to the point of uselessness through coding techniques such as those just listed.

Here is a list of the top 10 reasons why pooling might not be turned on. This text was first made available in the OLE DB Readme file.

1. The registry value OLEDB_Services must be present under the provider’s HKEY_CLASSES_ROOT/<CLSID> key. OLEDB_Services is most commonly set to 0xffffffff, or to desired bits of DBPROPVAL_OS_*. If this does not exist or is set to 0×00000000, pooling will never occur. For more information, refer to “Setting Provider Service Defaults” in the OLE DB Services documentation.
2. The consumer can override the OLEDB_Services key and disable pooling by setting DBPROP_INIT_OLEDBSERVICES. For more information, refer to “Overriding Provider Service Defaults” in the OLE DB Services documentation.
3. The provider must be free-threaded. If a provider developer uses the OLE DB provider templates for Visual C++ or Visual Basic, the templates will not create free-threaded providers by default. In those cases, pooling will be disabled regardless of what the property or registry indicates.
4. Setting DBPROP_INIT_PROMPT and DBPROMP_INIT_HWND disables pooling of a data source object. These properties must be set to either VT_EMPTY or NOPROMPT.
5. Consumers cannot use QueryInterface for any interface unknown to service components prior to initialization. That is, applications that need to pool should not use QueryInterface, as COM suggests, to see what interfaces the provider supports. For example, a simple QI for IDBAsynchStatus, to determine whether the provider is Asynch or not, eliminates the data source object from the pool.
6. Calling IDBProperties::GetPropertyInfo prior to initialization disables pooling.
7. If IDBInitialize::UnInitialize is called, the released data source object will not be pooled.
8. Pooling does not occur on Windows 95.
9. Providers must correctly implement aggregation.
10. Do not use data source object notifications. If you use a QueryInterface for IConnectionPointContainer to advise a listener to the data source object, the object will never be pooled.

NOTE   These items are cumulative. That is, any item will disable pooling for that data source object, regardless of what the other values are.

Recommendations for Mapping Relational Data to Business Entities

To map relational data to business entities, consider the following recommendations:

• Take the time to analyze and model the logical business entities of your application, rather than defining a separate business entity for every table. One of the ways to model how your application works is to use Unified Modeling Language (UML). UML is a formal design notation for modeling objects in an object-oriented application, and for capturing information about how objects represent automated processes, human interactions, and associations. For more information, see Modeling Your Application and Data.
• Do not define separate business entities to represent many-to-many tables in the database; these relationships can be exposed through methods implemented in your Data Access Logic Component. For example, the OrderDetails table in the preceding example is not mapped to a separate business entity; instead, the Orders data access logic component encapsulates the OrderDetails table to achieve the many-to-many relationship between the Order and Product tables.
• If you have methods that return a particular type of business entity, place these methods in the Data Access Logic Component for that type. For example, if you are retrieving all orders for a customer, implement that function in the Order Data Access Logic Component because your return value is of the type Order. Conversely, if you are retrieving all customers that have ordered a specific product, implement that function in the Customer Data Access Logic Component.
• Data access logic components typically access data from a single data source. If aggregation from multiple data sources is required, it is recommended to define a separate Data Access Logic Component to access each data source that can be called from a higher-level business process component that can perform the aggregation. There are two reasons for this recommendation:
  • Transaction management is centralized to the business process component and does not need to be controlled explicitly by the Data Access Logic Component. If you access multiple data sources from one Data Access Logic Component, you will need the Data Access Logic Component to be the root of transactions, which will introduce additional overhead on functions where you are only reading data.
  • Aggregation is usually not a requirement in all areas of the application, and by separating the access to the data, you can let the type stand alone as well as be part of an aggregation when needed.

What Is Modeling?

Application and data modeling is the process of identifying, documenting, and implementing the data and process requirements for your application. This involves reviewing existing data models and processes to see if they can be reused, and creating new data models and processes to suit your application’s unique requirements.

The major events in modeling are:

• Identifying the data and associated processes (such as that sales people in the field need to see the online product catalog and submit new customer orders).
• Defining the data (such as data types, sizes, and defaults).
• Ensuring data integrity (using business rules and validation checks).
• Defining the operational processes (such as security reviews and backups).
• Choosing a data storage technology (such as relational, hierarchical, or indexed).

It is important to understand that modeling often involves company management in unexpected ways. For example, data ownership (and the implied responsibility of data maintenance, accuracy, and timeliness) is often challenged by new insights into what data elements should be maintained by which organizations. Data design often forces a company to recognize how enterprise data systems are interdependent, and encourages the efficiencies, cost savings, and strategic opportunities that come from coordinated data planning.

At the conclusion of modeling, you have completely defined your application’s requirements, identified possible data and service reuse by other enterprise applications, and provided a strong foundation for future extensions.

Implementing Data Access Logic Components

A Data Access Logic Component is a stateless class, meaning that all messages exchanged can be interpreted independently. No state is held between calls. The Data Access Logic Component provides methods for accessing one or more related tables in a single database, or in some instances, multiple databases as in the case of horizontal database partitioning. Typically, the methods in a Data Access Logic Component invoke stored procedures to perform their operations.

One of the key goals of data access logic components is to hide the invocation and format idiosyncrasies of the database from the calling application. Data access logic components provide an encapsulated data-access service to these applications. Specifically, data access logic components handle the following implementation details:

• Manage and encapsulate locking schemes
• Handle security and authorization issues appropriately
• Handle transaction issues appropriately
• Perform data paging
• Perform data-dependent routing if required
• Implement a caching strategy if appropriate, for queries of nontransactional data
• Perform data streaming and data serialization

Microsoft provides the Data Access Application Block for .NET, which can be used as a generic data access helper component in your applications when you use Microsoft SQL Server™ databases.

To support a diverse range of business processes and applications, consider the following techniques to pass data to and from Data Access Logic Component methods:

• Passing business entity data into methods in the Data Access Logic Component. You can pass the data in several different formats: as a series of scalar values, as an XML string, as a DataSet or as a custom Business Entity Component.
• Returning business entity data from methods in the Data Access Logic Component. You can return the data in several different formats: as output-parameter scalar values, as an XML string, as a DataSet, as a custom Business Entity Component or as a data reader.

Recommendations for Using Stored Procedures with Data Access Logic Components

Consider the following recommendations for using stored procedures in conjunction with data access logic components:

• Exposing stored procedures. Data access logic components should be the only components that are exposed to database schema information, such as stored procedure names, parameters, tables, and fields. Your business entity implementation should have no knowledge of or dependency on database schemas.
• Associating stored procedures with data access logic components. Each stored procedure should be called by only one Data Access Logic Component, and should be associated with the Data Access Logic Component that owns the action. For example, imagine that a customer places an order with a retailer. You can write a stored procedure named OrderInsert, which creates the order in the database. In your application, you must decide whether to call the stored procedure from the Customer Data Access Logic Component or from the Order Data Access Logic Component. The Order Data Access Logic Component is a better choice because it handles all order-related processing (the Customer Data Access Logic Component handles customer information, such as the customer’s name and address).
• Naming stored procedures. When you define stored procedures for a Data Access Logic Component to use, choose stored procedure names that emphasize the Data Access Logic Component to which they pertain. This naming convention helps to easily identify which components call which stored procedures, and provides a way to logically group the stored procedures inside SQL Enterprise Manager. For example, you can proactively write stored procedures named CustomerInsert, CustomerUpdate, CustomerGetByCustomerID, and CustomerDelete for use by the Customer Data Access Logic Component, and then provide more specific stored procedures—such as CustomerGetAllInRegion—to support the business functions of your application.
  

  Note   Do not preface your stored procedure names with sp_, because doing so reduces performance. When you call a stored procedure that starts with sp_, SQL Server always checks the master database first, even if the stored procedure is qualified with the database name.

• Addressing security issues. If you accept user input to perform queries dynamically, do not create a string by concatenating values without using parameters. Also avoid using string concatenation in stored procedures if you are using sp_execute to execute the resulting string, or if you do not take advantage of sp_executesql parameter support.

Designing Data Tier Components and Passing Data Through Tiers

.NET Data Access Architecture Guide

.Net Framework home

Implementing a .NET Framework Data Provider

Pooling in the Microsoft Data Access Components

Data access