using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using System.Reflection;
using System.Runtime.CompilerServices;

namespace DL
{
    public sealed class DAL:DLCommon,IDisposable
    {
        private bool disposed;

        public DAL()
        {
            OpenDb();
        }
     
        //[MethodImpl(MethodImplOptions.Synchronized)]
        //public static DAL CreateInstance()
        //{
        //    if (db == null)
        //        db = new DAL();

        //    return db;
        //}

        public string LastError
        {
            get
            {
                return strError;
            }
        }

        public bool HasError
        {
            get
            {
                return blnError;
            }
        }

        public DataSet DatasetResult
        {
            get
            {
                return ds;
            }
        }

        public SqlDataReader DataReader
        {
            get
            {
                return dr;
            }
        }

        /// <summary>
        /// This function will open the database. The connection is stored in the variable Cn.
        /// </summary>
        /// <returns>Returns a boolean value, which denote the function failed or not</returns>
        private bool OpenDb()
        {
            try
            {
                Cn = new SqlConnection(strCnString);
                Cn.Open();
                IsDbOpened = true;
                blnError = false;
                return true;
            }
            catch(Exception ex)
            {
                strError = ex.Message;
                IsDbOpened = false;
                blnError = true;
                return false;
            }
        }

        /// <summary>
        /// This function used to get a dataset aganist a given sql command. This function will return null if any error occured.
        /// </summary>
        /// <param name="strSql">The sql which is to generate a dataset.</param>
        /// <returns></returns>
        public DataSet GetDataSet(string strSql)
        {
            try
            {
                if (IsDbOpened == false)
                {
                    strError = "Connection is not opened";
                    blnError = true;
                    return null;
                }

                using (adp = new SqlDataAdapter(strSql, Cn))
                {
                    using (ds = new DataSet())
                    {
                        adp.Fill(ds);
                        blnError = false;
                        return ds;
                    }
                }
                
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                blnError = true;
                return null;
            }            
        }

        public DataSet GetDataSet(string strSql,CommandType type)
        {
            try
            {
                if (IsDbOpened == false)
                {
                    strError = "Connection is not opened";
                    blnError = true;
                    return null;
                }

                using (adp = new SqlDataAdapter(strSql, Cn))
                {
                    adp.SelectCommand.CommandType = type;
                    if (type==CommandType.StoredProcedure && SqlParams != null )
                    {
                        adp.SelectCommand.Parameters.AddRange(SqlParams.ToArray());
                    }

                    using (ds = new DataSet())
                    {
                        adp.Fill(ds);
                        SqlParams = null;
                        blnError = false;
                        return ds;
                    }
                }
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                SqlParams = null;
                blnError = true;
                return null;
            }            
        }

        /// <summary>
        /// This function used to get a dataset aganist a given sql command. This function will return null if any error occured.
        /// </summary>
        /// <param name="strSql">The sql which is to generate a dataset.</param>
        ///   /// <param name="strTableName">The table name used to name the dataset</param>
        /// <returns></returns>
        public DataSet GetDataSet(string strSql,string strTableName)
        {
            try
            {
                if (IsDbOpened ==false)
                {
                    strError = "Connection is not opened";
                    blnError = true;
                    return null;
                }

                using (adp = new SqlDataAdapter(strSql, Cn))
                {
                    using (ds = new DataSet())
                    {
                        adp.Fill(ds, strTableName);
                        blnError = false;
                        return ds;
                    }
                }
               
               
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                blnError = true;
                return null;
            }
        }

        /// <summary>
        /// This function used to get a dataset aganist a given sql command. This function will return null if any error occured.
        /// </summary>
        /// <param name="strSql">The sql which is to generate a dataset.</param>
        ///   /// <param name="strTableName">The table name used to name the dataset</param>
        /// <returns></returns>
        public DataSet GetDataSet(string strSql, string strTableName,CommandType type)
        {
            try
            {
                if (IsDbOpened == false)
                {
                    strError = "Connection is not opened";
                    blnError = true;
                    return null;
                }

                using (adp = new SqlDataAdapter(strSql, Cn))
                {
                    adp.SelectCommand.CommandType = type;

                    if (type == CommandType.StoredProcedure && SqlParams != null)
                    {
                        adp.SelectCommand.Parameters.AddRange(SqlParams.ToArray());
                    }

                    using (ds = new DataSet())
                    {
                        adp.Fill(ds, strTableName);
                        SqlParams = null;
                        blnError = false;
                        return ds;
                    }
                }
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                SqlParams = null;
                blnError = true;
                return null;
            }
        }

        /// <summary>
        /// Init a new dataset
        /// </summary>
        /// <returns></returns>
        public bool CreateDataSet()
        {
            try
            {
                ds = new DataSet();
                blnError = false;
                return true;
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                blnError = true;
                return false;
            }
        }

        public bool OpenDataset(string strSql)
        {
            try
            {
                if (ds == null)
                {
                    strError = "Dataset is not initialized";
                    blnError = true;
                    return false;
                }

                if (IsDbOpened == false)
                {
                    strError = "Connection is not opened";
                    blnError = true;
                    return false;
                }

                using (adp = new SqlDataAdapter(strSql, Cn))
                {
                    adp.Fill(ds);
                }

                blnError = false;
                return true;
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                blnError = true;
                return false;
            }
        }

