May 06

How to create SQL Data Access Layer in C# using ADO.NET – Part 1

How to create SQL Data Access Layer in C# using ADO.NET – Part 1

1.   Introduction

In my previous post we discussed about the implementation of generic data access layer using factory pattern and using DbProviderFactory. This article would be useful for those developers who use SQL Server as a database and C# as a programming language.
Data access layer is important part of any software application. Typically, a large ASP.NET or ASP.NET MVC based web application uses SQL as a database. On the top of this database Data Access Layer is created. A business layer which compromise of business logic and business domain classes used to communicate with data access layer. A service layer either in WCF or Web API used to expose the business layer to client i.e. Channel factory or Web Client and finally some user interface like ASP.NET, ASP.NET MVC, Windows Forms or Windows Service.
The typical flow of the .NET based application is like
 

2.   Overview

ADO.Net Provides consistent way to access SQL Server database. Cconsumer applications uses ADO.NET to connect to these data sources and retrieve, manipulate, and update data. The ADO.NET classes are found in System.Data.dll.

The ADO.NET components have been designed to factor data access from data manipulation. There are two central components of ADO.NET that accomplish this: the DataSet, and the .NET Framework data provider, which is a set of components including the Connection, Command, DataReader, and DataAdapter objects.

The ADO.NET DataSet is the core component of the disconnected architecture of ADO.NET. The DataSet is explicitly designed for data access independent of any data source. The DataSet contains a collection of one or more DataTable objects made up of rows and columns of data, as well as primary key, foreign key, constraint, and relation information about the data in the DataTable objects.

The Connection object provides connectivity to a data source. The Command object enables access to database commands to return data, modify data, run stored procedures, and send or retrieve parameter information. The DataReader provides a high-performance stream of data from the data source. Finally, the DataAdapter provides the bridge between the DataSet object and the data source. 

The DataAdapter uses Command objects to execute SQL commands at the data source to both load 
the DataSet with data, and reconcile changes made to the data in the DataSet back to the data source.

Database Objects:
1.    ConnectionEstablishes a connection to a specific data source
2.    Comman – Executes a command against a data source
3.    DataReader – Reads a forward-only, read-only stream of data from a data source.
4.    DataAdapter – Populates a data set and resolves updates with the data source
The System.Data.SqlClient namespace provides access to versions of SQL Server, which encapsulates database-specific protocols.

The SqlConnection object represents a unique session to a SQL Server data source. With a client/server database system, it is equivalent to a network connection to the server. SqlConnection is used together with SqlDataAdapter and SqlCommand to increase performance when connecting to a Microsoft SQL Server database.

The SqlCommand object Represents a Transact-SQL statement or stored procedure to execute against a SQL Server database.

The SqlDataAdapter object represents a set of data commands and a database connection that are used to fill the DataSet and update a SQL Server database. The SqlDataAdapter, acts as a bridge between a DataSet and SQL Server for retrieving and saving data. The SqlDataAdapter provides this bridge by mapping Fill, which changes the data in the DataSet to match the data in the data source, and Update, which changes the data in the data source to match the data in the DataSet, using the appropriate Transact-SQL statements against the data source. SqlDataAdapter is used in conjunction with SqlConnection and SqlCommand to increase performance when connecting to a SQL Server database.

The SqlDataReader object Provides a way of reading a forward-only access to a SQL Server database. While the SqlDataReader is being used, the associated SqlConnection is busy serving the SqlDataReader, and no other operations can be performed on the SqlConnection other than closing it. This is the case until the Close method of the SqlDataReader is called.

The SqlTransaction object Represents a Transact-SQL transaction to be made in a SQL Server database. The application creates a SqlTransaction object by calling BeginTransaction on the SqlConnection object. All subsequent operations associated with the transaction (for example, committing or aborting the transaction), are performed on the SqlTransaction object.Try/Catch exception handling should always be used when committing or rolling back a SqlTransaction.

The DataSet supports disconnected data access and operations, allowing greater scalability because you no longer have to be connected to the database all the time.

3.   Practical Implementation

Before starting with the implementation execute thisdatabase script on your Sql Server instance using SSMS

Step 1: Create wrapper class to access database specific functions

This class used create the CRUD functions to return ADO.NET objects like DataTable, DataSet, DataReader and Scalar values. These functions I have created instances of SqlConnection, SqlParameter, SqlCommand and SqlDataAdapter. This wrapper class contains different overloads to support more functionality and database transactions as well.

I used this class same as my previouspost with smaller code changes.

