using System;
using System.Collections.Generic;
using System.Text;
using Harlinn.Oracle.DBTool.Types.Projects;
using Harlinn.Oracle.DBTool.Types.CSharp;
using System.Reflection;
namespace Harlinn.Oracle.DBTool.Generators.Utils
{
public class OracleSqlSelectHelper
{
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 GetWhereCriteriaStatement(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();
if (hasNullableColumns)
{
sb.AppendLine(indent + "// The query is against columns that may be NULL");
sb.AppendLine(indent + "StringBuilder sb = new StringBuilder( );");
sb.AppendLine(indent + "sb.AppendLine(\" WHERE \");");
for (int i = 0; i < columnCount; i++)
{
ProjectTabularField column = columns[i];
bool nullableCSharpType = TypeHelper.GetIsCSharpNullableType(column);
string argumentName = column.PropertyFieldName;
string parameterConstantName = ":" + column.PropertyFieldName;
string fieldNameConstantName = column.Name;
if (i > 0)
{
sb.AppendLine(indent + "sb.AppendLine(\" AND \");");
}
if (columnCount > 1)
{
sb.AppendLine(indent + "sb.Append( \" ( \");");
}
if (column.AllowDBNull)
{
if (nullableCSharpType)
{
sb.AppendLine(indent + "if( " + argumentName + ".HasValue )");
}
else
{
sb.AppendLine(indent + "if( " + argumentName + " != null )");
}
sb.AppendLine(indent + "{");
sb.AppendLine(indent + " sb.Append( \"" + fieldNameConstantName + " = " + parameterConstantName + "\");");
sb.AppendLine(indent + "}");
sb.AppendLine(indent + "else");
sb.AppendLine(indent + "{");
sb.AppendLine(indent + " sb.Append( \"" + fieldNameConstantName + " IS NULL \");");
sb.AppendLine(indent + "}");
}
else
{
sb.AppendLine(indent + "sb.Append( \"" + fieldNameConstantName + " = " + parameterConstantName + "\");");
}
if (columnCount > 1)
{
sb.AppendLine(indent + "sb.AppendLine(\" ) \");");
}
}
sb.AppendLine();
sb.AppendLine(indent + "string queryFilter = sb.ToString();");
}
else
{
sb.Append(indent + "string queryFilter = \" WHERE ");
for (int i = 0; i < columnCount; i++)
{
ProjectTabularField column = columns[i];
string parameterConstantName = ":" + column.PropertyFieldName;
string fieldNameConstantName = column.Name;
if (i > 0)
{
sb.Append(" AND ");
}
if (columnCount > 1)
{
sb.Append("( ");
sb.Append(fieldNameConstantName + " = " + parameterConstantName);
if (i < (columnCount - 1))
{
sb.Append(" )");
}
else
{
sb.Append(" )\";");
}
}
else
{
sb.Append(fieldNameConstantName + " = " + parameterConstantName + "\";");
}
}
}
return sb.ToString();
}
catch (Exception exc)
{
LogException(exc, System.Reflection.MethodBase.GetCurrentMethod());
throw;
}
}
public static string GetWhereCriteriaStatementForTimeStamp(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();
if (hasNullableColumns)
{
sb.AppendLine(indent + "// The query is against columns that may be NULL");
sb.AppendLine(indent + "StringBuilder sb = new StringBuilder( );");
sb.AppendLine(indent + "sb.AppendLine(\" WHERE \");");
for (int i = 0; i < columnCount; i++)
{
ProjectTabularField column = columns[i];
bool nullableCSharpType = TypeHelper.GetIsCSharpNullableType(column);
string argumentName = column.PropertyFieldName;
string parameterConstantName = ":" + column.PropertyFieldName;
string fieldNameConstantName = column.Name;
if (i > 0)
{
sb.AppendLine(indent + "sb.AppendLine(\" AND \");");
}
if (columnCount > 1)
{
sb.AppendLine(indent + "sb.Append( \" ( \");");
}
if (i < (columnCount - 1))
{
if (column.AllowDBNull)
{
if (nullableCSharpType)
{
sb.AppendLine(indent + "if( " + argumentName + ".HasValue )");
}
else
{
sb.AppendLine(indent + "if( " + argumentName + " != null )");
}
sb.AppendLine(indent + "{");
sb.AppendLine(indent + " sb.Append( \"" + fieldNameConstantName + " = " + parameterConstantName + "\");");
sb.AppendLine(indent + "}");
sb.AppendLine(indent + "else");
sb.AppendLine(indent + "{");
sb.AppendLine(indent + " sb.Append( \"" + fieldNameConstantName + " IS NULL \");");
sb.AppendLine(indent + "}");
}
else
{
sb.AppendLine(indent + "sb.Append( \"" + fieldNameConstantName + " = " + parameterConstantName + "\");");
}
}
else
{
sb.AppendLine(indent + "sb.Append( \"" + fieldNameConstantName + " = (SELECT MAX(" + fieldNameConstantName + ") FROM {0} WHERE " + fieldNameConstantName + " <= " + parameterConstantName + " )\");");
}
if (columnCount > 1)
{
sb.AppendLine(indent + "sb.AppendLine(\" ) \");");
}
}
sb.AppendLine();
sb.AppendLine(indent + "string queryFilter = sb.ToString();");
}
else
{
sb.Append(indent + "string queryFilter = \" WHERE ");
for (int i = 0; i < columnCount; i++)
{
ProjectTabularField column = columns[i];
string parameterConstantName = ":" + column.PropertyFieldName;
string fieldNameConstantName = column.Name;
if (i > 0)
{
sb.Append(" AND ");
}
if (i < (columnCount - 1))
{
if (columnCount > 1)
{
sb.Append("( ");
sb.Append(fieldNameConstantName + " = " + parameterConstantName);
if (i < (columnCount - 1))
{
sb.Append(" )");
}
else
{
sb.Append(" )\";");
}
}
else
{
sb.Append(fieldNameConstantName + " = " + parameterConstantName + "\";");
}
}
else
{
if (columnCount > 1)
{
sb.Append("( ");
sb.Append(fieldNameConstantName + " = (SELECT MAX(" + fieldNameConstantName + ") FROM {0} WHERE " + fieldNameConstantName + " <= " + parameterConstantName + " )");
if (i < (columnCount - 1))
{
sb.Append(" )");
}
else
{
sb.Append(" )\";");
}
}
else
{
sb.Append(fieldNameConstantName + " = (SELECT MAX(" + fieldNameConstantName + ") FROM {0} WHERE " + fieldNameConstantName + " <= " + parameterConstantName + " )\";");
}
}
}
}
return sb.ToString();
}
catch (Exception exc)
{
LogException(exc, System.Reflection.MethodBase.GetCurrentMethod());
throw;
}
}
public static string GetParameterDeclarationsForWhereCriteriaStatement(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];
bool nullableCSharpType = TypeHelper.GetIsCSharpNullableType(column);
string argumentName = column.PropertyFieldName;
string sqlParameterName = argumentName + "Parameter";
string parameterConstantName = ":" + column.PropertyFieldName;
string dataTypeConstantName = "OracleDbType." + column.ProviderType.ToString();
if (column.AllowDBNull)
{
if (nullableCSharpType)
{
sb.AppendLine(indent + "if( " + argumentName + ".HasValue )");
}
else
{
sb.AppendLine(indent + "if( " + argumentName + " != null )");
}
sb.AppendLine(indent + "{");
sb.AppendLine(indent + " OracleParameter " + sqlParameterName + " = oracleCommand.Parameters.Add(new OracleParameter( \"" + parameterConstantName + "\", " + dataTypeConstantName + " ));");
if (nullableCSharpType)
{
sb.AppendLine(indent + " " + sqlParameterName + ".Value = " + TypeHelper.GetCommandParameterConversion(column.PropertyType, column.ReaderType, argumentName + ".Value") + ";");
}
else
{
sb.AppendLine(indent + " " + sqlParameterName + ".Value = " + TypeHelper.GetCommandParameterConversion(column.PropertyType, column.ReaderType, argumentName) + ";");
}
sb.AppendLine(indent + "}");
}
else
{
sb.AppendLine(indent + "OracleParameter " + sqlParameterName + " = oracleCommand.Parameters.Add(new OracleParameter( \"" + parameterConstantName + "\", " + dataTypeConstantName + " ));");
if (nullableCSharpType)
{
//TypeHelper.GetCommandParameterConversion(column.PropertyType, column.ReaderType, argumentName)
//sb.AppendLine(indent + sqlParameterName + ".Value = " + argumentName + ".Value;");
sb.AppendLine(indent + sqlParameterName + ".Value = " + TypeHelper.GetCommandParameterConversion(column.PropertyType, column.ReaderType, argumentName + ".Value") + ";");
}
else
{
sb.AppendLine(indent + sqlParameterName + ".Value = " + TypeHelper.GetCommandParameterConversion(column.PropertyType, column.ReaderType, argumentName) + ";");
}
}
}
}
else
{
for (int i = 0; i < columnCount; i++)
{
ProjectTabularField column = columns[i];
bool nullableCSharpType = TypeHelper.GetIsCSharpNullableType(column);
string argumentName = column.PropertyFieldName;
string sqlParameterName = argumentName + "Parameter";
string parameterConstantName = ":" + column.PropertyFieldName;
string dataTypeConstantName = "OracleDbType." + column.ProviderType.ToString();
sb.AppendLine(indent + "OracleParameter " + sqlParameterName + " = oracleCommand.Parameters.Add(new OracleParameter( \"" + parameterConstantName + "\", " + dataTypeConstantName + " ));");
if (nullableCSharpType)
{
//sb.AppendLine(indent + sqlParameterName + ".Value = " + argumentName + ".Value;");
sb.AppendLine(indent + sqlParameterName + ".Value = " + TypeHelper.GetCommandParameterConversion(column.PropertyType, column.ReaderType, argumentName + ".Value") + ";");
}
else
{
//sb.AppendLine(indent + sqlParameterName + ".Value = " + argumentName + ";");
sb.AppendLine(indent + sqlParameterName + ".Value = " + TypeHelper.GetCommandParameterConversion(column.PropertyType, column.ReaderType, argumentName) + ";");
}
}
}
return sb.ToString();
}
catch (Exception exc)
{
LogException(exc, System.Reflection.MethodBase.GetCurrentMethod());
throw;
}
}
}
}
Submit an article or tip