
using System;
using System.Collections.Generic;
using System.Web;
using System.Collections;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using MySql.Data.MySqlClient;
using MySql.Data;
using CLS_BL;
using SUNWEB;
namespace CLS_DL
{
    public class MYSQLDB
    {
       
        public string strCon = ConfigurationManager.ConnectionStrings["ConMysql"].ConnectionString.ToString();
        #region ExecuteNonQuery
        public int ExecuteNonQuery(string sqlQuery)
        {
            BL_ErrorLog ObjLog = new BL_ErrorLog();
            MySqlCommand objCmd = new MySqlCommand();
            int count = 0;
            using (MySqlConnection con = new MySqlConnection(strCon))
            {
                con.Open();
                objCmd = new MySqlCommand(sqlQuery, con);
                try
                {
                    
                    count = Convert.ToInt16(objCmd.ExecuteNonQuery());
                }
                catch (Exception ex)
                {

                    ObjLog.Write(ex);

                }
                finally
                {
                    con.Close();
                    objCmd.Dispose();
                }


            }

            return count;


        }
        #endregion
        #region ExecuteDateTable
        public DataTable GetData_DT(string mysqlQuery)
        {
            BL_ErrorLog ObjLog = new BL_ErrorLog();
            DataTable objDT = new DataTable();
            MySqlDataAdapter objDA = new MySqlDataAdapter();
            objDT = new DataTable();


            using (MySqlConnection Cn = new MySqlConnection(strCon))
            {
                Cn.Open();
                objDA = new MySqlDataAdapter(mysqlQuery, Cn);
                try
                {
                    objDA.Fill(objDT);
                }
                catch (Exception ex)
                {
                    ObjLog.Write(ex);
                    Log.Error("Error:" ,ex);
                    Log.Info(mysqlQuery);
                }
                finally
                {
                    Cn.Close();
                    objDA.Dispose();
                }
            }

            return objDT;




        }
        #endregion
        #region ExecuteDateSet
        public DataSet GetData_DS(string mysqlQuery)
        {
            BL_ErrorLog ObjLog = new BL_ErrorLog();
            DataSet objDS = new DataSet();
            MySqlDataAdapter objDA = new MySqlDataAdapter();
            objDS = new DataSet();

            using (MySqlConnection Conn = new MySqlConnection(strCon))
            {
                Conn.Open();
                objDA = new MySqlDataAdapter(mysqlQuery, Conn);
                try
                {
                    objDA.Fill(objDS);
                }
                catch (Exception ex)
                {
                    ObjLog.Write(ex);
                }
                finally
                {
                    Conn.Close();
                    objDA.Dispose();
                }

            }

            return objDS;

        }
        #endregion
        #region ExecuteProcedure
        public int ExecuteProcedure(string spName, MySqlParameter[] param)
        {
            int count = 0;
            BL_ErrorLog ObjLog = new BL_ErrorLog();
            MySqlCommand objCmd = new MySqlCommand();
            using (MySqlConnection Cnn = new MySqlConnection(strCon))
            {
                Cnn.Open();
                objCmd = new MySqlCommand(spName, Cnn);
                objCmd.CommandType = CommandType.StoredProcedure;
                foreach (MySqlParameter para in param)
                {
                    objCmd.Parameters.Add(para);
                }
                try
                {
                    count = objCmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    ObjLog.Write(ex);
                }
                finally
                {
                    Cnn.Close();
                    objCmd.Dispose();
                }


            }


            return count;
        }
        #endregion
        #region ExecuteReader
        public MySqlDataReader ExecuteReader(string MYsqlQuery)
        {
            BL_ErrorLog ObjLog = new BL_ErrorLog();
            MySqlCommand objCmd = new MySqlCommand();
            SqlDataReader _objReader;
            string strData = string.Empty;

            using (MySqlConnection con = new MySqlConnection(strCon))
            {
                try
                {
                    con.Open();
                    objCmd = new MySqlCommand(MYsqlQuery, con);
                    objCmd.CommandTimeout = 0;
                }
                catch (Exception ex)
                {
                    con.Close();
                    ObjLog.Write(ex);
                }

            }

            return objCmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        #endregion
        #region ExecuteScalar
        public string ExecuteScalar(string MYsqlQuery)
        {
            BL_ErrorLog ObjLog = new BL_ErrorLog();
            string strData = string.Empty;
            MySqlCommand objCmd = new MySqlCommand();

            using (MySqlConnection conn = new MySqlConnection(strCon))
            {
                try
                {
                    conn.Open();
                    objCmd = new MySqlCommand(MYsqlQuery, conn);
                    objCmd.CommandTimeout = 0;
                    strData = Convert.ToString(objCmd.ExecuteScalar());
                }
                catch (Exception ex)
                {
                    ObjLog.Write(ex);
                    
                }
                finally
                {
                    conn.Close();
                    objCmd.Dispose();

                }

            }


            return strData;
        }
        #endregion


        #region Parameterized Approaches : Inorder to avoid the SQL Injections 
        /// <summary>
        ///  DataTable Parameterized Approach 
        /// </summary>
        /// <param name="mysqlQuery"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        /// 
        #region  DataTable 
        public DataTable NewGetData_DT(string mysqlQuery, MySqlParameter[] param)
        {
            BL_ErrorLog ObjLog = new BL_ErrorLog();
            DataTable objDT = new DataTable();
            MySqlDataAdapter objDA = new MySqlDataAdapter();
            MySqlCommand objCmd = new MySqlCommand();
            objDT = new DataTable();


            using (MySqlConnection Cn = new MySqlConnection(strCon))
            {
                Cn.Open();
                objCmd = new MySqlCommand(mysqlQuery, Cn);
                objDA = new MySqlDataAdapter();
                objCmd.CommandType = CommandType.Text;
                foreach (MySqlParameter para in param)
                {
                    objCmd.Parameters.Add(para);
                }

                objDA.SelectCommand = objCmd;
                try
                {
                    objDA.Fill(objDT);
                }
                catch (Exception ex)
                {
                    ObjLog.Write(ex);
                }
                finally
                {
                    Cn.Close();
                    objDA.Dispose();
                }
            }

            return objDT;




        }
        #endregion

        #region  DataSet 

        public DataSet NewGetData_DS(string mysqlQuery, MySqlParameter[] param)
        {
            BL_ErrorLog ObjLog = new BL_ErrorLog();
            DataSet objDS = new DataSet();
            MySqlCommand objCmd = new MySqlCommand();
            objCmd.CommandType = CommandType.Text;
            MySqlDataAdapter objDA = new MySqlDataAdapter();

            objDS = new DataSet();

            using (MySqlConnection Conn = new MySqlConnection(strCon))
            {
                Conn.Open();
                foreach (MySqlParameter para in param)
                {
                    objCmd.Parameters.Add(para);
                }
                objDA.SelectCommand = objCmd;

                try
                {
                    objDA.Fill(objDS);
                }
                catch (Exception ex)
                {
                    ObjLog.Write(ex);
                }
                finally
                {
                    Conn.Close();
                    objDA.Dispose();
                }

            }

            return objDS;

        }



        #endregion

        #region  Execute Scalar 

        /// <summary>
        ///  Executed Scalar with Parameterized Values
        /// </summary>
        /// <param name="MYsqlQuery"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public string NewExecuteScalar(string MYsqlQuery, MySqlParameter[] param)
        {
            BL_ErrorLog ObjLog = new BL_ErrorLog();
            string strData = string.Empty;
            MySqlCommand objCmd = new MySqlCommand();

            using (MySqlConnection conn = new MySqlConnection(strCon))
            {
                try
                {
                    conn.Open();
                    objCmd = new MySqlCommand(MYsqlQuery, conn);
                    objCmd.CommandTimeout = 0;


                    foreach (MySqlParameter para in param)
                    {
                        objCmd.Parameters.Add(para);
                    }


                    strData = Convert.ToString(objCmd.ExecuteScalar());
                }
                catch (Exception ex)
                {
                    ObjLog.Write(ex);
                }
                finally
                {
                    conn.Close();
                    objCmd.Dispose();

                }

            }


            return strData;
        }
        #endregion

        #region Execute QUERY : Insert / Update 
        public int ExecuteQuery(string Query, MySqlParameter[] param)
        {
            int count = 0;
            BL_ErrorLog ObjLog = new BL_ErrorLog();
            MySqlCommand objCmd = new MySqlCommand();
            using (MySqlConnection Cnn = new MySqlConnection(strCon))
            {
                Cnn.Open();
                objCmd = new MySqlCommand(Query, Cnn);
                objCmd.CommandType = CommandType.Text;
                foreach (MySqlParameter para in param)
                {
                    objCmd.Parameters.Add(para);
                }
                try
                {
                    count = objCmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    ObjLog.Write(ex);
                }
                finally
                {
                    Cnn.Close();
                    objCmd.Dispose();
                }


            }


            return count;
        }
        #endregion

        #endregion




    }
}