Writing a Data Access Layer (DAL) for SQL Server

By: Arshad Ali   |   Updated: 2010-06-07   |   Comments (8)   |   Related: > Application Development

Problem

Almost every application being developed stores data in some form to make it persistent, this could be either storing it in file, Excel, XML format or in relational database. So each time, when you develop an application, you are required to write code for storing and retrieving data from these data sources.

In this tip, I am going to show you how you can create a Data Access Layer (to store, retrieve and manage data in relational database) in ADO .NET.  I will show how you can make it data provider independent, so that you don't have to re-write your data access layer if the data storage source changes and also you can reuse it in other applications that you develop.

Solution

ADO .NET provides several classes to work with a relational database, for example if you have your data in SQL Server you can use an instance of SqlConnection class to connect to the SQL Server. To execute a command you would create an instance of the SqlCommand class, assign appropriate properties and execute the command on the connection you created.  Apart from these two important classes there are a couple of other classes as well, some of them I will be demonstrating in these examples.

In Code Block 1, you can see I am using these classes to connect to the AdventureWorks database and executing a SELECT statement against it.  In the first case I am keeping the resultset in a sql data reader (each record is fetched one at a time and hence the associated connection would be busy serving the user of the sql data reader until you call the close method) and in the second case I am keeping it in a data table of a dataset (all records are fetched at one time and stored in a memory structure).

C# Code Block 1 - Data Provider Specific

If you look carefully at the above code, even though it works fine you will notice two problems when it comes to portability and reusability. First, the connection string is hard coded in the code itself and hence you cannot port this code to another environment unless you make required changes in the code. Second, some of the classes here are specific to a SQL Server implementation (SqlConnection, SqlCommand, SqlReader, SqlDataAdapter etc) it means if at some point in time you are required to change the data storage source for example, SQL Server to Oracle or vice versa, you would be required to re-write the data access layer once again for the new data storage source.

ADO .NET 2.0 has come up with solutions for these problems. To solve the first problem it allows you to save configuration/connection details in a configuration file (app.config or web.config) and by using ConnectionStringSettings and ConfigurationManager classes of ADO .NET 2.0, you can connect to the appropriate server just by making the required change in the configuration file as shown below; obviously no code change is required.

<connectionStrings>

<add

name="AdventureWorksConnectionString"
connectionString="Server=ARSHADALI-LAP\ARSHADALI; Initial Catalog=AdventureWorks; Integrated Security=SSPI"
providerName="System.Data.SqlClient"/>

</connectionStrings>

The <add> sub-element of the connectionStrings element defines the data source with three different attributes as shown below:

• name - the friendly name for your connection
• connectionString - connection string which will be used to connect to the database
• providerName - data provider unique name, for example for SQL Server its System.Data.SqlClient and for Oracle its System.Data.OracleClient etc.

The ConnectionStringSettings and ConfigurationManager classes are available in System.Configuration namespace (System.Configuratio.dll) and hence you can make the required reference as shown below:

To solve the second problem,  ADO .NET 2.0  introduced several new classes (DbProviderFactories, DbProviderFactory and several database provider base classes) which are based on best practices of using design patterns with Abstract Factory Pattern (provides an interface which allows you to create families of related or dependent objects without specifying their concrete classes), and Factory Method Pattern (defines an interface for creating an object, and let subclasses decide which class to instantiate) or more specifically Provider Model Pattern (allows data access to delegate the responsibility of creating objects to another class).

The DbProviderFactories class exposes the GetFactory static method to create a concrete instance of DbProviderFactory on the basis of the passed provider name whereas DbProviderFactory class creates all the necessary concrete classes for a specific provider, for more details click here.

In Code Block 2, you can see I am using ConnectionStringSettings and ConfigurationManager classes to retrieve configuration detail from the configuration file and then passing provider name to the GetFactory method of DbProviderFactories class to instantiate an instance of DbProviderFactory class, which is based on the passed provider name. Next I am using the instance of DbProviderFactory to create a connection, command and data adapter of that type as well. So in nutshell this code will work, even if you decide to change the data source from one provider to another, just by making the required changes in the configuration file i.e. no code change are required.

C# Code Block 2 - Data Provider Independent

