.NET Installer that automatically installs MSDE

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

namespace JohnKenedy.DataAccess
{
    public class DataAccessTableDefinition
    {
        #region Properties
        private DataAccessLayer _DALLayer = null;
        public DataAccessLayer DALLayer
        {
            get
            {
                return _DALLayer;
            }
        }

        private string _TableName = "";
        public string TableName
        {
            get
            {
                return _TableName;
            }
        }

        private DataTable _ColumnDefinition = null;
        public DataTable ColumnDefinition
        {
            get
            {
                return _ColumnDefinition;
            }
        }

        private List<string> _PrimaryKeyColumns = new List<string>();
        public List<string> PrimaryKeyColumns
        {
            get
            {
                return _PrimaryKeyColumns;
            }
        }

        private List<string> _IdentityColumns = new List<string>();
        public List<string> IdentityColumns
        {
            get
            {
                return _IdentityColumns;
            }
        }

        private List<string> _NullAbleColumns = new List<string>();
        public List<string> NullAbleColumns
        {
            get
            {
                return _NullAbleColumns;
            }
        }
        #endregion

        public DataAccessTableDefinition(DataAccessLayer _dALLayer, string _tableName)
        {
            _DALLayer = _dALLayer;
            _TableName = _tableName;
            GetColumnDefinition();
        }

        private void GetColumnDefinition()
        {
            string _sql = "";
            if (_DALLayer.ConnectionType == SqlServerType.MSSQL)
            {
                 _sql = @"select a.COLUMN_NAME, a.DATA_TYPE, a.CHARACTER_MAXIMUM_LENGTH, b.IsPrimaryKey, a.IsIdentity, CASE WHEN a.IS_NULLABLE = 'YES' THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS IsNullAble
                            FROM
                            (
                                select *, COLUMNPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') AS IsIdentity from information_schema.columns where table_name = '{0}'
                            ) a
                            LEFT JOIN
                            (
                                select a.column_name, CAST(1 AS BIT) as ISPrimaryKey from information_schema.key_column_usage a
                                    left join information_schema.table_constraints b
                                    on a.constraint_name = b.constraint_name AND a.table_name = b.table_name
                                    where a.table_name = '{0}' and b.constraint_type = 'PRIMARY KEY'
                            ) b
                            on a.column_name = b.column_name
                            ORDER BY a.Ordinal_Position";
            
                _sql = string.Format(_sql, _TableName);
                _ColumnDefinition = _DALLayer.ExecuteDataSetText(_sql).Tables[0];
            }
            else if (_DALLayer.ConnectionType == SqlServerType.MYSQL)
            {
                _sql = @"select a.COLUMN_NAME, a.DATA_TYPE, a.CHARACTER_MAXIMUM_LENGTH, b.IsPrimaryKey, a.IsIdentity, CASE WHEN a.IS_NULLABLE = 'YES' THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS IsNullAble
                            FROM
                            (
                                select *, COLUMNPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') AS IsIdentity from information_schema.columns where table_name = '{0}'
                            ) a
                            LEFT JOIN
                            (
                                select a.column_name, CAST(1 AS BIT) as ISPrimaryKey from information_schema.key_column_usage a
                                    left join information_schema.table_constraints b
                                    on a.constraint_name = b.constraint_name AND a.table_name = b.table_name
                                    where a.table_name = '{0}' and b.constraint_type = 'PRIMARY KEY'
                            ) b
                            on a.column_name = b.column_name
                            ORDER BY a.Ordinal_Position";

                _sql = string.Format(_sql, _TableName);
                _ColumnDefinition = _DALLayer.ExecuteDataSetText(_sql).Tables[0];
            }
            else if (_DALLayer.ConnectionType == SqlServerType.OLEDB)
            {
                _sql = @"select a.COLUMN_NAME, a.DATA_TYPE, a.CHARACTER_MAXIMUM_LENGTH, b.IsPrimaryKey, a.IsIdentity, CASE WHEN a.IS_NULLABLE = 'YES' THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS IsNullAble
                            FROM
                            (
                                select *, COLUMNPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), COLUMN_NAME, 'IsIdentity') AS IsIdentity from information_schema.columns where table_name = '{0}'
                            ) a
                            LEFT JOIN
                            (
                                select a.column_name, CAST(1 AS BIT) as ISPrimaryKey from information_schema.key_column_usage a
                                    left join information_schema.table_constraints b
                                    on a.constraint_name = b.constraint_name AND a.table_name = b.table_name
                                    where a.table_name = '{0}' and b.constraint_type = 'PRIMARY KEY'
                            ) b
                            on a.column_name = b.column_name
                            ORDER BY a.Ordinal_Position";

