Database

SELECT S.szState, SUM(O.CAP_OFFLIN) AS fOffline
FROM tblStates AS S LEFT OUTER JOIN tblOutages AS O ON S.szState = O.UNIT_STATE
WHERE NOT(O.TA_START > '11/23/2011') AND NOT(O.TA_END < '11/23/2011')
GROUP BY S.szState

public class MConnection : IDisposable
    {
        #region IDisposable Members
        public void Dispose()
        {
            this.Close();
            if (this.m_ClientConnection != null)
                this.m_ClientConnection.Dispose();
            GC.SuppressFinalize(this);
        }
        #endregion

        #region --- Member ---
        IMConnection m_ClientConnection;
        internal IMConnection ClientConnection
        {
            get { return m_ClientConnection; }
        }

        private MClientProvider m_ClientProvider;
        public MClientProvider ClientProvider
        {
            get { return m_ClientProvider; }
        }

        private string m_ConnectionString;
        public string ConnectionString
        {
            get { return m_ConnectionString; }
        }

        private System.Data.ConnectionState m_State;
        public System.Data.ConnectionState State
        {
            get { return m_State; }
        }

        public System.ComponentModel.ISite Site
        {
            get
            {
                return m_ClientConnection.Site;
            }
            set
            {
                m_ClientConnection.Site = value;
            }
        }

        public string ServerVersion
        {
            get
            {
                return m_ClientConnection.ServerVersion;
            }
        }

        public string Database
        {
            get
            {
                return m_ClientConnection.Database;
            }
        }

        public string DataSource
        {
            get
            {
                return m_ClientConnection.DataSource;
            }
        }
        #endregion

        #region --- Constructor ---
        public MConnection(MClientProvider clientProvider)
        {
            this.m_ClientProvider = clientProvider;
            switch (this.m_ClientProvider)
            {
                case MClientProvider.MSSQL:
                    this.m_ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["mssqlString"].ConnectionString;
                    this.m_ClientConnection = new MSSQLConnection(this.m_ConnectionString, this.m_ClientProvider);
                    break;
                case MClientProvider.ORACLE:
                    this.m_ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["oracleString"].ConnectionString;
                    this.m_ClientConnection = new ORACLEConnection(this.m_ConnectionString, this.m_ClientProvider);
                    break;
                case MClientProvider.MySQL:
                    this.m_ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["mysqlString"].ConnectionString;
                    this.m_ClientConnection = new MySQLConnection(this.m_ConnectionString, this.m_ClientProvider);
                    break;
                case MClientProvider.OleDb:
                    this.m_ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["oledbString"].ConnectionString;
                    this.m_ClientConnection = new OLEDBConnection(this.m_ConnectionString, this.m_ClientProvider);
                    break;
            }
        }
        public MConnection(MClientProvider clientProvider, string connectionString)
        {
            this.m_ConnectionString = connectionString;
            this.m_ClientProvider = clientProvider;
            switch (this.m_ClientProvider)
            {
                case MClientProvider.MSSQL:
                    this.m_ClientConnection = new MSSQLConnection(this.m_ConnectionString, this.m_ClientProvider);
                    break;
                case MClientProvider.ORACLE:
                    this.m_ClientConnection = new ORACLEConnection(this.m_ConnectionString, this.m_ClientProvider);
                    break;
                case MClientProvider.MySQL:
                    this.m_ClientConnection = new MySQLConnection(this.m_ConnectionString, this.m_ClientProvider);
                    break;
                case MClientProvider.OleDb:
                    this.m_ClientConnection = new OLEDBConnection(this.m_ConnectionString, this.m_ClientProvider);
                    break;
            }
        }
        #endregion

        #region --- Methods ---
        public bool Open()
        {
            try
            {
                if (this.m_ClientConnection != null)
                {
                    this.m_ClientConnection.Open();
                    this.m_State = this.m_ClientConnection.State;
                    return this.m_State.Equals(System.Data.ConnectionState.Open);
                }
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Close()
        {
            try
            {
                if (this.m_ClientConnection != null)
                {
                    this.m_ClientConnection.Close();
                    this.m_State = System.Data.ConnectionState.Closed;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void BeginTransaction()
        {
            this.m_ClientConnection.BeginTransaction();
        }

        public void BeginTransaction(System.Data.IsolationLevel iso)
        {
            this.m_ClientConnection.BeginTransaction(iso);
        }

        public System.Runtime.Remoting.ObjRef CreateObjRef(Type requestedType)
        {
            return this.m_ClientConnection.CreateObjRef(requestedType);
        }

        public new object GetHashCode()
        {
            return m_ClientConnection.GetHashCode();
        }

        public object InitializeLifetimeService()
        {
            return this.m_ClientConnection.InitializeLifetimeService();
        }

        public object GetLifetimeService()
        {
            return this.m_ClientConnection.GetLifetimeService();
        }

        public void ChangeDatabase(string database)
        {
            this.m_ClientConnection.ChangeDatabase(database);
        }

        public System.Data.DataTable GetSchema()
        {
            return this.m_ClientConnection.GetSchema();
        }

        public System.Data.DataTable GetSchema(string collectionName)
        {
            return this.m_ClientConnection.GetSchema(collectionName);
        }

        public System.Data.DataTable GetSchema(string collectionName, string[] restrictionValues)
        {
            return this.m_ClientConnection.GetSchema(collectionName, restrictionValues);
        }

        public override bool Equals(object obj)
        {
            return this.Equals(obj);
        }

        public override string ToString()
        {
            return m_ClientConnection.ToString();
        }
        #endregion
    }

public class MCommand : IDisposable
    {
        #region IDisposable Members
        public void Dispose()
        {
            if (this.m_ClientCommand != null)
                this.m_ClientCommand.Dispose();
            GC.SuppressFinalize(this);
        }
        #endregion

        #region --- Member ---
        IMCommand m_ClientCommand;
        internal IMCommand ClientCommand
        {
            get { return m_ClientCommand; }
        }

        string m_CommandText;
        public string CommandText
        {
            get
            {
                return m_CommandText;
            }
            set
            {
                m_CommandText = value;
            }
        }

        int m_CommandTimeout = 500;
        public int CommandTimeout
        {
            get
            {
                return m_CommandTimeout;
            }
            set
            {
                m_CommandTimeout = value;
            }
        }

        System.Data.CommandType m_CommandType = System.Data.CommandType.Text;
        public System.Data.CommandType CommandType
        {
            get
            {
                return m_CommandType;
            }
            set
            {
                m_CommandType = value;
            }
        }

        MConnection m_Connection;
        public MConnection Connection
        {
            get
            {
                return m_Connection;
            }
            set
            {
                m_Connection = value;
            }
        }

        private MParameterCollection m_Parameters;
        public MParameterCollection Parameters
        {
            get { return m_Parameters; }
            set { m_Parameters = value; }
        }

        private string m_ParamPrefix;
        public string ParamPrefix
        {
            get { return m_ParamPrefix; }
            set { m_ParamPrefix = value; }
        }

        public System.ComponentModel.IContainer Container
        {
            get
            {
                return m_ClientCommand.Container;
            }
        }

        public System.ComponentModel.ISite Site
        {
            get
            {
                return m_ClientCommand.Site;
            }
            set
            {
                m_ClientCommand.Site = value;
            }
        }

        public System.Data.UpdateRowSource UpdatedRowSource
        {
            get
            {
                return m_ClientCommand.UpdatedRowSource;
            }
            set
            {
                m_ClientCommand.UpdatedRowSource = value;
            }
        }
        #endregion

        #region --- Private Constructor ---
        internal MCommand(object command, MConnection connection)
        {
            try
            {
                this.m_Connection = connection;
                switch (connection.ClientProvider)
                {
                    case MClientProvider.MSSQL:
                        this.m_ClientCommand = new MSSQLCommand(command as System.Data.SqlClient.SqlCommand);
                        break;
                    case MClientProvider.ORACLE:
                        this.m_ClientCommand = new ORACLECommand(command as System.Data.OracleClient.OracleCommand);
                        break;
                    case MClientProvider.MySQL:
                        break;
                    case MClientProvider.OleDb:
                        this.m_ClientCommand = new OLEDBCommand(command as System.Data.OleDb.OleDbCommand);
                        break;
                }
                this.m_ClientCommand.Connection = connection;
                this.m_CommandText = this.m_ClientCommand.CommandText;
                this.m_CommandType = this.m_ClientCommand.CommandType;
                this.m_CommandTimeout = this.m_ClientCommand.CommandTimeout;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        public MCommand()
        {
            this.m_Parameters = new MParameterCollection();
        }

        public MCommand(string commandText, MConnection connection)
        {
            this.m_CommandText = commandText;
            this.m_Connection = connection;
            this.m_Parameters = new MParameterCollection();
        }

        public MCommand(System.Data.CommandType commandType, string commandText, MConnection connection)
        {
            this.m_CommandType = commandType;
            this.m_CommandText = commandText;
            this.m_Connection = connection;
            this.m_Parameters = new MParameterCollection();
        }

        #region --- Private ---
        internal void CreateCommand()
        {
            try
            {
                switch (this.Connection.ClientProvider)
                {
                    case MClientProvider.MSSQL:
                        if (string.IsNullOrEmpty(m_ParamPrefix)) m_ParamPrefix = "@";
                        switch (this.CommandType)
                        {
                            case System.Data.CommandType.Text:
                                this.m_CommandText = this.m_CommandText.Replace(":", m_ParamPrefix).Replace("?", m_ParamPrefix);
                                break;
                        }
                        this.m_ClientCommand = new MSSQLCommand(this.m_CommandType, this.m_CommandText, this.m_CommandTimeout, this.m_Connection);
                        break;
                    case MClientProvider.ORACLE:
                        if (string.IsNullOrEmpty(m_ParamPrefix)) m_ParamPrefix = ":";
                        switch (this.CommandType)
                        {
                            case System.Data.CommandType.Text:
                                this.m_CommandText = this.m_CommandText.Replace("@", m_ParamPrefix).Replace("?", m_ParamPrefix);
                                break;
                        }
                        this.m_ClientCommand = new ORACLECommand(this.m_CommandType, this.m_CommandText, this.m_CommandTimeout, this.m_Connection);
                        break;
                    case MClientProvider.MySQL:
                        if (string.IsNullOrEmpty(m_ParamPrefix)) m_ParamPrefix = "?";
                        switch (this.CommandType)
                        {
                            case System.Data.CommandType.Text:
                                this.m_CommandText = this.m_CommandText.Replace("@", m_ParamPrefix).Replace(":", m_ParamPrefix);
                                break;
                        }
                        this.m_ClientCommand = new MySQLCommand(this.m_CommandType, this.m_CommandText, this.m_CommandTimeout, this.m_Connection);
                        break;
                    case MClientProvider.OleDb:
                        if (string.IsNullOrEmpty(m_ParamPrefix)) m_ParamPrefix = "@";
                        switch (this.CommandType)
                        {
                            case System.Data.CommandType.Text:
                                this.m_CommandText = this.m_CommandText.Replace(":", m_ParamPrefix).Replace("?", m_ParamPrefix);
                                break;
                        }
                        this.m_ClientCommand = new OLEDBCommand(this.m_CommandType, this.m_CommandText, this.m_CommandTimeout, this.m_Connection);
                        break;
                }
                if (this.m_Parameters != null)
                    foreach (MParameter item in this.m_Parameters)
                        CreateParameter(item.Name, item.Value, item.DbType, item.Direction);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        void CreateParameter(string name, object value, MSqlDbType dbtype, System.Data.ParameterDirection direction)
        {
            try
            {
                switch (this.Connection.ClientProvider)
                {
                    case MClientProvider.MSSQL:
                        System.Data.SqlDbType getSqlDbType = MConvert.SqlDbType(dbtype);
                        switch (getSqlDbType)
                        {
                            case System.Data.SqlDbType.Variant:
                                ((System.Data.SqlClient.SqlCommand)this.m_ClientCommand.CommandObject).Parameters.AddWithValue(m_ParamPrefix + name, (value != null) ? value : DBNull.Value).Direction = direction;
                                break;
                            default:
                                System.Data.SqlClient.SqlParameter mssqlParam = ((System.Data.SqlClient.SqlCommand)this.m_ClientCommand.CommandObject).CreateParameter();
                                mssqlParam.ParameterName = m_ParamPrefix + name;
                                if (value != null)
                                    mssqlParam.Value = value;
                                else
                                    mssqlParam.Value = DBNull.Value;
                                mssqlParam.SqlDbType = getSqlDbType;
                                mssqlParam.Direction = direction;
                                ((System.Data.SqlClient.SqlCommand)this.m_ClientCommand.CommandObject).Parameters.Add(mssqlParam);
                                break;
                        }
                        break;
                    case MClientProvider.ORACLE:
                        System.Data.OracleClient.OracleType getOracleType = MConvert.OracleDbType(dbtype);
                        switch (getOracleType)
                        {
                            case System.Data.OracleClient.OracleType.Blob:
                                ((System.Data.OracleClient.OracleCommand)this.m_ClientCommand.CommandObject).Parameters.AddWithValue(m_ParamPrefix + name, (value != null) ? value : DBNull.Value).Direction = direction;
                                break;
                            default:
                                System.Data.OracleClient.OracleParameter oracleParam = ((System.Data.OracleClient.OracleCommand)this.m_ClientCommand.CommandObject).CreateParameter();
                                oracleParam.ParameterName = m_ParamPrefix + name;
                                if (value != null)
                                    oracleParam.Value = value;
                                else
                                    oracleParam.Value = DBNull.Value;
                                oracleParam.OracleType = getOracleType;
                                oracleParam.Direction = direction;
                                ((System.Data.OracleClient.OracleCommand)this.m_ClientCommand.CommandObject).Parameters.Add(oracleParam);
                                break;
                        }
                        break;
                    case MClientProvider.MySQL:
                        MySql.Data.MySqlClient.MySqlDbType getMySqlDbType = MConvert.MySqlType(dbtype);
                        switch (getMySqlDbType)
                        {
                            case MySql.Data.MySqlClient.MySqlDbType.Blob:
                                ((MySql.Data.MySqlClient.MySqlCommand)this.m_ClientCommand.CommandObject).Parameters.AddWithValue(m_ParamPrefix + name, (value != null) ? value : DBNull.Value).Direction = direction;
                                break;
                            default:
                                MySql.Data.MySqlClient.MySqlParameter mysqlParam = ((MySql.Data.MySqlClient.MySqlCommand)this.m_ClientCommand.CommandObject).CreateParameter();
                                mysqlParam.ParameterName = m_ParamPrefix + name;
                                if (value != null)
                                    mysqlParam.Value = value;
                                else
                                    mysqlParam.Value = DBNull.Value;
                                mysqlParam.MySqlDbType = getMySqlDbType;
                                mysqlParam.Direction = direction;
                                ((MySql.Data.MySqlClient.MySqlCommand)this.m_ClientCommand.CommandObject).Parameters.Add(mysqlParam);
                                break;
                        }
                        break;
                    case MClientProvider.OleDb:
                        switch (dbtype)
                        {
                            case MSqlDbType.Date:
                            case MSqlDbType.DateTime:
                            case MSqlDbType.DateTime2:
                            case MSqlDbType.SmallDateTime:
                                value = Convert.ToDateTime(value).ToString("yyyy/MM/dd hh:mm:ss");
                                break;
                        }
                        System.Data.OleDb.OleDbType getOleDbType = MConvert.OleDBType(dbtype);
                        switch (getOleDbType)
                        {
                            case System.Data.OleDb.OleDbType.Empty:
                                ((System.Data.OleDb.OleDbCommand)this.m_ClientCommand.CommandObject).Parameters.AddWithValue(m_ParamPrefix + name, (value != null) ? value : DBNull.Value).Direction = direction;
                                break;
                            default:
                                System.Data.OleDb.OleDbParameter oledbParam = ((System.Data.OleDb.OleDbCommand)this.m_ClientCommand.CommandObject).CreateParameter();
                                oledbParam.ParameterName = m_ParamPrefix + name;
                                if (value != null)
                                    oledbParam.Value = value;
                                else
                                    oledbParam.Value = DBNull.Value;
                                oledbParam.OleDbType = getOleDbType;
                                oledbParam.Direction = direction;
                                ((System.Data.OleDb.OleDbCommand)this.m_ClientCommand.CommandObject).Parameters.Add(oledbParam);
                                break;
                        }
                        break;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region --- Methods ---
        public int ExecuteNonQuery()
        {
            CreateCommand();
            return m_ClientCommand.ExecuteNonQuery();
        }

        public object ExecuteScalar()
        {
            CreateCommand();
            return m_ClientCommand.ExecuteScalar();
        }

        public MDataAdapter ExecuteAdapter()
        {
            CreateCommand();
            return new MDataAdapter(this);
        }

        public System.Data.IDataReader ExecuteReader()
        {
            CreateCommand();
            return m_ClientCommand.ExecuteReader();
        }

        public System.Data.IDataReader ExecuteReader(System.Data.CommandBehavior commandBehavior)
        {
            CreateCommand();
            return m_ClientCommand.ExecuteReader();
        }

        public System.Runtime.Remoting.ObjRef CreateObjRef(Type requestType)
        {
            return m_ClientCommand.CreateObjRef(requestType);
        }

        public new object GetHashCode()
        {
            return m_ClientCommand.GetHashCode();
        }

        public object GetLifetimeService()
        {
            return m_ClientCommand.GetLifetimeService();
        }

        public object InitializeLifetimeService()
        {
            return m_ClientCommand.InitializeLifetimeService();
        }

        public void Prepare()
        {
            m_ClientCommand.Prepare();
        }

        public void ResetCommandTimeout()
        {
            m_ClientCommand.ResetCommandTimeout();
        }

        public void Cancel()
        {
            m_ClientCommand.Cancel();
        }

        public object Clone()
        {
            return m_ClientCommand.Clone();
        }
        #endregion
    }