DBAccess

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 DBAccess
/// </summary>

public class DBAccess
{
    private SqlConnection dbcon;
    public static string connectionString
    {
        get { return ConfigurationManager.ConnectionStrings["PSCPConStr"].ConnectionString; }
    }
    public DBAccess()
    {
        this.dbcon = new SqlConnection(connectionString);
    }

    public DataSet GetDataSet(string sql, SqlParameter[] parameters)
    {
        DataSet dataset = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter();
        SqlCommand cmd = new SqlCommand(sql, this.dbcon);
        foreach (SqlParameter p in parameters)
        {
            cmd.Parameters.Add(p);
        }
        adapter.SelectCommand = cmd;
        adapter.Fill(dataset);
        return dataset;
    }
    public DataSet GetDataSet(string sql)
    {
        DataSet dataset = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter();
        SqlCommand cmd = new SqlCommand(sql, this.dbcon);
        adapter.SelectCommand = cmd;
        adapter.Fill(dataset);
        return dataset;
    }
    public int ExecuteCommand(string sql, SqlParameter[] parameters)
    {
        SqlCommand cmd = new SqlCommand(sql, this.dbcon);
        foreach (SqlParameter p in parameters)
        {
            cmd.Parameters.Add(p);
        }
        cmd.Connection.Open();
        int ret = cmd.ExecuteNonQuery();
        cmd.Connection.Close();
        return ret;
    }

    public int ExecuteCommand(string sql)
    {
        SqlCommand cmd = new SqlCommand(sql, this.dbcon);
        cmd.Connection.Open();
        int ret = cmd.ExecuteNonQuery();
        cmd.Connection.Close();
        return ret;
    }

    public object GetScalar(string sql, SqlParameter[] parameters)
    {
        try
        {
            DataSet ds = this.GetDataSet(sql, parameters);
            return ds.Tables[0].Rows[0][0];
        }
        catch
        {
            return null;
        }
    }

    public object GetScalar(string sql, SqlParameter[] parameters, string columnName)
    {
        try
        {
            DataSet ds = this.GetDataSet(sql, parameters);
            return ds.Tables[0].Rows[0][columnName];
        }
        catch
        {
            return null;
        }
    }

    public object GetScalar(string sql, string columnName)
    {
        try
        {
            DataSet ds = this.GetDataSet(sql);
            return ds.Tables[0].Rows[0][columnName];
        }
        catch
        {
            return null;
        }
    }

    public object GetScalar(string sql)
    {
        try
        {
            DataSet ds = this.GetDataSet(sql);
            return ds.Tables[0].Rows[0][0];
        }
        catch
        {
            return null;
        }
    }

    public object GetFromStoredProc(string storeProcName, SqlParameter[] collection, string output)
    {
        object toRet;
        SqlCommand cmd = new SqlCommand(storeProcName, this.dbcon);
        cmd.CommandType = CommandType.StoredProcedure;
        foreach (SqlParameter param in collection)
        {
            cmd.Parameters.Add(param);
        }
        cmd.Parameters[output].Direction = ParameterDirection.Output;
        this.dbcon.Open();
        cmd.ExecuteNonQuery();
        toRet = cmd.Parameters[output].Value;
        this.dbcon.Close();
        return toRet;
    }

    public void RunStoredProc(string storeProcName, SqlParameter[] collection)
    {
        SqlCommand cmd = new SqlCommand(storeProcName, this.dbcon);
        cmd.CommandTimeout = 40000;
        cmd.CommandType = CommandType.StoredProcedure;
        foreach (SqlParameter param in collection)
        {
            cmd.Parameters.Add(param);
        }
        this.dbcon.Open();
        cmd.ExecuteNonQuery();
        this.dbcon.Close();
    }

    public DataRow GetDataRow(string sql)
    {
        return this.GetDataSet(sql).Tables[0].Rows[0];
    }

    public DataRow GetDataRow(string sql, SqlParameter[] parameters)
    {
        return this.GetDataSet(sql, parameters).Tables[0].Rows[0];
    }
}

Continue Reading