SQL DataAccess

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Text;

namespace Common
{
    public class DataAccess
    {
        #region Private members

        private SqlDataAdapter _da;
        private SqlCommand _cmd;
        private string _sp_name;
        private string _server_name;
        private string _database_name;
        private string _user_name;
        private string _password;
        private const string OLE_DB = "oledb";
        //private bool isDisposed = false;

        #endregion

        #region Constructors

        /// <summary>
        /// Default constructor, instantiates
        /// a new SqlCommand object to use.
        /// </summary>
        public DataAccess()
        {
            this.CmdObject = new SqlCommand();
        }

        /// <summary>
        /// This constructor takes one parameter,
        /// which is the name of the database
        /// to connect to.
        /// </summary>
        /// <param name="dbName"></param>
        public DataAccess(string dbName)
        {
            this.CmdObject = new SqlCommand();
            this.Schema = dbName;
        }

        public DataAccess(string schema, string server, string username, string password)
        {
            this.Schema = schema;
            this.ServerName = server;
            this.UserName = username;
            this.Password = password;
        }

        public DataAccess(string dbName, string commandText)
        {
            this.CmdObject = new SqlCommand();
            this.Schema = dbName;
            this.CommandText = commandText;
        }

        /// <summary>
        /// This constructor takes two parameters: first the name
        /// of the database, then the name of a stored procedure
        /// within that database.
        /// </summary>
        /// <param name="dbName"></param>
        /// <param name="commandText"></param>
        public DataAccess(string dbName, string commandText, CommandType commandType)
        {
            this.CmdObject = new SqlCommand();
            this.Schema = dbName;
            this.CommandText = commandText;
            this.CmdType = commandType;
        }

        #endregion


        public void DBOps(string sql)
        {
            DBOps(sql, CommandType.Text, null);
        }

        public void DBOps(string sql, CommandType commandtype, List<SqlParameter> dbparams)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = ConnectionString(this.Schema, this.ServerName, this.UserName, this.Password);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandType = commandtype;

            if (dbparams != null)
            {
                foreach (SqlParameter sq in dbparams)
                {
                    cmd.Parameters.Add(sq);
                }
            }


            cmd.ExecuteNonQuery();
        }

        public DataTable FetchData(string sql)
        {
            return FetchData(sql, CommandType.Text, null);
        }

        public DataTable FetchData(string sql, CommandType commandtype, List<SqlParameter> dbparams)
        {
            if (!string.IsNullOrEmpty(sql))
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConnectionString(this.Schema, this.ServerName, this.UserName, this.Password);
                conn.Open();

                SqlCommand cmd = new SqlCommand();

                cmd.CommandType = commandtype;

                if (dbparams != null)
                {
                    foreach (SqlParameter param in dbparams)
                    {
                        cmd.Parameters.Add(param);
                    }
                }

                cmd.CommandText = sql;
                cmd.Connection = conn;
                SqlDataReader rdr;
                rdr = cmd.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(rdr);
                conn.Close();
                rdr.Close();
                return dt;
            }
            else
            {
                return null;
            }
        }

        #region Private Methods


        private static string ConnectionString(string schema, string server, string user, string password)
        {
            string conn = string.Format("Server={0};Database={1};User Id={2};Password={3};", server, schema, user, password);
            return conn ;
        }

        private DataSet GetDataSet(bool useSqlClient)
        {
            ///summ creates a new DataSet
            using (DataSet _ds = new DataSet())
            {
                _ds.Locale = CultureInfo.InvariantCulture;

                using (this.CmdObject)
                {
                    this.CmdObject.Connection = DataAccess.GetSqlServerConnection(
                        this.ServerName, this.Schema,
                        useSqlClient ? "sqlclient" : "oledb", this.UserName, this.Password);

                    this.CmdObject.CommandText = this.CommandText;
                    this.CmdObject.CommandType = this.CmdType;

                    // create new SqlDataAdapter and fill DataSet
                    using (_da = new SqlDataAdapter(this.CmdObject))
                    {
                        // fill the DataSet
                        _da.Fill(_ds, "recordset");

                        // create new DataView from the resultant table
                        return _ds;
                    }
                }
            }
        }

        private static SqlConnection GetSqlServerConnection(string schema, string server, string provider, string username, string password)
        {
            StringBuilder _connstring = new StringBuilder(string.Empty);

            switch (provider.ToLower())
            {
                case "sqlclient":
                    _connstring.Append(string.Format("Data Source={0};Initial Catalog={1};" +
                        "User Id={2};Password={3};", server, schema, username, password));
                    break;
                case OLE_DB:
                    _connstring.Append(string.Format("Provider=sqloledb;Data Source={0}" +
                        ";Initial Catalog={1};User Id={2}Password={3};", server, schema, username, password));
                    break;

                default:
                    throw new Exception("Invalid provider string specified");
            }

            return new SqlConnection(_connstring.ToString());
        }

        private DataSet GetDataSet()
        {
            return this.GetDataSet(true);
        }

        public object MyDataObject
        {
            get
            {
                using (DataSet _ds = this.GetDataSet())
                {
                    // create new DataView from the resultant table
                    return (Object)(_ds.Tables["recordset"]);
                }
            }
        }

        public DataView MyDataView
        {
            get
            {
                using (DataSet _ds = this.GetDataSet())
                {
                    // create new DataView from the resultant table
                    return new DataView(_ds.Tables["recordset"]);
                }
            }
        }

        public DataTable MyDataTable
        {
            get
            {
                using (DataSet _ds = this.GetDataSet())
                {
                    // create new DataView from the resultant table
                    return _ds.Tables["recordset"] as DataTable;
                }
            }
        }

        private string ServerName
        {
            get { return _server_name; }
            set { _server_name = value; }
        }

        private string UserName
        {
            get { return _user_name; }
            set { _user_name = value; }
        }

        public string Password
        {
            get { return _password; }
            set { _password = value; }
        }

        public string Schema
        {
            get { return _database_name; }
            set { _database_name = value; }
        }

        public string CommandText
        {
            // stored procedure name property accessor methods
            get { return _sp_name; }

            set { _sp_name = value; }
        }

        public CommandType CmdType
        {
            get;
            set;
        }

        public SqlCommand CmdObject
        {
            get { return _cmd; }
            set { _cmd = value; }
        }

        #endregion

        #region IDisposable method implementation
        protected void Dispose(bool disposing)
        {
            if (disposing)
            {
                // Code to dispose the managed resources of the class
            }
            // Code to dispose the un-managed resources of the class

            //isDisposed = true;
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
        #endregion

    }
}