A Data Access Layer comprises of a collection of classes, interfaces and their methods and properties that are used to perform CRUD (Create, Read, Update and Delete) operations in the application. A Data Access Layer encapsulates the code that is used to connect to the database and perform these operations and it actually works as a link between the business entities in your application and the actual data storage layer. You typically use the Data Access Layer to create and populate business entities with data from the database and for updating and storing business entities in the database.
Features of a Data Access Layer
A Data Access Layer should provide the following features:
■Connect to the database
■Open and Close connections
■Support for CRUD operations
■Transaction management
■Provider independence
■Concurrency management
Some of the points that we should keep in mind are :
•Create all your database access routines as generic, versatile objects, rather than client-side repeated-code methods. All your UI should do is interact with these components, and not have to work out any details.
•If you want to page data or provide your application with functionality use a Dataset as the preferred method of disconnected data, otherwise use a Datareader for all your data retrieval. For XML users this would translate to an XmlReader , and StreamReader for text files, both equivalent as that of a DataReader for their respective file types.
•Use the correct managed data provider for your particular database, ex. System.Data.SqlClient for SQL Server , System.Data.OleDb for Access, System.Data.OracleClient for Oracle , etc.
•Use Strongly-Typed Datasets over the standard, common un-Typed ones when possible, as this yields better performance. A generated typed Dataset is basically an early bound class inheriting the Dataset base class, as opposed to the typical late bound, non-inheritable Dataset. Thus, it allows for greater flexibility in dealing with your data. In turn, you'll be dealing with easier to read code, whereby you can access fields and tables by customizable names, instead of the conventional collection-based way.
•Take full advantage of .NET Caching, as this will significantly boost performance and greatly diminish any persistent database interaction. However, and an important however is, if you decide on caching your data from within your data object, then conventional caching methods won't apply. Within the confines of components, and its interaction with the caller page, these requests happen via an HTTP request. Therefore, caching within your component can only be implemented by using the HttpContext.Cache Class property, part of .NET's Page class.
•Use parameterized Stored Procedures along side .NET's Command Class Prepare() method, that caches your query for all future SQL Server uses.
objCommand.Prepare()
•Make chunky calls to your database rather than smaller, chatty calls. It's better to group all similar, associated calls in one SQL Server access. In other words, use one solid connection to retrieve as much as you can, as opposed to multiple ones.
•Take advantage of connection pooling (whereby all your connection strings are identical) by storing all your connection strings in your web.config file. Furthermore, you'll find that your application will scale alot better with any increased network traffic by doubling, even tripling the default Max Pool Size of 100 to 300, and even bumping up the default Connect Timeout of 10 seconds to 60. Additionally, if your not enlisting any transactional procedures, include enlist=false; to your database's connection string for added performance.
Additionally, if your not enlisting any transactional procedures, include enlist=false ; to your database's connection string for added performance.
•Finally, remember to close, clear and dispose of all your data objects no matter what. If you would like to further confirm that your database connection is indeed closed, you would write:
A good article on building a data access layer is as follows :http://msdn.microsoft.com/en-us/library/aa581776.aspx