        public bool OpenDataset(string strSql,CommandType type)
        {
            try
            {
                if (ds == null)
                {
                    strError = "Dataset is not initialized";
                    blnError = true;
                    return false;
                }

                if (IsDbOpened == false)
                {
                    strError = "Connection is not opened";
                    blnError = true;
                    return false;
                }

                using (adp = new SqlDataAdapter(strSql, Cn))
                {
                    adp.SelectCommand.CommandType = type;

                    if (type == CommandType.StoredProcedure && SqlParams != null)
                    {
                        adp.SelectCommand.Parameters.AddRange(SqlParams.ToArray());
                    }

                    adp.Fill(ds);
                }

                SqlParams = null;
                blnError = false;
                return true;
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                SqlParams = null;
                blnError = true;
                return false;
            }
        }

        public bool OpenDataset(string strSql,string strTableName)
        {
            try
            {
                if (ds == null)
                {
                    strError = "Dataset is not initialized";
                    blnError = true;
                    return false;
                }

                if (IsDbOpened == false)
                {
                    strError = "Connection is not opened";
                    blnError = true;
                    return false;
                }

                using (adp = new SqlDataAdapter(strSql, Cn))
                {
                    adp.Fill(ds, strTableName);
                }

                blnError = false;
                return true;
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                blnError = true;
                return false;
            }
        }

        public bool OpenDataset(string strSql, string strTableName,CommandType type)
        {
            try
            {
                if (ds == null)
                {
                    strError = "Dataset is not initialized";
                    blnError = true;
                    return false;
                }

                if (IsDbOpened == false)
                {
                    strError = "Connection is not opened";
                    blnError = true;
                    return false;
                }

                using (adp = new SqlDataAdapter(strSql, Cn))
                {
                    adp.SelectCommand.CommandType = type;

                    if (type == CommandType.StoredProcedure && SqlParams != null)
                    {
                        adp.SelectCommand.Parameters.AddRange(SqlParams.ToArray());
                    }

                    adp.Fill(ds, strTableName);
                }

                SqlParams = null;
                blnError = false;
                return true;
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                SqlParams = null;
                blnError = true;
                return false;
            }
        }

        public DataTable GetDataTable(string strSql, CommandType type)
        {
            try
            {
                if (IsDbOpened == false)
                {
                    strError = "Connection is not opened";
                    blnError = true;
                    return null;
                }

                using (adp = new SqlDataAdapter(strSql, Cn))
                {
                    adp.SelectCommand.CommandType = type;
                    if (type == CommandType.StoredProcedure && SqlParams != null)
                    {
                        adp.SelectCommand.Parameters.AddRange(SqlParams.ToArray());
                    }

                    using (dt = new DataTable())
                    {
                        adp.Fill(dt);
                        SqlParams = null;
                        blnError = false;
                        return dt;
                    }
                }
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                SqlParams = null;
                blnError = true;
                return null;
            }
        }

        public DataTable GetDataTable(string strSql)
        {
            try
            {
                if (IsDbOpened == false)
                {
                    strError = "Connection is not opened";
                    blnError = true;
                    return null;
                }

                using (adp = new SqlDataAdapter(strSql, Cn))
                {
                    using (dt = new DataTable())
                    {
                        adp.Fill(dt);
                        blnError = false;
                        return dt;
                    }
                }
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                blnError = true;
                return null;
            }
        }

        public SqlDataReader GetDataReader(string strSql)
        {
            try
            {
                if (IsDbOpened == false)
                {
                    strError = "Connection is not opened";
                    blnError = true;
                    return null;
                }

                using (cmd = new SqlCommand(strSql, Cn))
                {
                    dr = cmd.ExecuteReader();
                    blnError = false;
                    return dr;
                }
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                blnError = true;
                return null;
            }
        }

        public SqlDataReader GetDataReader(string strSql,CommandType type)
        {
            try
            {
                if (IsDbOpened == false)
                {
                    strError = "Connection is not opened";
                    blnError = true;
                    return null;
                }

                using (cmd = new SqlCommand(strSql, Cn))
                {
                    cmd.CommandType = type;
                    if (type == CommandType.StoredProcedure && SqlParams != null)
                    {
                        cmd.Parameters.AddRange(SqlParams.ToArray());
                    }
                    dr = cmd.ExecuteReader();
                    SqlParams = null;
                    blnError = false;
                    return dr;
                }
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                SqlParams = null;
                blnError = true;
                return null;
            }
        }

        public bool OpenDataReader(string strSql)
        {
            try
            {
                if (IsDbOpened == false)
                {
                    strError = "Connection is not opened";
                    IsReaderOpened = false;
                    blnError = true;
                    return false;
                }

                using (cmd = new SqlCommand(strSql, Cn))
                {
                   dr = cmd.ExecuteReader();
                   IsReaderOpened = true;
                   blnError = false;
                   return true;
                }
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                IsReaderOpened = false;
                blnError = true;
                return false;
            }
        }

