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

namespace SHIM911_LIB.DB
{
    public class SQLHelper
    {
        //string strCon = ConfigurationSettings.AppSettings["DSN"].ToString();
        //string strCon  = "Server=(local);database=poster;user id=sa;password=45850";
        string strCon = SHIM911_LIB.Config.ConfigData.getValue("ConnectionString").ToString();
        private SqlConnection con = null;

        public void Open()
        { con.Open(); }

        public void Close()
        { con.Close(); }

        public SqlConnection GetConnection()
        {
            if (con == null) { con = new SqlConnection(strCon); }
            return con;
        }

        public DataSet FillDataSet(string CommandText, CommandType CommandSort, string strAlias, SqlParameter[] Params)
        {
            SqlCommand Comm = new SqlCommand(CommandText, GetConnection());
            
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataSet ds = new DataSet();
            try
            {
                Comm.CommandType = CommandSort;
                if (Params != null)
                {
                    foreach (SqlParameter oEachParams in Params)
                    {
                        Comm.Parameters.Add(oEachParams);
                    }
                }
                adapter.SelectCommand = Comm;
                adapter.Fill(ds, strAlias);

                Comm.Parameters.Clear();
                return ds;
            }
            catch
            {
                return null;
            }
        }

        public DataSet FillDataSet(string CommandText, CommandType CommandSort, DataSet dsDataSet, string strAlias, SqlParameter[] Params)
        {
            SqlCommand Comm = new SqlCommand(CommandText, GetConnection());
            SqlDataAdapter adapter = new SqlDataAdapter();
            try
            {
                Comm.CommandType = CommandSort;
                if (Params != null)
                {
                    foreach (SqlParameter oEachParams in Params)
                    {
                        Comm.Parameters.Add(oEachParams);
                    }
                }
                adapter.SelectCommand = Comm;
                adapter.Fill(dsDataSet, strAlias);

                Comm.Parameters.Clear();
                return dsDataSet;
            }
            catch
            {
                return null;
            }
        }

        public int ExecuteSpNonQuery(string CommandText, CommandType CommandSort, SqlParameter[] Params)
        {
            SqlCommand Comm = new SqlCommand(CommandText, GetConnection());
            int intRet;
            //			try
            //			{
            Comm.CommandType = CommandSort;
            if (Params != null)
            {
                foreach (SqlParameter oEachParams in Params)
                {
                    Comm.Parameters.Add(oEachParams);
                }
            }

            Comm.Connection.Open();
            intRet = Comm.ExecuteNonQuery();

            if (Comm.Connection.State != ConnectionState.Closed)
            {
                Comm.Connection.Close();
            }

            Comm.Parameters.Clear();
            return intRet;
            //			}
            //			catch
            //			{				
            //				return 0;
            //			}
        }

        public SqlDataReader ReturnDataReader(string CommandText, CommandType CommandSort, SqlParameter[] Params)
        {
            SqlCommand Comm = new SqlCommand(CommandText, GetConnection());
            SqlDataReader reader;
            //			try
            //			{
            Comm.CommandType = CommandSort;
            if (Params != null)
            {
                foreach (SqlParameter oEachParams in Params)
                {
                    Comm.Parameters.Add(oEachParams);
                }
            }
            Comm.Connection.Open();
            //DataReader °´Ã¼¸¸ close Çϸé ÀÌ¿¡ ÇØ´çÇÏ´Â Connection Àº ÀÚµ¿À¸·Î ´ÝÈù´Ù
            reader = Comm.ExecuteReader(CommandBehavior.CloseConnection);

            return reader;
            //			}
            //			catch
            //			{
            //				return null;
            //			}
        }

        public object ReturnScalarValue(string CommandText, CommandType CommandSort, SqlParameter[] Params)
        {
            SqlCommand Comm = new SqlCommand(CommandText, GetConnection());
            object objRet;
            try
            {
                Comm.CommandType = CommandSort;
                if (Params != null)
                {
                    foreach (SqlParameter oEachParams in Params)
                    {
                        Comm.Parameters.Add(oEachParams);
                    }
                }
                Comm.Connection.Open();
                objRet = Comm.ExecuteScalar();

                if (Comm.Connection.State != ConnectionState.Closed)
                {
                    Comm.Connection.Close();
                }

                return objRet;
            }
            catch
            {
                return null;
            }
        }
    }
}