SQLHelper Class

The SqlHelper class is a utility class that can be used to execute commands in a SQL Server database.

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

/// <summary>
/// Summary description for Class_SQLHelper
/// </summary>
public class Class_SQLHelper
{
    private SqlCommand cmd = new SqlCommand();
    private SqlConnection conn;
    private bool bool_Status;
    private string str_Error;

    public Class_SQLHelper()
    {
        conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionstring"].ToString());
    }

    protected void OpenConnection()
    {
        try
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
        }
        catch (Exception ex)
        {
            OperationStatus = false;
            ErrorMessage = "Error : " + ex.Message;
        }
    }

    protected void CloseConnection()
    {
        try
        {
            if (conn.State != ConnectionState.Closed)
                conn.Close();
        }
        catch (Exception ex)
        {
            OperationStatus = false;
            ErrorMessage = "Error : " + ex.Message;
        }
    }

    public SqlConnection Connection
    {
        get { return conn; }
    }

    public SqlCommand Command
    {
        get { return cmd; }
        set { cmd = value; }
    }

    public bool OperationStatus
    {
        get { return bool_Status; }
        set { bool_Status = value; }
    }

    public string ErrorMessage
    {
        get { return str_Error; }
        set { str_Error = value; }
    }
}

Here we have created Class_SQLHelper to access database. Now we need to inherit this class to other class where we would like to use these resources. For example we have to manage CRUD operation of News so we can create other class like News.cs or Class_News.cs and inherit this class with Class_SQLHelper.cs as shown below :

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

/// <summary>
/// Summary description for Class_News
/// </summary>
public class Class_News : Class_SQLHelper
{
    public int NewsID { get; set; }
    public string Title { get; set; }
    public string NewsImagePath { get; set; }

    public DateTime PostedDate { get; set; }
    public string PostedBy { get; set; }
    public string Description { get; set; }
    public Boolean Status { get; set; }
    public DataTable NewsTable { get; set; }
    public string SearchText { get; set; }

    public Class_News()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    public void AddNews()
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "USP_AddNews";
            cmd.Parameters.AddWithValue("@Title", Title);
            cmd.Parameters.AddWithValue("@NewsImagePath", NewsImagePath);

            cmd.Parameters.AddWithValue("@PostedDate", PostedDate);
            cmd.Parameters.AddWithValue("@PostedBy", PostedBy);
            cmd.Parameters.AddWithValue("@Description", Description);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Connection;
            OpenConnection();
            NewsID = Convert.ToInt32(cmd.ExecuteScalar());
            if (NewsID > 0)
            {
                OperationStatus = true;
            }
            else
            {
                OperationStatus = false;
                ErrorMessage = "Error : News not added successfully.";
            }
        }
        catch (Exception ex)
        {
            ErrorMessage = "Error : " + ex.Message;
            OperationStatus = false;
        }
        finally
        {
            CloseConnection();
        }
    }

    public void UpdateNewsByID()
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "USP_UpdateNewsByID";
            cmd.Parameters.AddWithValue("@NewsID", NewsID);
            cmd.Parameters.AddWithValue("@Title", Title);
            cmd.Parameters.AddWithValue("@NewsImagePath", NewsImagePath);

            cmd.Parameters.AddWithValue("@PostedDate", PostedDate);
            cmd.Parameters.AddWithValue("@PostedBy", PostedBy);
            cmd.Parameters.AddWithValue("@Description", Description);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Connection;
            OpenConnection();
            NewsID = Convert.ToInt32(cmd.ExecuteScalar());
            if (NewsID > 0)
            {
                OperationStatus = true;
            }
            else
            {
                OperationStatus = false;
                ErrorMessage = "Error : News not updated successfully.";
            }
        }
        catch (Exception ex)
        {
            ErrorMessage = "Error : " + ex.Message;
            OperationStatus = false;
        }
        finally
        {
            CloseConnection();
        }
    }

    public void GetNewsByID()
    {
        try
        {
            SqlDataAdapter ad = new SqlDataAdapter();
            DataTable Sqldatatable = new DataTable();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "USP_GetNewsByID";
            cmd.Parameters.AddWithValue("@NewsID", NewsID);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Connection;
            ad.SelectCommand = cmd;
            OpenConnection();
            ad.Fill(Sqldatatable);
            NewsTable = Sqldatatable;
            OperationStatus = true;
            CloseConnection();
            Sqldatatable.Dispose();
            cmd.Dispose();
            ad.Dispose();


        }
        catch (Exception ex)
        {
            ErrorMessage = "Error : " + ex.Message;
            OperationStatus = false;
        }
        finally
        {
            CloseConnection();
        }
    }

    public void GetAllNews()
    {
        try
        {
            SqlDataAdapter ad = new SqlDataAdapter();
            DataTable Sqldatatable = new DataTable();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "USP_GetAllNews";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@SearchText", SearchText);
            cmd.Connection = Connection;
            ad.SelectCommand = cmd;
            OpenConnection();
            ad.Fill(Sqldatatable);
            NewsTable = Sqldatatable;
            OperationStatus = true;
            CloseConnection();
            Sqldatatable.Dispose();
            cmd.Dispose();
            ad.Dispose();
        }
        catch (Exception ex)
        {
            ErrorMessage = "Error : " + ex.Message;
            OperationStatus = false;
        }
        finally
        {
            CloseConnection();
        }
    }

    public void GetNews()
    {
        try
        {
            SqlDataAdapter ad = new SqlDataAdapter();
            DataTable Sqldatatable = new DataTable();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "USP_GetNews";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Connection;
            ad.SelectCommand = cmd;
            OpenConnection();
            ad.Fill(Sqldatatable);
            NewsTable = Sqldatatable;
            OperationStatus = true;
            CloseConnection();
            Sqldatatable.Dispose();
            cmd.Dispose();
            ad.Dispose();
        }
        catch (Exception ex)
        {
            ErrorMessage = "Error : " + ex.Message;
            OperationStatus = false;
        }
        finally
        {
            CloseConnection();
        }
    }
    public void DeleteNewsByID()
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "USP_DeleteNewsByID";
            cmd.Parameters.AddWithValue("@NewsID", NewsID);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Connection;
            OpenConnection();
            NewsID = Convert.ToInt32(cmd.ExecuteScalar());
            if (NewsID == -1)
            {
                OperationStatus = false;
                ErrorMessage = "Error : Property ID " + NewsID + " does not Exist.";
            }
            else
            {
                OperationStatus = true;
            }
        }
        catch (Exception ex)
        {
            ErrorMessage = "Error : " + ex.Message;
            OperationStatus = false;
        }
        finally
        {
            CloseConnection();
        }
    }
    public void GetMaxNewsID()
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "USP_GetMaxNewsID";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Connection;
            OpenConnection();
            NewsID = Convert.ToInt32(cmd.ExecuteScalar());
            OperationStatus = true;
            CloseConnection();
            cmd.Dispose();
        }
        catch (Exception ex)
        {
            ErrorMessage = "Error : " + ex.Message;
            OperationStatus = false;
        }
        finally
        {
            CloseConnection();
        }
    }

    public void UpdateNewsStatusByID()
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "USP_UpdateNewsStatusByID";
            cmd.Parameters.AddWithValue("@NewsID", NewsID);
            cmd.Parameters.AddWithValue("@Status", Status);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Connection;
            OpenConnection();
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            ErrorMessage = "Error : " + ex.Message;
            OperationStatus = false;
        }
        finally
        {
            CloseConnection();
        }
    }
}