| public static BuildSelectList ( CustomView view, ModelRoot model ) : string | ||
| view | CustomView | |
| model | ModelRoot | |
| return | string | |
public static string GetBody(Table table, ModelRoot model)
{
StringBuilder sb = new StringBuilder();
if (table.AllowModifiedAudit)
{
sb.AppendLine("IF (@" + model.Database.ModifiedDateColumnName + " IS NULL)");
sb.AppendLine("SET @" + model.Database.ModifiedDateColumnName + " = GetDate();");
sb.AppendLine();
}
sb.AppendLine("SET NOCOUNT OFF;");
List <Table> tableList = table.GetTableHierarchy();
foreach (Table t in tableList)
{
sb.AppendLine("UPDATE ");
sb.AppendLine("[" + t.DatabaseName + "] ");
sb.AppendLine("SET");
sb.AppendLine(BuildSetStatement(t, model));
sb.AppendLine("WHERE");
sb.AppendLine("\t" + BuildUpdateWhereStatement(t, model, ((table == t) && table.AllowTimestamp)));
sb.AppendLine();
}
sb.AppendLine("SELECT");
sb.Append(Globals.BuildSelectList(table, model, true));
sb.AppendLine("FROM ");
sb.AppendLine(table.GetFullHierarchyTableJoin());
sb.AppendLine("WHERE");
sb.AppendLine("\t" + BuildSelectWhereStatement(table));
return(sb.ToString());
}
public static string GetBody(Table table, ModelRoot model)
{
StringBuilder sb = new StringBuilder();
if (table.AllowCreateAudit)
{
sb.AppendLine("if (@" + model.Database.CreatedDateColumnName + " IS NULL)");
}
else
{
sb.AppendLine("DECLARE @" + model.Database.CreatedDateColumnName + " datetime");
}
sb.AppendLine("SET @" + model.Database.CreatedDateColumnName + " = GetDate()");
foreach (Column dc in table.PrimaryKeyColumns)
{
if (dc.Identity == IdentityTypeConstants.Code)
{
sb.AppendLine("SET @" + ValidationHelper.MakeDatabaseScriptIdentifier(dc.DatabaseName) + " = (select case when max([" + dc.DatabaseName + "]) is null then 1 else max([" + dc.DatabaseName + "]) + 1 end from [" + Globals.GetTableDatabaseName(model, table) + "])");
}
else if (dc.Identity == IdentityTypeConstants.Database)
{
//sb.AppendLine("DECLARE @" + ValidationHelper.MakeDatabaseScriptIdentifier(dc.DatabaseName) + " " + dc.DataType);
}
}
if (table.ParentTable == null)
{
AppendInsertionStatement(sb, table, model);
}
else
{
List <Table> tableList = table.GetTableHierarchy();
foreach (Table t in tableList)
{
AppendInsertionStatement(sb, t, model);
//On the base table save the primary keys as variables
if (t.ParentTable == null)
{
sb.Append(BuildInheritedPKBaseTableVariables(t, model));
}
}
}
sb.AppendLine();
sb.AppendLine("SELECT ");
sb.AppendLine(Globals.BuildSelectList(table, model, true));
sb.AppendLine("FROM");
sb.AppendLine(table.GetFullHierarchyTableJoin());
sb.AppendLine("WHERE");
sb.AppendLine(" " + BuildInsertSelectWhereStatement(table, model) + ";");
return(sb.ToString());
}
private static string BuildStoredProcedure(Table table, ModelRoot model, List <Column> allColumns)
{
StringBuilder sb = new StringBuilder();
int index = 0;
sb.AppendLine();
sb.AppendLine("CREATE TABLE #tmpTable");
sb.AppendLine("(");
foreach (Column dc in table.PrimaryKeyColumns)
{
sb.Append("[" + dc.DatabaseName + "]");
sb.Append(" ");
sb.Append(dc.DataType);
if (StringHelper.Match(dc.DataType.ToString(), "binary", true) ||
StringHelper.Match(dc.DataType.ToString(), "char", true) ||
StringHelper.Match(dc.DataType.ToString(), "decimal", true) ||
StringHelper.Match(dc.DataType.ToString(), "nchar", true) ||
StringHelper.Match(dc.DataType.ToString(), "numeric", true) ||
StringHelper.Match(dc.DataType.ToString(), "nvarchar", true) ||
StringHelper.Match(dc.DataType.ToString(), "varbinary", true) ||
StringHelper.Match(dc.DataType.ToString(), "varchar", true))
{
sb.Append("(" + dc.GetLengthString() + ")");
}
if (index < table.PrimaryKeyColumns.Count - 1)
{
sb.Append(",");
}
sb.AppendLine();
index++;
}
//sb.Remove(sb.Length - 3, 3);
sb.AppendLine(")");
sb.AppendLine();
sb.AppendLine("DECLARE @total__ivqatedr int");
sb.AppendLine("DECLARE @orderByColumnIndex int");
sb.AppendLine("-- remove top x values from the temp table based upon the specific page requested");
sb.AppendLine("SET @total__ivqatedr = (@pageSize * @page)");
sb.AppendLine("IF (@total__ivqatedr <> 0)");
sb.AppendLine("BEGIN");
sb.AppendLine(" SET ROWCOUNT @total__ivqatedr");
sb.AppendLine("END");
sb.AppendLine("INSERT INTO #tmpTable");
sb.AppendLine("(");
sb.Append(Globals.BuildPrimaryKeySelectList(model, table, false));
sb.AppendLine(")");
//SELECT CLAUSE
sb.AppendLine("SELECT");
sb.Append(Globals.BuildPrimaryKeySelectList(model, table, true));
sb.AppendLine("FROM");
sb.AppendLine(table.GetFullHierarchyTableJoin());
sb.AppendLine("WHERE");
for (int ii = 0; ii < allColumns.Count; ii++)
{
Column column = allColumns[ii];
//If this is text then do a like, other wise equals
string comparer = "=";
if (ModelHelper.IsTextType(column.DataType))
{
comparer = "LIKE";
}
string tableName = Globals.GetTableDatabaseName(model, (Table)column.ParentTableRef.Object);
sb.Append(" (@orderByColumn = '" + column.DatabaseName + "' and (((@filter is null) or ([" + tableName + "].[" + column.DatabaseName + "] is null)) or (@filter is not null and [" + tableName + "].[" + column.DatabaseName + "] " + comparer + " @filter)))");
if (ii < allColumns.Count - 1)
{
sb.AppendLine();
sb.Append("or");
}
sb.AppendLine();
}
//ORDER BY CLAUSE
sb.AppendLine("ORDER BY");
for (int ii = 0; ii < allColumns.Count; ii++)
{
Column column = allColumns[ii];
string tableName = Globals.GetTableDatabaseName(model, (Table)column.ParentTableRef.Object);
sb.AppendLine(" CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN '" + column.DatabaseName + "' THEN [" + tableName + "].[" + column.DatabaseName + "] END END DESC, ");
sb.Append(" CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN '" + column.DatabaseName + "' THEN [" + tableName + "].[" + column.DatabaseName + "] END END");
if (ii < allColumns.Count - 1)
{
sb.Append(", ");
}
sb.AppendLine();
}
sb.AppendLine();
sb.AppendLine("-- set @count based on the rows moved in the previous statement");
//sb.AppendLine("SET @count = ( SELECT count(*) FROM [#tmpTable] )" );
//REPEAT SELECT CLAUSE FOR COUNT
sb.AppendLine("SET ROWCOUNT 0");
sb.AppendLine("SET @count = (");
sb.AppendLine("SELECT count(*)");
sb.AppendLine("FROM");
sb.AppendLine(table.GetFullHierarchyTableJoin());
sb.AppendLine("WHERE");
for (int ii = 0; ii < allColumns.Count; ii++)
{
Column column = allColumns[ii];
string tableName = Globals.GetTableDatabaseName(model, (Table)column.ParentTableRef.Object);
string comparer = "=";
if (ModelHelper.IsTextType(column.DataType))
{
comparer = "LIKE";
}
sb.Append(" (@orderByColumn = '" + column.DatabaseName + "' and (((@filter is null) or ([" + tableName + "].[" + column.DatabaseName + "] is null)) or (@filter is not null and [" + tableName + "].[" + column.DatabaseName + "] " + comparer + " @filter)))");
if (ii < allColumns.Count - 1)
{
sb.AppendLine();
sb.Append("or");
}
sb.AppendLine();
}
sb.AppendLine(")");
sb.AppendLine();
sb.AppendLine("-- remove top x values from the temp table based upon the specific page requested");
sb.AppendLine("SET @total__ivqatedr = (@pageSize * @page) - @pageSize");
sb.AppendLine("IF (@total__ivqatedr <> 0)");
sb.AppendLine("BEGIN");
sb.AppendLine(" SET ROWCOUNT @total__ivqatedr");
sb.AppendLine(" DELETE FROM #tmpTable");
sb.AppendLine("END");
sb.AppendLine();
sb.AppendLine("-- return the number of rows requested as the page size");
sb.AppendLine("SET ROWCOUNT @pageSize");
sb.AppendLine("SELECT");
sb.Append(Globals.BuildSelectList(table, model, true));
sb.AppendLine("FROM");
sb.AppendLine(" [#tmpTable]");
sb.Append(" INNER JOIN " + table.GetFullHierarchyTableJoin() + " ON ");
bool pkFirstTime = true;
foreach (Column pkColumn in table.PrimaryKeyColumns)
{
if (!pkFirstTime)
{
sb.AppendLine(" AND");
}
else
{
pkFirstTime = false;
}
sb.AppendFormat("#tmpTable.[{0}] = [{1}].[{0}]", pkColumn.DatabaseName.ToLower(), Globals.GetTableDatabaseName(model, table).ToUpper());
}
sb.AppendLine();
sb.AppendLine("ORDER BY");
for (int ii = 0; ii < allColumns.Count; ii++)
{
Column column = allColumns[ii];
string tableName = Globals.GetTableDatabaseName(model, (Table)column.ParentTableRef.Object);
sb.AppendLine(" CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN '" + column.DatabaseName + "' THEN [" + tableName + "].[" + column.DatabaseName + "] END END DESC, ");
sb.Append(" CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN '" + column.DatabaseName + "' THEN [" + tableName + "].[" + column.DatabaseName + "] END END");
if (ii < allColumns.Count - 1)
{
sb.Append(", ");
}
sb.AppendLine();
}
sb.AppendLine();
sb.AppendLine("DROP TABLE #tmpTable");
sb.AppendLine();
sb.AppendLine("GO");
sb.AppendLine("SET QUOTED_IDENTIFIER OFF ");
sb.AppendLine("GO");
sb.AppendLine("SET ANSI_NULLS ON ");
sb.AppendLine("GO");
return(sb.ToString());
}
