Tuesday, December 4, 2012

Very Simple Data Access Layer for Three Tire Application

Below code is for very simple Data Access Layer that can be use in three tier application, it also includes the Method used in with Transaction




using System;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace DataAccessLayer
{
    public static class DAcess
    {
        static string constr = "";
        static SqlCommand sqlcom = new SqlCommand();
        static SqlConnection sqlcon = new SqlConnection();
        static SqlDataAdapter sqlda;
        static SqlTransaction SqlTran;
       
        public static void ConnectToDatabase()
        {
            if (sqlcon.State == ConnectionState.Closed)
            {
                constr = ConfigurationManager.ConnectionStrings["ZaaSConString"].ToString();
                sqlcon = new SqlConnection(constr);
                sqlcon.Open();
            }
        }
        public static void DisconnectToDataBase()
        {
            if(sqlcon.State == ConnectionState.Open)
            sqlcon.Close();
        }

        public static DataTable returnDataTable(string Query)
        {
            DataTable dtResult = new DataTable("ResultTable");
            try
            {
                ConnectToDatabase();
                sqlda = new SqlDataAdapter(Query, sqlcon);
                sqlda.Fill(dtResult);
            }
            catch (Exception ex)
            {
            }
            finally
            {
            }
            return dtResult;
        }
        public static DataTable returnDataTableSpecial(string Query)
        {
            DataTable dtResult = new DataTable("ResultTable");
            try
            {
                ConnectToDatabase();
                sqlcom = new SqlCommand(Query, sqlcon, SqlTran);
                sqlda = new SqlDataAdapter(sqlcom);
                sqlda.Fill(dtResult);
            }
            catch (Exception ex)
            {
            }
            finally
            {
            }
            return dtResult;
        }

        public static int ExecuteQuery(string Query)
        {
            int Result = 0;

            try
            {
                ConnectToDatabase();
                sqlcom = new SqlCommand(Query, sqlcon);
                Result = sqlcom.ExecuteNonQuery();
                sqlcon.Close();
            }
            catch (Exception ex)
            {
                sqlcon.Close();
                throw ex;
            }
            finally
            {
                sqlcon.Close();
            }

           
            return Result;
        }
        public static int NewExecuteQuery(string Query)
        {
            int Result = 0;
            sqlcom = new SqlCommand(Query, sqlcon, SqlTran);
            Result = sqlcom.ExecuteNonQuery();
            return Result;
        }
        public static void StartTransaction()
        {
            SqlTran = sqlcon.BeginTransaction(IsolationLevel.ReadUncommitted);
        }
        public static void CommitTransaction()
        {
            SqlTran.Commit();
        }
        public static void RollBackTransaction()
        {
            SqlTran.Rollback();
        }
        public static void CloseTransaction()
        {
            SqlTran.Dispose();
        }
       
       
        public static string returnScalar(string Query)
        {
            string ResultString = "";

            try
            {
                ConnectToDatabase();
                sqlcom = new SqlCommand(Query, sqlcon);
                ResultString = Convert.ToString(sqlcom.ExecuteScalar());
                sqlcon.Close();
            }
            catch (Exception ex)
            {
                sqlcon.Close();
                throw ex;
            }
            finally
            {
                sqlcon.Close();
            }


            return ResultString;
        }
        public static string NewreturnScalar(string Query)
        {
            string ResultString = "";
            sqlcom = new SqlCommand(Query, sqlcon,SqlTran);
            ResultString = Convert.ToString(sqlcom.ExecuteScalar());
            return ResultString;
        }


    }
}


No comments:

Post a Comment