databasedev.co.uk - database solutions and downloads for microsoft access

ADO.NET

Best Practices of Designing and Implementing a Data Access Layer

This article takes a look at the strategies that can be adopted for implementing a generic data access layer using ADO.NET. It discusses how efficiently you can make use of generics to design and implement a data access layer that can work with almost any database. It also discusses the pros and cons of using the Data Access Application Block from Microsoft.

What is a Data Access Layer?

Generally an application can be divided into:

  • A User Interface Layer
  • A Business Logic Layer
  • A Data Access Layer

The User Interface Layer is concerned with interacting with the user and display data. The Business Logic Layer is concerned with executing the business logic operations of the application based on certain business rules.

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

The ADO.NET library provides you some major classes and interfaces that you can use to design and implement your data access layer.

  • Connection
  • Command
  • Data Reader
  • Data Adapter
  • IDBConnection
  • IDataReader
  • IDBCommand
  • IDBDataAdapter

The ADO.NET library supports the following providers:

  • SQL Server Data Provider
  • Oracle Data Provider
  • ODBC Data Provider
  • OleDB Data Provider

In order to make the data access layer provider independent, you can use the factory pattern. We will now see how we can design a factory class that can be used to return a specific data provider, connection, command, data adapter or data reader, all based on the database type you are using. Here is the code:

using System.Data.Common;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Data.OracleClient;

public enum DataProviderType
    {
        Oracle, SqlServer, OleDb, Odbc
    }

internal class DBFactory
    {
        private static DbProviderFactory objFactory = null;

        public static DbProviderFactory GetDataProvider(DataProviderType provider)
        {
            switch (provider)
            {
                case DataProviderType.SqlServer:
                    objFactory = SqlClientFactory.Instance;
                    break;
                case DataProviderType.OleDb:
                    objFactory = OleDbFactory.Instance;
                    break;
                case DataProviderType.Oracle:
                    objFactory = OracleClientFactory.Instance;
                    break;
                case DataProviderType.Odbc:
                    objFactory = OdbcFactory.Instance;
                    break;
            }
            return objFactory;
        }

        public static DbConnection GetConnection(DataProviderType providerType)
        {
            switch (providerType)
            {
                case DataProviderType.SqlServer:
                    return new SqlConnection();
                case DataProviderType.OleDb:
                    return new OleDbConnection();
                case DataProviderType.Odbc:
                    return new OdbcConnection();
                case ProviderType.Oracle:
                    return new OracleConnection();
                default:
                    return null;
            }
        }

        public static DbCommand GetCommand(DataProviderType providerType)
        {
            switch (providerType)
            {
                case DataProviderType.SqlServer:
                    return new SqlCommand();
                case DataProviderType.OleDb:
                    return new OleDbCommand();
                case DataProviderType.Odbc:
                    return new OdbcCommand();
                case DataProviderType.Oracle:
                    return new OracleCommand();
                default:
                    return null;
            }
        }

        public static DbDataAdapter GetDataAdapter(DataProviderType providerType)
        {
            switch (providerType)
            {
                case DataProviderType.SqlServer:
                    return new SqlDataAdapter();
                case DataProviderType.OleDb:
                    return new OleDbDataAdapter();
                case DataProviderType.Odbc:
                    return new OdbcDataAdapter();
                case DataProviderType.Oracle:
                    return new OracleDataAdapter();
                default:
                    return null;
            }
        }
    }

Now we will create a wrapper on top of this class called DBManager. Here is the code:

using System.Data.Common;
using System;
using System.Data;

public sealed class DataManager
{
    private DbConnection dbConnection;
    private String strConnectionString;
    private DataProviderType dataProviderType;

    public DBManager(DataProviderType providerType, string
     connectionString)
    {
        this.strConnectionString = connectionString;
        this.dataProviderType = providerType;
        dbConnection = DBFactory.GetConnection(providerType);
        dbConnection.ConnectionString = connectionString;
    }

    public void Open()
    {
        if (dbConnection.State != ConnectionState.Open)
            dbConnection.Open();
    }

    public void Close()
    {
        if (dbConnection.State != ConnectionState.Closed)
            dbConnection.Close();
    }

    
    public DbConnection Connection
    {
        get
        {
            return dbConnection;
        }
    }

    public String ConnectionString
    {
        get
        {
            return strConnectionString;
        }
    }

    public DataProviderType DBProvider
    {
        get
        {
            return dataProviderType;
        }
    }

    public DataSet GetDataSet(String sqlString)
    {
     using (DbDataAdapter dbDataAdapter = DBFactory.GetDataAdapter(this.DBProvider))
        {
            try
            {

                dbDataAdapter.SelectCommand = DBFactory.GetCommand(this.DBProvider);
                dbDataAdapter.SelectCommand.CommandText = sqlString;
                dbDataAdapter.SelectCommand.Connection = this.Connection;

                DataSet dataSet = new DataSet();
                DataTable dataTable = new DataTable();
                dataTable.BeginLoadData();
                dbDataAdapter.Fill(dataTable);
                dataTable.EndLoadData();
                dataSet.EnforceConstraints = false;
                dataSet.Tables.Add(dataTable);
                return dataSet;
            }

            catch (Exception ex)
            {
                return null;
            }
        }
    }
}

You can now use this class to perform CRUD (Create, Read, Update, and Delete) operations in your database. I leave it to my readers to incorporate more and more methods as per the requirements in this class.

You can also use the DataManager class to bind data to the data controls. To bind data to a repeater control, use this code:

DBManager dbManager = new DBManager(DataProvider.SqlServer);
dbManager.ConnectionString = 
ConfigurationSettings.AppSettings["ConnectionString"].ToString();

try
{
  dbManager.Open();
  Repeater1.DataSource = 
  dbManager.GetDataSet("Select employeeCode, employeeName, 
  			employeeAdress from employee");
  Repeater1.DataBind();
}
 
catch (Exception ex)
{
  throw;
}
 
finally
{
  dbManager.Close();
}

Conclusion

This article has presented the best practices and strategies for implementing a data access layer. It should be noted that you should open connections as late as possible and close them as early as possible to optimize the data access performance.

The Author

Joydip Kanjilal is a Microsoft MVP in ASP.NET.

He has more than 12 years of industry experience in IT with more than six years in Microsoft .NET and its related technologies.

He has authored articles for some of the most reputable sites, including http://www.asptoday.com, http://www.devx.com, http://www.aspalliance.com, http://www.aspnetpro.com, http://www.sql-server-performance.com, and http://www.sswug.com.

Many of these articles have been selected at http://www.asp.net, MicrosoftÂ’s official site for ASP.NET. Joydip was also a community credit winner at http://www.community-credit.com a number of times.

He is currently working as a Lead Architect in a reputable company in Hyderabad, India. He has years of experience in designing and architecting solutions for various domains. His technical strengths include, C, C++, VC++, Java, C#, Microsoft .NET, AJAX, Design Patterns, SQL Server, Operating Systems, and Computer Architecture.

Joydip blogs at http://aspadvice.com/blogs/joydip and spends most of his time reading books and blogs, and writing books and articles. His hobbies include watching cricket and soccer and playing chess