namespace Repository
{
    public class SQLRepository
    {

        DbConnector dbCon = new DbConnector();

        #region Query Execute
        public Result ExecuteQuery(string SQL)
        {
            Result oResult = new Result();
            SqlCommand oCmd = null;

            try
            {

                if (dbCon.Connect != null)
                {
                    dbCon.OpenConnection();
                    oCmd = new SqlCommand(SQL, dbCon.Connect);
                    oCmd.ExecuteNonQuery();
                    oResult.ResultState = true;
                }
            }
            catch (Exception ex)
            {
                oResult.ResultState = false;
                oResult.SqlError = ex.Message;
            }
            finally
            {
                dbCon.CloseConnection();
            }
            return oResult;
        }

        public Result ExecuteQuery(List SQL)
        {
            Result oResult = new Result();
            SqlTransaction oTransaction = null;
            SqlCommand oCmd = null;

            try
            {

                if (dbCon.Connect != null)
                {
                    dbCon.OpenConnection();
                    oTransaction = dbCon.Connect.BeginTransaction();
                    foreach (string s in SQL)
                    {
                        oCmd = new SqlCommand(s, dbCon.Connect);
                        oCmd.Transaction = oTransaction;
                        oCmd.ExecuteNonQuery();
                        oResult.ResultState = true;
                    }
                    oTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                oResult.ResultState = false;
                oResult.SqlError = ex.Message;
                if (oTransaction != null)
                    oTransaction.Rollback();
            }
            finally
            {
                dbCon.CloseConnection();
            }
            return oResult;
        }

        public Result ExecuteQueryParam(List SQL)
        {
            Result oResult = new Result();
            SqlTransaction oTransaction = null;
            SqlCommand oCmd = null;

            try
            {

                if (dbCon.Connect != null)
                {
                    dbCon.OpenConnection();
                    oTransaction = dbCon.Connect.BeginTransaction();
                    foreach (SQLQuery s in SQL)
                    {
                        oCmd = new SqlCommand(s.sql, dbCon.Connect);
                        oCmd.Transaction = oTransaction;

                        foreach (SQLParam param in s.Param)
                        {
                            SqlParameter picparameter = new SqlParameter();
                            picparameter.SqlDbType = param.DbType; //SqlDbType.Image;
                            picparameter.ParameterName = param.ParamName; //"@PIC";
                            picparameter.Value = param.ParamValue;
                            oCmd.Parameters.Add(picparameter);
                        }

                        oCmd.ExecuteNonQuery();
                        oResult.ResultState = true;
                    }
                    oTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                oResult.ResultState = false;
                oResult.SqlError = ex.Message;
                if (oTransaction != null)
                    oTransaction.Rollback();
            }
            finally
            {
                dbCon.CloseConnection();
            }
            return oResult;
        }

        #endregion

        public Result Select(string SQL)
        {
            Result oResult = new Result();
            SqlCommand oCmd = null;
            try
            {

                if (dbCon.Connect != null)
                {
                    oCmd = new SqlCommand(SQL, dbCon.Connect);
                    oCmd.CommandTimeout = 0;
                    SqlDataAdapter adapter = new SqlDataAdapter(oCmd);
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);
                    oResult.ResultState = true;
                    oResult.Data = dt;
                }
            }
            catch (Exception ex)
            {
                oResult.ResultState = false;
                oResult.SqlError = ex.Message;
            }
            finally
            {
                dbCon.CloseConnection();
            }
            return oResult;
        }

    }

    public class DbConnector
    {

        private SqlConnection connection;

        public DbConnector()
        {
            connection = new SqlConnection(GlobalConnection());
        }

        public string GlobalConnection()
        {

            //string entityConnectionString = ConfigurationManager.ConnectionStrings["ERPEntities"].ConnectionString;
            //string providerConnectionString = new EntityConnectionStringBuilder(entityConnectionString).ProviderConnectionString;
            //return providerConnectionString;

            string str = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
            //ConfigurationSettings.AppSettings["CNN"];
            return str;
        }

        public SqlConnection Connect
        {
            get { return connection; }
        }

        public ConnectionState OpenConnection()
        {
            Connect.Open();
            return ConnectionState.Open;
        }

        public ConnectionState CloseConnection()
        {
            Connect.Close();
            return ConnectionState.Closed;
        }
    }

    public class SQLQuery
    {
        public string sql { get; set; }
        public List Param = new List();
    }

    public class SQLParam
    {
        public string ParamName { get; set; }
        public byte[] ParamValue { get; set; }
        public SqlDbType DbType { get; set; }
    }

    //public class Result
    //{
    //    public bool ResultState;
    //    public string SqlError;
    //    public DataTable Data;
    //}

}