Data Access Layer (OLTP)---a Technical Reference Guide for Designing Mission-Critical OLTP Solutions

  • Article

Want more guides like this one? Go to Technical Reference Guides for Designing Mission-Critical Solutions.

A Data Access Layer (DAL) is generally designed and deployed to create a development experience that insulates application developers from being domain experts in the persistence layer. This allows database experts to optimize interaction with the persistence layer without unduly impacting the application development process. It is easier to educate this small group of people responsible for DAL best practices and adherence guidelines than if the entire group of developers were to develop Microsoft SQL Server access and persistency code. This layer is generally characterized by:

  • The development platform and the availability of the data access libraries.

  • The database interaction styles (for example, transactional, non-transactional, and chatty versus set-oriented) and interfaces (for example, commands for execution, retrieving of answer sets, and fetch and synching).

  • The option to leverage specific database management system (DBMS) features versus maintaining some degree of database independence. This is of particular interest to the independent software vendor (ISV) developer community.

A key side benefit of the DAL is to better position oneself to benefit from potential future technology enhancements by using them within DAL without major rework of the business application logic layer.

Best Practices

The following resources provide additional information. (Note that the full URLs for the hyperlinked text are provided in the Appendix at the end of this document).

Overview of the Available Technologies

  • The presentation Data Development GPS: Guidance for Choosing with Right Data Access Technology for Your Application Today1 provides an overview of mapping between the available data access technologies and specific application needs.

  • It is important to understand that there is a significant mismatch between how objects are represented in applications and how entities are designed in relational databases, particularly in regard to many to many relationships. For example, an airline booking system might have tables such as Flights and Passengers along with a linking table FlightManifests that determines which passengers are on which flights. The object layer, however, should not simply expose these three tables as objects. More likely, it would expose a Flight object that contains a collection of Passenger objects (as a property) and a Passenger object that contains a collection of Flight objects. Simple object-relational mapping layers that translate each relational table to an application object invariably lead to either a poor object layer design or a poor database design. LINQ to SQL which is described in the article LINQ to SQL2 offers a simple one to one table to object mapping. By comparison, the Entity Framework which is described in the article ADO.NET Entity Framework,3 offers a richer mapping of objects to relational entities.

  • The white paper Guide to Data Development Platform for .NET Developers4 covers various facets of the .NET data development platform and includes both client-side and service-based APIs, in addition to Microsoft .NET APIs for programming at a server level inside the SQL Server 2008 database and for developing and testing a SQL Server database application.

General Development Guidelines

  • Use stored procedures for your application tier except for the most basic Create, Retrieve, Update, and Delete (CRUD) statements. Besides numerous other benefits, these procedures also offer protection against SQL Injection. For more detail, see the articles:

  • If you require database interaction control at a fine detail level, consider using core ADO.NET classes that form the basis for data access in .NET. They provide the most common and familiar development patterns for data access (connect, query, process) and directly support features like TVPs and Spatial Data Types. For applications where you do not need more than simple connections and streaming results, ADO.NET is a good choice to get a job done quickly and also provide for fine-grained control, more so than the Entity Framework.

  • As a general rule, keep transactions as short as possible and avoid round trips between the application layer and the DBMS inside a logical operation (or physical transaction). Also avoid the use of Microsoft Distributed Transaction Coordinator (MSDTC) transactions when not necessary. For specific guidance on transactional behaviors of the data access code and for other performance optimizations, see the "Design Consideration" section of Chapter 12- Improving ADO.NET Performance.7

  • SQL Server connections are expensive to create and should be reused where possible. Connection pooling is a useful technology for achieving this aim. Pooled connections should be opened as late as possible and closed as soon as possible. Pools are per process for all the connections with the same characteristics (for example, connection string syntax, and security token). The article SQL Server Connection Pooling (ADO.NET)8 contains specific connection pooling guidance.

  • Use Table-Valued Parameters (TVPs) to reduce database round-trips with stored procedures and to substitute large IN clause in ad hoc Transact-SQL statements. For more information, see the article Table-Valued Parameters (Database Engine)9 and PowerPoint presentation Passing a Set of Data to Microsoft SQL Server 2008: How, What, and Why.10

  • Make sure to use simple parameterization or specific techniques (for example, prepared commands, and sp_executesql) to reuse existing query plans as much as possible and to reduce recompilations, as described in the article Plan Caching in SQL Server 2008.11

  • With Entity Framework 4.0 and LINQ to Entities, evaluate Compiled Query and pre-generated views for performance improvement on statement executions and first-time entity data model (EDM) loading. Considerations to improve the performance of .Net 4 Entity Framework applications are discussed in the "Strategies for Improving Performance" section of the document Performance Considerations (Entity Framework).12

Case Studies and References

SQL Server Data access technologies have been critical in many deployments. Examples include:

Questions and Considerations

This section provides questions and issues to consider when working with your customers.

  • Different technology approaches for implementing a DAL align with different business priorities. Object-Relational Modeling tools, such as Entity Framework, provide a higher degree of abstraction and agility (such as offline data sets and synchronization), albeit at the cost of performance. Hand-coded frameworks, such as employing ADO.NET classes (DataReader, etc) can be optimized for performance, but require additional custom code development and are more strongly aligned with a specific persistence store. Discuss with your customer to help chose the appropriate model.

  • Discuss with customers the particular deployment strategy for the DAL that can be driven along many dimensions with various technology offerings and their corresponding pros and cons. For a background, it is worth watching the TechEd 2010 presentation1 to get a historical perspective and to understand that the various APIs are continuously evolving.

Appendix

Following are the full URLs for the hyperlinked text.

1 Data Development GPS: Guidance for Choosing with Right Data Access Technology for Your Application Todayhttps://www.msteched.com/2010/NorthAmerica/DEV324

2 LINQ to SQLhttps://msdn.microsoft.com/en-us/library/bb386976.aspx

3 ADO.NET Entity Frameworkhttps://msdn.microsoft.com/en-us/library/bb399572.aspx

4 Guide to Data Development Platform for .NET Developershttps://msdn.microsoft.com/library/ff770157.aspx

5 Stored Procedure Basicshttps://msdn.microsoft.com/en-us/library/ms191436.aspx

6 SQL Injectionhttps://msdn.microsoft.com/en-us/library/ms161953.aspx

7 Chapter 12- Improving ADO.NET Performancehttps://msdn.microsoft.com/en-us/library/ff647768.aspx

8 SQL Server Connection Pooling (ADO.NET)https://msdn.microsoft.com/en-us/library/8xx3tyca(v=VS.100).aspx

9 Table-Valued Parameters (Database Engine)https://msdn.microsoft.com/en-us/library/bb510489.aspx

10 Passing a Set of Data to Microsoft SQL Server 2008: How, What, and Whyhttp://ecn.channel9.msdn.com/o9/te/NorthAmerica/2010/pptx/DAT404.pptx

11 Plan Caching in SQL Server 2008https://msdn.microsoft.com/en-us/library/ee343986(SQL.100).aspx

12 Performance Considerations (Entity Framework)https://msdn.microsoft.com/en-us/library/cc853327.aspx

13 Epicor Software Corporation: Software Company Eases Development, Reduces Server Requests by 90 Percenthttps://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=4000006887

14 Veracity Solutions: Latest Developer Tools Increase Productivity by 20 Percent for Small Software Companyhttps://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=4000007305