// http://svc.luckstar.com.tw/CodeHelper/cs/index.html
// 2017-04-11, honda@luckstar.com.tw, Create for demo dispose template. e.g. Implement CSqlClient usage.

// Please ref to: 
// CSqlClient.cs.txt, 
// CDBPubs.cs.txt, 
// CDBNorthwind.cs.txt, 
// CRunSqlClient.cs.tx 
// and Dispose-Template.txt

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
// add
using System.Data;
using System.Data.SqlClient;


namespace ConsoleBase
{
    public class CSqlClient : IDisposable
    {
        SqlConnection mConnection;
        SqlTransaction mTransaction;
        Boolean mbBeginTransaction;
        public string msConnection
        {
            get { return mConnection.ConnectionString; }
            protected set { mConnection.ConnectionString = value; }
        }

        protected CSqlClient()
        {
            CreateConnection(string.Empty);
        }
        public CSqlClient(string sConnection)
        {
            CreateConnection(sConnection);
        }
        ~CSqlClient()
        {
            Dispose(false);
        }
        protected void CreateConnection(string sConnection)
        {
            mConnection = new SqlConnection(sConnection);
            mbBeginTransaction = false;
            mbDisposed = false;
        }
        public DataSet GetDataSet(string sCmd)
        {
            return GetDataSet(sCmd, string.Empty);
        }
        public DataSet GetDataSet(string sCmd, string sSPExecName)
        {
            DataSet ds1 = new DataSet();
            SqlCommand cmd1 = new SqlCommand();
            SqlDataAdapter adapter1;
            string sMySQLCmd;
            if (mConnection.State == ConnectionState.Closed)
                mConnection.Open();

            if (string.IsNullOrEmpty(sSPExecName))
                sMySQLCmd = sCmd;
            else
                sMySQLCmd = "dbo.sp_EXEC '" + sSPExecName + "', N'" + sCmd.Replace("'", "''") + "'";

            cmd1.CommandTimeout = 0;
            cmd1.CommandText = sMySQLCmd;
            cmd1.Connection = mConnection;
            cmd1.Transaction = mTransaction;

            adapter1 = new SqlDataAdapter(cmd1);
            adapter1.Fill(ds1);
            if (!mbBeginTransaction)
                if (mConnection.State == ConnectionState.Open)
                    mConnection.Close();

            return ds1;
        }

        public DataTable GetDataTable(string sCmd)
        {
            return GetDataTable(sCmd, string.Empty);
        }
        public DataTable GetDataTable(string sCmd, string sSPExecName)
        {
            DataSet ds1 = GetDataSet(sCmd, sSPExecName);
            if (ds1 == null)
                return null;

            return ds1.Tables[0];
            // sample loop:
            //if (dt1.Rows.Count < 1)
            //    throw new Exception(string.Format("Rows.Count<1. {0}.", sCmd));
            //for (int i = 0; i < dt1.Rows.Count; i++)
            //{
            //    string sField;
            //    sField = dt1.Rows[i][0].ToString();
            //    sField = dt1.Rows[i]["name"].ToString();
            //}
        }
        public int ExecuteNonQuery(string sCmd)
        {
            return ExecuteNonQuery(sCmd, string.Empty);
        }

        public int ExecuteNonQuery(string sCmd, string sSPExecName)
        {
            int iAffect;
            DataSet ds1 = new DataSet();
            SqlCommand cmd1 = new SqlCommand();
            string sMySQLCmd;
            if (mConnection.State == ConnectionState.Closed)
                mConnection.Open();

            if (string.IsNullOrEmpty(sSPExecName))
                sMySQLCmd = sCmd;
            else
                sMySQLCmd = "dbo.sp_EXEC '" + sSPExecName + "', N'" + sCmd.Replace("'", "''") + "'";

            cmd1.CommandTimeout = 0;
            cmd1.CommandText = sMySQLCmd;
            cmd1.Connection = mConnection;
            cmd1.Transaction = mTransaction;

            iAffect = cmd1.ExecuteNonQuery();
            if (!mbBeginTransaction)
                if (mConnection.State == ConnectionState.Open)
                    mConnection.Close();

            return iAffect;
        }

