Custom Paging using AJAX with Sorting

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace JS.Data
{
    public sealed class DataProvider
    {
        #region Fields

        private static volatile DataProvider instance;
        private static object syncRoot = new Object();

        private string _connectionString = string.Empty;
        private SqlConnection _dbConnection = null;
        private SqlTransaction _dbTransaction = null;

        #endregion

        #region Properties

        public static DataProvider Instance
        {
            get
            {
                if (instance == null)
                {
                    lock (syncRoot)
                    {
                        if (instance == null)
                            instance = new DataProvider();
                    }
                }

                return instance;
            }
        }

        public string ConnectionString
        {
            get { return _connectionString; }
            set { _connectionString = value; }
        }

        #endregion

        #region Constructor

        private DataProvider()
        {
            _connectionString = ConfigurationManager.ConnectionStrings["Js_Connection"].ConnectionString;
        }

        #endregion

        #region Methods

        public void StartTransaction()
        {
            try
            {
                this.OpenConnection();
                if (_dbConnection != null)
                    _dbTransaction = _dbConnection.BeginTransaction(IsolationLevel.ReadCommitted, "dbTransaction");
            }
            catch
            {
                _dbTransaction = null;
                this.CloseConnection();
            }
        }

        public void CommitTransaction()
        {
            try
            {
                if (_dbTransaction != null)
                    _dbTransaction.Commit();
            }
            catch
            {
                throw;
            }
            finally
            {
                _dbTransaction = null;
                this.CloseConnection();
            }
        }

        public void RollbackTransaction()
        {
            try
            {
                if (_dbTransaction != null)
                    _dbTransaction.Rollback();
            }
            catch
            {
                throw;
            }
            finally
            {
                _dbTransaction = null;
                this.CloseConnection();
            }
        }

        public void ExecuteQuery(string commandText)
        {
            SqlCommand dbCommand = GetDataCommand(commandText);
            try
            {
                if (dbCommand != null)
                    dbCommand.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
            finally
            {
                if (dbCommand != null)
                {
                    if (dbCommand.Connection != null)
                    {
                        if (dbCommand.Connection.State == ConnectionState.Open)
                        {
                            dbCommand.Connection.Close();
                            dbCommand.Connection.Dispose();
                        }
                    }
                    dbCommand.Dispose();
                }
            }
        }

        public object ExecuteQuery(string commandText, Hashtable @Parameters)
        {
            object result = null;
            SqlCommand dbCommand = GetDataCommand(commandText, @Parameters);
            try
            {
                if (dbCommand != null)
                {
                    if (this._dbTransaction == null)
                        this.OpenConnection();

                    dbCommand.Connection = this._dbConnection;

                    result = dbCommand.ExecuteNonQuery();
                    if (dbCommand.Parameters != null && dbCommand.Parameters.Contains("@Output"))
                        result = dbCommand.Parameters["@Output"].Value;
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (dbCommand != null)
                {
                    if (dbCommand.Transaction == null)
                    {
                        if (dbCommand.Connection != null)
                        {
                            if (dbCommand.Connection.State == ConnectionState.Open)
                            {
                                dbCommand.Connection.Close();
                                dbCommand.Connection.Dispose();
                            }
                        }
                    }
                    dbCommand.Dispose();
                }
            }
            return result;
        }

        public object ExecuteNonQuery(string commandText, Hashtable @parameters)
        {
            object result = null;
            string strResult = string.Empty;
            try
            {
                SqlCommand dbCommand = this.GetDataCommand(commandText, parameters);
                if (dbCommand != null)
                {
                    if (this._dbTransaction == null)
                        this.OpenConnection();

                    dbCommand.Connection = this._dbConnection;

                    if (_dbTransaction != null)
                        dbCommand.Transaction = this._dbTransaction;

                    if (dbCommand.Connection != null && dbCommand.Connection.State == ConnectionState.Open)
                    {
                        result = dbCommand.ExecuteNonQuery();
                        if (dbCommand.Parameters != null && dbCommand.Parameters.Contains("@Output"))
                            result = dbCommand.Parameters["@Output"].Value;
                        dbCommand.Dispose();
                    }
                }
            }
            catch
            {
                result = -1;
            }
            finally
            {
                if (_dbTransaction == null)
                    this.CloseConnection();
            }
            return result;
        }

        public int ExecuteNonQuery(string commandText)
        {
            int result = -1;
            try
            {
                SqlCommand dbCommand = this.GetDataCommand(commandText);
                if (dbCommand != null)
                {
                    if (this._dbTransaction == null)
                        this.OpenConnection();

                    dbCommand.Connection = this._dbConnection;

                    if (_dbTransaction != null)
                        dbCommand.Transaction = this._dbTransaction;

                    if (dbCommand.Connection != null && dbCommand.Connection.State == ConnectionState.Open)
                        result = dbCommand.ExecuteNonQuery();


                    dbCommand.Dispose();
                }
            }
            catch
            {
                result = -1;
            }
            finally
            {
                if (_dbTransaction == null)
                    this.CloseConnection();
            }
            return result;
        }

        public int ExecuteScaler(string commandText, Hashtable @parameters)
        {
            object result = string.Empty;
            try
            {
                SqlCommand dbCommand = this.GetDataCommand(commandText, parameters);
                if (dbCommand != null)
                {
                    if (this._dbTransaction == null)
                        this.OpenConnection();

                    dbCommand.Connection = this._dbConnection;

                    if (_dbTransaction != null)
                        dbCommand.Transaction = this._dbTransaction;

                    if (dbCommand.Connection != null && dbCommand.Connection.State == ConnectionState.Open)
                    {
                        result = dbCommand.ExecuteScalar();
                        if (@parameters.Contains("@output"))
                        {
                            int output = 0;
                            int.TryParse(@parameters["@output"].ToString(), out output);
                            if (output > 0)
                                result = output;
                        }

                        dbCommand.Dispose();
                    }
                }
            }
            catch
            {
                result = -1;
            }
            finally
            {
                if (_dbTransaction == null)
                    this.CloseConnection();
            }
            return Convert.ToInt32(result);
        }

        public object ExecuteScalarQuery(string commandText)
        {
            object result = "";
            try
            {
                SqlCommand dbCommand = this.GetDataCommand(commandText);
                if (dbCommand != null)
                {
                    if (this._dbTransaction == null)
                        this.OpenConnection();

                    dbCommand.Connection = this._dbConnection;

                    if (_dbTransaction != null)
                        dbCommand.Transaction = this._dbTransaction;

                    if (dbCommand.Connection != null && dbCommand.Connection.State == ConnectionState.Open)
                        result = dbCommand.ExecuteScalar();

                    dbCommand.Dispose();
                }
            }
            catch
            {
                result = -1;
            }
            finally
            {
                if (_dbTransaction == null)
                    this.CloseConnection();
            }
            return result;
        }

        public DataSet GetDataSet(string commandText, Hashtable @parameters)
        {
            DataSet result = new DataSet();
            try
            {
                SqlCommand dbCommand = this.GetDataCommand(commandText, parameters);
                if (dbCommand != null)
                {
                    this.OpenConnection();
                    dbCommand.Connection = this._dbConnection;

                    if (dbCommand.Connection != null && dbCommand.Connection.State == ConnectionState.Open)
                    {
                        using (SqlDataAdapter dbAdapter = new SqlDataAdapter(dbCommand))
                        {
                            dbAdapter.Fill(result);
                        }
                    }

                    dbCommand.Dispose();
                }
            }
            catch
            {
                result = null;
            }
            finally
            {
                this.CloseConnection();
            }
            return result;
        }

        public DataSet GetDataSet(string commandText)
        {
            DataSet result = new DataSet();
            try
            {
                SqlCommand dbCommand = this.GetDataCommand(commandText);
                if (dbCommand != null)
                {
                    this.OpenConnection();
                    dbCommand.Connection = this._dbConnection;

                    if (dbCommand.Connection != null && dbCommand.Connection.State == ConnectionState.Open)
                    {
                        using (SqlDataAdapter dbAdapter = new SqlDataAdapter(dbCommand))
                        {
                            dbAdapter.Fill(result);
                        }
                    }

                    dbCommand.Dispose();
                }
            }
            catch
            {
                result = null;
            }
            finally
            {
                this.CloseConnection();
            }
            return result;
        }

        public DataTable GetDataTable(string commandText, Hashtable @parameters)
        {
            DataTable result = new DataTable();
            try
            {
                SqlCommand dbCommand = this.GetDataCommand(commandText, parameters);
                if (dbCommand != null)
                {
                    this.OpenConnection();
                    dbCommand.Connection = this._dbConnection;

                    if (dbCommand.Connection != null && dbCommand.Connection.State == ConnectionState.Open)
                    {
                        using (SqlDataAdapter dbAdapter = new SqlDataAdapter(dbCommand))
                        {
                            dbAdapter.Fill(result);
                        }
                    }

                    dbCommand.Dispose();
                }
            }
            catch
            {
                result = null;
            }
            finally
            {
                this.CloseConnection();
            }
            return result;
        }

        public DataTable GetDataTable(string commandText)
        {
            DataTable result = new DataTable();
            try
            {
                SqlCommand dbCommand = this.GetDataCommand(commandText);
                dbCommand.CommandTimeout = 60;
                if (dbCommand != null)
                {
                    this.OpenConnection();
                    dbCommand.Connection = this._dbConnection;

                    if (dbCommand.Connection != null && dbCommand.Connection.State == ConnectionState.Open)
                    {
                        using (SqlDataAdapter dbAdapter = new SqlDataAdapter(dbCommand))
                        {
                            dbAdapter.Fill(result);
                        }
                    }
                    dbCommand.Dispose();
                }
            }
            catch
            {
                result = null;
            }
            finally
            {
                this.CloseConnection();
            }
            return result;
        }

        #endregion

        #region Private Methods

        private void OpenConnection()
        {
            try
            {
                if (_dbConnection == null)
                    _dbConnection = new SqlConnection(ConnectionString);

                _dbConnection.Open();
            }
            catch
            {
                _dbConnection = null;
            }
        }

        private void CloseConnection()
        {
            try
            {
                if (_dbConnection != null && _dbConnection.State == ConnectionState.Open)
                    _dbConnection.Close();

                _dbConnection = null;
            }
            catch
            {
                _dbConnection = null;
            }
        }

        private SqlCommand GetDataCommand(string commandText, Hashtable @parameters)
        {
            SqlCommand dbCommand = null;
            try
            {
                dbCommand = new SqlCommand();
                dbCommand.CommandText = commandText;
                dbCommand.CommandType = CommandType.StoredProcedure;

                if (parameters != null && parameters.Count > 0)
                {
                    foreach (string parameterKey in parameters.Keys)
                    {
                        SqlParameter dbParameter = new SqlParameter(parameterKey, parameters[parameterKey]);
                        if (parameterKey == "@Output")
                        {
                            dbParameter.Direction = ParameterDirection.Output;
                            dbParameter.SqlDbType = System.Data.SqlDbType.NVarChar;
                            dbParameter.Size = -1;
                        }

                        dbCommand.Parameters.Add(dbParameter);
                    }

                }
            }
            catch
            {
                dbCommand = null;
            }
            return dbCommand;
        }

        //private SqlCommand GetDataCommand(string commandText, Hashtable @Parameters)
        //{
        //    SqlCommand dbCommand = null;
        //    try
        //    {
        //        if (this.dbTransaction == null)
        //            OpenConnection();

        //        if (this.dbConnection != null && this.dbConnection.State == ConnectionState.Open)
        //        {
        //            dbCommand = new SqlCommand();
        //            dbCommand.Connection = this.dbConnection;
        //            if (this.dbTransaction != null)
        //                dbCommand.Transaction = this.dbTransaction;

        //            dbCommand.CommandText = commandText;
        //            dbCommand.CommandType = CommandType.StoredProcedure;
        //            if (@Parameters != null)
        //            {
        //                foreach (string parameter in @Parameters.Keys)
        //                {
        //                    SqlParameter dbParameter = new SqlParameter(parameter, @Parameters[parameter]);
        //                    if (parameter == "@Output")
        //                        dbParameter.Direction = ParameterDirection.Output;

        //                    dbCommand.Parameters.Add(dbParameter);
        //                }
        //            }
        //        }
        //    }
        //    catch (SqlException ex)
        //    {
        //        throw ex;
        //        //logger.Error("SQL Error: ", ex);
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //        //logger.Error("Data Error: ", ex);
        //    }
        //    return dbCommand;
        //}

        private SqlCommand GetDataCommand(string commandText)
        {
            SqlCommand dbCommand = null;
            try
            {
                dbCommand = new SqlCommand();
                dbCommand.CommandText = commandText;
                dbCommand.CommandType = CommandType.Text;
            }
            catch
            {
                dbCommand = null;
            }
            return dbCommand;
        }
        #endregion
    }
}