using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using Harlinn.Oracle.DBTool.Types.Projects;
using Harlinn.Oracle.DBTool.Types.CSharp;
namespace Harlinn.Oracle.DBTool.Generators.Utils
{
public class OracleSqlUpdateHelper
{
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 GenerateSQLUpdateStatement(ProjectTable table, string indent)
{
try
{
StringBuilder sb = new StringBuilder();
string result = string.Empty;
List<ProjectTabularField> fields = table.GetFieldList();
sb.AppendLine("\"UPDATE {0} SET \" +");
int fieldCount = fields.Count;
List<ProjectTabularField> fieldsToUpdate = new List<ProjectTabularField>();
for (int i = 0; i < fieldCount; i++)
{
ProjectTabularField field = fields[i];
if (field.InPrimaryKey == false )
{
fieldsToUpdate.Add(field);
}
}
List<ProjectTabularField> fieldsForOutput = new List<ProjectTabularField>();
for (int i = 0; i < fieldCount; i++)
{
ProjectTabularField field = fields[i];
if (field.Concurrency)
{
fieldsForOutput.Add(field);
}
}
List<ProjectTabularField> fieldsForWhereStatement = new List<ProjectTabularField>();
for (int i = 0; i < fieldCount; i++)
{
ProjectTabularField field = fields[i];
if ((field.InPrimaryKey == true) || (field.Concurrency == true))
{
fieldsForWhereStatement.Add(field);
}
}
for (int i = 0; i < fieldsToUpdate.Count; i++)
{
ProjectTabularField field = fieldsToUpdate[i];
string fieldName = field.Name;
string parameterName = field.DatabaseParameterName;
if (i < (fieldsToUpdate.Count - 1))
{
if (field.Concurrency == true)
{
sb.AppendLine(indent + " \"" + fieldName + " = " + fieldName + " + 1, \" +");
}
else
{
sb.AppendLine(indent + " \"" + fieldName + " = " + parameterName + " , \" +");
}
}
else
{
if (field.Concurrency == true)
{
sb.AppendLine(indent + " \"" + fieldName + " = " + fieldName + " + 1 \" +");
}
else
{
sb.AppendLine(indent + " \"" + fieldName + " = " + parameterName + " \" +");
}
}
}
sb.AppendLine(indent + " \" WHERE \" + ");
for (int i = 0; i < fieldsForWhereStatement.Count; i++)
{
ProjectTabularField field = fieldsForWhereStatement[i];
string fieldName = field.Name;
string parameterName = field.DatabaseParameterName;
if (i < (fieldsForWhereStatement.Count - 1))
{
sb.AppendLine(indent + " \"(" + fieldName + " = " + parameterName + ") AND \" +");
}
else
{
if (fieldsForWhereStatement.Count > 0)
{
if (fieldsForOutput.Count > 0)
{
sb.AppendLine(indent + " \"(" + fieldName + " = " + parameterName + ") \" +");
}
else
{
sb.AppendLine(indent + " \"(" + fieldName + " = " + parameterName + ") \";");
}
}
else
{
if (fieldsForOutput.Count > 0)
{
sb.AppendLine(indent + " \"" + fieldName + " = " + parameterName + " \" +");
}
else
{
sb.AppendLine(indent + " \"" + fieldName + " = " + parameterName + " \";");
}
}
}
}
if (fieldsForOutput.Count > 0)
{
sb.Append(indent + "\" RETURNING ");
for (int i = 0; i < fieldsForOutput.Count; i++)
{
ProjectTabularField column = fieldsForOutput[i];
string columnConstantName = column.Name;
if (i < (fieldsForOutput.Count - 1))
{
sb.Append( columnConstantName + ", ");
}
else
{
sb.Append(columnConstantName + " ");
}
}
sb.Append(" INTO ");
for (int i = 0; i < fieldsForOutput.Count; i++)
{
ProjectTabularField column = fieldsForOutput[i];
string parameterConstantName = column.DatabaseParameterName;
if (i < (fieldsForOutput.Count - 1))
{
sb.Append(parameterConstantName + ", ");
}
else
{
sb.Append(parameterConstantName + "\";");
}
}
}
result = sb.ToString();
return result;
}
catch (Exception exc)
{
LogException(exc, System.Reflection.MethodBase.GetCurrentMethod());
throw;
}
}
public static string GetParameterDeclarationsForUpdate(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.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 (column.Concurrency == false)
{
if (nullableCSharpType)
{
sb.AppendLine(indent + " " + sqlParameterName + ".Value = " + TypeHelper.GetCommandParameterConversion(column.PropertyType, column.ReaderType, elementPropertyAccessor + ".Value") + ";");
}
else
{
sb.AppendLine(indent + " " + sqlParameterName + ".Value = " + TypeHelper.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 (column.Concurrency == false)
{
if (nullableCSharpType)
{
sb.AppendLine(indent + sqlParameterName + ".Value = " + TypeHelper.GetCommandParameterConversion(column.PropertyType, column.ReaderType, elementPropertyAccessor + ".Value") + ";");
}
else
{
sb.AppendLine(indent + sqlParameterName + ".Value = " + TypeHelper.GetCommandParameterConversion(column.PropertyType, column.ReaderType, elementPropertyAccessor) + ";");
}
}
else
{
sb.AppendLine(indent + sqlParameterName + ".Direction = ParameterDirection.InputOutput;");
sb.AppendLine(indent + sqlParameterName + ".Value = " + TypeHelper.GetCommandParameterConversion(column.PropertyType, column.ReaderType, elementPropertyAccessor) + ";");
}
}
sb.AppendLine();
}
}
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.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 (column.Concurrency == false)
{
if (nullableCSharpType)
{
//sb.AppendLine(indent + sqlParameterName + ".Value = " + argumentName + ".Value;");
sb.AppendLine(indent + sqlParameterName + ".Value = " + TypeHelper.GetCommandParameterConversion(column.PropertyType, column.ReaderType, elementPropertyAccessor + ".Value") + ";");
}
else
{
//sb.AppendLine(indent + sqlParameterName + ".Value = " + argumentName + ";");
sb.AppendLine(indent + sqlParameterName + ".Value = " + TypeHelper.GetCommandParameterConversion(column.PropertyType, column.ReaderType, elementPropertyAccessor) + ";");
}
}
else
{
sb.AppendLine(indent + sqlParameterName + ".Direction = ParameterDirection.InputOutput;");
sb.AppendLine(indent + sqlParameterName + ".Value = " + TypeHelper.GetCommandParameterConversion(column.PropertyType, column.ReaderType, elementPropertyAccessor) + ";");
}
sb.AppendLine();
}
}
return sb.ToString();
}
catch (Exception exc)
{
LogException(exc, System.Reflection.MethodBase.GetCurrentMethod());
throw;
}
}
}
}