Click here to Skip to main content
15,889,034 members
Search within:


Articles / Desktop Programming / WPF
 

DBTool for Oracle - Part 1

Rate me:
Please Sign up or sign in to vote.
4.92/5 (45 votes)
13 Apr 2014CPOL18 min read 137.2K   5.1K   88  
Enhance productivity and reliability, write your own tools.
 
using System;
using System.Collections.Generic;
using Harlinn.Oracle.DBTool.Types.Projects;
using Harlinn.Oracle.DBTool.Types.CSharp;
using Oracle.DataAccess.Client;
using Harlinn.Oracle.DBTool.Generators.Utils;


namespace Harlinn.Oracle.DBTool.Generators.Reader
{
    public class ReaderGenerator : GeneratorBase
    {
        ProjectTabular tabular;
        SortedSet<string> createMethodMethods = new SortedSet<string>();

        public ReaderGenerator(ProjectTabular tabular)
        {
            this.tabular = tabular;    
        }

        public override string GetFilename()
        {
            string className = tabular.GetReaderClassName();

            string directory = GetDirectory();

            string result = directory + "\\" + className + ".cs";
            return result;
        }

        public override string GetDirectory()
        {
            string namespace_ = Project.Current.DatabaseNamespace;
            string directory = Project.Current.NamespaceToDirectory(namespace_);
            return directory;
        }