using System;
using System.Data;
using System.Data.SqlClient;
namespace DataAccessHandler
{
    public class SqlHelper
    {
        private string ConnectionString { get; set; }
        public SqlHelper(string connectionString)
        {
            ConnectionString = connectionString;
        }
        public void CloseConnection(SqlConnection connection)
        {
            connection.Close();
        }
        public SqlParameter CreateParameter(string name, object value, DbType dbType)
        {
            return CreateParameter(name, 0, value, dbType, ParameterDirection.Input);
        }
        public SqlParameter CreateParameter(string name, int size, object value, DbType dbType)
        {
            return CreateParameter(name, size, value, dbType, ParameterDirection.Input);
        }
        public SqlParameter CreateParameter(string name, int size, object value, DbType dbType, ParameterDirection direction)
        {
            return new SqlParameter
            {
                DbType = dbType,
                ParameterName = name,
                Size = size,
                Direction = direction,
                Value = value
            };
        }
        public DataTable GetDataTable(string commandText, CommandType commandType, SqlParameter[] parameters = null)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand(commandText, connection))
                {
                    command.CommandType = commandType;
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    var dataset = new DataSet();
                    var dataAdaper = new SqlDataAdapter(command);
                    dataAdaper.Fill(dataset);
                    return dataset.Tables[0];
                }
            }
        }
        public DataSet GetDataSet(string commandText, CommandType commandType, SqlParameter[] parameters = null)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand(commandText, connection))
                {
                    command.CommandType = commandType;
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    var dataset = new DataSet();
                    var dataAdaper = new SqlDataAdapter(command);
                    dataAdaper.Fill(dataset);
                    return dataset;
                }
            }
        }
        public IDataReader GetDataReader(string commandText, CommandType commandType, SqlParameter[] parameters, out SqlConnection connection)
        {
            IDataReader reader = null;
            connection = new SqlConnection(ConnectionString);
            connection.Open();
            var command = new SqlCommand(commandText, connection);
            command.CommandType = commandType;
            if (parameters != null)
            {
                foreach (var parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }
            }
            reader = command.ExecuteReader();
           
            return reader;
        }
        public void Delete(string commandText, CommandType commandType, SqlParameter[] parameters = null)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand(commandText, connection))
                {
                    command.CommandType = commandType;
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    command.ExecuteNonQuery();
                }
            }
        }
        public void Insert(string commandText, CommandType commandType, SqlParameter[] parameters)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand(commandText, connection))
                {
                    command.CommandType = commandType;
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    command.ExecuteNonQuery();
                }
            }
        }
        public int Insert(string commandText, CommandType commandType, SqlParameter[] parameters, out int lastId)
        {
            lastId = 0;
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand(commandText, connection))
                {
                    command.CommandType = commandType;
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    object newId = command.ExecuteScalar();
                    lastId = Convert.ToInt32(newId);
                }
            }
            return lastId;
        }
        public long Insert(string commandText, CommandType commandType, SqlParameter[] parameters, out long lastId)
        {
            lastId = 0;
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand(commandText, connection))
                {
                    command.CommandType = commandType;
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    object newId = command.ExecuteScalar();
                    lastId = Convert.ToInt64(newId);
                }
            }
            return lastId;
        }
        public void InsertWithTransaction(string commandText, CommandType commandType, SqlParameter[] parameters)
        {
            SqlTransaction transactionScope = null;
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                transactionScope = connection.BeginTransaction();
                using (var command = new SqlCommand(commandText, connection))
                {
                    command.CommandType = commandType;
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    try
                    {
                        command.ExecuteNonQuery();
                        transactionScope.Commit();
                    }
                    catch (Exception)
                    {
                        transactionScope.Rollback();
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }
        public void InsertWithTransaction(string commandText, CommandType commandType, IsolationLevel isolationLevel, SqlParameter[] parameters)
        {
            SqlTransaction transactionScope = null;
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                transactionScope = connection.BeginTransaction(isolationLevel);
                using (var command = new SqlCommand(commandText, connection))
                {
                    command.CommandType = commandType;
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    try
                    {
                        command.ExecuteNonQuery();
                        transactionScope.Commit();
                    }
                    catch (Exception)
                    {
                        transactionScope.Rollback();
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }
        public void Update(string commandText, CommandType commandType, SqlParameter[] parameters)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand(commandText, connection))
                {
                    command.CommandType = commandType;
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    command.ExecuteNonQuery();
                }
            }
        }
        public void UpdateWithTransaction(string commandText, CommandType commandType, SqlParameter[] parameters)
        {
            SqlTransaction transactionScope = null;
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                transactionScope = connection.BeginTransaction();
                using (var command = new SqlCommand(commandText, connection))
                {
                    command.CommandType = commandType;
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    try
                    {
                        command.ExecuteNonQuery();
                        transactionScope.Commit();
                    }
                    catch (Exception)
                    {
                        transactionScope.Rollback();
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }
        public void UpdateWithTransaction(string commandText, CommandType commandType, IsolationLevel isolationLevel, SqlParameter[] parameters)
        {
            SqlTransaction transactionScope = null;
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                transactionScope = connection.BeginTransaction(isolationLevel);
                using (var command = new SqlCommand(commandText, connection))
                {
                    command.CommandType = commandType;
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    try
                    {
                        command.ExecuteNonQuery();
                        transactionScope.Commit();
                    }
                    catch (Exception)
                    {
                        transactionScope.Rollback();
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }
        public object GetScalarValue(string commandText, CommandType commandType, SqlParameter[] parameters= null)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand(commandText, connection))
                {
                    command.CommandType = commandType;
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                    }
                    returncommand.ExecuteScalar();
                }
            }
        }
    }
}
Step 2: Test the wrapper class using console application

