code/Data/Database.cs

Go to the documentation of this file.

// TODO: refactor
namespace Sylloge.Data
{
    public enum DatabaseType
    {
        ODBC,
        OLEDB
    }

    public abstract class Database : System.IDisposable
    {
        #region Class Members

        #region Protected Members

        protected System.Data.Common.DbDataAdapter m_Adapter { get; set; }
        protected System.Data.Common.DbConnection m_Conn { get; set; }
        protected string m_ConnStr { get; set; }
        protected bool m_IsOpen { get; set; }
        protected string m_Name { get; set; }
        protected System.Data.DataTable m_Table { get; set; }
        protected Sylloge.Data.DatabaseType m_Type { get; set; }

        #endregion

        #region Public Members

        public System.Data.DataSet Dataset { get; protected set; }

        public Sylloge.Data.DatabaseType Type
        {
            get
            {
                return this.m_Type;
            }
        }

        #endregion

        #endregion

        #region Class Methods

        public Database(Sylloge.Data.DatabaseType type)
        {
            this.Initialize(type);
        }

        ~Database()
        {
            this.Dispose();
        }

        public void Dispose()
        {
            this.Close();
            System.GC.SuppressFinalize(this);
        }

        public void Close()
        {
            if (this.m_Conn != null) {
                this.m_Conn.Close();
                this.m_Conn.Dispose();
            }
            if (this.m_Adapter != null) { this.m_Adapter.Dispose(); }
            if (this.Dataset != null) { this.Dataset.Dispose(); }
            if (this.m_Table != null) { this.m_Table.Dispose(); }
            this.Dataset = null;
            this.m_Adapter = null;
            this.m_Conn = null;
            this.m_Table = null;
            this.m_IsOpen = false;
        }

        public bool ColumnExists(string columnName)
        {
            return this.m_Table.Columns.Contains(columnName);
        }

        public System.Data.DataTable GetTables()
        {
            System.Data.DataTable dt = null;
            if (this.m_Type == Sylloge.Data.DatabaseType.ODBC) {
                dt = ((System.Data.Odbc.OdbcConnection)this.m_Conn).GetSchema("Tables");
            } else {
                dt = ((System.Data.OleDb.OleDbConnection)this.m_Conn).GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            }
            return dt;
        }

        private void Initialize(Sylloge.Data.DatabaseType type)
        {
            this.m_Type = type;
            if (this.m_Type == Sylloge.Data.DatabaseType.ODBC) {
                this.m_Conn = new System.Data.Odbc.OdbcConnection();
                this.m_Adapter = new System.Data.Odbc.OdbcDataAdapter();
                this.m_Adapter.DeleteCommand = new System.Data.Odbc.OdbcCommand();
                this.m_Adapter.InsertCommand = new System.Data.Odbc.OdbcCommand();
                this.m_Adapter.SelectCommand = new System.Data.Odbc.OdbcCommand();
                this.m_Adapter.UpdateCommand = new System.Data.Odbc.OdbcCommand();
            } else {
                this.m_Conn = new System.Data.OleDb.OleDbConnection();
                this.m_Adapter = new System.Data.OleDb.OleDbDataAdapter();
                this.m_Adapter.DeleteCommand = new System.Data.OleDb.OleDbCommand();
                this.m_Adapter.InsertCommand = new System.Data.OleDb.OleDbCommand();
                this.m_Adapter.SelectCommand = new System.Data.OleDb.OleDbCommand();
                this.m_Adapter.UpdateCommand = new System.Data.OleDb.OleDbCommand();
            }
            this.m_Adapter.AcceptChangesDuringUpdate = true;
            this.m_Adapter.AcceptChangesDuringFill = true;
            this.Dataset = new System.Data.DataSet();
            this.m_Table = new System.Data.DataTable();
        }

        public bool IsConnected
        {
            get
            {
                return (this.m_Conn != null) && this.m_IsOpen;
            }
        }

        public bool Open()
        {
            return this.Open(this.m_Name, this.m_ConnStr);
        }

        private bool Open(string dbName, string connString)
        {
            if (dbName == null || dbName.Trim() == string.Empty ||
                connString == null || connString.Trim() == string.Empty) {
                return false;
            }
            this.Close();
            this.Initialize(this.m_Type);
            this.m_Name = dbName;
            this.m_ConnStr = connString;
            this.m_IsOpen = false;
            try {
                this.m_Conn.ConnectionString = connString;
                this.m_Conn.Open();
                this.m_Adapter.DeleteCommand.Connection = this.m_Conn;
                this.m_Adapter.InsertCommand.Connection = this.m_Conn;
                this.m_Adapter.SelectCommand.Connection = this.m_Conn;
                this.m_Adapter.UpdateCommand.Connection = this.m_Conn;
                this.m_IsOpen = true;
            } catch (System.Exception ex) {
                // Reason for this could be invalid password
                Sylloge.App.Out("There was an error while trying to open the database.", ex);
                this.m_IsOpen = false;
            }
            return this.m_IsOpen;
        }

        public System.Data.DataTable Query(string table, string query)
        {
            if (!this.Dataset.Tables.Contains(table)) {
                throw new System.Exception("Invalid table specified");
            }
            try {
                this.Dataset.Clear();
                // We specifically cast here to ensure the queries are sent/received properly
                if (this.m_Type == Sylloge.Data.DatabaseType.ODBC) {
                    ((System.Data.Odbc.OdbcDataAdapter)this.m_Adapter).SelectCommand.CommandText = query;
                    ((System.Data.Odbc.OdbcDataAdapter)this.m_Adapter).SelectCommand.ExecuteNonQuery();
                    ((System.Data.Odbc.OdbcDataAdapter)this.m_Adapter).Fill(this.Dataset, table);
                } else {
                    ((System.Data.OleDb.OleDbDataAdapter)this.m_Adapter).SelectCommand.CommandText = query;
                    ((System.Data.OleDb.OleDbDataAdapter)this.m_Adapter).SelectCommand.ExecuteNonQuery();
                    ((System.Data.OleDb.OleDbDataAdapter)this.m_Adapter).Fill(this.Dataset, table);
                }
                return this.Dataset.Tables[table];
            } catch (System.Data.Odbc.OdbcException odbcex) {
                if (this.m_Type != Sylloge.Data.DatabaseType.ODBC) {
                    throw new System.Exception(("Unexpected ODBC error for table '" + table + "': " + odbcex.Message), odbcex);
                }
                throw new System.Exception(("ODBC error querying table '" + table + "': " + odbcex.Message), odbcex);
            } catch (System.Data.OleDb.OleDbException oledbex) {
                if (this.m_Type != Sylloge.Data.DatabaseType.OLEDB) {
                    throw new System.Exception(("Unexpected OLEDB error for table '" + table + "': " + oledbex.Message), oledbex);
                }
                throw new System.Exception(("Error querying table : '" + table + "'" + oledbex.Message), oledbex);
            } catch (System.Exception ex) {
                throw new System.Exception(("Error querying table '" + table + "': " + ex.Message), ex);
            }
        }

        #endregion
    }

    public class OleDb : Sylloge.Data.Database
    {
        public OleDb() : base(Sylloge.Data.DatabaseType.OLEDB) { }
    }

    public class Odbc : Sylloge.Data.Database
    {
        public Odbc() : base(Sylloge.Data.DatabaseType.ODBC) { }
    }
}