| public static GetTableDatabaseName ( ModelRoot model, Table table ) : string | ||
| model | ModelRoot | |
| table | Table | |
| return | string | |
public static string GetSQLCreateTable(ModelRoot model, Table table)
{
StringBuilder sb = new StringBuilder();
string tableName = Globals.GetTableDatabaseName(model, table);
sb.AppendLine("if not exists(select * from sysobjects where name = '" + tableName + "' and xtype = 'U')");
sb.AppendLine("CREATE TABLE [dbo].[" + tableName + "] (");
bool firstLoop = true;
foreach (Reference columnRef in table.Columns)
{
Column column = (Column)columnRef.Object;
if (!firstLoop)
{
sb.AppendLine(",");
}
else
{
firstLoop = false;
}
sb.Append(AppendColumnDefinition(column, true, true));
}
AppendModifiedAudit(model, table, sb);
AppendCreateAudit(model, table, sb);
AppendTimestamp(model, table, sb);
sb.AppendLine();
sb.AppendLine(") ON [PRIMARY]");
sb.AppendLine();
return(sb.ToString());
}
public static string GetSQLCreateAuditTable(ModelRoot model, Table table)
{
StringBuilder sb = new StringBuilder();
string tableName = "__AUDIT__" + Globals.GetTableDatabaseName(model, table);
sb.AppendLine("if not exists(select * from sysobjects where name = '" + tableName + "' and xtype = 'U')");
sb.AppendLine("CREATE TABLE [dbo].[" + tableName + "] (");
sb.AppendLine("[__rowid] [INT] NOT NULL IDENTITY,");
sb.AppendLine("[__action] [INT] NOT NULL,");
sb.AppendLine("[__insertdate] [DATETIME] CONSTRAINT [DF__" + table.DatabaseName + "__AUDIT] DEFAULT " + (model.UseUTCTime ? "GetUTCDate()" : "GetDate()") + " NOT NULL,");
ColumnCollection columnList = table.GetColumns();
foreach (Column column in columnList)
{
sb.Append(AppendColumnDefinition(column, false, false, false));
if (columnList.IndexOf(column) < columnList.Count - 1)
{
sb.Append(",");
}
sb.AppendLine();
}
sb.AppendLine(") ON [PRIMARY]");
sb.AppendLine();
return(sb.ToString());
}
public static string GetBody(Table table, ModelRoot model)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("DELETE FROM ");
sb.AppendLine(" [" + Globals.GetTableDatabaseName(model, table) + "] ");
sb.AppendLine("WHERE ");
sb.AppendLine(" " + BuildDeleteWhereStatement(table) + ";");
if (table.ParentTable != null)
{
string parentSPName = "gen_" + Globals.GetPascalName(model, table.ParentTable) + "Delete";
sb.Append("exec " + parentSPName);
int pkIndex = 0;
foreach (Column dc in table.PrimaryKeyColumns)
{
sb.Append(" @Original_" + ValidationHelper.MakeDatabaseIdenitifer(dc.DatabaseName));
pkIndex++;
if (pkIndex < table.PrimaryKeyColumns.Count)
{
sb.Append(",");
}
}
}
sb.AppendLine();
return(sb.ToString());
}
private static string BuildInsertSelectWhereStatement(Table table, ModelRoot model)
{
StringBuilder output = new StringBuilder();
List <Column> primaryKeyCols = new List <Column>(table.PrimaryKeyColumns);
for (int ii = 0; ii < primaryKeyCols.Count; ii++)
{
Column dc = (Column)primaryKeyCols[ii];
output.Append("[" + Globals.GetTableDatabaseName(model, table) + "].[" + dc.DatabaseName + "]");
output.Append(" = ");
if (dc.Identity == IdentityTypeConstants.Database)
{
output.Append("SCOPE_IDENTITY()");
}
else
{
output.AppendFormat("@{0}", dc.DatabaseName);
}
if (ii < primaryKeyCols.Count - 1)
{
output.Append(" AND" + Environment.NewLine + "\t");
}
}
return(output.ToString());
}
public static string BuildPrimaryKeySelectList(ModelRoot model, Table table, bool qualifiedNames)
{
int index = 0;
StringBuilder output = new StringBuilder();
foreach (Column dc in table.PrimaryKeyColumns)
{
output.Append(" [");
if (qualifiedNames)
{
output.Append(Globals.GetTableDatabaseName(model, table));
output.Append("].[");
}
output.Append(dc.DatabaseName.ToLower() + "]");
if (index < table.PrimaryKeyColumns.Count - 1)
{
output.Append(",");
}
output.AppendLine();
index++;
}
return(output.ToString());
}
public static string GetSQLInsertStaticData(Table table)
{
try
{
StringBuilder sb = new StringBuilder();
ModelRoot model = (ModelRoot)table.Root;
//Generate static data
if (table.StaticData.Count > 0)
{
bool isIdentity = false;
foreach (Column column in table.PrimaryKeyColumns)
{
isIdentity |= (column.Identity == IdentityTypeConstants.Database);
}
sb.AppendLine("--INSERT STATIC DATA FOR TABLE [" + Globals.GetTableDatabaseName(model, table) + "]");
if (isIdentity)
{
sb.AppendLine("SET identity_insert [" + Globals.GetTableDatabaseName(model, table) + "] on");
}
foreach (RowEntry rowEntry in table.StaticData)
{
string fieldList = "";
string valueList = "";
foreach (CellEntry cellEntry in rowEntry.CellEntries)
{
Column column = ((Column)cellEntry.ColumnRef.Object);
fieldList += "[" + column.Name + "],";
string sqlValue = cellEntry.GetSQLData();
if (sqlValue == "")
{
if (column.AllowNull)
{
valueList += sqlValue + "NULL,";
}
else if (column.Default != "")
{
if (ModelHelper.IsTextType(column.DataType))
{
valueList += sqlValue + "'" + column.Default.Replace("'", "''") + "',";
}
else
{
valueList += sqlValue + column.Default + ",";
}
}
else
{
if (ModelHelper.IsTextType(column.DataType))
{
valueList += sqlValue + "'',";
}
else
{
valueList += sqlValue + "0,";
}
}
}
else
{
valueList += sqlValue + ",";
}
}
if (fieldList.EndsWith(","))
{
fieldList = fieldList.Substring(0, fieldList.Length - 1);
}
if (valueList.EndsWith(","))
{
valueList = valueList.Substring(0, valueList.Length - 1);
}
sb.Append("if not exists(select * from [" + Globals.GetTableDatabaseName(model, table) + "] where ");
foreach (Column column in table.PrimaryKeyColumns)
{
string pkData = rowEntry.CellEntries[column.Name].GetSQLData();
sb.Append("([" + column.DatabaseName + "] = " + pkData + ")");
if (table.PrimaryKeyColumns.IndexOf(column) < table.PrimaryKeyColumns.Count - 1)
{
sb.Append(" AND ");
}
}
sb.Append(") ");
sb.AppendLine("INSERT INTO [" + Globals.GetTableDatabaseName(model, table) + "] (" + fieldList + ") values (" + valueList + ")");
//if (table.PrimaryKeyColumns.Count == 1)
//{
// Column pkColumn = ((Column)table.PrimaryKeyColumns[0]);
// string pkData = rowEntry.CellEntries[pkColumn.Name].GetSQLData();
// sb.Append("if not exists(select * from [" + Globals.GetTableDatabaseName(model, table) + "] where [" + pkColumn.DatabaseName + "] = " + pkData + ") ");
// sb.AppendLine("INSERT INTO [" + Globals.GetTableDatabaseName(model, table) + "] (" + fieldList + ") values (" + valueList + ")");
//}
//else
//{
// //Fallback is to just insert
// sb.AppendLine("INSERT INTO [" + Globals.GetTableDatabaseName(model, table) + "] (" + fieldList + ") values (" + valueList + ")");
//}
}
if (isIdentity)
{
sb.AppendLine("SET identity_insert [" + Globals.GetTableDatabaseName(model, table) + "] off");
}
sb.AppendLine();
}
return(sb.ToString());
}
catch (Exception ex)
{
throw;
}
}
public static string GetSQLDropTable(Table t)
{
StringBuilder sb = new StringBuilder();
string objectName = "PK_" + Globals.GetTableDatabaseName((ModelRoot)t.Root, t);
#region Delete Parent Relations
for (int ii = t.ParentRoleRelations.Count - 1; ii >= 0; ii--)
{
Relation parentR = (Relation)t.ParentRoleRelations[ii];
Table parentT = (Table)parentR.ParentTableRef.Object;
Table childT = (Table)parentR.ChildTableRef.Object;
for (int jj = parentT.ParentRoleRelations.Count - 1; jj >= 0; jj--)
{
//Relation chlidR = (Relation)parentT.ParentRoleRelations[jj];
if (parentR.ParentTableRef.Object == t)
{
objectName = "FK_" +
parentR.DatabaseRoleName + "_" + Globals.GetTableDatabaseName((ModelRoot)t.Root, childT) +
"_" + Globals.GetTableDatabaseName((ModelRoot)t.Root, parentT);
//sb.AppendLine("--MARK 3");
sb.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'" + objectName + "'))");
sb.AppendLine("ALTER TABLE [" + childT.DatabaseName + "] DROP CONSTRAINT [" + objectName + "]");
sb.AppendLine();
}
}
}
#endregion
#region Delete Child Relations
for (int ii = t.ChildRoleRelations.Count - 1; ii >= 0; ii--)
{
Relation childR = (Relation)t.ChildRoleRelations[ii];
Table parentT = (Table)childR.ParentTableRef.Object;
Table childT = (Table)childR.ChildTableRef.Object;
for (int jj = parentT.ParentRoleRelations.Count - 1; jj >= 0; jj--)
{
Relation parentR = (Relation)parentT.ParentRoleRelations[jj];
if (parentR.ChildTableRef.Object == t)
{
objectName = "FK_" +
parentR.DatabaseRoleName + "_" + Globals.GetTableDatabaseName((ModelRoot)t.Root, childT) +
"_" + Globals.GetTableDatabaseName((ModelRoot)t.Root, parentT);
//sb.AppendLine("--MARK 4");
sb.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'" + objectName + "'))");
sb.AppendLine("ALTER TABLE [" + childT.DatabaseName + "] DROP CONSTRAINT [" + objectName + "]");
sb.AppendLine();
}
}
}
#endregion
#region Delete Primary Key
//sb.AppendLine("--MARK 2");
sb.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'" + objectName + "'))");
sb.AppendLine("ALTER TABLE [" + t.DatabaseName + "] DROP CONSTRAINT [" + objectName + "]");
sb.AppendLine();
#endregion
#region Delete Indexes
foreach (Reference reference in t.Columns)
{
Column c = (Column)reference.Object;
string indexName = "IDX_" + t.Name.Replace("-", "") + "_" + c.Name.Replace("-", "");
indexName = indexName.ToUpper();
sb.AppendLine("if exists (select * from dbo.sysindexes where name = '" + indexName + "')");
sb.AppendLine("DROP INDEX [" + indexName + "] ON [" + t.DatabaseName + "]");
sb.AppendLine();
}
#endregion
//Drop the actual table
sb.AppendLine("if exists (select * from sysobjects where name = '" + t.DatabaseName + "' and xtype = 'U')");
sb.AppendLine("DROP TABLE [" + t.DatabaseName + "]");
return(sb.ToString());
}
public static string GetSQLDropColumn(Column column)
{
StringBuilder sb = new StringBuilder();
Table t = (Table)column.ParentTableRef.Object;
#region Delete Defaults
sb.Append("select 'ALTER TABLE [" + t.DatabaseName + "] DROP CONSTRAINT ' + [name] as 'sql' ");
sb.Append("into #t ");
sb.Append("from sysobjects ");
sb.Append("where id IN( ");
sb.Append("select sc.cdefault ");
sb.Append("FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id ");
sb.Append("LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id ");
sb.Append("WHERE SO.xtype = 'U' and SO.NAME = '" + t.DatabaseName + "' and SC.NAME = '" + column.DatabaseName + "' ");
sb.AppendLine(")");
sb.AppendLine("declare @sql nvarchar (1000)");
sb.AppendLine("SELECT @sql = MAX([sql]) from #t");
sb.AppendLine("exec (@sql)");
sb.AppendLine("drop table #t");
#endregion
#region Delete Parent Relations
for (int ii = t.ParentRoleRelations.Count - 1; ii >= 0; ii--)
{
Relation parentR = (Relation)t.ParentRoleRelations[ii];
Table parentT = (Table)parentR.ParentTableRef.Object;
Table childT = (Table)parentR.ChildTableRef.Object;
if (parentR.ParentTableRef.Object == t)
{
bool removeRelationship = false;
foreach (ColumnRelationship cr in parentR.ColumnRelationships)
{
if (cr.ParentColumnRef.Object == column)
{
removeRelationship = true;
}
}
if (removeRelationship)
{
string objectName = "FK_" +
parentR.DatabaseRoleName + "_" + Globals.GetTableDatabaseName((ModelRoot)t.Root, childT) +
"_" + Globals.GetTableDatabaseName((ModelRoot)t.Root, parentT);
//sb.AppendLine("--MARK 6");
sb.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'" + objectName + "'))");
sb.AppendLine("ALTER TABLE [" + childT.DatabaseName + "] DROP CONSTRAINT [" + objectName + "]");
sb.AppendLine();
}
}
}
#endregion
#region Delete Child Relations
for (int ii = t.ChildRoleRelations.Count - 1; ii >= 0; ii--)
{
Relation childR = (Relation)t.ChildRoleRelations[ii];
Table parentT = (Table)childR.ParentTableRef.Object;
Table childT = (Table)childR.ChildTableRef.Object;
for (int jj = parentT.ParentRoleRelations.Count - 1; jj >= 0; jj--)
{
Relation parentR = (Relation)parentT.ParentRoleRelations[jj];
if (parentR.ChildTableRef.Object == t)
{
bool removeRelationship = false;
foreach (ColumnRelationship cr in childR.ColumnRelationships)
{
if ((cr.ChildColumnRef.Object == column) || (cr.ParentColumnRef.Object == column))
{
removeRelationship = true;
}
}
if (removeRelationship)
{
string objectName = "FK_" +
parentR.DatabaseRoleName + "_" + Globals.GetTableDatabaseName((ModelRoot)t.Root, childT) +
"_" + Globals.GetTableDatabaseName((ModelRoot)t.Root, parentT);
//sb.AppendLine("--MARK 1");
sb.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'" + objectName + "'))");
sb.AppendLine("ALTER TABLE [" + childT.DatabaseName + "] DROP CONSTRAINT [" + objectName + "]");
sb.AppendLine();
}
}
}
}
#endregion
#region Delete if Primary Key
bool removePrimaryKey = false;
foreach (Column c in t.PrimaryKeyColumns)
{
if (c == column)
{
removePrimaryKey = true;
}
}
if (removePrimaryKey)
{
string objectName = "PK_" + Globals.GetTableDatabaseName((ModelRoot)t.Root, t) + "";
//Delete Primary Key
//sb.AppendLine("--MARK 5");
sb.AppendLine("--PRIMARY KEY FOR TABLE [" + t.DatabaseName + "]");
sb.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'" + objectName + "'))");
sb.AppendLine("ALTER TABLE [" + t.DatabaseName + "] DROP CONSTRAINT [" + objectName + "]");
sb.AppendLine();
}
#endregion
#region Delete Indexes
foreach (Reference reference in t.Columns)
{
Column c = (Column)reference.Object;
if (string.Compare(column.DatabaseName, c.DatabaseName, true) == 0)
{
string indexName = "IDX_" + t.Name.Replace("-", "") + "_" + c.Name.Replace("-", "");
indexName = indexName.ToUpper();
sb.AppendLine("if exists (select * from dbo.sysindexes where name = '" + indexName + "')");
sb.AppendLine("DROP INDEX [" + indexName + "] ON [" + t.DatabaseName + "]");
sb.AppendLine();
}
}
#endregion
#region Delete actual column
sb.AppendLine("if exists (select * from syscolumns c inner join sysobjects o on c.id = o.id where c.name = '" + column.DatabaseName + "' and o.name = '" + t.DatabaseName + "')");
sb.AppendLine("ALTER TABLE [" + t.DatabaseName + "] DROP COLUMN [" + column.DatabaseName + "]");
#endregion
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());
}
private static void AppendInsertionStatement(StringBuilder sb, Table table, ModelRoot model)
{
List <Column> pkIdentites = new List <Column>();
foreach (Column dc in table.PrimaryKeyColumns)
{
if (dc.Identity == IdentityTypeConstants.Database)
{
pkIdentites.Add(dc);
}
}
//Null out identites if < 0, so the row will get an autonumber
foreach (Column column in pkIdentites)
{
sb.AppendLine("IF (@" + column.DatabaseName + " < 0) SET @" + column.DatabaseName + " = NULL;");
}
if (pkIdentites.Count > 0)
{
sb.Append("if (");
foreach (Column column in pkIdentites)
{
sb.Append("(@" + column.DatabaseName + " IS NULL)");
if (pkIdentites.IndexOf(column) < pkIdentites.Count - 1)
{
sb.Append(" AND ");
}
}
sb.AppendLine(")");
sb.AppendLine("BEGIN");
}
sb.AppendLine();
sb.AppendLine("INSERT INTO [" + Globals.GetTableDatabaseName(model, table) + "]");
sb.AppendLine("(");
sb.AppendLine(BuildInsertColumns(table, model));
sb.AppendLine(")");
sb.AppendLine("VALUES");
sb.AppendLine("(");
sb.AppendLine(BuildInsertValues(table, model));
sb.AppendLine(");");
sb.AppendLine();
if (pkIdentites.Count > 0)
{
sb.AppendLine("END");
sb.AppendLine("ELSE");
sb.AppendLine("BEGIN");
sb.AppendLine("SET identity_insert [" + table.DatabaseName + "] on");
sb.AppendLine("INSERT INTO [" + Globals.GetTableDatabaseName(model, table) + "]");
sb.AppendLine("(");
sb.AppendLine(BuildInsertColumns(table, model, pkIdentites));
sb.AppendLine(")");
sb.AppendLine("VALUES");
sb.AppendLine("(");
sb.AppendLine(BuildInsertValues(table, model, pkIdentites));
sb.AppendLine(");");
sb.AppendLine("SET identity_insert [" + table.DatabaseName + "] off");
sb.AppendLine("END");
sb.AppendLine();
}
}
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());
}
