DBTool for Oracle - Part 1

using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using Harlinn.Common;
using Harlinn.DBTool.Projects;


namespace Harlinn.DBTool.CodeGenerators.Utils
{
    public class OracleSqlInsertHelper
    {
        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 static string GenerateSQLInsertStatement(ProjectTable table, string indent)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                string result = string.Empty;

                List<ProjectTabularField> fields = table.GetFieldList();
                int fieldCount = fields.Count;

                List<ProjectTabularField> outputFields = new List<ProjectTabularField>();


                string databaseFields = ColumnHelper.GetCommaSeparatedFieldList(fields);
                sb.AppendLine("\"INSERT INTO {0}(" + databaseFields + ") \" +");

                sb.Append(indent + "\" VALUES(");

                for (int i = 0; i < fieldCount; i++)
                {
                    ProjectTabularField field = fields[i];
                    if (field.Concurrency)
                    {
                        sb.Append("0");
                    }
                    else if (string.IsNullOrWhiteSpace(field.Sequence) == false)
                    {
                        sb.Append(field.Sequence + ".NEXTVAL");
                        outputFields.Add(field);
                    }
                    else
                    {
                        sb.Append(field.DatabaseParameterName);
                    }
                    if (i < (fieldCount - 1))
                    {
                        sb.Append(", ");
                    }
                    else
                    {
                        sb.Append(")\"");
                        if (outputFields.Count > 0)
                        {
                            sb.AppendLine(" +");
                        }
                    }
                }

                if (outputFields.Count > 0)
                {
                    sb.Append(indent + "\" RETURNING ");
                    for (int i = 0; i < outputFields.Count; i++)
                    {
                        ProjectTabularField field = outputFields[i];

                        sb.Append(field.Name);

                        if (i < (outputFields.Count - 1))
                        {
                            sb.Append(", ");
                        }

                    }
                    sb.Append(" INTO ");
                    for (int i = 0; i < outputFields.Count; i++)
                    {
                        ProjectTabularField field = outputFields[i];

                        sb.Append(field.DatabaseParameterName);

                        if (i < (outputFields.Count - 1))
                        {
                            sb.Append(", ");
                        }
                        else
                        {
                            sb.Append("\"");
                        }
                    }
                }

                result = sb.ToString();

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