        public void Generate()
        {
            string commonNamespace = Project.Current.CommonNamespace;
            string dataNamespace = Project.Current.DataNamespace;       
            string databaseNamespace = Project.Current.DatabaseNamespace;
            string className = tabular.GetReaderClassName();
            string dataClassName = tabular.GetDataTypeClassName();
            string dataPropertyName = tabular.GetBaseName();
            string qualifiedDBName = tabular.GetQualifiedDBElementName();

            WriteLine("using System;");
            WriteLine("using System.Data;");
            WriteLine("using System.Text;");
            WriteLine("using System.ComponentModel;");
            WriteLine("using System.Reflection;");

            WriteLine("using Oracle.DataAccess.Client;");
            WriteLine();
            WriteLine();
            WriteLine("using "+ commonNamespace + ";");
            WriteLine("using "+ dataNamespace + ";");

            WriteLine();
            WriteLine("namespace " + databaseNamespace);
            WriteLine("{");
            WriteLine("    public partial class " + className+" : Reader");
            WriteLine("    {");
            WriteLine();
            WriteLine("        private static readonly log4net.ILog sfLog = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);");
            WriteLine();
            WriteLine("        private static void LogException(Exception exc, MethodBase method)");
            WriteLine("        {");
            WriteLine("            " + commonNamespace + ".Logger.LogException(sfLog, exc, method);");
            WriteLine("        }");
            WriteLine();

            List<ProjectTabularField> fields = tabular.GetFieldList();
            List<ProjectTabularField> primaryKeyFields = tabular.GetPrimaryKeyFieldList();


            WriteLine("        public const string DEFAULT_QUALIFIED_DBNAME = \""+qualifiedDBName+"\";");
            WriteLine("        public const string FULL_SELECT = \"SELECT " + ColumnHelper.GetCommaSeparatedFieldList(fields) + " FROM {0}\";");
            WriteLine("        public const string KEY_FIELDS = \"" + ColumnHelper.GetCommaSeparatedFieldList(primaryKeyFields) + "\";");


            WriteLine();

            for(int i = 0; i < fields.Count; i++ )
            {
                ProjectTabularField field = fields[i];

                WriteLine("        public const int "+field.Name+" = " + i.ToString() + ";");

            }

            WriteLine();
            if (tabular is ProjectTable)
            {
                ProjectTable projectTable = (ProjectTable)tabular;
                if (projectTable.IsTagValue)
                {
                    WriteLine("        private long tag;");
                }
            }
            WriteLine();

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

            GenerateCreateMethods();

            GenerateTagValueCreateMethods();


            WriteLine();
            if (tabular is ProjectTable)
            {
                ProjectTable projectTable = (ProjectTable)tabular;
                if (projectTable.IsTagValue)
                {
                    if (projectTable.IsTagValue)
                    {
                        WriteLine("        public long Tag");
                        WriteLine("        {");
                        WriteLine("            get");
                        WriteLine("            {");
                        WriteLine("                return tag;");
                        WriteLine("            }");
                        WriteLine("            set");
                        WriteLine("            {");
                        WriteLine("                this.tag = value;");
                        WriteLine("            }");
                        WriteLine("        }");
                        WriteLine();
                    }
                }
            }
            WriteLine();


            WriteLine();

            for (int i = 0; i < fields.Count; i++)
            {
                ProjectTabularField field = fields[i];
                string propertyType = field.PropertyType;
                string propertyName = field.PropertyName;
                string propertyReaderFunction = field.ReaderFunction;


                if(string.IsNullOrWhiteSpace(field.Comments) == false)
                {
                    WriteLine("        [Description(\""+field.Comments+"\")]");
                }

                if(string.IsNullOrWhiteSpace(field.DisplayName) == false)
                {
                    WriteLine("        [DisplayName(\"" + field.DisplayName + "\")]");
                }

                


                WriteLine("        public " + propertyType + " " + propertyName);
                WriteLine("        {");
                WriteLine("            get");
                WriteLine("            {");
                WriteLine("                try");
                WriteLine("                {");

                if (field.AllowDBNull)
                {
                    WriteLine("                    if(IsDBNull(" + field.Name + ") == false)");
                    WriteLine("                    {");
                    WriteLine("                        " + propertyType + " result = " + TypeHelper.GetReaderConversion ( field.ReaderType, propertyType, propertyReaderFunction + "(" + field.Name + ")") + ";");
                    WriteLine("                        return result;");
                    WriteLine("                    }");
                    WriteLine("                    return null;");
                }
                else
                {
                    WriteLine("                    " + propertyType + " result = " + TypeHelper.GetReaderConversion(field.ReaderType, propertyType, propertyReaderFunction + "(" + field.Name + ")") + ";");
                    WriteLine("                    return result;");
                }

                WriteLine("                }");
                WriteLine("                catch(Exception exc)");
                WriteLine("                {");
                WriteLine("                    LogException(exc, MethodBase.GetCurrentMethod());");
                WriteLine("                    throw;");
                WriteLine("                }");

                WriteLine("            }");
                WriteLine("        }");
                WriteLine();
                WriteLine();

            }


            WriteLine("        public " + dataClassName + " " + dataPropertyName);
            WriteLine("        {");
            WriteLine("            get");
            WriteLine("            {");
            WriteLine("                try");
            WriteLine("                {");

            string tagProperty = string.Empty;
            if (tabular is ProjectTable)
            {
                ProjectTable projectTable = (ProjectTable)tabular;
                if (projectTable.IsTagValue)
                {
                    tagProperty = " Tag,";
                }
            }

            if (dataPropertyName == "Tag")
            {
                WriteLine("                    " + dataClassName + " result = " + dataClassName + ".Create(" + ColumnHelper.GetCommaSeparatedPropertyNameList(fields) + " );");
            }
            else
            {

                WriteLine("                    " + dataClassName + " result = new " + dataClassName + "(" + tagProperty + ColumnHelper.GetCommaSeparatedPropertyNameList(fields) + " );");
            }
            if (tabular is ProjectTable)
            {
                ProjectTable projectTable = (ProjectTable)tabular;
                ProjectTabularField concurrencyField = projectTable.GetConcurrencyField();
                if (concurrencyField != null)
                {
                    WriteLine("                    result." + projectTable.ElementStatePropertyName + " = ElementState.Stored;");
                }

            }

            WriteLine("                    return result;");
            WriteLine("                }");
            WriteLine("                catch(Exception exc)");
            WriteLine("                {");
            WriteLine("                    LogException(exc, MethodBase.GetCurrentMethod());");
            WriteLine("                    throw;");
            WriteLine("                }");
            WriteLine("            }");
            WriteLine("        }");



            WriteLine("    }");
            WriteLine("}");
            

        }


