public override String CreateQueryOfTableNameDelete(Table table)
{
StringBuilder sb = new StringBuilder(32);
String whereQuery = this.CreateWhereQuery(table.GetPrimaryKeyOrTimestampColumns(), true);
sb.AppendFormat("Create Procedure {0}Delete", table.Name);
sb.AppendLine();
sb.Append("(");
sb.Append(CreateText(table.GetPrimaryKeyOrTimestampColumns(), column => String.Format("@PK_{0}", column.GetDeclareParameterText()), ",", true));
sb.AppendLine(") As");
sb.AppendLine();
sb.AppendFormat("delete [{0}]", table.Name);
sb.AppendLine();
sb.Append(whereQuery);
return sb.ToString();
}
public override String CreateQueryOfTableNameUpdate(Table table)
{
StringBuilder sb = new StringBuilder(32);
List<Column> columns = null;
Column timestampColumn = null;
String whereQuery = this.CreateWhereQuery(table.GetPrimaryKeyOrTimestampColumns(), "T01.", true);
foreach (var column in table.Columns)
{
if (column.DbType.IsTimestamp() == true) timestampColumn = column;
}
sb.AppendFormat("Create Procedure {0}Update", table.Name);
sb.AppendLine();
sb.Append("(");
sb.Append(CreateText(table.Columns, column =>
{
if (column.CanUpdateValueColumn() == true)
{
return column.GetDeclareParameterText();
}
else
{
return "Out " + column.GetDeclareParameterText();
}
}, ",", true));
sb.Append(",");
sb.Append(CreateText(table.GetPrimaryKeyOrTimestampColumns(), column => String.Format("PK_{0}", column.GetDeclareParameterText()), ",", true));
sb.AppendLine(") ");
sb.AppendLine();
sb.AppendLine("Begin");
sb.AppendLine("if(");
sb.Append(CreateText(table.GetPrimaryKeyColumns(), column => String.Format("{0} = PK_{0}", column.Name), "and ", true));
sb.AppendLine(") Then");
columns = table.GetColumns(false, false).ToList();
if (columns.Count == 0)
{
sb.AppendFormat("Set {0} = {0};", table.Columns[0].Name);
sb.AppendLine();
}
else if (columns.Count > 0)
{
sb.AppendFormat("update {0} As T01 set ", table.Name);
sb.AppendLine();
sb.Append(CreateText(columns, column => String.Format("T01.{0} = {0}", column.Name), ",", true));
sb.AppendLine();
sb.Append(whereQuery);
}
sb.AppendLine(";");
sb.AppendLine("else");
sb.AppendFormat("update {0} As T01 set ", table.Name);
sb.AppendLine();
sb.Append(CreateText(table.Columns.Where(column => column.CanUpdateValueColumn()), column => String.Format("T01.{0} = {0}", column.Name), ",", true));
sb.AppendLine();
sb.Append(whereQuery);
sb.AppendLine(";");
sb.AppendLine("end if;");
if (timestampColumn != null)
{
sb.AppendFormat("set {0} = CURRENT_TIMESTAMP();", timestampColumn.Name);
sb.AppendLine();
}
sb.AppendLine("End;");
sb.AppendLine("//");
return sb.ToString();
}
public override String CreateQueryOfTableNameUpdate(Table table)
{
StringBuilder sb = new StringBuilder(32);
List<Column> columns = null;
List<Column> outputColumns = new List<Column>();
String whereQuery = this.CreateWhereQuery(table.GetPrimaryKeyOrTimestampColumns(), true);
foreach (var column in table.Columns)
{
if (column.CanUpdateValueColumn() == false)
{
outputColumns.Add(column);
}
}
sb.AppendFormat("Create Procedure {0}Update", table.Name);
sb.AppendLine();
sb.Append("(");
sb.Append(CreateText(table.Columns, column =>
{
if (column.CanUpdateValueColumn() == true)
{
return "@" + column.GetDeclareParameterText();
}
else
{
return "@" + column.GetDeclareParameterText() + " output";
}
}, ",", true));
sb.Append(",");
sb.Append(CreateText(table.GetPrimaryKeyOrTimestampColumns(), column => String.Format("@PK_{0}", column.GetDeclareParameterText()), ",", true));
sb.AppendLine(") As");
sb.AppendLine();
sb.AppendLine("if(");
sb.Append(CreateText(table.GetPrimaryKeyColumns(), column => String.Format("@{0} = @PK_{0}", column.Name), "and ", true));
sb.AppendLine(")");
columns = table.GetColumns(false, false).ToList();
sb.AppendLine("begin");
if (columns.Count == 0)
{
sb.AppendFormat("Set @{0} = @{0}", table.Columns[0].Name);
sb.AppendLine();
}
else if (columns.Count > 0)
{
sb.AppendFormat("update [{0}] set ", table.Name);
sb.AppendLine();
sb.Append(CreateText(columns, column => String.Format("[{0}] = @{0}", column.Name), ",", true));
sb.AppendLine();
sb.Append(whereQuery);
}
sb.AppendLine("end");
sb.AppendLine("else");
sb.AppendLine("begin");
sb.AppendFormat("update [{0}] set ", table.Name);
sb.AppendLine();
sb.Append(CreateText(table.Columns.Where(column => column.CanUpdateValueColumn()), column => String.Format("[{0}] = @{0}", column.Name), ",", true));
sb.AppendLine();
sb.Append(whereQuery);
sb.AppendLine("end");
if (outputColumns.Count > 0)
{
sb.AppendLine();
sb.Append("select ");
sb.Append(CreateText(outputColumns, column => String.Format("@{0} = {0}", column.Name), ", ", false));
sb.AppendFormat(" from {0} with(nolock)", table.Name);
sb.AppendLine();
sb.AppendLine(this.CreateWhereQuery(table.GetPrimaryKeyColumns(), false));
}
return sb.ToString();
}