        public static string GetParameterDeclarationsForInsert(List<ProjectTabularField> columns, string indent)
        {
            try
            {
                bool hasNullableColumns = false;
                int columnCount = columns.Count;
                for (int i = 0; i < columnCount; i++)
                {
                    ProjectTabularField column = columns[i];
                    if (column.AllowDBNull)
                    {
                        hasNullableColumns = true;
                        break;
                    }
                }

                StringBuilder sb = new StringBuilder();

                sb.AppendLine();

                if (hasNullableColumns)
                {


                    for (int i = 0; i < columnCount; i++)
                    {
                        ProjectTabularField column = columns[i];
                        if (column.Concurrency == false)
                        {
                            bool nullableCSharpType = GeneratorTypeHelper.GetIsCSharpNullableType(column);
                            string argumentName = column.PropertyFieldName;
                            string sqlParameterName = argumentName + "Parameter";
                            string parameterConstantName = column.DatabaseParameterName;
                            string dataTypeConstantName = "OracleDbType." + column.ProviderType.ToString();

                            string elementPropertyAccessor = "element." + column.PropertyName;

                            if (column.AllowDBNull)
                            {

                                sb.AppendLine(indent + "OracleParameter " + sqlParameterName + " = oracleCommand.Parameters.Add(new OracleParameter( \"" + parameterConstantName + "\", " + dataTypeConstantName + " ));");
                                if (nullableCSharpType)
                                {
                                    sb.AppendLine(indent + "if( " + elementPropertyAccessor + ".HasValue )");
                                }
                                else
                                {
                                    sb.AppendLine(indent + "if( " + elementPropertyAccessor + " != null )");
                                }
                                sb.AppendLine(indent + "{");


                                if (string.IsNullOrWhiteSpace(column.Sequence))
                                {
                                    if (nullableCSharpType)
                                    {
                                        sb.AppendLine(indent + "    " + sqlParameterName + ".Value = " + GeneratorTypeHelper.GetCommandParameterConversion(column.PropertyType, column.ReaderType, elementPropertyAccessor + ".Value") + ";");
                                    }
                                    else
                                    {
                                        sb.AppendLine(indent + "    " + sqlParameterName + ".Value = " + GeneratorTypeHelper.GetCommandParameterConversion(column.PropertyType, column.ReaderType, elementPropertyAccessor) + ";");
                                    }
                                }
                                else
                                {
                                    sb.AppendLine(indent + "    " + sqlParameterName + ".Direction = ParameterDirection.Output;");
                                }

                                sb.AppendLine(indent + "}");
                                sb.AppendLine(indent + "else");
                                sb.AppendLine(indent + "{");
                                sb.AppendLine(indent + "    " + sqlParameterName + ".IsNullable = true;");
                                sb.AppendLine(indent + "    " + sqlParameterName + ".Value = null;");
                                sb.AppendLine(indent + "}");

                            }
                            else
                            {
                                sb.AppendLine(indent + "OracleParameter " + sqlParameterName + " = oracleCommand.Parameters.Add(new OracleParameter( \"" + parameterConstantName + "\", " + dataTypeConstantName + " ));");

                                if (string.IsNullOrWhiteSpace(column.Sequence))
                                {
                                    if (nullableCSharpType)
                                    {
                                        sb.AppendLine(indent + sqlParameterName + ".Value = " + GeneratorTypeHelper.GetCommandParameterConversion(column.PropertyType, column.ReaderType, elementPropertyAccessor + ".Value") + ";");
                                    }
                                    else
                                    {
                                        sb.AppendLine(indent + sqlParameterName + ".Value = " + GeneratorTypeHelper.GetCommandParameterConversion(column.PropertyType, column.ReaderType, elementPropertyAccessor) + ";");
                                    }
                                }
                                else
                                {
                                    sb.AppendLine(indent + sqlParameterName + ".Direction = ParameterDirection.Output;");
                                }
                            }
                            sb.AppendLine();
                        }
                    }

                }
                else
                {
                    for (int i = 0; i < columnCount; i++)
                    {
                        ProjectTabularField column = columns[i];
                        if (column.Concurrency == false)
                        {
                            bool nullableCSharpType = GeneratorTypeHelper.GetIsCSharpNullableType(column);
                            string argumentName = column.PropertyFieldName;
                            string sqlParameterName = argumentName + "Parameter";
                            string parameterConstantName = column.DatabaseParameterName;
                            string dataTypeConstantName = "OracleDbType." + column.ProviderType.ToString();
                            string elementPropertyAccessor = "element." + column.PropertyName;

                            sb.AppendLine(indent + "OracleParameter " + sqlParameterName + " = oracleCommand.Parameters.Add(new OracleParameter( \"" + parameterConstantName + "\", " + dataTypeConstantName + " ));");

                            if (string.IsNullOrWhiteSpace(column.Sequence))
                            {
                                if (nullableCSharpType)
                                {
                                    //sb.AppendLine(indent + sqlParameterName + ".Value = " + argumentName + ".Value;");
                                    sb.AppendLine(indent + sqlParameterName + ".Value = " + GeneratorTypeHelper.GetCommandParameterConversion(column.PropertyType, column.ReaderType, elementPropertyAccessor + ".Value") + ";");
                                }
                                else
                                {
                                    //sb.AppendLine(indent + sqlParameterName + ".Value = " + argumentName + ";");
                                    sb.AppendLine(indent + sqlParameterName + ".Value = " + GeneratorTypeHelper.GetCommandParameterConversion(column.PropertyType, column.ReaderType, elementPropertyAccessor) + ";");
                                }
                            }
                            else
                            {
                                sb.AppendLine(indent + sqlParameterName + ".Direction = ParameterDirection.Output;");
                            }
                            sb.AppendLine();
                        }
                    }
                }
                return sb.ToString();
            }
            catch (Exception exc)
            {
                LogException(exc, System.Reflection.MethodBase.GetCurrentMethod());
                throw;
            }
        }


    }
}