Helper Class for N-TIER Architecture

dotnet tutorial

using System;
using System.Data;
using System.Web;
using System.Configuration;
using System.Web.Configuration;
using System.Data.OleDb ;
using System.Data.SqlClient;
/// <summary>
/// Summary description for CommonUtility
/// </summary>
///
namespace Data
{
    public class Utility
    {
        public static SqlConnection  con;
        public static OleDbConnection oleCon;

        SqlDataReader dr;

        SqlDataAdapter adp;
        DataSet ds;
     
        // create connection
        public Utility()
        {
          
            string connectionstring = WebConfigurationManager.AppSettings["MMConString"].ToString();
           con = new SqlConnection(connectionstring);
         //  con.Open();
        }

        public static OleDbConnection  Conn()
        {
         
            string connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:/RunningProject/AppraisalForm/DataBase/AppraisalSystem.mdb; Persist Security Info=False";
            ////E:\PRATIBHA\Projects\AppraisalForm\DataBase

                       
            oleCon = new OleDbConnection(connectionstring);
            if (oleCon.State == ConnectionState.Closed)
            {
                oleCon.Open();
            }
            else
            {
                oleCon.Close();
                oleCon.Open();
            }
            return oleCon;
        }

        public static string TimeFormat(int time)
        {
            if (time > 0)
            {
                int hrs = time / 3600;
                int mins = (time % 3600)/60;
                return hrs + " hr" + mins+" mins";
            }
            return "0 Min";
        }

        public int ExecuteNonQuery(string text, SqlParameter[] param, bool procedure, bool returnparam)
        {
          


           
                SqlCommand cmd = new SqlCommand();
                if (procedure == true)
                    cmd.CommandType = CommandType.StoredProcedure;
                else
                    cmd.CommandType = CommandType.Text;
                cmd.CommandText = text;
                foreach (SqlParameter par in param)
                {
                    cmd.Parameters.Add(par);
                }
                cmd.Connection = con;
                con.Open();
                int i = cmd.ExecuteNonQuery();
                if (returnparam)
                {       
                    if (cmd.Parameters["@id"].Value != null)
                    {
                        int id = Convert.ToInt32(cmd.Parameters["@id"].Value);
                        return id;
                    }
                }
                return i;
       
        

                return -1;
        

        }

        public int ExecuteNonQuery(string text, SqlParameter[] param, bool procedure, bool returnparam, SqlTransaction transaction, bool trans)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                if (procedure == true)
                    cmd.CommandType = CommandType.StoredProcedure;
                else
                    cmd.CommandType = CommandType.Text;
                cmd.CommandText = text;
                foreach (SqlParameter par in param)
                {
                    cmd.Parameters.Add(par);
                }
                cmd.Connection = con;
                con.Open();
                cmd.Transaction = transaction;
                int i = cmd.ExecuteNonQuery();
                if (returnparam)
                {
                    if (cmd.Parameters["@id"].Value != null)
                    {
                        int id = Convert.ToInt32(cmd.Parameters["@id"].Value);
                        return id;
                    }
                }
                return i;
            }
            catch
            {
                return -1;
            }
            finally
            {
                con.Close();
            }
        }
        // execute a query with executescalar
        // text contains name of stored procedure or text of command
        // param contains list of paramaters
        // procedure shows whether text contains stored procedure name or not
        public object ExecuteScalar(string text, SqlParameter[] param, bool procedure)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                if (procedure == true)
                    cmd.CommandType = CommandType.StoredProcedure;
                else
                    cmd.CommandType = CommandType.Text;
                cmd.CommandText = text;
                cmd.Connection = con;
                if (param != null)
                {
                    foreach (SqlParameter par in param)
                    {
                        cmd.Parameters.Add(par);
                    }
                }
                con.Open();
                object o = cmd.ExecuteScalar();
                return o;
            }
            catch
            {
                return null;
            }
            finally
            {
                con.Close();
            }
        }
        // execute a query to return a datareader
        // text contains name of stored procedure or text of command
        // param contains list of paramaters
        // procedure shows whether text contains stored procedure name or not
        public SqlDataReader ExecuteReader(string text, SqlParameter[] param, bool procedure)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                if (procedure == true)
                    cmd.CommandType = CommandType.StoredProcedure;
                else
                    cmd.CommandType = CommandType.Text;
                cmd.CommandText = text;
                cmd.Connection = con;
                if (param != null)
                {
                    foreach (SqlParameter par in param)
                    {
                        cmd.Parameters.Add(par);
                    }
                }
                con.Open();
                dr = cmd.ExecuteReader();
                return dr;
            }
            catch
            {
                return null;
            }
            finally
            {

            }
        }
        // execute a query to return  a dataset
        // text contains name of stored procedure or text of command
        // param contains list of paramaters
        // procedure shows whether text contains stored procedure name or not
        public DataSet ExecuteDataSet(string text, SqlParameter[] param, bool procedure)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                if (procedure == true)
                    cmd.CommandType = CommandType.StoredProcedure;
                else
                    cmd.CommandType = CommandType.Text;
                cmd.CommandText = text;
                cmd.Connection = con;
                if (param != null)
                {
                    foreach (SqlParameter par in param)
                    {
                        cmd.Parameters.Add(par);
                    }
                }
                adp = new SqlDataAdapter(cmd);
                //     adp = new SqlDataAdapter(cmd, con);
                ds = new DataSet();
                adp.Fill(ds);
                return ds;
            }
            catch (Exception ex) { return null; }
            finally
            {
                con.Close();
            }
        }
    }
}
********************************************************************************


How to call utility class


inside the dal.cs

  public static int myDemoMethod(objofPropertyclass )
    {
Data.Utility _objDal = new Data.Utility();
    
        SqlParameter[] paramMYard = new SqlParameter[3];
              paramMYard[0] = new SqlParameter("@TransactionType", _objofPropertyclass.transactionType);
        //what
        paramMYard[1] = new SqlParameter("@Condition",_objofPropertyclass.condition);
        paramMYard[2] = new SqlParameter("@Colour", _objofPropertyclass.colour);
int result = _objDal.ExecuteNonQuery("StordProcedureName", paramMYard, true,true);


        if (result > 0)
        {
            return result;
        }
        return 0;

}