Monday 26 December 2011

sql connection class for asp.net project

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

/// <summary>
/// Summary description for DataUtility
/// </summary>
public class DataUtility
{
    # region All Module level variables
    SqlDataAdapter Da;
    SqlConnection Con;
    SqlCommand Cmd;
    DataSet ds;

    # endregion
    public DataUtility()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    private void OpenConnection()
    {
        if (Con == null)
        {
            Con = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["PIS_Billing_Connection"].ToString());
        }
        // lets open the connection

        if (Con.State == ConnectionState.Closed)
        {
            Con.Open();
        }

        Cmd = new SqlCommand();

        //Set active connection with the command object

        Cmd.Connection = Con;

    }



    private void CloseConnection()
    {
        if (Con.State == ConnectionState.Open)
        {
            Con.Close();
        }
    }
    /// <summary>
    /// This is to release the memory from the connection object
    /// </summary>
    private void DisposeConnection()
    {
        if (Con != null)
        {
            Con.Dispose();
            Con = null;
        }
    }
    public int ExecuteSql(String Sql)
    {
        OpenConnection();
        //int Result;
        //Set command object properties

        Cmd.CommandType = CommandType.Text;
        Cmd.CommandText = Sql;
        Cmd.CommandTimeout = 3000;

        // Now call the method

        int Result = Cmd.ExecuteNonQuery();

        CloseConnection();
        DisposeConnection();

        return Result;
    }
    public bool IsExist(string sql)
    {

        OpenConnection();
        //now set command object properties
        Cmd.CommandType = CommandType.Text;
        Cmd.CommandText = sql;
        Cmd.CommandTimeout = 2000;

        //Execute Command object method
        int Result = (int)Cmd.ExecuteScalar();


        CloseConnection();
        DisposeConnection();

        if (Result > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    public SqlDataReader GetDataReader(String Sql)
    {
        OpenConnection();
        SqlDataReader dReader;
        Cmd.CommandType = CommandType.Text;
        Cmd.CommandText = Sql;
        Cmd.CommandTimeout = 1000;

        dReader = Cmd.ExecuteReader();
        //CommandBehavior.CloseConnection
        return dReader;
    }
    public DataTable GetDataTable(String sql)
    {
        DataTable dt = new DataTable();
        try
        {
            OpenConnection();
          

            Da = new SqlDataAdapter();
            Cmd.CommandType = CommandType.Text;
            Cmd.CommandText = sql;
            Cmd.CommandTimeout = 2000;

            Da.SelectCommand = Cmd;
            //ds = new DataSet();
            Da.Fill(dt);
            //dt = ds.Tables["table"];
            CloseConnection();
            return dt;
        }
        catch
        {
            return dt;
        }
    }
    public DataTable returnDataTable(string sql)
    {
        //Con.Open();
        ds = new DataSet();
        Da = new SqlDataAdapter(sql, Con);
        Da.Fill(ds, "datatable");
        DataTable dt = new DataTable();
        dt = ds.Tables["datatable"];
        Con.Close();
        return (dt);
    }
}

No comments:

Post a Comment