First create the class specific to User table in database. We will use this class to Insert and Update and Delete functionalities.

I used this class same as my previous post as it is with smaller changes.

using System;
namespace DataAccessHandler.ConsoleApp
{
    public class User
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public DateTime Dob { get; set; }
        public bool IsActive { get; set; }
    }
}
using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Data.SqlClient;
namespace DataAccessHandler.ConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine(“============== Using SQL Helper =======================nnn”);
            UsingSqlHelper();
            Console.WriteLine(“nnPress any key to exist…”);
            Console.ReadKey();
        }
        private static void UsingSqlHelper()
        {
            var dbManager = new SqlHelper(ConfigurationManager.ConnectionStrings[“DBConnection”].ToString());
            var user = new User
            {
                FirstName = “First”,
                LastName = “Last”,
                Dob = DateTime.Now.AddDays(-3000),
                IsActive = true
            };
            var parameters = new List<SqlParameter>();
            parameters.Add(dbManager.CreateParameter(“@FirstName”, 50, user.FirstName, DbType.String));
            parameters.Add(dbManager.CreateParameter(“@LastName”, user.LastName, DbType.String));
            parameters.Add(dbManager.CreateParameter(“@Dob”, user.Dob, DbType.DateTime));
            parameters.Add(dbManager.CreateParameter(“@IsActive”, 50, user.IsActive, DbType.Boolean));
            //INSERT
            int lastId = 0;
            dbManager.Insert(“DAH_User_Insert”, CommandType.StoredProcedure, parameters.ToArray(), out lastId);
            Console.WriteLine(“nINSERTED ID: “ + lastId);
            //DATATABLE
            var dataTable = dbManager.GetDataTable(“DAH_User_GetAll”, CommandType.StoredProcedure);
            Console.WriteLine(“nTOTAL ROWS IN TABLE: “ + dataTable.Rows.Count);
            //DATAREADER
            SqlConnection connection = null;
            var dataReader = dbManager.GetDataReader(“DAH_User_GetAll”, CommandType.StoredProcedure, null, out connection);
            try
            {
                user = new User();
                while (dataReader.Read())
                {
                    user.FirstName = dataReader[“FirstName”].ToString();
                    user.LastName = dataReader[“LastName”].ToString();
                }
                Console.WriteLine(string.Format(“nDATA READER VALUES FirstName{0}: LastName:{1}”, user.FirstName, user.LastName));
            }
            catch (Exception)
            {
            }
            finally
            {
                dataReader.Close();
                dbManager.CloseConnection(connection);
            }
            //SCALAR
            object scalar = dbManager.GetScalarValue(“DAH_User_Scalar”, CommandType.StoredProcedure);
            Console.WriteLine(“nSCALAR VALUE: “ + scalar.ToString());
            //UPDATE
            user = new User
            {
                Id = lastId,
                FirstName = “First1”,
                LastName = “Last1”,
                Dob = DateTime.Now.AddDays(-5000)
            };
            parameters = new List<SqlParameter>();
            parameters.Add(dbManager.CreateParameter(“@Id”, user.Id, DbType.Int32));
            parameters.Add(dbManager.CreateParameter(“@FirstName”, 50, user.FirstName, DbType.String));
            parameters.Add(dbManager.CreateParameter(“@LastName”, user.LastName, DbType.String));
            parameters.Add(dbManager.CreateParameter(“@Dob”, user.Dob, DbType.DateTime));
            dbManager.Update(“DAH_User_Update”, CommandType.StoredProcedure, parameters.ToArray());
            //DATATABLE
            dataTable = dbManager.GetDataTable(“DAH_User_GetAll”, CommandType.StoredProcedure);
            Console.WriteLine(string.Format(“nUPADTED VALUES FirstName{0}: LastName:{1}”, dataTable.Rows[0][“FirstName”].ToString(), dataTable.Rows[0][“LastName”].ToString()));
            //DELETE
            parameters = new List<SqlParameter>();
            parameters.Add(dbManager.CreateParameter(“@Id”, user.Id, DbType.Int32));
            dbManager.Delete(“DAH_User_Delete”, CommandType.StoredProcedure, parameters.ToArray());
            //DATATABLE
            dataTable = dbManager.GetDataTable(“DAH_User_GetAll”, CommandType.StoredProcedure);
            Console.WriteLine(“nTOTAL ROWS IN TABLE: “ + dataTable.Rows.Count);
        }
    }
}
Step 3: Output of Console Application

 

4.   Summary

In this article I have explained how to create SQL Data Access Layer in C# using ADO.NET. We discussed all three approaches to work with data access. In my next article I will show you how console application code actually used to create data access layer in live application. I hope you enjoyed this article. If you like this article don’t forget to add your comments.

5.   Download Code

Click hereto download the source code of this article.

 

Leave a Reply