Monday, 16 December 2013

C# DotNet code for Data Access Layer class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    public class DataAccessLayer
    {
        private static string _connectionString = string.Empty;

        static string getDataConnectionString()
        {
            //return ConfigurationManager.ConnectionStrings["conStr"].ToString();
            //return ConfigurationManager.AppSettings["ConnString"].ToString();
            return _connectionString;
        }

        public static string setCnnecton
        {
            get
            {
                return _connectionString;
            }
            set
            {
                _connectionString = value;
            }
        }     

        public static int ExecuteNonquery(Dictionary<string, object> parameters, string strSPName)
        {
            int mReturnValue = 0;
            try
            {
                using (SqlConnection con = new SqlConnection(getDataConnectionString()))
                {
                    using (SqlCommand cmd = new SqlCommand("", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = strSPName;
                        foreach (KeyValuePair<string, object> parameter in parameters)
                        {
                            cmd.Parameters.AddWithValue(parameter.Key, parameter.Value);                  
                        }
                        con.Open();
                        mReturnValue = cmd.ExecuteNonQuery();
                    }
                }
                return mReturnValue;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static int ExecuteScalar(Dictionary<string, object> parameters, string strSPName)
        {
            int mReturnValue = 0;
            try
            {
                using (SqlConnection con = new SqlConnection(getDataConnectionString()))
                {
                    using (SqlCommand cmd = new SqlCommand("", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = strSPName;
                        foreach (KeyValuePair<string, object> parameter in parameters)
                        {
                            cmd.Parameters.AddWithValue(parameter.Key, parameter.Value);
                        }
                        con.Open();
                        mReturnValue = (int)cmd.ExecuteScalar();
                    }
                }
                return mReturnValue;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataSet ExecuteDataSet(Dictionary<string, object> parameters, string strSPName)
        {
            DataSet ds = new DataSet();
            try
            {
                using (SqlConnection con = new SqlConnection(getDataConnectionString()))
                {
                    using (SqlCommand cmd = new SqlCommand("", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = strSPName;
                        foreach (KeyValuePair<string, object> parameter in parameters)
                        {
                            cmd.Parameters.AddWithValue(parameter.Key, parameter.Value);
                        }
                        con.Open();
                        using (SqlDataAdapter adpt = new SqlDataAdapter(cmd))
                        {
                            adpt.Fill(ds);
                        }
                    }
                }
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable ExecuteDataTable(Dictionary<string, object> parameters, string strSPName)
        {
            DataSet ds = new DataSet();
            try
            {
                using (SqlConnection con = new SqlConnection(getDataConnectionString()))
                {
                    using (SqlCommand cmd = new SqlCommand("", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = strSPName;
                        foreach (KeyValuePair<string, object> parameter in parameters)
                        {
                            cmd.Parameters.AddWithValue(parameter.Key, parameter.Value);
                        }
                        con.Open();
                        using (SqlDataAdapter adpt = new SqlDataAdapter(cmd))
                        {
                            adpt.Fill(ds);
                        }
                    }
                }
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}


No comments:

Post a Comment