Base Class to handle basic ADO Steps

Z Hussain

How to creat a base class or utility that handles the basics of creating a connection, command, parameters, etc. so that I don’t have that code in each method or class reduces chances for errors and simplifies updates?

Example: I have many Methods and I'm sure there'z a way to create a base class by atleast creating a Constructor or creating and inheriting base class

//First Method
public void InsertToDoList(ToDoListProp ToDoListProp)
{
SqlConnection objConn = new SqlConnection();
SqlCommand objCmd = new SqlCommand();
SqlDataAdapter objDa = new SqlDataAdapter();
DataSet objDs = new DataSet();
objConn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
objCmd = new SqlCommand();
objCmd.CommandText = "sp_WebToDoListSet";
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Connection = objConn;
}
//Second Method
public DataSet GetToDoList(int EmployeeId)
{
List ProjectList;
ProjectList = new List();
SqlConnection objConn = new SqlConnection();
SqlCommand objCmd = new SqlCommand();
SqlDataAdapter objDa = new SqlDataAdapter();
DataSet objDs = new DataSet();
// EmpId = int.Parse(System.Web.HttpContext.Current.Session["EmployeeID"].ToString());
objConn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
objCmd = new SqlCommand();
objCmd.CommandText = "sp_GetToDoList";
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Connection = objConn;
}
//Another Method
public void DeleteToDoList(int ToDoListId)
{
SqlConnection objConn = new SqlConnection();
SqlCommand objCmd = new SqlCommand();
SqlDataAdapter objDa = new SqlDataAdapter();
DataSet objDs = new DataSet();
objConn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
objCmd = new SqlCommand();
objCmd.CommandText = "sp_DeleteToDoList";
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.Connection = objConn;
}

Any Help will be much appreciated!

Thanks

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Gajendra Jangid7y ago
Hello Zack,
use following class
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Connection_Class
{
    public class Connection_Query
    {

        string ConnectionString = "";
        SqlConnection con;

        public void OpenConection()
        {
            con = new SqlConnection(ConnectionString);
            con.Open();
        }


        public void CloseConnection()
        {
            con.Close();
        }


        public void ExecuteQueries(string Query_)
        {
            SqlCommand cmd = new SqlCommand(Query_,con);
            cmd.ExecuteNonQuery();
        }


        public SqlDataReader DataReader(string Query_)
        {
            SqlCommand cmd = new SqlCommand(Query_,con);
            SqlDataReader dr = cmd.ExecuteReader();
            return dr;
        }


        public object ShowDataInGridView(string Query_)
        {
            SqlDataAdapter dr = new SqlDataAdapter(Query_, ConnectionString);
            DataSet ds = new DataSet();
            dr.Fill(ds);
            object dataum = ds.Tables[0];
            return dataum;
        }
    }
}
Salman Beg7y ago
Hi.
For your requirement, what you can do is create a class which conatins Custom methods for ExecuteNonQuery etc. And after that you need to create the object of that class and by using object you need to call those methods. The below link conatins an example probably you can follow that and start implementing it,

https://www.mssqltips.com/sqlservertip/3009/how-to-create-an-adonet-data-access-utility-class-for-sql-server/

Thanks.