        public bool OpenDataReader(string strSql,CommandType type)
        {
            try
            {
                if (IsDbOpened == false)
                {
                    strError = "Connection is not opened";
                    IsReaderOpened = false;
                    blnError = true;
                    return false;
                }

                using (cmd = new SqlCommand(strSql, Cn))
                {
                    cmd.CommandType = type;
                    if (type == CommandType.StoredProcedure && SqlParams != null)
                    {
                        cmd.Parameters.AddRange(SqlParams.ToArray());
                    }
                    dr = cmd.ExecuteReader();

                    IsReaderOpened = true;
                    SqlParams = null;
                    blnError = false;
                    return true;
                }
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                SqlParams = null;
                IsReaderOpened = false;
                blnError = true;
                return false;
            }
        }

        public bool MoveReader()
        {
            try
            {
                if (IsReaderOpened == false)
                {
                    strError = "Reader is not opened";
                    blnError = true;
                    return false;
                }
                if (dr.Read())
                {
                    blnError = false;
                    return true;
                }
                else
                {
                    strError = "Cant open the reader";
                    return false;
                }
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                blnError = true;
                return false;
            }
        }

        public bool CloseReader()
        {
            try
            {
                if (IsReaderOpened == false)
                {
                    strError = "Reader is not opened";
                    blnError = true;
                    return false;
                }
                else
                {
                    dr.Close();
                    blnError = false;
                    return true;
                }
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                blnError = true;
                return false;
            }
        }

        public bool ExecuteSql(string strSql)
        {
            try
            {
                if (IsDbOpened == false)
                {
                    strError = "Connection is not opened";
                    blnError = true;
                    return false;
                }

                using (cmd = new SqlCommand(strSql, Cn))
                {
                    cmd.ExecuteNonQuery();
                }

                blnError = false;
                return true;
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                blnError = true;
                return false;
            }
        }

        public bool AddParameter(string strParamName, object value)
        {
            try
            {
                if (SqlParams == null)
                {
                    SqlParams = new List<SqlParameter>();
                }

                SqlParams.Add(new SqlParameter(strParamName, value));
                blnError = false;
                return true;
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                blnError = true;
                return false;
            }
        }

        public bool AddParameter(string strParamName, object value,DbType type,int intSize)
        {
            try
            {
                if (SqlParams == null)
                {
                    SqlParams = new List<SqlParameter>();
                }

                SqlParameter p = new SqlParameter(strParamName, value);
                p.DbType = type;
                p.Size = intSize;

                SqlParams.Add(p);

                blnError = false;
                return true;
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                blnError = true;
                return false;
            }
        }

        public bool AddParameter(params SqlParameter[] _params)
        {
            try
            {
                if (SqlParams == null)
                {
                    SqlParams = new List<SqlParameter>();
                }

                SqlParams.AddRange(_params);
                blnError = false;
                return true;
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                blnError = true;
                return false;
            }
        }
    
        public bool ExecuteSp(string strSpName)
        {
            try
            {
                if (IsDbOpened == false)
                {
                    strError = "Connection is not opened";
                    blnError = true;
                    return false;
                }

                using (cmd = new SqlCommand(strSpName, Cn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    if (SqlParams != null)
                    {
                        cmd.Parameters.AddRange(SqlParams.ToArray());
                    }
                    cmd.ExecuteNonQuery();
                }

                SqlParams = null;

                blnError = false;
                return true;
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                SqlParams = null;
                blnError = true;
                return false;
            }
        }

        public object GetScalar(string strSql)
        {
            try
            {
                if (IsDbOpened == false)
                {
                    strError = "Connection is not opened";
                    blnError = true;
                    return null;
                }

                using(cmd=new SqlCommand(strSql,Cn))
                {
                    blnError = false;
                    return cmd.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                blnError = true;
                return null;
            }
        }

        public object GetScalar(string strSql,CommandType type)
        {
            try
            {
                if (IsDbOpened == false)
                {
                    strError = "Connection is not opened";
                    blnError = true;
                    return null;
                }

                using (cmd = new SqlCommand(strSql, Cn))
                {
                    cmd.CommandType = type;
                    if (type == CommandType.StoredProcedure && SqlParams != null)
                    {
                        cmd.Parameters.AddRange(SqlParams.ToArray());
                    }

                    SqlParams = null;

                    blnError = false;
                    return cmd.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                strError = ex.Message;
                SqlParams = null;
                blnError = true;
                return null;
            }
        }

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

        private void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                if (disposing)
                {
                    SqlParams = null;

                    if (Cn != null)
                    {
                        Cn.Close();
                        Cn.Dispose();
                    }

                    if (cmd != null)
                    {
                        cmd.Dispose();
                    }

                    if (dr != null)
                    {
                        dr.Dispose();
                    }

                    if (adp != null)
                    {
                        adp.Dispose();
                    }

                    if (ds != null)
                    {
                        ds.Dispose();
                    }

                    if (dt != null)
                    {
                        dt.Dispose();
                    }

                }
            }
            disposed = true;
        }

        ~DAL()
        {
            Dispose(false);
        }
    }
}