                _sql = string.Format(_sql, _TableName);
                _ColumnDefinition = _DALLayer.ExecuteDataSetText(_sql).Tables[0];
            }
            else if (_DALLayer.ConnectionType == SqlServerType.ORACLE)
            {
            }

            #region Get All PrimaryKey/Identity Columns
            _PrimaryKeyColumns.Clear();
            foreach (DataRow _row in _ColumnDefinition.Rows)
            {
                if (_row["IsPrimaryKey"].ToString() == "1" || _row["IsPrimaryKey"].ToString().ToLower() == "true")
                {
                    _PrimaryKeyColumns.Add(_row["Column_Name"].ToString().ToLower());
                }
                if (_row["IsIdentity"].ToString() == "1" || _row["IsIdentity"].ToString().ToLower() == "true")
                {
                    _IdentityColumns.Add(_row["Column_Name"].ToString().ToLower());
                }
                if (_row["IsNullAble"].ToString() == "1" || _row["IsNullAble"].ToString().ToLower() == "true")
                {
                    _NullAbleColumns.Add(_row["Column_Name"].ToString().ToLower());
                }
            }
            #endregion
        }

        #region Is PrimaryKey/Identity/Exist
        private bool IsColumnPrimaryKey(string _columnName)
        {
            DataRow[] _rows = _ColumnDefinition.Select("COLUMN_NAME = '" + _columnName + "'");
            if (_rows == null || _rows.Length <= 0) return false;
            if (_rows[0]["IsPrimaryKey"].ToString() == "1") return true;
            return false;
        }

        private bool IsColumnIdentity(string _columnName)
        {
            DataRow[] _rows = _ColumnDefinition.Select("COLUMN_NAME = '" + _columnName + "'");
            if (_rows == null || _rows.Length <= 0) return false;
            if (_rows[0]["IsIdentity"].ToString() == "1") return true;
            return false;
        }

        private bool IsColumnExist(string _columnName)
        {
            DataRow[] _rows = _ColumnDefinition.Select("COLUMN_NAME = '" + _columnName + "'");
            if (_rows == null || _rows.Length <= 0) return false;
            return true;
        }

        public int GetStringColumnMaxLength(string _columnName)
        {
            DataRow[] _rows = _ColumnDefinition.Select("COLUMN_NAME = '" + _columnName + "'");
            if (_rows == null || _rows.Length <= 0) return 0;
            return DataAccessTableManager.IntParse(_rows[0]["CHARACTER_MAXIMUM_LENGTH"]);
        }
        #endregion

        #region Insert/Update/Select
        // INSERT/UPDATE
        public DataAccessTableFiller GetFiller()
        {
            DataAccessTableFiller _new = new DataAccessTableFiller(this);
            return _new;
        }

        // SELECT All
        public IDbCommand GetDataAll(string _sort)
        {
            IDbCommand _com = _DALLayer.GetCommand();
            _com.CommandType = CommandType.Text;

            _com.CommandText = "SELECT * FROM " + _TableName;

            if (_sort != null && _sort != "")
            {
                _com.CommandText = " ORDER BY " + _sort;
            }
            return _com;
        }

        // SELECT COMMAND
        public IDbCommand GetDataByWhere(IList<string> _selectColumn, string _where, IDbDataParameter[] _params, string _sort)
        {
            IDbCommand _com = _DALLayer.GetCommand();
            _com.CommandType = CommandType.Text;

            string _columns = "*";
            if (_selectColumn != null && _selectColumn.Count > 0)
            {
                _columns = "";
                foreach (string _str in _selectColumn)
                {
                    if (_columns != "") _columns = _columns + ",";
                    _columns = _columns + _str;
                }
            }
            
            string _select = "";
            if (_where == "")
            {
                _select = "SELECT {0} FROM {1}";
                _select = string.Format(_select, _columns, _TableName);
            }
            else
            {
                _select = "SELECT {0} FROM {1} WHERE {2}";
                _select = string.Format(_select, _columns, _TableName, _where);
            }

            if (_sort != null && _sort != "")
            {
                _select += " ORDER BY " + _sort;
            }

            _com.CommandText = _select;

            foreach (IDbDataParameter _param in _params)
            {
                _com.Parameters.Add(_param);
            }

            return _com;
        }

        // SELECT
        public IDbCommand GetDataBy(IList<string> _selectColumn, IList<KeyValuePair<string, object>> _pairFilter, string _sort)
        {
            IDbCommand _com = _DALLayer.GetCommand();
            _com.CommandType = CommandType.Text;            

            string _columns = "*";
            if (_selectColumn != null && _selectColumn.Count > 0)
            {
                _columns = "";
                foreach (string _str in _selectColumn)
                {
                    if (_columns != "") _columns = _columns + ",";
                    _columns = _columns + _str;
                }
            }

            string _select = "SELECT {0} FROM {1} ";
            _select = string.Format(_select, _columns, _TableName);

            string _where = " WHERE ";
            string _filter = "";

            int _count = 1;
            
            foreach(KeyValuePair<string, object> _pair in _pairFilter)
            {
                if (_filter != "") _filter = _filter + " AND ";
                _filter = _filter + _pair.Key + "=@Param" + _count.ToString();

                IDbDataParameter _newParam = _com.CreateParameter();
                _newParam.ParameterName = "@Param" + _count.ToString();
                _newParam.Value = _pair.Value;

                _com.Parameters.Add(_newParam);
                _count++;
            }

            if (_filter != "")
                _com.CommandText = _select + _where + _filter;
            else
                _com.CommandText = _select;

            if (_sort != null && _sort != "")
            {
                _com.CommandText = _com.CommandText + " ORDER BY " + _sort;
            }
            return _com;
        }

        // SELECT
        public IDbCommand GetDataByManual(string _sql, IDbDataParameter[] _params)
        {
            IDbCommand _com = _DALLayer.GetCommand();
            _com.CommandType = CommandType.Text;
            _com.CommandText = _sql;

            if (_params != null)
            {
                foreach (IDbDataParameter _param in _params)
                {
                    _com.Parameters.Add(_param);
                }
            }

            return _com;
        }
        #endregion

        #region IDbCommand Execution Return
        public DataSet ExecuteDataSetFromCommand(IDbCommand _com)
        {
            DataSet _ds = new DataSet();
            IDataAdapter _adapter = _DALLayer.GetDataAdapter(_com);
            _adapter.Fill(_ds);
            //DataAccessLayer.Audit.AddAuditForSessionUser(_TableName, GetCommandAuditStatement(_com));
            return _ds;
        }

        public DataTable ExecuteDataTableFromCommand(IDbCommand _com)
        {
            DataTable _result = null;
            DataSet _ds = new DataSet();
            IDataAdapter _adapter = _DALLayer.GetDataAdapter(_com);
            _adapter.Fill(_ds);
            if (_ds.Tables.Count > 0) _result = _ds.Tables[0];            

            //DataAccessLayer.Audit.AddAuditForSessionUser(_TableName, GetCommandAuditStatement(_com));
            return _result;
        }

        public IDataReader ExecuteReaderFromCommand(IDbCommand _com)
        {
            IDataReader _reader = _com.ExecuteReader();
            //DataAccessLayer.Audit.AddAuditForSessionUser(_TableName, GetCommandAuditStatement(_com));

            return _reader;
        }

        public long ExecuteNonQueryInsertIdentityValueFromCommandWithoutAudit(DataAccessTableFiller _filler)
        {
            object _result = _filler.LastCommand.ExecuteScalar();
            long _long = DataAccessTableManager.LongParse(_result);
            return _long;
        }

        public long ExecuteNonQueryInsertIdentityValueFromCommand(DataAccessTableFiller _filler)
        {
            object _result = _filler.LastCommand.ExecuteScalar();            
            long _long = DataAccessTableManager.LongParse(_result);
            DataAccessLayer.Audit.AddAuditForSessionUser(_TableName, DataAccessAudit.GetCommandAuditStatement(_filler.LastCommand), _filler.AuditNote);
            if (_filler.Definition.PrimaryKeyColumns.Count == 1 && _result != null)
            {
                _filler.AddColumnValue(_filler.Definition.PrimaryKeyColumns[0], _result);
                if (_filler.RowData != null)
                {
                    _filler.RowData[_filler.Definition.PrimaryKeyColumns[0]] = _result;
                    _filler.RowData.AcceptChanges();
                }
            }
            return _long;
        }

        public int ExecuteNonQueryInsertFromCommand(DataAccessTableFiller _filler)
        {
            int _result = _filler.LastCommand.ExecuteNonQuery();
            DataAccessLayer.Audit.AddAuditForSessionUser(_TableName, DataAccessAudit.GetCommandAuditStatement(_filler.LastCommand), _filler.AuditNote);
            return _result;
        }

        public int ExecuteNonQueryUpdateFromCommand(DataAccessTableFiller _filler)
        {
            int _result = _filler.LastCommand.ExecuteNonQuery();
            DataAccessLayer.Audit.AddAuditForSessionUser(_TableName, DataAccessAudit.GetCommandAuditStatement(_filler.LastCommand), _filler.AuditNote);
            return _result;
        }

        public int ExecuteNonQueryDeleteFromCommand(DataAccessTableFiller _filler)
        {
            int _result = _filler.LastCommand.ExecuteNonQuery();
            DataAccessLayer.Audit.AddAuditForSessionUser(_TableName, DataAccessAudit.GetCommandAuditStatement(_filler.LastCommand), _filler.AuditNote);
            return _result;
        }

        public int ExecuteNonQueryDeleteFromWhere(string _sql, IDbDataParameter[] _params)
        {
            string _sqlx = "DELETE FROM {0} WHERE " + _sql;
            _sqlx = string.Format(_sqlx, _TableName);

            IDbCommand _com = _DALLayer.GetCommand();
            _com.CommandType = CommandType.Text;
            _com.CommandText = _sqlx;

            if (_params != null)
            {
                foreach (IDbDataParameter _param in _params)
                {
                    _com.Parameters.Add(_param);
                }
            }
            
            int _result = _com.ExecuteNonQuery();
            DataAccessLayer.Audit.AddAuditForSessionUser(_TableName, DataAccessAudit.GetCommandAuditStatement(_com), "");
            return _result;
        }   
        #endregion

        #region IsType
        public string GetTypeName(string _typename)
        {
            if (_ColumnDefinition == null || _ColumnDefinition.Rows.Count <= 0) return "";
            DataRow[] _rows = _ColumnDefinition.Select("Column_Name='" + _typename + "'");
            if (_rows == null || _rows.Length <= 0) return "";
            return _rows[0]["Data_Type"].ToString();
        }
        
        public bool IsDateTimeType(string _columnName)
        {
            string _typename = GetTypeName(_columnName);
            _typename = _typename.ToLower();
            if (_typename == "datetime" || _typename == "date" || _typename == "smalldatetime" || _typename == "date")
            {
                return true;
            }
            return false;
        }

        public bool IsFixStringType(string _columnName)
        {
            string _typename = GetTypeName(_columnName);
            _typename = _typename.ToLower();
            if (_typename == "varchar" || _typename == "char")
            {
                return true;
            }
            return false;
        }

        public bool IsNonFixStringType(string _columnName)
        {
            string _typename = GetTypeName(_columnName);
            _typename = _typename.ToLower();
            if (_typename == "nvarchar" || _typename == "nchar")
            {
                return true;
            }
            return false;
        }

        public bool IsStringType(string _columnName)
        {
            string _typename = GetTypeName(_columnName);
            _typename = _typename.ToLower();
            if (_typename == "varchar" || _typename == "nvarchar" ||
                _typename == "char" || _typename == "nchar")
            {
                return true;
            }
            return false;
        }

		public bool IsImageType(string _columnName)
		{
			string _typename = GetTypeName(_columnName);
			_typename = _typename.ToLower();
			if (_typename == "image" || _typename == "byte")
			{
				return true;
			}
			return false;
		}

        public bool IsValueType(string _columnName)
        {
            string _typename = GetTypeName(_columnName);
            _typename = _typename.ToLower();
            if (_typename == "int" || _typename == "bigint" ||
                _typename == "tinyint" || _typename == "smallint" ||
                _typename == "decimal" || _typename == "float")
            {
                return true;
            }
            return false;
        }

        public bool IsIntValueType(string _columnName)
        {
            string _typename = GetTypeName(_columnName);
            _typename = _typename.ToLower();
            if (_typename == "int")
            {
                return true;
            }
            return false;
        }

        public bool IsBigIntValueType(string _columnName)
        {
            string _typename = GetTypeName(_columnName);
            _typename = _typename.ToLower();
            if (_typename == "bigint")
            {
                return true;
            }
            return false;
        }

        public bool IsDecimalValueType(string _columnName)
        {
            string _typename = GetTypeName(_columnName);
            _typename = _typename.ToLower();
            if (_typename == "decimal")
            {
                return true;
            }
            return false;
        }

        public bool IsShortValueType(string _columnName)
        {
            string _typename = GetTypeName(_columnName);
            _typename = _typename.ToLower();
            if (_typename == "tinyint" || _typename == "smallint")
            {
                return true;
            }
            return false;
        }

        public bool IsBitType(string _columnName)
        {
            string _typename = GetTypeName(_columnName);
            _typename = _typename.ToLower();
            if (_typename == "bit")
            {
                return true;
            }
            return false;
        }

        public object GetDefaultValueFor(string _columnName)
        {
            if (IsValueType(_columnName)) return 0;
            if (IsImageType(_columnName)) return new byte[] { 0 };
            if (IsNonFixStringType(_columnName)) return "";
            if (IsFixStringType(_columnName)) return "".PadLeft(GetStringColumnMaxLength(_columnName), ' ');
            if (IsDateTimeType(_columnName)) return DateTime.MinValue;
            if (IsBitType(_columnName)) return false;
            return 0;
        }
        #endregion
    }
}