DBTool for Oracle - Part 1

using System;
using System.Data;
using System.Text;
using System.ComponentModel;
using System.Reflection;
using Oracle.DataAccess.Client;


using Harlinn.Oracle.DBTool.Example.Common;
using Harlinn.Oracle.DBTool.Example.Types;

namespace Harlinn.Oracle.DBTool.Example.DB
{
    public partial class ShortStringValueElementReader : Reader
    {

        private static readonly log4net.ILog sfLog = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        private static void LogException(Exception exc, MethodBase method)
        {
            Harlinn.Oracle.DBTool.Example.Common.Logger.LogException(sfLog, exc, method);
        }

        public const string DEFAULT_QUALIFIED_DBNAME = "HARLINNDATA.SHORTSTRINGVALUES";
        public const string FULL_SELECT = "SELECT TS,FLAGS,VALUE FROM {0}";
        public const string KEY_FIELDS = "TS";

        public const int TS = 0;
        public const int FLAGS = 1;
        public const int VALUE = 2;

        private long tag;

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

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

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

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

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





        private static OracleDataReader CreateReader( string qualifiedDBName )
        {
            try
            {
                OracleConnection oracleConnection = GetConnection();
                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 ShortStringValueElementReader CreateReaderByTimeStamp(  DateTime ts)
        {
            ShortStringValueElementReader result = CreateReaderByTimeStamp( GetConnection(), DEFAULT_QUALIFIED_DBNAME,ts);
            return result;
        }

        public static ShortStringValueElementReader CreateReaderByTimeStamp( OracleConnection oracleConnection , DateTime ts)
        {
            ShortStringValueElementReader result = CreateReaderByTimeStamp( oracleConnection, DEFAULT_QUALIFIED_DBNAME,ts);
            return result;
        }

        public static ShortStringValueElementReader CreateReaderByTimeStamp( string qualifiedDBName,  DateTime ts)
        {
            ShortStringValueElementReader result = CreateReaderByTimeStamp( GetConnection(), qualifiedDBName,ts);
            return result;
        }

        public static ShortStringValueElementReader CreateReaderByTimeStamp( OracleConnection oracleConnection ,string qualifiedDBName,  DateTime ts)
        {
            try
            {
                string fullSelect = string.Format(FULL_SELECT, qualifiedDBName);
                OracleCommand oracleCommand = oracleConnection.CreateCommand();
                using (oracleCommand)
                {
                    oracleCommand.BindByName = true;
                    string queryFilter = " WHERE TS = :ts";
                    string selectStatement = fullSelect + queryFilter;
                    oracleCommand.CommandText = selectStatement;


                    OracleParameter tsParameter = oracleCommand.Parameters.Add(new OracleParameter( ":ts", OracleDbType.Decimal ));
                    tsParameter.Value = ts.ToUniversalTime().Ticks;

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


        public static ShortStringValueElementReader CreateShortStringValueForTimeStamp( OracleConnection oracleConnection, string qualifiedDBName, DateTime ts )
        {
            try
            {
                string fullSelect = string.Format(FULL_SELECT, qualifiedDBName);
                string queryFilter = string.Format(" WHERE TS = (SELECT MAX(TS) FROM {0} WHERE TS <= :timeStamp_ )", qualifiedDBName);
                OracleCommand oracleCommand = oracleConnection.CreateCommand();
                using (oracleCommand)
                {
                    oracleCommand.BindByName = true;
                    string selectStatement = fullSelect + queryFilter;
                    oracleCommand.CommandText = selectStatement;

                    oracleCommand.Parameters.Add(":timeStamp_", OracleDbType.Decimal).Value = ts.ToUniversalTime().Ticks;

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

        public static ShortStringValueElementReader CreateShortStringValueForLastTimeStamp( OracleConnection oracleConnection, string qualifiedDBName )
        {
            try
            {
                string fullSelect = string.Format(FULL_SELECT, qualifiedDBName);
                string queryFilter = string.Format(" WHERE TS = ( SELECT MAX(TS) FROM {0} )", qualifiedDBName);
                OracleCommand oracleCommand = oracleConnection.CreateCommand();
                using (oracleCommand)
                {
                    oracleCommand.BindByName = true;
                    string selectStatement = fullSelect + queryFilter;
                    oracleCommand.CommandText = selectStatement;

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

        public static ShortStringValueElementReader CreateShortStringValueForInterval( OracleConnection oracleConnection, string qualifiedDBName, DateTime startOfInterval, DateTime endOfInterval )
        {
            try
            {
                string fullSelect = string.Format(FULL_SELECT, qualifiedDBName);
                string queryFilter = string.Format(" WHERE TS BETWEEN ( SELECT MAX(TS) FROM {0} WHERE TS <= :startTimeStamp ) AND ( SELECT MAX(TS) FROM {0} WHERE TS <= :endTimeStamp )", qualifiedDBName) + " ORDER BY " + KEY_FIELDS;
                OracleCommand oracleCommand = oracleConnection.CreateCommand();
                using (oracleCommand)
                {
                    oracleCommand.BindByName = true;
                    string selectStatement = fullSelect + queryFilter;
                    oracleCommand.CommandText = selectStatement;

                    oracleCommand.Parameters.Add(":startTimeStamp", OracleDbType.Decimal).Value = startOfInterval.ToUniversalTime().Ticks;
                    oracleCommand.Parameters.Add(":endTimeStamp", OracleDbType.Decimal).Value = endOfInterval.ToUniversalTime().Ticks;

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



        public long Tag
        {
            get
            {
                return tag;
            }
            set
            {
                this.tag = value;
            }
        }



        public DateTime TimeStamp
        {
            get
            {
                try
                {
                    DateTime result = new DateTime(Convert.ToInt64(GetDecimal(TS)),DateTimeKind.Utc);
                    return result;
                }
                catch(Exception exc)
                {
                    LogException(exc, MethodBase.GetCurrentMethod());
                    throw;
                }
            }
        }


        public long Flags
        {
            get
            {
                try
                {
                    long result = Convert.ToInt64(GetDecimal(FLAGS));
                    return result;
                }
                catch(Exception exc)
                {
                    LogException(exc, MethodBase.GetCurrentMethod());
                    throw;
                }
            }
        }


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


        public ShortStringValueElementData ShortStringValue
        {
            get
            {
                try
                {
                    ShortStringValueElementData result = new ShortStringValueElementData( Tag,TimeStamp,Flags,Value );
                    return result;
                }
                catch(Exception exc)
                {
                    LogException(exc, MethodBase.GetCurrentMethod());
                    throw;
                }
            }
        }
    }
}