        public void GenerateCreateMethods()
        {
            GenerateCreateByPrimaryKeyMethod();
 
            ProjectTabularIndexes indexes = tabular.GetIndexes();
            foreach(ProjectTabularIndex index in indexes.Children)
            {
                GenerateCreateByIndexMethod(index);
            }


            ProjectTabularReferences references = tabular.GetReferences();
            foreach (ProjectTabularReference reference in references.Children)
            {
                GenerateCreateByReferenceMethod(reference);
            }

        }


        public void GenerateCreateByPrimaryKeyMethod()
        {
            string className = tabular.GetReaderClassName();
            ProjectTabularPrimaryKey primaryKey = tabular.GetPrimaryKey();
            if (primaryKey != null)
            {
                string methodName = MethodNamesHelper.GetCreateReaderMethodName(primaryKey);
                if (createMethodMethods.Contains(methodName))
                {
                    return;
                }
                createMethodMethods.Add(methodName);
                List<ProjectTabularField> argumentFields = primaryKey.GetFieldList();

                string arguments = ColumnHelper.GetCommaSeparatedPropertyFieldNameList(argumentFields);
                string argumentDeclaration = ColumnHelper.GetArgumentDeclarationList(argumentFields);

                WriteLine("        public static " + className + " " + methodName + "( " + argumentDeclaration + ")");
                WriteLine("        {");
                WriteLine("            " + className + " result = " + methodName + "( GetConnection(), DEFAULT_QUALIFIED_DBNAME," + arguments + ");");
                WriteLine("            return result;");
                WriteLine("        }");
                WriteLine();

                WriteLine("        public static " + className + " " + methodName + "( OracleConnection oracleConnection ," + argumentDeclaration + ")");
                WriteLine("        {");
                WriteLine("            " + className + " result = " + methodName + "( oracleConnection, DEFAULT_QUALIFIED_DBNAME," + arguments + ");");
                WriteLine("            return result;");
                WriteLine("        }");
                WriteLine();
                WriteLine("        public static " + className + " " + methodName + "( string qualifiedDBName, " + argumentDeclaration + ")");
                WriteLine("        {");
                WriteLine("            " + className + " result = " + methodName + "( GetConnection(), qualifiedDBName," + arguments + ");");
                WriteLine("            return result;");
                WriteLine("        }");
                WriteLine();
                WriteLine("        public static " + className + " " + methodName + "( OracleConnection oracleConnection ,string qualifiedDBName, " + argumentDeclaration + ")");
                WriteLine("        {");
                WriteLine("            try");
                WriteLine("            {");
                WriteLine("                string fullSelect = string.Format(FULL_SELECT, qualifiedDBName);");
                WriteLine("                OracleCommand oracleCommand = oracleConnection.CreateCommand();");
                WriteLine("                using (oracleCommand)");
                WriteLine("                {");
                WriteLine("                    oracleCommand.BindByName = true;");

                WriteLine(OracleSqlSelectHelper.GetWhereCriteriaStatement(argumentFields, "                    "));
                WriteLine("                    string selectStatement = fullSelect + queryFilter;");
                WriteLine("                    oracleCommand.CommandText = selectStatement;");

                WriteLine();

                WriteLine(OracleSqlSelectHelper.GetParameterDeclarationsForWhereCriteriaStatement(argumentFields, "                    "));

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


        public void GenerateCreateByIndexMethod(ProjectTabularIndex index)
        {
            string className = tabular.GetReaderClassName();
            if (index != null)
            {
                string methodName = MethodNamesHelper.GetCreateReaderMethodName(index);
                if (createMethodMethods.Contains(methodName))
                {
                    return;
                }
                createMethodMethods.Add(methodName);
                List<ProjectTabularField> argumentFields = index.GetFieldList();

                string arguments = ColumnHelper.GetCommaSeparatedPropertyFieldNameList(argumentFields);
                string argumentDeclaration = ColumnHelper.GetArgumentDeclarationList(argumentFields);

                WriteLine("        public static " + className + " " + methodName + "( " + argumentDeclaration + ")");
                WriteLine("        {");
                WriteLine("            " + className + " result = " + methodName + "( GetConnection(), DEFAULT_QUALIFIED_DBNAME," + arguments + ");");
                WriteLine("            return result;");
                WriteLine("        }");
                WriteLine();
                WriteLine("        public static " + className + " " + methodName + "( OracleConnection oracleConnection, " + argumentDeclaration + ")");
                WriteLine("        {");
                WriteLine("            " + className + " result = " + methodName + "( oracleConnection, DEFAULT_QUALIFIED_DBNAME," + arguments + ");");
                WriteLine("            return result;");
                WriteLine("        }");
                WriteLine();
                WriteLine("        public static " + className + " " + methodName + "( string qualifiedDBName, " + argumentDeclaration + ")");
                WriteLine("        {");
                WriteLine("            " + className + " result = " + methodName + "( GetConnection(), qualifiedDBName," + arguments + ");");
                WriteLine("            return result;");
                WriteLine("        }");
                WriteLine();
                WriteLine("        public static " + className + " " + methodName + "( OracleConnection oracleConnection, string qualifiedDBName," + argumentDeclaration + ")");
                WriteLine("        {");
                WriteLine("            try");
                WriteLine("            {");
                WriteLine("                string fullSelect = string.Format(FULL_SELECT, qualifiedDBName);");
                WriteLine("                OracleCommand oracleCommand = oracleConnection.CreateCommand();");
                WriteLine("                using (oracleCommand)");
                WriteLine("                {");
                WriteLine("                    oracleCommand.BindByName = true;");
                WriteLine(OracleSqlSelectHelper.GetWhereCriteriaStatement(argumentFields, "                    "));
                WriteLine("                    string selectStatement = fullSelect + queryFilter;");
                WriteLine("                    oracleCommand.CommandText = selectStatement;");
                WriteLine();
                WriteLine(OracleSqlSelectHelper.GetParameterDeclarationsForWhereCriteriaStatement(argumentFields, "                    "));
                WriteLine("                    OracleDataReader result = oracleCommand.ExecuteReader(CommandBehavior.SingleResult);");
                WriteLine("                    return new " + className + "( result );");
                WriteLine("                }");
                WriteLine("            }");
                WriteLine("            catch (Exception exc)");
                WriteLine("            {");
                WriteLine("                LogException(exc, MethodBase.GetCurrentMethod());");
                WriteLine("                throw;");
                WriteLine("            }");
                WriteLine("        }");
                WriteLine();
                WriteLine();
                if (tabular is ProjectTable)
                {
                    ProjectTable projectTable = (ProjectTable)tabular;
                    if ((projectTable.IsTimeSeries)&&(projectTable.IsTagValue == false))
                    {
                        methodName = MethodNamesHelper.GetCreateReaderForTimeStampMethodName(index);
                        if (createMethodMethods.Contains(methodName))
                        {
                            return;
                        }
                        createMethodMethods.Add(methodName);
                        WriteLine("        public static " + className + " " + methodName + "( " + argumentDeclaration + ")");
                        WriteLine("        {");
                        WriteLine("            " + className + " result = " + methodName + "( GetConnection(), DEFAULT_QUALIFIED_DBNAME," + arguments + ");");
                        WriteLine("            return result;");
                        WriteLine("        }");
                        WriteLine();
                        WriteLine("        public static " + className + " " + methodName + "( OracleConnection oracleConnection, " + argumentDeclaration + ")");
                        WriteLine("        {");
                        WriteLine("            " + className + " result = " + methodName + "( oracleConnection, DEFAULT_QUALIFIED_DBNAME," + arguments + ");");
                        WriteLine("            return result;");
                        WriteLine("        }");
                        WriteLine();
                        WriteLine("        public static " + className + " " + methodName + "( string qualifiedDBName, " + argumentDeclaration + ")");
                        WriteLine("        {");
                        WriteLine("            " + className + " result = " + methodName + "( GetConnection(), qualifiedDBName," + arguments + ");");
                        WriteLine("            return result;");
                        WriteLine("        }");
                        WriteLine();
                        WriteLine("        public static " + className + " " + methodName + "( OracleConnection oracleConnection, string qualifiedDBName," + argumentDeclaration + ")");
                        WriteLine("        {");
                        WriteLine("            try");
                        WriteLine("            {");
                        WriteLine("                OracleCommand oracleCommand = oracleConnection.CreateCommand();");
                        WriteLine("                using (oracleCommand)");
                        WriteLine("                {");
                        WriteLine("                    oracleCommand.BindByName = true;");
                        WriteLine(OracleSqlSelectHelper.GetWhereCriteriaStatementForTimeStamp(argumentFields, "                    "));
                        WriteLine("                    string selectStatement = string.Format( FULL_SELECT + queryFilter, qualifiedDBName);");
                        WriteLine("                    oracleCommand.CommandText = selectStatement;");
                        WriteLine();
                        WriteLine(OracleSqlSelectHelper.GetParameterDeclarationsForWhereCriteriaStatement(argumentFields, "                    "));
                        WriteLine("                    OracleDataReader result = oracleCommand.ExecuteReader(CommandBehavior.SingleResult);");
                        WriteLine("                    return new " + className + "( result );");
                        WriteLine("                }");
                        WriteLine("            }");
                        WriteLine("            catch (Exception exc)");
                        WriteLine("            {");
                        WriteLine("                LogException(exc, MethodBase.GetCurrentMethod());");
                        WriteLine("                throw;");
                        WriteLine("            }");
                        WriteLine("        }");
                        WriteLine();
                        WriteLine();
                    }
                }
            }
        }

        public void GenerateCreateByReferenceMethod(ProjectTabularReference reference)
        {
            string className = tabular.GetReaderClassName();
            if (reference != null)
            {
                string methodName = MethodNamesHelper.GetCreateReaderMethodName(reference);
                if (createMethodMethods.Contains(methodName))
                {
                    return;
                }
                createMethodMethods.Add(methodName);
                List<ProjectTabularField> argumentFields = reference.GetFieldList();

                string arguments = ColumnHelper.GetCommaSeparatedPropertyFieldNameList(argumentFields);
                string argumentDeclaration = ColumnHelper.GetArgumentDeclarationList(argumentFields);

                WriteLine("        public static " + className + " " + methodName + "( " + argumentDeclaration + ")");
                WriteLine("        {");
                WriteLine("            " + className + " result = " + methodName + "( DEFAULT_QUALIFIED_DBNAME," + arguments + ");");
                WriteLine("            return result;");
                WriteLine("        }");
                WriteLine();
                WriteLine("        public static " + className + " " + methodName + "( string qualifiedDBName," + argumentDeclaration + ")");
                WriteLine("        {");
                WriteLine("            try");
                WriteLine("            {");
                WriteLine("                string fullSelect = string.Format(FULL_SELECT, qualifiedDBName);");
                WriteLine("                OracleConnection oracleConnection = GetConnection();");
                WriteLine("                OracleCommand oracleCommand = oracleConnection.CreateCommand();");
                WriteLine("                using (oracleCommand)");
                WriteLine("                {");
                WriteLine("                    oracleCommand.BindByName = true;");
                WriteLine(OracleSqlSelectHelper.GetWhereCriteriaStatement(argumentFields, "                    "));
                WriteLine("                    string selectStatement = fullSelect + queryFilter + \" ORDER BY \" + KEY_FIELDS;");
                WriteLine("                    oracleCommand.CommandText = selectStatement;");
                WriteLine();
                WriteLine(OracleSqlSelectHelper.GetParameterDeclarationsForWhereCriteriaStatement(argumentFields, "                    "));
                WriteLine("                    OracleDataReader result = oracleCommand.ExecuteReader(CommandBehavior.SingleResult);");
                WriteLine("                    return new " + className + "( result );");
                WriteLine("                }");
                WriteLine("            }");
                WriteLine("            catch (Exception exc)");
                WriteLine("            {");
                WriteLine("                LogException(exc, MethodBase.GetCurrentMethod());");
                WriteLine("                throw;");
                WriteLine("            }");
                WriteLine("        }");
                WriteLine();
                WriteLine();
            }
        }


        private void GenerateTagValueCreateMethods()
        {
            if (tabular is ProjectTable)
            {
                ProjectTable projectTable = (ProjectTable)tabular;

                if (projectTable.IsTagValue)
                {
                    string className = tabular.GetReaderClassName();
                    string methodName = "Create" + tabular.GetBaseName() + "ForTimeStamp";

                    WriteLine("        public static " + className + " " + methodName + "( OracleConnection oracleConnection, string qualifiedDBName, DateTime ts )");
                    WriteLine("        {");
                    WriteLine("            try");
                    WriteLine("            {");
                    WriteLine("                string fullSelect = string.Format(FULL_SELECT, qualifiedDBName);");
                    WriteLine("                string queryFilter = string.Format(\" WHERE TS = (SELECT MAX(TS) FROM {0} WHERE TS <= :timeStamp_ )\", qualifiedDBName);");
                    WriteLine("                OracleCommand oracleCommand = oracleConnection.CreateCommand();");
                    WriteLine("                using (oracleCommand)");
                    WriteLine("                {");
                    WriteLine("                    oracleCommand.BindByName = true;");
                    WriteLine("                    string selectStatement = fullSelect + queryFilter;");
                    WriteLine("                    oracleCommand.CommandText = selectStatement;");
                    WriteLine();
                    WriteLine("                    oracleCommand.Parameters.Add(\":timeStamp_\", OracleDbType.Decimal).Value = ts.ToUniversalTime().Ticks;");
                    WriteLine();
                    WriteLine("                    OracleDataReader result = oracleCommand.ExecuteReader(CommandBehavior.SingleResult);");
                    WriteLine("                    return new " + className + "( result );");
                    WriteLine("                }");
                    WriteLine("            }");
                    WriteLine("            catch (Exception exc)");
                    WriteLine("            {");
                    WriteLine("                LogException(exc, MethodBase.GetCurrentMethod());");
                    WriteLine("                throw;");
                    WriteLine("            }");
                    WriteLine("        }");
                    WriteLine();

                    methodName = "Create" + tabular.GetBaseName() + "ForLastTimeStamp";

                    WriteLine("        public static " + className + " " + methodName + "( OracleConnection oracleConnection, string qualifiedDBName )");
                    WriteLine("        {");
                    WriteLine("            try");
                    WriteLine("            {");
                    WriteLine("                string fullSelect = string.Format(FULL_SELECT, qualifiedDBName);");
                    WriteLine("                string queryFilter = string.Format(\" WHERE TS = ( SELECT MAX(TS) FROM {0} )\", qualifiedDBName);");
                    WriteLine("                OracleCommand oracleCommand = oracleConnection.CreateCommand();");
                    WriteLine("                using (oracleCommand)");
                    WriteLine("                {");
                    WriteLine("                    oracleCommand.BindByName = true;");
                    WriteLine("                    string selectStatement = fullSelect + queryFilter;");
                    WriteLine("                    oracleCommand.CommandText = selectStatement;");
                    WriteLine();
                    WriteLine("                    OracleDataReader result = oracleCommand.ExecuteReader(CommandBehavior.SingleResult);");
                    WriteLine("                    return new " + className + "( result );");
                    WriteLine("                }");
                    WriteLine("            }");
                    WriteLine("            catch (Exception exc)");
                    WriteLine("            {");
                    WriteLine("                LogException(exc, MethodBase.GetCurrentMethod());");
                    WriteLine("                throw;");
                    WriteLine("            }");
                    WriteLine("        }");
                    WriteLine();


                    methodName = "Create" + tabular.GetBaseName() + "ForInterval";

                    WriteLine("        public static " + className + " " + methodName + "( OracleConnection oracleConnection, string qualifiedDBName, DateTime startOfInterval, DateTime endOfInterval )");
                    WriteLine("        {");
                    WriteLine("            try");
                    WriteLine("            {");
                    WriteLine("                string fullSelect = string.Format(FULL_SELECT, qualifiedDBName);");
                    WriteLine("                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;");
                    WriteLine("                OracleCommand oracleCommand = oracleConnection.CreateCommand();");
                    WriteLine("                using (oracleCommand)");
                    WriteLine("                {");
                    WriteLine("                    oracleCommand.BindByName = true;");
                    WriteLine("                    string selectStatement = fullSelect + queryFilter;");
                    WriteLine("                    oracleCommand.CommandText = selectStatement;");
                    WriteLine();
                    WriteLine("                    oracleCommand.Parameters.Add(\":startTimeStamp\", OracleDbType.Decimal).Value = startOfInterval.ToUniversalTime().Ticks;");
                    WriteLine("                    oracleCommand.Parameters.Add(\":endTimeStamp\", OracleDbType.Decimal).Value = endOfInterval.ToUniversalTime().Ticks;");
                    WriteLine();
                    WriteLine("                    OracleDataReader result = oracleCommand.ExecuteReader(CommandBehavior.SingleResult);");
                    WriteLine("                    return new " + className + "( result );");
                    WriteLine("                }");
                    WriteLine("            }");
                    WriteLine("            catch (Exception exc)");
                    WriteLine("            {");
                    WriteLine("                LogException(exc, MethodBase.GetCurrentMethod());");
                    WriteLine("                throw;");
                    WriteLine("            }");
                    WriteLine("        }");
                    WriteLine();

                    WriteLine();

                }
            }

        }



    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Espen Harlinn
Architect Sea Surveillance AS
Norway Norway
Chief Architect - Sea Surveillance AS.

Specializing in integrated operations and high performance computing solutions.

I’ve been fooling around with computers since the early eighties, I’ve even done work on CP/M and MP/M.

Wrote my first “real” program on a BBC micro model B based on a series in a magazine at that time. It was fun and I got hooked on this thing called programming ...

A few Highlights:

  • High performance application server development
  • Model Driven Architecture and Code generators
  • Real-Time Distributed Solutions
  • C, C++, C#, Java, TSQL, PL/SQL, Delphi, ActionScript, Perl, Rexx
  • Microsoft SQL Server, Oracle RDBMS, IBM DB2, PostGreSQL
  • AMQP, Apache qpid, RabbitMQ, Microsoft Message Queuing, IBM WebSphereMQ, Oracle TuxidoMQ
  • Oracle WebLogic, IBM WebSphere
  • Corba, COM, DCE, WCF
  • AspenTech InfoPlus.21(IP21), OsiSoft PI


More information about what I do for a living can be found at: harlinn.com or LinkedIn

You can contact me at espen@harlinn.no

Comments and Discussions

Permalink
Advertise
Privacy
Cookies
Terms of Use
Layout: fixed | fluid

Posted 15 Mar 2013

Article Copyright 2013 by Espen Harlinn
Everything else Copyright © CodeProject, 1999-2024

Web04 2.8:2024-04-02:1