DBTool for Oracle - Part 1

using System;
using System.Data;
using System.Text;
using System.Reflection;
using Oracle.DataAccess.Client;
using Harlinn.Common;

namespace Harlinn.DBTool.DataSources.Oracle.DB
{
    public class ColumnCommentReader : Reader
    {
        private static readonly log4net.ILog sfLog = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        private static void LogException(Exception exc, MethodBase method)
        {
            Logger.LogException(sfLog, exc, method);
        }


        public const string DEFAULT_QUALIFIED_DBNAME = "SYS.ALL_COL_COMMENTS";
        public const string FULL_SELECT = "SELECT OWNER,TABLE_NAME,COLUMN_NAME,COMMENTS FROM {0}";
        public const string KEY_FIELDS = "";

        public const int OWNER = 0;
        public const int TABLE_NAME = 1;
        public const int COLUMN_NAME = 2;
        public const int COMMENTS = 3;


        public ColumnCommentReader( )
            : base( CreateReader( DEFAULT_QUALIFIED_DBNAME ) )
        {
        }

        public ColumnCommentReader ( string qualifiedDBName )
            : base( CreateReader( qualifiedDBName ) )
        {
        }

        public ColumnCommentReader ( OracleConnection oracleConnection )
            : base( CreateReader( oracleConnection ) )
        {
        }

        public ColumnCommentReader ( OracleConnection oracleConnection, string qualifiedDBName )
            : base( CreateReader( oracleConnection, qualifiedDBName ) )
        {
        }

        public ColumnCommentReader(OracleDataReader reader)
            : base( reader )
        {
        }





        private static OracleDataReader CreateReader( string qualifiedDBName )
        {
            try
            {
                OracleConnection oracleConnection = ConnectionManager.Connection;
                OracleDataReader result = CreateReader(oracleConnection,qualifiedDBName);
                return result;
            }
            catch (Exception exc)
            {
                LogException(exc, MethodBase.GetCurrentMethod());
                throw;
            }
        }


        private static OracleDataReader CreateReader( OracleConnection oracleConnection )
        {
            try
            {
                OracleDataReader result = CreateReader(oracleConnection,DEFAULT_QUALIFIED_DBNAME);
                return result;
            }
            catch (Exception exc)
            {
                LogException(exc, MethodBase.GetCurrentMethod());
                throw;
            }
        }


        private static OracleDataReader CreateReader( OracleConnection oracleConnection, string qualifiedDBName )
        {
            try
            {
                string sql = string.Format(FULL_SELECT, qualifiedDBName) + " ORDER BY " + KEY_FIELDS;
                OracleCommand oracleCommand = oracleConnection.CreateCommand();
                using (oracleCommand)
                {
                    oracleCommand.CommandText = sql;
                    OracleDataReader result = oracleCommand.ExecuteReader(CommandBehavior.SingleResult);
                    return result;
                }
            }
            catch (Exception exc)
            {
                LogException(exc, MethodBase.GetCurrentMethod());
                throw;
            }
        }


        public static ColumnCommentReader CreateReader(string owner, string tableName, string columnName)
        {
            try
            {
                ColumnCommentReader result = CreateReader(ConnectionManager.Connection, owner, tableName, columnName);
                return result;
            }
            catch (Exception exc)
            {
                LogException(exc, MethodBase.GetCurrentMethod());
                throw;
            }
        }

        public static ColumnCommentReader CreateReader(OracleConnection oracleConnection, string owner, string tableName, string columnName)
        {
            try
            {
                string fullSelect = string.Format(FULL_SELECT, DEFAULT_QUALIFIED_DBNAME);
                OracleCommand oracleCommand = oracleConnection.CreateCommand();
                using (oracleCommand)
                {
                    oracleCommand.BindByName = true;
                    string queryFilter = " WHERE OWNER = :owner AND TABLE_NAME = :tableName AND COLUMN_NAME = :columnName";
                    string selectStatement = fullSelect + queryFilter;
                    oracleCommand.CommandText = selectStatement;


                    OracleParameter ownerParameter = oracleCommand.Parameters.Add(new OracleParameter(":owner", OracleDbType.Varchar2));
                    ownerParameter.Value = owner;

                    OracleParameter tableNameParameter = oracleCommand.Parameters.Add(new OracleParameter(":tableName", OracleDbType.Varchar2));
                    tableNameParameter.Value = tableName;

                    OracleParameter columnNameParameter = oracleCommand.Parameters.Add(new OracleParameter(":columnName", OracleDbType.Varchar2));
                    columnNameParameter.Value = columnName;

                    OracleDataReader result = oracleCommand.ExecuteReader(CommandBehavior.SingleResult | CommandBehavior.SingleRow);
                    return new ColumnCommentReader(result);
                }
            }
            catch (Exception exc)
            {
                LogException(exc, MethodBase.GetCurrentMethod());
                throw;
            }
        }


        public static ColumnCommentReader CreateReader(string owner, string tableName)
        {
            try
            {
                ColumnCommentReader result = CreateReader(ConnectionManager.Connection, owner, tableName);
                return result;
            }
            catch (Exception exc)
            {
                LogException(exc, MethodBase.GetCurrentMethod());
                throw;
            }
        }

        public static ColumnCommentReader CreateReader(OracleConnection oracleConnection, string owner, string tableName)
        {
            try
            {
                string fullSelect = string.Format(FULL_SELECT, DEFAULT_QUALIFIED_DBNAME);
                OracleCommand oracleCommand = oracleConnection.CreateCommand();
                using (oracleCommand)
                {
                    oracleCommand.BindByName = true;
                    string queryFilter = " WHERE OWNER = :owner AND TABLE_NAME = :tableName ORDER BY COLUMN_NAME";
                    string selectStatement = fullSelect + queryFilter;
                    oracleCommand.CommandText = selectStatement;


                    OracleParameter ownerParameter = oracleCommand.Parameters.Add(new OracleParameter(":owner", OracleDbType.Varchar2));
                    ownerParameter.Value = owner;

                    OracleParameter tableNameParameter = oracleCommand.Parameters.Add(new OracleParameter(":tableName", OracleDbType.Varchar2));
                    tableNameParameter.Value = tableName;

                    OracleDataReader result = oracleCommand.ExecuteReader(CommandBehavior.SingleResult);
                    return new ColumnCommentReader(result);
                }
            }
            catch (Exception exc)
            {
                LogException(exc, MethodBase.GetCurrentMethod());
                throw;
            }
        }




        public string Owner
        {
            get
            {
                try
                {
                    string result = GetString(OWNER);
                    return result;
                }
                catch(Exception exc)
                {
                    LogException(exc, MethodBase.GetCurrentMethod());
                    throw;
                }
            }
        }


        public string TableName
        {
            get
            {
                try
                {
                    string result = GetString(TABLE_NAME);
                    return result;
                }
                catch(Exception exc)
                {
                    LogException(exc, MethodBase.GetCurrentMethod());
                    throw;
                }
            }
        }


        public string ColumnName
        {
            get
            {
                try
                {
                    string result = GetString(COLUMN_NAME);
                    return result;
                }
                catch(Exception exc)
                {
                    LogException(exc, MethodBase.GetCurrentMethod());
                    throw;
                }
            }
        }


        public string Comments
        {
            get
            {
                try
                {
                    if(IsDBNull(COMMENTS) == false)
                    {
                        string result = GetString(COMMENTS);
                        return result;
                    }
                    return null;
                }
                catch(Exception exc)
                {
                    LogException(exc, MethodBase.GetCurrentMethod());
                    throw;
                }
            }
        }
        

    }
}