private void AppendFullTemplate(StringBuilder sb)
{
try
{
foreach (Column column in _currentTable.GetColumnsFullHierarchy())
{
#region Field Select
if (column.IsSearchable)
{
string storedProcedureName = "gen_" + Globals.GetPascalName(_model, _currentTable) + "SelectBy" + column.PascalName;
sb.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + storedProcedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
sb.AppendLine(" drop procedure [dbo].[" + storedProcedureName + "]");
sb.AppendLine("GO");
sb.AppendLine();
sb.AppendLine("SET QUOTED_IDENTIFIER ON ");
sb.AppendLine("GO");
sb.AppendLine("SET ANSI_NULLS ON ");
sb.AppendLine("GO");
sb.AppendLine();
sb.AppendLine("CREATE PROCEDURE [dbo].[" + storedProcedureName + "]");
sb.AppendLine("(");
sb.AppendLine("@" + column.DatabaseName + " " + column.DataType + (ModelHelper.VariableLengthType(column.DataType) ? "(" + column.GetLengthString() + ")" : "") + ",");
sb.AppendLine(" @paging_PageIndex int = -1, -- page number selected by the user");
sb.AppendLine(" @paging_RecordsperPage int = -1, -- number of items on the page");
sb.AppendLine(" @paging_OrderByColumn varchar(100) = '', -- name of column to order things by");
sb.AppendLine(" @paging_Ascending bit = 1, -- order column ascending or descending");
sb.AppendLine(" @paging_Count int out -- number of items in the collection");
sb.AppendLine(")");
sb.AppendLine("AS");
sb.AppendLine();
sb.AppendLine("SET NOCOUNT ON;");
sb.AppendLine();
sb.AppendLine();
sb.AppendLine("CREATE TABLE #tmpTable");
sb.AppendLine("(");
for (int ii = 0; ii < _currentTable.PrimaryKeyColumns.Count; ii++)
{
Column dc = (Column)_currentTable.PrimaryKeyColumns[ii];
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 (ii < _currentTable.PrimaryKeyColumns.Count - 1)
sb.Append(",");
sb.AppendLine();
}
sb.Remove(sb.Length - 1, 1);
sb.AppendLine();
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 = (@paging_RecordsperPage * @paging_PageIndex)");
sb.AppendLine("IF (@total__ivqatedr <> 0) AND (@paging_PageIndex <> -1)");
sb.AppendLine("BEGIN");
sb.AppendLine(" SET ROWCOUNT @total__ivqatedr");
sb.AppendLine("END");
sb.AppendLine("INSERT INTO #tmpTable");
sb.AppendLine("(");
sb.Append(Globals.BuildPrimaryKeySelectList(_model, _currentTable, false));
sb.AppendLine(")");
//SELECT CLAUSE
sb.AppendLine("SELECT");
sb.Append(Globals.BuildPrimaryKeySelectList(_model, _currentTable, true));
sb.AppendLine("FROM");
sb.AppendLine(_currentTable.GetFullHierarchyTableJoin());
sb.AppendLine("WHERE");
sb.AppendLine("[" + Globals.GetTableDatabaseName(_model, (Table)column.ParentTableRef.Object) + "].[" + column.DatabaseName + "] = @" + column.DatabaseName);
ArrayList validColumns = GetValidColumns();
//ORDER BY CLAUSE
sb.AppendLine("ORDER BY");
for (int ii = 0; ii < validColumns.Count; ii++)
{
Column column2 = (Column)validColumns[ii];
string tableName = Globals.GetTableDatabaseName(_model, (Table)column2.ParentTableRef.Object);
sb.AppendLine(" CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + column2.DatabaseName + "' THEN [" + tableName + "].[" + column2.DatabaseName + "] END END DESC, ");
sb.Append(" CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + column2.DatabaseName + "' THEN [" + tableName + "].[" + column2.DatabaseName + "] END END");
if (ii < validColumns.Count - 1)
{
sb.Append(", ");
}
sb.AppendLine();
}
if (_currentTable.AllowCreateAudit)
{
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedByColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedByColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedByColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedByColumnName + "] END END");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedDateColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedDateColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedDateColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedDateColumnName + "] END END");
}
if (_currentTable.AllowModifiedAudit)
{
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedByColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedByColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedByColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedByColumnName + "] END END");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedDateColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedDateColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedDateColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedDateColumnName + "] END END");
}
sb.AppendLine();
sb.AppendLine("-- set @paging_Count based on the rows moved in the previous statement");
//REPEAT SELECT CLAUSE FOR COUNT
sb.AppendLine("SET ROWCOUNT 0");
sb.AppendLine("SET @paging_Count = (");
sb.AppendLine("SELECT count(*)");
sb.AppendLine("FROM");
sb.AppendLine(_currentTable.GetFullHierarchyTableJoin());
sb.AppendLine("WHERE");
sb.AppendLine("[" + Globals.GetTableDatabaseName(_model, (Table)column.ParentTableRef.Object) + "].[" + column.DatabaseName + "] = @" + column.DatabaseName);
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 = (@paging_RecordsperPage * @paging_PageIndex) - @paging_RecordsperPage");
sb.AppendLine("IF (@total__ivqatedr <> 0) AND (@paging_PageIndex <> -1)");
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("IF (@paging_PageIndex <> -1)");
sb.AppendLine("BEGIN");
sb.AppendLine("SET ROWCOUNT @paging_RecordsperPage");
sb.AppendLine("END");
sb.AppendLine("SELECT");
sb.Append(Globals.BuildSelectList(_currentTable, _model, true));
sb.AppendLine("FROM");
sb.AppendLine(" [#tmpTable]");
sb.Append(" INNER JOIN " + _currentTable.GetFullHierarchyTableJoin() + " ON ");
bool pkFirstTime = true;
foreach (Column pkColumn in _currentTable.PrimaryKeyColumns)
{
if (!pkFirstTime)
{
sb.AppendLine(" AND");
}
else
{
pkFirstTime = false;
}
sb.AppendFormat("#tmpTable.[{0}] = [{1}].[{0}]", pkColumn.DatabaseName.ToLower(), Globals.GetTableDatabaseName(_model, _currentTable).ToUpper());
}
sb.AppendLine();
sb.AppendLine("ORDER BY");
for (int ii = 0; ii < validColumns.Count; ii++)
{
Column column2 = (Column)validColumns[ii];
string tableName = Globals.GetTableDatabaseName(_model, (Table)column2.ParentTableRef.Object);
sb.AppendLine(" CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + column2.DatabaseName + "' THEN [" + tableName + "].[" + column2.DatabaseName + "] END END DESC, ");
sb.Append(" CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + column2.DatabaseName + "' THEN [" + tableName + "].[" + column2.DatabaseName + "] END END");
if (ii < validColumns.Count - 1)
{
sb.Append(", ");
}
sb.AppendLine();
}
if (_currentTable.AllowCreateAudit)
{
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedByColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedByColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedByColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedByColumnName + "] END END");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedDateColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedDateColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedDateColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedDateColumnName + "] END END");
}
if (_currentTable.AllowModifiedAudit)
{
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedByColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedByColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedByColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedByColumnName + "] END END");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedDateColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedDateColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedDateColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedDateColumnName + "] END END");
}
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");
sb.AppendLine();
if (_model.Database.GrantExecUser != string.Empty)
{
sb.AppendFormat("GRANT EXECUTE ON [dbo].[{0}] TO [{1}]", storedProcedureName, _model.Database.GrantExecUser).AppendLine();
sb.AppendLine("GO");
sb.AppendLine();
}
}
#endregion
#region Field Range Select
if (column.IsSearchable && column.IsRangeType)
{
string storedProcedureName = "gen_" + Globals.GetPascalName(_model, _currentTable) + "SelectBy" + column.PascalName + "Range";
sb.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + storedProcedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
sb.AppendLine(" drop procedure [dbo].[" + storedProcedureName + "]");
sb.AppendLine("GO");
sb.AppendLine();
sb.AppendLine("SET QUOTED_IDENTIFIER ON ");
sb.AppendLine("GO");
sb.AppendLine("SET ANSI_NULLS ON ");
sb.AppendLine("GO");
sb.AppendLine();
sb.AppendLine("CREATE PROCEDURE [dbo].[" + storedProcedureName + "]");
sb.AppendLine("(");
sb.AppendLine("@" + column.DatabaseName + "Start " + column.DataType + (ModelHelper.VariableLengthType(column.DataType) ? "(" + column.GetLengthString() + ")" : "") + ",");
sb.AppendLine("@" + column.DatabaseName + "End " + column.DataType + (ModelHelper.VariableLengthType(column.DataType) ? "(" + column.GetLengthString() + ")" : "") + ",");
sb.AppendLine(" @paging_PageIndex int = -1, -- page number selected by the user");
sb.AppendLine(" @paging_RecordsperPage int = -1, -- number of items on the page");
sb.AppendLine(" @paging_OrderByColumn varchar(100) = '', -- name of column to order things by");
sb.AppendLine(" @paging_Ascending bit = 1, -- order column ascending or descending");
sb.AppendLine(" @paging_Count int out -- number of items in the collection");
sb.AppendLine(")");
sb.AppendLine("AS");
sb.AppendLine();
sb.AppendLine("SET NOCOUNT ON;");
sb.AppendLine();
sb.AppendLine();
sb.AppendLine("CREATE TABLE #tmpTable");
sb.AppendLine("(");
for (int ii = 0; ii < _currentTable.PrimaryKeyColumns.Count; ii++)
{
Column dc = (Column)_currentTable.PrimaryKeyColumns[ii];
sb.Append("[" + dc.DatabaseName + "] ");
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 (ii < _currentTable.PrimaryKeyColumns.Count - 1)
sb.Append(",");
sb.AppendLine();
}
sb.Remove(sb.Length - 1, 1);
sb.AppendLine();
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 = (@paging_RecordsperPage * @paging_PageIndex)");
sb.AppendLine("IF (@total__ivqatedr <> 0) AND (@paging_PageIndex <> -1)");
sb.AppendLine("BEGIN");
sb.AppendLine(" SET ROWCOUNT @total__ivqatedr");
sb.AppendLine("END");
sb.AppendLine("INSERT INTO #tmpTable");
sb.AppendLine("(");
sb.Append(Globals.BuildPrimaryKeySelectList(_model, _currentTable, false));
sb.AppendLine(")");
//SELECT CLAUSE
sb.AppendLine("SELECT");
sb.Append(Globals.BuildPrimaryKeySelectList(_model, _currentTable, true));
sb.AppendLine("FROM");
sb.AppendLine(_currentTable.GetFullHierarchyTableJoin());
sb.AppendLine("WHERE");
string colName = "[" + Globals.GetTableDatabaseName(_model, (Table)column.ParentTableRef.Object) + "].[" + column.DatabaseName + "]";
sb.Append("((@" + column.DatabaseName + "Start IS NULL) OR ");
sb.Append("(@" + column.DatabaseName + "Start <= " + colName + ")) AND ");
sb.Append("((@" + column.DatabaseName + "End IS NULL) OR ");
sb.AppendLine("(@" + column.DatabaseName + "End > " + colName + "))");
ArrayList validColumns = GetValidColumns();
//ORDER BY CLAUSE
sb.AppendLine("ORDER BY");
for (int ii = 0; ii < validColumns.Count; ii++)
{
Column column2 = (Column)validColumns[ii];
string tableName = Globals.GetTableDatabaseName(_model, (Table)column2.ParentTableRef.Object);
sb.AppendLine(" CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + column2.DatabaseName + "' THEN [" + tableName + "].[" + column2.DatabaseName + "] END END DESC, ");
sb.Append(" CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + column2.DatabaseName + "' THEN [" + tableName + "].[" + column2.DatabaseName + "] END END");
if (ii < validColumns.Count - 1)
{
sb.Append(", ");
}
sb.AppendLine();
}
if (_currentTable.AllowCreateAudit)
{
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedByColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedByColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedByColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedByColumnName + "] END END");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedDateColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedDateColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedDateColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedDateColumnName + "] END END");
}
if (_currentTable.AllowModifiedAudit)
{
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedByColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedByColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedByColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedByColumnName + "] END END");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedDateColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedDateColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedDateColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedDateColumnName + "] END END");
}
sb.AppendLine();
sb.AppendLine("-- set @paging_Count based on the rows moved in the previous statement");
//REPEAT SELECT CLAUSE FOR COUNT
sb.AppendLine("SET ROWCOUNT 0");
sb.AppendLine("SET @paging_Count = (");
sb.AppendLine("SELECT count(*)");
sb.AppendLine("FROM");
sb.AppendLine(_currentTable.GetFullHierarchyTableJoin());
sb.AppendLine("WHERE");
sb.Append("((@" + column.DatabaseName + "Start IS NULL) OR ");
sb.Append("(@" + column.DatabaseName + "Start <= " + colName + ")) AND ");
sb.Append("((@" + column.DatabaseName + "End IS NULL) OR ");
sb.AppendLine("(@" + column.DatabaseName + "End > " + colName + "))");
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 = (@paging_RecordsperPage * @paging_PageIndex) - @paging_RecordsperPage");
sb.AppendLine("IF (@total__ivqatedr <> 0) AND (@paging_PageIndex <> -1)");
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("IF (@paging_PageIndex <> -1)");
sb.AppendLine("BEGIN");
sb.AppendLine("SET ROWCOUNT @paging_RecordsperPage");
sb.AppendLine("END");
sb.AppendLine("SELECT");
sb.Append(Globals.BuildSelectList(_currentTable, _model, true));
sb.AppendLine("FROM");
sb.AppendLine(" [#tmpTable]");
sb.Append(" INNER JOIN " + _currentTable.GetFullHierarchyTableJoin() + " ON ");
bool pkFirstTime = true;
foreach (Column pkColumn in _currentTable.PrimaryKeyColumns)
{
if (!pkFirstTime)
{
sb.AppendLine(" AND");
}
else
{
pkFirstTime = false;
}
sb.AppendFormat("#tmpTable.[{0}] = [{1}].[{0}]", pkColumn.DatabaseName.ToLower(), Globals.GetTableDatabaseName(_model, _currentTable).ToUpper());
}
sb.AppendLine();
sb.AppendLine("ORDER BY");
for (int ii = 0; ii < validColumns.Count; ii++)
{
Column column2 = (Column)validColumns[ii];
string tableName = Globals.GetTableDatabaseName(_model, (Table)column2.ParentTableRef.Object);
sb.AppendLine(" CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + column2.DatabaseName + "' THEN [" + tableName + "].[" + column2.DatabaseName + "] END END DESC, ");
sb.Append(" CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + column2.DatabaseName + "' THEN [" + tableName + "].[" + column2.DatabaseName + "] END END");
if (ii < validColumns.Count - 1)
{
sb.Append(", ");
}
sb.AppendLine();
}
if (_currentTable.AllowCreateAudit)
{
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedByColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedByColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedByColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedByColumnName + "] END END");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedDateColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedDateColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedDateColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.CreatedDateColumnName + "] END END");
}
if (_currentTable.AllowModifiedAudit)
{
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedByColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedByColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedByColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedByColumnName + "] END END");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedDateColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedDateColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedDateColumnName + "' THEN [" + Globals.GetTableDatabaseName(_model, _currentTable) + "].[" + ((Widgetsphere.Generator.Models.ModelRoot)(_currentTable.Root)).Database.ModifiedDateColumnName + "] END END");
}
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");
sb.AppendLine();
if (_model.Database.GrantExecUser != string.Empty)
{
sb.AppendFormat("GRANT EXECUTE ON [dbo].[{0}] TO [{1}]", storedProcedureName, _model.Database.GrantExecUser).AppendLine();
sb.AppendLine("GO");
sb.AppendLine();
}
}
#endregion
}
}
catch (Exception ex)
{
throw;
}
}