The complete code listing of the data access using data provider specific classes (Code Block 1) is provided in the below text area.

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.SqlClient; namespace DAL_DataProviderSpecific { class Program { static void Main(string[] args) { try { //Create a connection to connect to SQL Server using (SqlConnection _sqlConnectionForAdventureWorks = new SqlConnection (@"Server=ARSHADALI-LAP\ARSHADALI; Initial Catalog=AdventureWorks; Integrated Security=SSPI")) { _sqlConnectionForAdventureWorks.Open(); //Create a command to execute SqlCommand _sqlCommand = new SqlCommand(); _sqlCommand.CommandText = "SELECT TOP 5 FirstName, LastName, JobTitle FROM HumanResources.vEmployee"; _sqlCommand.CommandType = CommandType.Text; _sqlCommand.Connection = _sqlConnectionForAdventureWorks; /* Data Reader Demo */ //Execute the command and store the data result-set into a data reader SqlDataReader _sqlReader = _sqlCommand.ExecuteReader(); //Read each record from data reader at a time while (_sqlReader.Read()) { Console.WriteLine(String.Format("{0}, {1}, {2}", _sqlReader["FirstName"], _sqlReader["LastName"], _sqlReader["JobTitle"])); } _sqlReader.Close(); /* Data Adaptor and Dataset Demo */ //Execute the command and store the data result-set into a data table of a dataset DataSet _dataSet = new DataSet(); SqlDataAdapter _sqlDataAdaptor = new SqlDataAdapter(); _sqlDataAdaptor.SelectCommand = _sqlCommand; _sqlDataAdaptor.Fill(_dataSet); //Iterate through the records and columns to get its specific values //A dataset may contain more than one datatable, as becuase I am using a //single query to fill one datatable, I am using 0 indexer below foreach (DataRow _dataRow in _dataSet.Tables[0].Rows) { foreach (DataColumn _dataColumn in _dataSet.Tables[0].Columns) { Console.Write(_dataRow[_dataColumn.ColumnName] + ", " ); } Console.WriteLine(""); } } } catch (Exception exp) { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine(exp.Message); } } } }

The complete code listing of the data access using data provider independent classes (Code Block 2) is provided in the below text area.

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.Common; using System.Configuration; namespace DAL_DataProviderIndependent { class Program { static void Main(string[] args) { try { ConnectionStringSettings _configSettings = ConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"]; DbProviderFactory _dbProvider = DbProviderFactories.GetFactory(_configSettings.ProviderName); //Create a connection to connect as per provided provider name using (DbConnection _dbConn = _dbProvider.CreateConnection()) { _dbConn.ConnectionString = _configSettings.ConnectionString; _dbConn.Open(); //Create a command to execute DbCommand _dbCommand = _dbProvider.CreateCommand(); _dbCommand.Connection = _dbConn; _dbCommand.CommandText = "SELECT TOP 5 FirstName, LastName, JobTitle FROM HumanResources.vEmployee"; _dbCommand.CommandType = CommandType.Text; /* Data Reader Demo */ //Execute the command and store the data result-set into a data reader DbDataReader dbReader = _dbCommand.ExecuteReader(); //Read each record from data reader at a time while (dbReader.Read()) { Console.WriteLine(String.Format("{0}, {1}, {2}", dbReader["FirstName"], dbReader["LastName"], dbReader["JobTitle"])); } dbReader.Close(); /* Data Adaptor and Dataset Demo */ //Execute the command and store the data result-set into a data table of a dataset DataSet _dataSet = new DataSet(); DbDataAdapter _dbDataAdaptor = _dbProvider.CreateDataAdapter(); _dbDataAdaptor.SelectCommand = _dbCommand; _dbDataAdaptor.Fill(_dataSet); //Iterate through the records and columns to get its specific values //A dataset may contain more than one datatable, as becuase I am using a //single query to fill one datatable, I am using 0 indexer below foreach (DataRow _dataRow in _dataSet.Tables[0].Rows) { foreach (DataColumn _dataColumn in _dataSet.Tables[0].Columns) { Console.Write(_dataRow[_dataColumn.ColumnName] + ", "); } Console.WriteLine(""); } if (_dbConn.State == ConnectionState.Open) _dbConn.Close(); } } catch (Exception exp) { Console.ForegroundColor = ConsoleColor.Red; Console.WriteLine(exp.Message); } } } }

Next Steps

• Review Writing Provider-Independent Code in ADO.NET on msdn.
• Stay tuned for my next tip on using Data Access Application Block (DAAB).
• Review my all previous tips.

About the author

Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips