May 08

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

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

1.   Introduction

In my previous post we discussed about the implementation of SQL Data access layer using C#. We created SQLHelper class to create CRUD functions to return ADO.NET objects like DataTable, DataSet, DataReader and Scalar values. Later on we created console based application to test the SQLHelper class functionality.
 

 

In this article I will show you how to create typical data access layer that could be helpful in live assignments.

2.   Overview

In this article we will use previous article classes like User and SQLHelper. Move these classes to Domain and Core folders respectively. Create UserDal class inside DAL folder. Now the project structure will look like below.
 
 
               
This project structure contains Core folder in which the SqlHelper class is placed. DAL folder used for entity specific data access layer (this is what in today’s article). I have kept database schema used to create this post. Domain folder basically used to keep databased entity specific classes and these classes could be useful for model binding and data annotations

3.   Practical Implementation

 
Before starting with the implementation execute this database script on your Sql Server instance using SSMS. I have placed same script in project folder also.
I have already created the SQLHelper and User class in my previous article so I will copy it to irrespective folder. Now create the BaseDal and UserDal classes inside DAL folder.
 
First create the BaseDal which will keep common settings to SQLHelper object instance and function to close the database connection
using System.Configuration;
using System.Data.SqlClient;
using SqlDAL.Core;
namespace SqlDAL.DAL
{
    public class BaseDal
    {
        public SqlHelper sqlHelper = null;
        public SqlConnection connection = null;
        public BaseDal()
        {
            sqlHelper = new SqlHelper(ConfigurationManager.ConnectionStrings[“DBConnection”].ToString());
        }
        public void CloseConnection()
        {
            sqlHelper.CloseConnection(connection);
        }
    }
}
Now create the DAL for User table and access functions created in SQLHelper class. Please note that the DataReader is connected in architecture hence we need to close the reader and then close the connection as well. All other functions are written inside using function which will take care of closing database connections. In data access layer we need to create parameters and command text only.
                using System;
using System.Data;
using System.Collections.Generic;
using System.Data.SqlClient;
using SqlDAL.Domain;
namespace SqlDAL.DAL
{
    public class UserDal : BaseDal
    {
        public int Insert(User user)
        {
            var parameters = new List<SqlParameter>();
            parameters.Add(sqlHelper.CreateParameter(“@FirstName”, 50, user.FirstName, DbType.String));
            parameters.Add(sqlHelper.CreateParameter(“@LastName”, user.LastName, DbType.String));
            parameters.Add(sqlHelper.CreateParameter(“@Dob”, user.Dob, DbType.DateTime));
            parameters.Add(sqlHelper.CreateParameter(“@IsActive”, 50, user.IsActive, DbType.Boolean));
            int lastId = 0;
            sqlHelper.Insert(“DAH_User_Insert”, CommandType.StoredProcedure, parameters.ToArray(), out lastId);
            return lastId;
        }
        public void Update(User user)
        {
            var parameters = new List<SqlParameter>();
            parameters.Add(sqlHelper.CreateParameter(“@Id”, user.Id, DbType.Int32));
            parameters.Add(sqlHelper.CreateParameter(“@FirstName”, 50, user.FirstName, DbType.String));
            parameters.Add(sqlHelper.CreateParameter(“@LastName”, user.LastName, DbType.String));
            parameters.Add(sqlHelper.CreateParameter(“@Dob”, user.Dob, DbType.DateTime));
            sqlHelper.Update(“DAH_User_Update”, CommandType.StoredProcedure, parameters.ToArray());
        }
        public void Delete(int id)
        {
            var parameters = new List<SqlParameter>();
            parameters.Add(sqlHelper.CreateParameter(“@Id”, id, DbType.Int32));
            sqlHelper.Delete(“DAH_User_Delete”, CommandType.StoredProcedure, parameters.ToArray());
        }
        public User GetById(int id)
        {
            var parameters = new List<SqlParameter>();
            parameters.Add(sqlHelper.CreateParameter(“@Id”, id, DbType.Int32));
            var dataReader = sqlHelper.GetDataReader(“DAH_User_GetById”, CommandType.StoredProcedure, parameters.ToArray(), out connection);
            try
            {
                var user = new User();
                while (dataReader.Read())
                {
                    user.FirstName = dataReader[“FirstName”].ToString();
                    user.LastName = dataReader[“LastName”].ToString();
                }
                return user;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dataReader.Close();
                CloseConnection();
            }
        }
        public IEnumerable<User> GetAll()
        {
            var parameters = new List<SqlParameter>();
            var dataReader = sqlHelper.GetDataReader(“DAH_User_GetAll”, CommandType.StoredProcedure, null, out connection);
            try
            {
                var users = new List<User>();
                while (dataReader.Read())
                {
                    var user = new User();
                    user.FirstName = dataReader[“FirstName”].ToString();
                    user.LastName = dataReader[“LastName”].ToString();
                    users.Add(user);
                }
                return users;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dataReader.Close();
                CloseConnection();
            }
        }
        public IEnumerable<User> SelectAll()
        {
            var userDataTable = sqlHelper.GetDataTable(“DAH_User_GetAll”, CommandType.StoredProcedure);
            var users = new List<User>();
            foreach (DataRow row in userDataTable.Rows)
            {
                var user = new User();
                user.FirstName = row[“FirstName”].ToString();
                user.LastName = row[“LastName”].ToString();
                users.Add(user);
            }
            return users;
        }
        public int GetScalarValue()
        {
            object scalarValue = sqlHelper.GetScalarValue(“DAH_User_Scalar”, CommandType.StoredProcedure);
            return Convert.ToInt32(scalarValue);
        }
    }
}
Let’s create console based application to test the UserDal functionality. This is something like your Business layer from where the instance of data access layer is created.
using System;
using System.Linq;
using SqlDAL.Domain;
using SqlDAL.DAL;
namespace SqlDAL
{
    public class Program
    {
        static void Main(string[] args)
        {
            var userDal = new UserDal();
            var user = new User
            {
                FirstName = “First”,
                LastName = “Last”,
                Dob = DateTime.Now.AddDays(-3000),
                IsActive = true
            };
            //INSERT
            var lastId = userDal.Insert(user);
            Console.WriteLine(“nINSERTED ID: “ + lastId);
            //DATATABLE
            var users = userDal.SelectAll();
            Console.WriteLine(“nTOTAL ROWS IN TABLE(DATATABLE): “ + users.Count());
            //DATAREADER
            users = userDal.GetAll();
            Console.WriteLine(“nTOTAL ROWS IN TABLE(DATAREADER): “ + users.Count());
            userDal = new UserDal();
           
            //SCALAR
            int scalar = userDal.GetScalarValue();
            Console.WriteLine(“nSCALAR VALUE: “ + scalar.ToString());
            //UPDATE
            user = new User
            {
                Id = lastId,
                FirstName = “First1”,
                LastName = “Last1”,
                Dob = DateTime.Now.AddDays(-5000)
            };
            userDal.Update(user);
            var userDetails = userDal.GetById(user.Id);
            Console.WriteLine(string.Format(“nUPADTED VALUES FirstName{0}: LastName:{1}”, userDetails.FirstName, userDetails.LastName));
            //DELETE
            userDal.Delete(user.Id);
            users = userDal.SelectAll();
            Console.WriteLine(“nTOTAL ROWS IN TABLE(DATATABLE): “ + users.Count());
            Console.WriteLine(“nnPress any key to exist…”);
            Console.ReadKey();
        }
    }
}
Now run the program which will produce below output

4.   Summary

In this article I have explained how to create SQL Data Access Layer in C# using ADO.NET and called it from business layer (console based). In my next example I will show you how this data access layer useful in Web API based and AngularJS 2.0 based web 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.

 

 

 

1 comment

  1. Thanks. Good information !!

Leave a Reply