        public int ExecuteNonQuery(string sCmd, SqlParameter[] para1)
        {
            int iAffect;
            DataSet ds1 = new DataSet();
            SqlCommand cmd1 = new SqlCommand();

            if (mConnection.State == ConnectionState.Closed)
                mConnection.Open();

            if (para1 != null)
            {
                SqlParameter paraNew;

                foreach (SqlParameter p1 in para1)
                {
                    paraNew = new SqlParameter(p1.ParameterName, p1.SqlDbType, p1.Size);
                    if (p1.Value.Equals(""))
                    {
                        paraNew.Value = System.DBNull.Value;
                    }
                    else
                    {
                        paraNew.Value = p1.Value;
                    }
                    cmd1.Parameters.Add(paraNew);
                }
            }
            cmd1.CommandTimeout = 0;
            cmd1.CommandText = sCmd;
            cmd1.Connection = mConnection;
            cmd1.Transaction = mTransaction;

            iAffect = cmd1.ExecuteNonQuery();
            if (!mbBeginTransaction)
                if (mConnection.State == ConnectionState.Open)
                    mConnection.Close();

            return iAffect;
        }
        public Object ExecuteScalar(string sCmd, string sSPExecName)
        {
            Object oResult;
            SqlCommand cmd1 = new SqlCommand();
            string sMySQLCmd;

            if (mConnection.State == ConnectionState.Closed)
                mConnection.Open();

            if (string.IsNullOrEmpty(sSPExecName))
                sMySQLCmd = sCmd;
            else
                sMySQLCmd = "dbo.sp_EXEC '" + sSPExecName + "', N'" + sCmd.Replace("'", "''") + "'";

            cmd1.CommandTimeout = 0;
            cmd1.CommandText = sMySQLCmd;
            cmd1.Connection = mConnection;
            cmd1.Transaction = mTransaction;

            oResult = cmd1.ExecuteScalar();
            if (!mbBeginTransaction)
                if (mConnection.State == ConnectionState.Open)
                    mConnection.Close();

            return oResult;
        }
        public SqlDataReader ExecuteReader(string sCmd)
        {
            return ExecuteReader(sCmd, string.Empty);
        }
        public SqlDataReader ExecuteReader(string sCmd, string sSPExecName)
        {
            SqlDataReader reader1;
            SqlCommand cmd1 = new SqlCommand();
            string sMySQLCmd;
            if (mConnection.State == ConnectionState.Closed)
                mConnection.Open();

            if (string.IsNullOrEmpty(sSPExecName))
                sMySQLCmd = sCmd;
            else
                sMySQLCmd = "dbo.sp_EXEC '" + sSPExecName + "', N'" + sCmd.Replace("'", "''") + "'";

            cmd1.CommandTimeout = 0;
            cmd1.CommandText = sMySQLCmd;
            cmd1.Connection = mConnection;
            cmd1.Transaction = mTransaction;

            reader1 = cmd1.ExecuteReader();
            // sample read and close.
            //while (reader1.Read())
            //{
            //    string sField;
            //    sField = reader1[0].ToString();
            //    sField = reader1["name"].ToString();
            //}
            //reader1.Close();
            return reader1;
        }


        public void BeginTransaction()
        {
            if (mConnection.State == ConnectionState.Closed)
                mConnection.Open();

            mTransaction = mConnection.BeginTransaction();
            mbBeginTransaction = true;
        }
        public void Commit()
        {
            if (mTransaction != null)
                mTransaction.Commit();

            mbBeginTransaction = false;
            if (mConnection.State == ConnectionState.Open)
                mConnection.Close();
        }

        public void Rollback()
        {
            if (mTransaction != null)
                mTransaction.Rollback();

            mbBeginTransaction = false;
            if (mConnection.State == ConnectionState.Open)
                mConnection.Close();
        }


        #region Dispose Part

        Boolean mbDisposed = false;
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!mbDisposed)
            {
                if (disposing)
                {
                    // Release managed resources here.
                    if (mTransaction != null)
                    {
                        if (mbBeginTransaction)
                        {
                            mTransaction.Rollback();
                            mbBeginTransaction = false;
                        }
                    }
                    if (mConnection != null)
                    {
                        if (mConnection.State == ConnectionState.Open)
                            mConnection.Close();

                        mConnection.Dispose();
                    }
                }
                // Release unmanaged resources here.
                // Set large fields to null.
                // Call Dispose on your base class.
                mConnection = null;
                mbDisposed = true;
                //base.Disposed(disposing);
            }
        }
        #endregion
    }
}