|
public static GetTableDatabaseName ( |
||
| model | ||
| table | ||
| return | string | |
public static string GetBody(Table table, ModelRoot model)
{
if (table.Immutable)
{
return(string.Empty);
}
var sb = new StringBuilder();
sb.AppendLine("DELETE FROM");
sb.AppendLine(" [" + table.GetSQLSchema() + "].[" + Globals.GetTableDatabaseName(model, table) + "] ");
sb.AppendLine("WHERE ");
sb.AppendLine(" " + BuildDeleteWhereStatement(table, model) + ";");
sb.AppendLine();
sb.AppendLine("if (@@RowCount = 0) return;");
sb.AppendLine();
if (table.ParentTable != null)
{
var moduleName = (string.IsNullOrEmpty(model.ModuleName) ? string.Empty : "_" + model.ModuleName);
sb.Append("exec [" + table.ParentTable.GetSQLSchema() + "].[" + model.GetStoredProcedurePrefix() + "_" + table.ParentTable.PascalName + moduleName + "_Delete]");
var pkIndex = 0;
foreach (var column in table.PrimaryKeyColumns.OrderBy(x => x.Name))
{
sb.Append(" @Original_" + column.ToDatabaseCodeIdentifier() + " = @Original_" + column.ToDatabaseCodeIdentifier());
pkIndex++;
if (pkIndex < table.PrimaryKeyColumns.Count)
{
sb.Append(",");
}
}
}
sb.AppendLine();
return(sb.ToString());
}
private static string BuildInsertSelectWhereStatement(Table table, ModelRoot model)
{
var output = new StringBuilder();
var primaryKeyCols = new List <Column>(table.PrimaryKeyColumns.OrderBy(x => x.Name));
for (var ii = 0; ii < primaryKeyCols.Count; ii++)
{
var column = primaryKeyCols[ii];
output.Append("[" + table.GetSQLSchema() + "].[" + Globals.GetTableDatabaseName(model, table) + "].[" + column.DatabaseName + "]");
output.Append(" = ");
if (column.Identity == IdentityTypeConstants.Database)
{
output.Append("SCOPE_IDENTITY()");
}
else
{
output.AppendFormat("@{0}", column.ToDatabaseCodeIdentifier());
}
if (ii < primaryKeyCols.Count - 1)
{
output.Append(" AND" + Environment.NewLine + "\t");
}
}
return(output.ToString());
}
public static string BuildPrimaryKeySelectList(ModelRoot model, Table table, bool qualifiedNames)
{
var index = 0;
var output = new StringBuilder();
foreach (var column in table.PrimaryKeyColumns.OrderBy(x => x.Name))
{
output.Append(" [");
if (qualifiedNames)
{
output.Append(Globals.GetTableDatabaseName(model, table));
output.Append("].[");
}
output.Append(column.DatabaseName + "]");
if (index < table.PrimaryKeyColumns.Count - 1)
{
output.Append(",");
}
output.AppendLine();
index++;
}
return(output.ToString());
}
private static string BuildStoredProcedure(Table table, ModelRoot model, List <Column> allColumns)
{
var sb = new StringBuilder();
var index = 0;
sb.AppendLine("CREATE TABLE #tmpTable");
sb.AppendLine("(");
foreach (var column in table.PrimaryKeyColumns.OrderBy(x => x.Name))
{
sb.Append("\t[" + column.DatabaseName + "] " + column.GetSQLDefaultType());
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");
var likeList = allColumns.Where(x =>
x.DataType != System.Data.SqlDbType.Xml &&
x.DataType != System.Data.SqlDbType.Text &&
x.DataType != System.Data.SqlDbType.NText &&
x.DataType != System.Data.SqlDbType.Image)
.ToList();
for (var ii = 0; ii < likeList.Count; ii++)
{
var column = likeList[ii];
//If this is text then do a like, other wise equals
var comparer = "=";
if (ModelHelper.IsTextType(column.DataType))
{
comparer = "LIKE";
}
var t = column.ParentTableRef.Object as Table;
var tableName = "[" + t.GetSQLSchema() + "].[" + Globals.GetTableDatabaseName(model, t) + "]";
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 < likeList.Count - 1)
{
sb.AppendLine();
sb.Append("or");
}
sb.AppendLine();
}
//ORDER BY CLAUSE
sb.AppendLine("ORDER BY");
for (var ii = 0; ii < likeList.Count; ii++)
{
var column = likeList[ii];
var t = column.ParentTableRef.Object as Table;
var tableName = "[" + t.GetSQLSchema() + "].[" + Globals.GetTableDatabaseName(model, t) + "]";
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 < likeList.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 (var ii = 0; ii < likeList.Count; ii++)
{
var column = likeList[ii];
var t = column.ParentTableRef.Object as Table;
var tableName = "[" + t.GetSQLSchema() + "].[" + Globals.GetTableDatabaseName(model, t) + "]";
var 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 < likeList.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 ");
var pkFirstTime = true;
foreach (var pkColumn in table.PrimaryKeyColumns.OrderBy(x => x.Name))
{
if (!pkFirstTime)
{
sb.AppendLine(" AND");
}
else
{
pkFirstTime = false;
}
sb.AppendFormat("#tmpTable.[{0}] = [{2}].[{1}].[{0}]", pkColumn.DatabaseName, Globals.GetTableDatabaseName(model, table).ToUpper(), table.GetSQLSchema());
}
sb.AppendLine();
sb.AppendLine("ORDER BY");
for (var ii = 0; ii < likeList.Count; ii++)
{
var column = likeList[ii];
var t = column.ParentTableRef.Object as Table;
var tableName = "[" + t.GetSQLSchema() + "].[" + Globals.GetTableDatabaseName(model, t) + "]";
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 < likeList.Count - 1)
{
sb.Append(", ");
}
sb.AppendLine();
}
sb.AppendLine();
sb.AppendLine("DROP TABLE #tmpTable");
sb.AppendLine();
sb.AppendLine("GO");
return(sb.ToString());
}
private static void AppendInsertionStatement(StringBuilder sb, Table table, ModelRoot model)
{
var pkIdentites = new List <Column>();
foreach (var column in table.PrimaryKeyColumns.OrderBy(x => x.Name))
{
if (column.Identity == IdentityTypeConstants.Database)
{
pkIdentites.Add(column);
}
//else
//{
// var col2 = table.GetBasePKColumn(column);
// if (col2 != null)
// if (col2.Identity == IdentityTypeConstants.Database) pkIdentites.Add(column);
//}
}
//Null out identites if < 0, so the row will get an autonumber
foreach (var column in pkIdentites)
{
sb.AppendLine("IF (@" + column.ToDatabaseCodeIdentifier() + " < 0) SET @" + column.ToDatabaseCodeIdentifier() + " = NULL;");
}
if (pkIdentites.Count > 0)
{
sb.Append("if (");
foreach (var column in pkIdentites)
{
sb.Append("(@" + column.ToDatabaseCodeIdentifier() + " IS NULL)");
if (pkIdentites.IndexOf(column) < pkIdentites.Count - 1)
{
sb.Append(" AND ");
}
}
sb.AppendLine(")");
sb.AppendLine("BEGIN");
}
//bool hasPrimaryInsert = (table.GetColumns().Count(x => !x.PrimaryKey) > 0);
//if (hasPrimaryInsert)
//{
sb.AppendLine("INSERT INTO [" + table.GetSQLSchema() + "].[" + Globals.GetTableDatabaseName(model, table) + "]");
sb.AppendLine("(");
sb.Append(BuildInsertColumns(table, model));
sb.AppendLine(")");
sb.AppendLine("VALUES");
sb.AppendLine("(");
sb.Append(BuildInsertValues(table, model));
sb.AppendLine(");");
sb.AppendLine();
sb.AppendLine("if (@@RowCount = 0) return;");
sb.AppendLine();
if (pkIdentites.Count > 0)
{
sb.AppendLine("END");
sb.AppendLine("ELSE");
sb.AppendLine("BEGIN");
sb.AppendLine("SET identity_insert [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] on");
sb.AppendLine("INSERT INTO [" + table.GetSQLSchema() + "].[" + Globals.GetTableDatabaseName(model, table) + "]");
sb.AppendLine("(");
sb.Append(BuildInsertColumns(table, model, pkIdentites));
sb.AppendLine(")");
sb.AppendLine("VALUES");
sb.AppendLine("(");
sb.Append(BuildInsertValues(table, model, pkIdentites));
sb.AppendLine(");");
sb.AppendLine();
sb.AppendLine("if (@@RowCount = 0) return;");
sb.AppendLine();
sb.AppendLine("SET identity_insert [" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] off");
sb.AppendLine("END");
}
sb.AppendLine();
}
public static string GetBody(Table table, ModelRoot model)
{
var sb = new StringBuilder();
if (!table.Immutable)
{
if (table.AllowCreateAudit)
{
sb.AppendLine("if (@" + model.Database.CreatedDateColumnName + " IS NULL)");
sb.AppendLine("SET @" + model.Database.CreatedDateColumnName + " = " + model.GetSQLDefaultDate());
}
if (table.AllowModifiedAudit)
{
if (model.EFVersion == EFVersionConstants.EF4)
{
sb.AppendLine("DECLARE @" + model.Database.ModifiedDateColumnName + " [DateTime]");
sb.AppendLine("SET @" + model.Database.ModifiedDateColumnName + " = " + model.GetSQLDefaultDate());
}
else if (model.EFVersion == EFVersionConstants.EF6)
{
//Modified Date - This is where we override the placeholder parameter for EF6 runtime.
sb.Append("SET @" + model.Database.ModifiedDateColumnName + " = " + model.GetSQLDefaultDate());
sb.AppendLine("--Entity Framework 6 Required Modified Date be passed in, overwrite it here.");
}
else
{
throw new NotImplementedException(string.Format("model.EFVersion [{0}] not supported", model.EFVersion));
}
}
foreach (var column in table.PrimaryKeyColumns.OrderBy(x => x.Name))
{
if (column.Identity == IdentityTypeConstants.Code)
{
sb.AppendLine("SET @" + column.ToDatabaseCodeIdentifier() + " = (select case when max([" + column.DatabaseName + "]) is null then 1 else max([" + column.DatabaseName + "]) + 1 end from [" + Globals.GetTableDatabaseName(model, table) + "])");
}
else if (column.Identity == IdentityTypeConstants.Database)
{
//sb.AppendLine("DECLARE @" + column.ToDatabaseCodeIdentifier() + " " + dc.DataType);
}
}
if (table.ParentTable == null)
{
AppendInsertionStatement(sb, table, model);
}
else
{
var tableList = table.GetTableHierarchy();
foreach (var 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());
}
public static string GetBody(Table table, ModelRoot model)
{
var sb = new StringBuilder();
if (!table.Immutable)
{
if (table.AllowCreateAudit)
{
sb.AppendLine("if (@" + model.Database.CreatedDateColumnName + " IS NULL)");
sb.AppendLine("SET @" + model.Database.CreatedDateColumnName + " = " + model.GetSQLDefaultDate());
}
if (table.AllowModifiedAudit)
{
sb.AppendLine("DECLARE @" + model.Database.ModifiedDateColumnName + " [DateTime]");
sb.AppendLine("SET @" + model.Database.ModifiedDateColumnName + " = " + model.GetSQLDefaultDate());
}
foreach (var column in table.PrimaryKeyColumns.OrderBy(x => x.Name))
{
if (column.Identity == IdentityTypeConstants.Code)
{
sb.AppendLine("SET @" + column.ToDatabaseCodeIdentifier() + " = (select case when max([" + column.DatabaseName + "]) is null then 1 else max([" + column.DatabaseName + "]) + 1 end from [" + Globals.GetTableDatabaseName(model, table) + "])");
}
else if (column.Identity == IdentityTypeConstants.Database)
{
//sb.AppendLine("DECLARE @" + column.ToDatabaseCodeIdentifier() + " " + dc.DataType);
}
}
if (table.ParentTable == null)
{
AppendInsertionStatement(sb, table, model);
}
else
{
var tableList = table.GetTableHierarchy();
foreach (var 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());
}
