Connection Class in C#

This is my first article. I don't know how to explain everything but I try my best and I am hoping for good comments to motivate me.

I have noticed that Usually whenever a newbie writes C# code to access a database he/she writes the database access code multiple times for various functions like they write the complete code for each insert, update, delete and so on.

So, I am here to teach you how to make a connection class that I learned from my great and respectable teacher "Sardar Ishtiaq Ahmed". You can use that class in any project where you want to access the database.

Note: You should have knowledge of DataGridView and SqlDataReader to understand its 2 methods. The purpose of this article is to teach you all how to avoid the repitition of the database access code.

OK. Here is the step-by-step method to code the connection class.

Step 1

Import the following namespaces.

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

Step 2

Create a namespace and a class in it:

namespace ConnectionNamespace
{
   public class Connection_Query
   {
      //Here put all the methods which I am defining below. . .
   }
}

Step 3

Create a varriable string for saving the connection string and a SqlConnection object:

string ConnectionString = "";
SqlConnection con;

Step 4

Create a method to open the connecction. First I make a method OpenConnection() for accessing the database call at the very beginning to open the database connection.

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

Step 5

Now create a CloseConnection method. After completing all the processes call this method to close the connection of the database that we opened in Step 4.

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

Step 6

ExecuteQuries Method: Whenever you want to execute a query, like an insert, update or delete query then simply call this function using the object of a class and pass your query to the function.

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

Step 7

Now create a SqlDataReader Method. Learn how to use the SqlDataReader so you can understand how to use this class. Here I will explain the use of DataReader since you know my focus is the Connection class.

This function will return the data of type SqlDataReader. Make an object of SqlDataReader and call the method in it with the relevent query in it as an argument.

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

Step 8

Now show the DataInGridView. As I said earlier, in the case of SqlDataReader you also should have the knowledge of the DataGridView. If you don't then learn about it so you can understant the method and its use.

It will return an object data type value as an object that is the parent of all the variables in C#.

We will initialize the datasource of the GridView with this method and a SQL Query in it as an argument.

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;
}

The following is the complete code of the Connection 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;
        }
    }
}

Note: the documentation file is also provided with it. 

Class Documentation

This is a class named "Connection_Class" to perform insert, update and delete and search to show the data in a DataGridView and also perform SqlDataReader operations.

Public Member Functions

void OpenConection ()

Call this method to open the connection.

void CloseConnection ()

Call this method to close the connection.

void ExecuteQueries (string Query_)

Call this method to perform insert, delete and update functions.

SqlDataReader DataReader (string Query_)

Call this method when to show data in a TextBox or Label.

object ShowDataInGridView (string Query_)

Call this method to show data in a DataGridView.

Member Function Documentation 

1. void CloseConnection ()
   
   Call this method to close the connection.
   
2. SqlDataReader DataReader (string Query_)
   
   Call this method when want to show data in a TextBox or Label.
   
   The following is the code to show how to use this method:
   

   SqlDataReader dr = ClassObject.DataReader("Select * From Student");
   dr.Read();
   
   textBox1.Text = dr["Stdnt_Name"].tostring();

   Parameters
   
   Query_ : Pass the query here.
   
   Returns
   
   Return SqlDataReader object
   
   
3. void ExecuteQueries (string Query_)
   
   Call this method to perform insert, delete, update function.
   
   Parameters
   
   Query_: your required query.
   
   
4. void OpenConection ()
   
   Call this method to open the connection.
   
   
5. object ShowDataInGridView (string Query_)
   
   Call this method to show data in a DataGridView.
   
   The following is the code to descride how to show data in GridView using it.
   

   dataGridView1.datasource = ClassObject.ShowDataInGridView("Select * From Student")

   Parameters
   
   Query_ : Place the required query here.
   
   Returns:
   
   return Object with data to Show in GridView

This class was genrated by HammadMaqbool.

Note : Use it with ASP.NET and C#.