Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Writing a Data Access Layer (DAL) for SQL Server


By:   |   Last Updated: 2010-06-07   |   Comments (8)   |   Related Tips: More > 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

//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("");
    
}
}

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

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

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

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


Next Steps


Last Updated: 2010-06-07


next webcast button


next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, January 17, 2012 - 12:54:03 AM - Arshad Back To Top

Well that's not a problem at all, C# code is based on Microsoft .NET framework and can be converted to any other language which are compatible with or is based on .NET framework.

There are several online free web based tool for conversion:

http://www.developerfusion.com/tools/convert/csharp-to-vb/

http://www.carlosag.net/tools/codetranslator/


Monday, January 16, 2012 - 4:42:24 PM - WEB Back To Top

It appears the authors are making C# the lingua franca of SQL Server and SSIS. It would be nice to have some variety.

 


Wednesday, June 23, 2010 - 4:42:16 AM - CGSJohnson Back To Top

Hi, dbeayon.  Instead of using a reader, you would use a data set and just bind the gridview to the data set, like below.

 gridview.datasource = dsName;

gridview.bind(); or is it gridview.databind();

I hope that this helps.

Thanks...Chris


Tuesday, June 22, 2010 - 3:44:15 PM - dbeayon Back To Top

How does this approach affect objects like GridView that rely on data binding?


Saturday, June 19, 2010 - 10:22:35 PM - CGSJohnson Back To Top

Thanks for the reply.  I guess it makes sense...if you close the connection, the other data objects (commands, data sets, data readers, etc.) will go out of scope, and, as you said, be reclaimed by the garbage collector.  Thanks again.

- Chris


Friday, June 18, 2010 - 1:52:49 PM - arshad0384 Back To Top

Yes Chris, this code looks good.

Actually its recommended or in fact It's a best practice to close database connection immediately after you are done with databae access to ensure connection returns to connection pool for re-use. Needless to say every connection to database has associted memory overhead so returning it as soon as possible to connection pool also ensures that it has less impact on SQL Server memory.

For other objects, these are anyway will be reclaimed by garbage collector once these objects go out of scope.


Friday, June 18, 2010 - 8:30:05 AM - CGSJohnson Back To Top

 Great post...thanks.  One question...is the below code sufficient or should you explicitly close all of the data objects?

if (_dbConn.State == ConnectionState.Open)
                        _dbConn.Close();

 

Thanks...Chris


Thursday, June 17, 2010 - 10:22:55 AM - rreid Back To Top

Thanks for this - for years we've worked to keep the connection details generalized so (for example) we can move a database to a different server without modifying the apps themselves.   But 99 out of 100 tutorials on database programming start with a hard coded connection string in main() and usually logging in as 'sa' no less.   And almost none consider that you might not be connecting to an MS SQL Server.

My older methods are not really right for VS10 or Flex programming - your article pulled together the pieces I knew were out there for me

 roger reid


Learn more about SQL Server tools