private void AppendFullTemplate(StringBuilder sb)
{
try
{
string storedProcedureName = _model.GetStoredProcedurePrefix() + "_" + _currentComponent.PascalName + "SelectByModifiedDateRange";
sb.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'[" + _currentComponent.GetSQLSchema() + "].[" + storedProcedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
sb.AppendLine(" drop procedure [" + _currentComponent.GetSQLSchema() + "].[" + 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 [" + _currentComponent.GetSQLSchema() + "].[" + storedProcedureName + "]");
sb.AppendLine("(");
sb.AppendLine(" @start_date [DateTime],");
sb.AppendLine(" @end_date [DateTime]");
sb.AppendLine(")");
sb.AppendLine("AS");
sb.AppendLine();
sb.AppendLine("SET NOCOUNT ON;");
sb.AppendLine();
string fieldName = "[" + _currentComponent.Parent.DatabaseName + "].[" + _model.Database.ModifiedDateColumnName + "]";
//SELECT CLAUSE
sb.AppendLine("SELECT");
sb.Append(Globals.BuildSelectList(_currentComponent, _model));
sb.AppendLine("FROM");
sb.AppendLine(_currentComponent.Parent.GetFullHierarchyTableJoin());
sb.AppendLine("WHERE");
sb.AppendLine("(((" + fieldName + " IS NULL) AND (@start_date IS NULL)) OR (@start_date <= " + fieldName + ")) AND ");
sb.AppendLine("(((" + fieldName + " IS NULL) AND (@end_date IS NULL)) OR (@end_date >= " + fieldName + "))");
sb.AppendLine("GO");
sb.AppendLine();
if (!string.IsNullOrEmpty(_model.Database.GrantExecUser))
{
sb.AppendFormat("GRANT EXECUTE ON [" + _currentComponent.Parent.GetSQLSchema() + "].[{0}] TO [{1}]", storedProcedureName, _model.Database.GrantExecUser).AppendLine();
sb.AppendLine("GO");
sb.AppendLine();
}
}
catch (Exception ex)
{
throw;
}
}
private void AppendFullTemplate(StringBuilder sb)
{
try
{
sb.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'[" + _currentComponent.GetSQLSchema() + "].[" + StoredProcedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
sb.AppendLine(" drop procedure [" + _currentComponent.GetSQLSchema() + "].[" + 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 [" + _currentComponent.GetSQLSchema() + "].[" + StoredProcedureName + "]");
sb.AppendLine("(");
sb.AppendLine(" @page [Int], -- page number selected by the user");
sb.AppendLine(" @pageSize [Int], -- number of items on the page");
sb.AppendLine(" @orderByColumn [Varchar] (100), -- name of column to order things by");
sb.AppendLine(" @ascending [Bit], -- order column ascending or descending");
sb.AppendLine(" @filter [Varchar] (100) = null, -- filter statement passed in to determine like criteria on order by column");
sb.AppendLine(" @count [Int] out -- number of items in the collection");
sb.AppendLine(")");
sb.AppendLine("AS");
sb.AppendLine();
sb.AppendLine("SET NOCOUNT ON;");
sb.AppendLine();
this.BuildPagingSelect(sb);
sb.AppendLine();
sb.AppendLine("GO");
sb.AppendLine();
//sb.AppendLine("SET QUOTED_IDENTIFIER OFF");
//sb.AppendLine("GO");
//sb.AppendLine("SET ANSI_NULLS ON");
//sb.AppendLine("GO");
if (!string.IsNullOrEmpty(_model.Database.GrantExecUser))
{
sb.AppendFormat("GRANT EXECUTE ON [" + _currentComponent.GetSQLSchema() + "].[{0}] TO [{1}]", StoredProcedureName, _model.Database.GrantExecUser).AppendLine();
sb.AppendLine("GO");
sb.AppendLine();
}
}
catch (Exception ex)
{
throw;
}
}
private void AppendFullTemplate(StringBuilder sb)
{
try
{
sb.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'[" + _currentComponent.GetSQLSchema() + "].[" + StoredProcedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
sb.AppendLine(" drop procedure [" + _currentComponent.GetSQLSchema() + "].[" + 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 [" + _currentComponent.GetSQLSchema() + "].[" + StoredProcedureName + "]");
sb.AppendLine("AS");
sb.AppendLine();
sb.AppendLine("SET NOCOUNT ON;");
sb.AppendLine();
sb.AppendLine("SELECT");
sb.Append(Globals.BuildSelectList(_currentComponent, _model));
sb.AppendLine("FROM ");
sb.AppendLine(_currentComponent.Parent.GetFullHierarchyTableJoin());
sb.AppendLine("GO");
sb.AppendLine();
//sb.AppendLine("SET QUOTED_IDENTIFIER OFF");
//sb.AppendLine("GO");
//sb.AppendLine("SET ANSI_NULLS ON");
//sb.AppendLine("GO");
if (!string.IsNullOrEmpty(_model.Database.GrantExecUser))
{
sb.AppendFormat("GRANT EXECUTE ON [" + _currentComponent.GetSQLSchema() + "].[{0}] TO [{1}]", StoredProcedureName, _model.Database.GrantExecUser).AppendLine();
sb.AppendLine("GO");
sb.AppendLine();
}
}
catch (Exception ex)
{
throw;
}
}
public void GenerateContent(StringBuilder sb)
{
if (_currentComponent.Parent.TypedTable != TypedTableConstants.None)
{
return;
}
if (_currentComponent.Parent.Immutable)
{
return;
}
try
{
sb.AppendLine("if exists(select * from sys.objects where name = '" + StoredProcedureName + "' and type = 'P' and type_desc = 'SQL_STORED_PROCEDURE')");
sb.AppendLine(" drop procedure [" + _currentComponent.GetSQLSchema() + "].[" + StoredProcedureName + "]");
sb.AppendLine("GO");
sb.AppendLine();
//Just drop the procedure if no CRUD SP
if (!_model.Database.UseGeneratedCRUD)
{
return;
}
sb.AppendLine("CREATE PROCEDURE [" + _currentComponent.GetSQLSchema() + "].[" + StoredProcedureName + "]");
sb.AppendLine("(");
sb.Append(this.BuildParameterList());
sb.AppendLine(")");
sb.AppendLine("AS");
sb.AppendLine();
if (_currentComponent.Parent.AllowModifiedAudit)
{
sb.AppendLine("IF (@" + _model.Database.ModifiedDateColumnName + " IS NULL)");
sb.AppendLine("SET @" + _model.Database.ModifiedDateColumnName + " = " + _model.GetSQLDefaultDate() + ";");
sb.AppendLine();
}
sb.AppendLine("SET NOCOUNT OFF;");
var tableList = new List <Table>();
foreach (var column in _currentComponent.GeneratedColumns)
{
if (!tableList.Contains(column.ParentTable))
{
tableList.Add(column.ParentTable);
}
}
foreach (var table in tableList)
{
//If there is nothing to set then do not do anything
var setStatment = BuildSetStatement(table);
if (!string.IsNullOrEmpty(setStatment))
{
sb.AppendLine("UPDATE");
sb.AppendLine("\t[" + table.GetSQLSchema() + "].[" + table.DatabaseName + "] ");
sb.AppendLine("SET");
sb.AppendLine(setStatment);
sb.AppendLine("WHERE");
sb.AppendLine("\t" + BuildUpdateWhereStatement(table));
sb.AppendLine();
sb.AppendLine("if (@@RowCount = 0) return;");
sb.AppendLine();
}
}
sb.AppendLine("SELECT");
sb.Append(Globals.BuildSelectList(_currentComponent, _model));
sb.AppendLine("FROM ");
sb.AppendLine(_currentComponent.Parent.GetFullHierarchyTableJoin());
sb.AppendLine("WHERE");
sb.AppendLine("\t" + BuildSelectWhereStatement());
sb.AppendLine("GO");
sb.AppendLine();
if (!string.IsNullOrEmpty(_model.Database.GrantExecUser))
{
sb.AppendFormat("GRANT EXECUTE ON [" + _currentComponent.GetSQLSchema() + "].[{0}] TO [{1}]", StoredProcedureName, _model.Database.GrantExecUser);
sb.AppendLine();
sb.AppendLine("GO");
sb.AppendLine();
}
}
catch (Exception ex)
{
throw;
}
}
private void AppendFullTemplate(StringBuilder sb)
{
try
{
sb.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'[" + _currentComponent.GetSQLSchema() + "].[" + StoredProcedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
sb.AppendLine(" drop procedure [" + _currentComponent.GetSQLSchema() + "].[" + 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();
if (_currentComponent.Parent.SelfReference)
{
sb.AppendLine();
sb.AppendLine("CREATE PROCEDURE [" + _currentComponent.GetSQLSchema() + "].[" + StoredProcedureName + "]");
sb.AppendLine("(");
sb.AppendLine(" @xml xml,");
sb.AppendLine(" @direction char(4),");
sb.AppendLine(" @levels int");
sb.AppendLine(")");
sb.AppendLine("AS");
sb.AppendLine();
sb.AppendLine("DECLARE @newItemCount int");
sb.AppendLine("DECLARE @count int");
sb.AppendLine("DECLARE @index int");
sb.AppendLine();
sb.Append("CREATE TABLE #TmpIds (");
int ii = 0;
foreach (ColumnRelationship columnRelationship in _currentComponent.Parent.SelfReferenceRelation.ColumnRelationships)
{
var parentColumn = columnRelationship.ParentColumn;
var childColumn = columnRelationship.ChildColumn;
sb.Append("[" + parentColumn.DatabaseName + "] " + parentColumn.DatabaseType);
if (ii < _currentComponent.Parent.SelfReferenceRelation.FkColumns.Count() - 1)
{
sb.Append(", ");
}
ii++;
}
sb.AppendLine(")");
sb.AppendLine();
sb.Append("INSERT INTO #TmpIds(");
ii = 0;
foreach (ColumnRelationship columnRelationship in _currentComponent.Parent.SelfReferenceRelation.ColumnRelationships)
{
var parentColumn = columnRelationship.ParentColumn;
var childColumn = columnRelationship.ChildColumn;
sb.Append("[" + parentColumn.DatabaseName + "]");
if (ii < _currentComponent.Parent.SelfReferenceRelation.FkColumns.Count() - 1)
{
sb.Append(", ");
}
ii++;
}
sb.AppendLine(")");
sb.AppendLine(" SELECT");
ii = 0;
foreach (ColumnRelationship columnRelationship in _currentComponent.Parent.SelfReferenceRelation.ColumnRelationships)
{
var parentColumn = columnRelationship.ParentColumn;
var childColumn = columnRelationship.ChildColumn;
sb.Append("T.c.value('./" + parentColumn.DatabaseName + "[1]', '" + parentColumn.GetSQLDefaultType(true) + "') [" + parentColumn.DatabaseName + "]");
if (ii < _currentComponent.Parent.SelfReferenceRelation.FkColumns.Count() - 1)
{
sb.AppendLine(", ");
}
ii++;
}
sb.AppendLine();
sb.AppendLine("FROM @xml.nodes('//Item') T(c)");
sb.AppendLine();
sb.AppendLine("IF (@direction = 'DOWN' OR @direction = 'BOTH')");
sb.AppendLine("BEGIN");
sb.AppendLine(" SET @newItemCount = (SELECT COUNT(*) FROM #TmpIds)");
sb.AppendLine(" SET @count = @newItemCount");
sb.AppendLine(" SET @index = 1");
sb.AppendLine(" WHILE(@newItemCount > 0 and @index <= @levels)");
sb.AppendLine(" BEGIN");
sb.Append(" INSERT INTO #TmpIds(");
ii = 0;
foreach (ColumnRelationship columnRelationship in _currentComponent.Parent.SelfReferenceRelation.ColumnRelationships)
{
var parentColumn = columnRelationship.ParentColumn;
var childColumn = columnRelationship.ChildColumn;
sb.Append("[" + childColumn.DatabaseName + "]");
if (ii < _currentComponent.Parent.SelfReferenceRelation.FkColumns.Count() - 1)
{
sb.Append(", ");
}
ii++;
}
sb.AppendLine(")");
sb.AppendLine(" SELECT");
ii = 0;
foreach (ColumnRelationship columnRelationship in _currentComponent.Parent.SelfReferenceRelation.ColumnRelationships)
{
var parentColumn = columnRelationship.ParentColumn;
var childColumn = columnRelationship.ChildColumn;
sb.Append(" [" + _currentComponent.Parent.DatabaseName + "].[" + parentColumn.DatabaseName + "]");
if (ii < _currentComponent.Parent.SelfReferenceRelation.FkColumns.Count() - 1)
{
sb.AppendLine(", ");
}
ii++;
}
sb.AppendLine();
sb.AppendLine(" FROM ");
sb.AppendLine(" ["+ Globals.GetTableDatabaseName(_model, _currentComponent.Parent) + "]");
sb.Append(" INNER JOIN #TmpIds ON ");
ii = 0;
foreach (ColumnRelationship columnRelationship in _currentComponent.Parent.SelfReferenceRelation.ColumnRelationships)
{
var parentColumn = columnRelationship.ParentColumn;
var childColumn = columnRelationship.ChildColumn;
sb.Append("[" + _currentComponent.Parent.DatabaseName + "].[" + childColumn.DatabaseName + "] = #TmpIds.[" + parentColumn.DatabaseName + "]");
if (ii < _currentComponent.Parent.SelfReferenceRelation.FkColumns.Count() - 1)
{
sb.Append(" AND ");
}
ii++;
}
sb.AppendLine();
sb.AppendLine(" WHERE");
ii = 0;
foreach (ColumnRelationship columnRelationship in _currentComponent.Parent.SelfReferenceRelation.ColumnRelationships)
{
var parentColumn = columnRelationship.ParentColumn;
var childColumn = columnRelationship.ChildColumn;
sb.Append(" ["+ _currentComponent.Parent.DatabaseName + "].[" + childColumn.DatabaseName + "] NOT IN (SELECT [" + parentColumn.DatabaseName + "] from #TmpIds)");
if (ii < _currentComponent.Parent.SelfReferenceRelation.FkColumns.Count() - 1)
{
sb.AppendLine(" AND ");
}
ii++;
}
sb.AppendLine();
sb.AppendLine(" SET @newItemCount = (SELECT COUNT(*) FROM #TmpIds) - @count");
sb.AppendLine(" SET @count = (SELECT COUNT(*) FROM #TmpIds)");
sb.AppendLine(" SET @index = @index + 1");
sb.AppendLine(" END");
sb.AppendLine("END");
sb.AppendLine("IF (@direction = 'UP' OR @direction = 'BOTH')");
sb.AppendLine("BEGIN");
sb.AppendLine(" SET @newItemCount = (SELECT COUNT(*) FROM #TmpIds)");
sb.AppendLine(" SET @count = @newItemCount");
sb.AppendLine(" SET @index = 1");
sb.AppendLine(" WHILE(@newItemCount > 0 and @index <= @levels)");
sb.AppendLine(" BEGIN");
sb.Append(" INSERT INTO #TmpIds(");
ii = 0;
foreach (ColumnRelationship columnRelationship in _currentComponent.Parent.SelfReferenceRelation.ColumnRelationships)
{
var parentColumn = columnRelationship.ParentColumn;
var childColumn = columnRelationship.ChildColumn;
sb.Append("[" + parentColumn.DatabaseName + "]");
if (ii < _currentComponent.Parent.SelfReferenceRelation.FkColumns.Count() - 1)
{
sb.Append(", ");
}
ii++;
}
sb.AppendLine(")");
sb.AppendLine(" SELECT");
ii = 0;
foreach (ColumnRelationship columnRelationship in _currentComponent.Parent.SelfReferenceRelation.ColumnRelationships)
{
var parentColumn = columnRelationship.ParentColumn;
var childColumn = columnRelationship.ChildColumn;
sb.AppendLine(" ["+ _currentComponent.Parent.DatabaseName + "].[" + childColumn.DatabaseName + "]");
if (ii < _currentComponent.Parent.SelfReferenceRelation.FkColumns.Count() - 1)
{
sb.Append(", ");
}
ii++;
}
sb.AppendLine();
sb.AppendLine(" FROM #TmpIds INNER JOIN ["+ _currentComponent.Parent.DatabaseName + "] ON ");
ii = 0;
foreach (ColumnRelationship columnRelationship in _currentComponent.Parent.SelfReferenceRelation.ColumnRelationships)
{
var parentColumn = columnRelationship.ParentColumn;
var childColumn = columnRelationship.ChildColumn;
sb.AppendLine("[" + _currentComponent.Parent.DatabaseName + "].[" + childColumn.DatabaseName + "] = #TmpIds.[" + parentColumn.DatabaseName + "]");
if (ii < _currentComponent.Parent.SelfReferenceRelation.FkColumns.Count() - 1)
{
sb.Append(" AND ");
}
ii++;
}
sb.AppendLine(" WHERE");
ii = 0;
foreach (ColumnRelationship columnRelationship in _currentComponent.Parent.SelfReferenceRelation.ColumnRelationships)
{
var parentColumn = columnRelationship.ParentColumn;
var childColumn = columnRelationship.ChildColumn;
sb.AppendLine(" ["+ _currentComponent.Parent.PascalName + "].[" + childColumn.DatabaseName + "] NOT IN (SELECT [" + parentColumn.DatabaseName + "] from #TmpIds)");
if (ii < _currentComponent.Parent.SelfReferenceRelation.FkColumns.Count() - 1)
{
sb.Append(" AND ");
}
ii++;
}
sb.AppendLine();
sb.AppendLine(" SET @newItemCount = (SELECT COUNT(*) FROM #TmpIds) - @count");
sb.AppendLine(" SET @count = (SELECT COUNT(*) FROM #TmpIds)");
sb.AppendLine(" SET @index = @index + 1");
sb.AppendLine(" END");
sb.AppendLine("END");
sb.AppendLine();
sb.AppendLine("SELECT");
sb.Append(Globals.BuildSelectList(_currentComponent, _model));
sb.AppendLine("FROM ");
sb.AppendLine(_currentComponent.Parent.GetFullHierarchyTableJoin());
sb.AppendLine("WHERE");
ii = 0;
foreach (ColumnRelationship columnRelationship in _currentComponent.Parent.SelfReferenceRelation.ColumnRelationships)
{
var parentColumn = columnRelationship.ParentColumn;
var childColumn = columnRelationship.ChildColumn;
sb.Append(" (["+ _currentComponent.Parent.DatabaseName + "].[" + childColumn.DatabaseName + "] IN (SELECT [" + parentColumn.DatabaseName + "] from #TmpIds))");
if (ii < _currentComponent.Parent.SelfReferenceRelation.FkColumns.Count() - 1)
{
sb.AppendLine(" AND ");
}
ii++;
}
sb.AppendLine();
sb.AppendLine();
sb.AppendLine("DROP TABLE #TmpIds");
sb.AppendLine();
}
else
{
sb.AppendLine();
sb.AppendLine("CREATE PROCEDURE [" + _currentComponent.GetSQLSchema() + "].[" + StoredProcedureName + "]");
sb.AppendLine("(");
sb.AppendLine(" @xml xml");
sb.AppendLine(")");
sb.AppendLine("AS");
sb.AppendLine();
sb.AppendLine();
sb.AppendLine("SELECT");
sb.Append(Globals.BuildSelectList(_currentComponent, _model));
sb.AppendLine("FROM ");
sb.AppendLine(_currentComponent.Parent.GetFullHierarchyTableJoin());
sb.AppendLine("WHERE");
int index = 0;
foreach (var c in _currentComponent.Parent.PrimaryKeyColumns.OrderBy(x => x.Name))
{
if (index != 0)
{
sb.AppendLine();
sb.AppendLine(" AND");
}
sb.AppendLine();
sb.AppendLine(" [" + _currentComponent.Parent.GetSQLSchema() + "].[" + Globals.GetTableDatabaseName(_model, _currentComponent.Parent) + "].[" + c.DatabaseName + "] IN (SELECT T.c.value('./" + c.DatabaseName + "[1]', '" + c.GetSQLDefaultType(true) + "') [" + c.DatabaseName + "] ");
sb.AppendLine("FROM @xml.nodes('//Item') T(c))");
index++;
}
sb.AppendLine();
}
sb.AppendLine();
sb.AppendLine("GO");
//sb.AppendLine("SET QUOTED_IDENTIFIER OFF");
//sb.AppendLine("GO");
//sb.AppendLine("SET ANSI_NULLS ON");
//sb.AppendLine("GO");
if (!string.IsNullOrEmpty(_model.Database.GrantExecUser))
{
sb.AppendFormat("GRANT EXECUTE ON [" + _currentComponent.GetSQLSchema() + "].[{0}] TO [{1}]", StoredProcedureName, _model.Database.GrantExecUser).AppendLine();
sb.AppendLine("GO");
sb.AppendLine();
}
//Now add the SINGLE primary key selection stored procedure
sb.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'[" + _currentComponent.GetSQLSchema() + "].[" + StoredProcedureName2 + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
sb.AppendLine(" drop procedure [" + _currentComponent.GetSQLSchema() + "].[" + StoredProcedureName2 + "]");
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 [" + _currentComponent.GetSQLSchema() + "].[" + StoredProcedureName2 + "]");
sb.AppendLine("(");
int count2 = 0;
foreach (var c in _currentComponent.Parent.PrimaryKeyColumns.OrderBy(x => x.Name))
{
sb.Append(" @" + c.DatabaseName + " " + c.DatabaseType);
if (count2 < _currentComponent.Parent.PrimaryKeyColumns.Count - 1)
{
sb.AppendLine(",");
}
count2++;
}
sb.AppendLine();
sb.AppendLine(")");
sb.AppendLine("AS");
sb.AppendLine();
sb.AppendLine("SELECT ");
sb.AppendLine(Globals.BuildSelectList(_currentComponent, _model));
sb.AppendLine("FROM");
sb.AppendLine(_currentComponent.Parent.GetFullHierarchyTableJoin());
sb.AppendLine("WHERE ");
count2 = 0;
foreach (var c in _currentComponent.Parent.PrimaryKeyColumns.OrderBy(x => x.Name))
{
sb.Append("[" + Globals.GetTableDatabaseName(_model, _currentComponent.Parent) + "].[" + c.DatabaseName + "] = @" + c.DatabaseName + " ");
if (count2 < _currentComponent.Parent.PrimaryKeyColumns.Count - 1)
{
sb.Append("AND ");
}
count2++;
}
sb.AppendLine();
sb.AppendLine("GO");
if (!string.IsNullOrEmpty(_model.Database.GrantExecUser))
{
sb.AppendFormat("GRANT EXECUTE ON [" + _currentComponent.GetSQLSchema() + "].[{0}] TO [{1}]", StoredProcedureName2, _model.Database.GrantExecUser).AppendLine();
sb.AppendLine("GO");
sb.AppendLine();
}
}
catch (Exception ex)
{
throw;
}
}
private void AppendFullTemplate(StringBuilder sb)
{
try
{
foreach (Reference reference in _currentComponent.Columns)
{
Column column = (Column)reference.Object;
#region Field Select
if (column.IsSearchable)
{
string storedProcedureName = _model.GetStoredProcedurePrefix() + "_" + _currentComponent.PascalName + "SelectBy" + column.PascalName;
sb.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'[" + _currentComponent.GetSQLSchema() + "].[" + storedProcedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
sb.AppendLine(" drop procedure [" + _currentComponent.GetSQLSchema() + "].[" + 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 [" + _currentComponent.GetSQLSchema() + "].[" + storedProcedureName + "]");
sb.AppendLine("(");
sb.AppendLine("\t@" + column.DatabaseName + " " + column.GetSQLDefaultType() + ",");
sb.AppendLine("\t@paging_PageIndex [Int] = -1, -- page number selected by the user");
sb.AppendLine("\t@paging_RecordsperPage [Int] = -1, -- number of items on the page");
sb.AppendLine("\t@paging_OrderByColumn [Varchar] (100) = '', -- name of column to order things by");
sb.AppendLine("\t@paging_Ascending [Bit] = 1, -- order column ascending or descending");
sb.AppendLine("\t@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("CREATE TABLE #tmpTable");
sb.AppendLine("(");
for (int ii = 0; ii < _currentComponent.Parent.PrimaryKeyColumns.Count; ii++)
{
Column dc = (Column)_currentComponent.Parent.PrimaryKeyColumns[ii];
sb.Append("\t[" + dc.DatabaseName + "] " + dc.GetSQLDefaultType());
if (ii < _currentComponent.Parent.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, _currentComponent.Parent, false));
sb.AppendLine(")");
//SELECT CLAUSE
sb.AppendLine("SELECT");
sb.Append(Globals.BuildPrimaryKeySelectList(_model, _currentComponent.Parent, true));
sb.AppendLine("FROM");
sb.AppendLine(_currentComponent.Parent.GetFullHierarchyTableJoin());
sb.AppendLine("WHERE");
sb.AppendLine("[" + Globals.GetTableDatabaseName(_model, (Table)column.ParentTableRef.Object) + "].[" + column.DatabaseName + "] = @" + column.DatabaseName);
IEnumerable <Column> validColumns = GetValidColumns();
//ORDER BY CLAUSE
sb.AppendLine("ORDER BY");
int index = 0;
foreach (Column column2 in validColumns.OrderBy(x => x.Name))
{
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 (index < validColumns.Count() - 1)
{
sb.Append(", ");
}
sb.AppendLine();
index++;
}
if (_currentComponent.Parent.AllowCreateAudit)
{
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedByColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedByColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedByColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedByColumnName + "] END END");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedDateColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedDateColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedDateColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedDateColumnName + "] END END");
}
if (_currentComponent.Parent.AllowModifiedAudit)
{
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedByColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedByColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedByColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedByColumnName + "] END END");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedDateColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedDateColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedDateColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.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(_currentComponent.Parent.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(_currentComponent, _model));
sb.AppendLine("FROM");
sb.AppendLine(" [#tmpTable]");
sb.Append(" INNER JOIN " + _currentComponent.Parent.GetFullHierarchyTableJoin() + " ON ");
bool pkFirstTime = true;
foreach (Column pkColumn in _currentComponent.Parent.PrimaryKeyColumns.OrderBy(x => x.Name))
{
if (!pkFirstTime)
{
sb.AppendLine(" AND");
}
else
{
pkFirstTime = false;
}
sb.AppendFormat("#tmpTable.[{0}] = [{1}].[{0}]", pkColumn.DatabaseName.ToLower(), _currentComponent.Parent.PascalName.ToUpper());
}
sb.AppendLine();
sb.AppendLine("ORDER BY");
index = 0;
foreach (Column column2 in validColumns.OrderBy(x => x.Name))
{
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 (index < validColumns.Count() - 1)
{
sb.Append(", ");
}
sb.AppendLine();
index++;
}
if (_currentComponent.Parent.AllowCreateAudit)
{
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedByColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedByColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedByColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedByColumnName + "] END END");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedDateColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedDateColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedDateColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedDateColumnName + "] END END");
}
if (_currentComponent.Parent.AllowModifiedAudit)
{
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedByColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedByColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedByColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedByColumnName + "] END END");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedDateColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedDateColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedDateColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.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 (!string.IsNullOrEmpty(_model.Database.GrantExecUser))
{
sb.AppendFormat("GRANT EXECUTE ON [" + _currentComponent.GetSQLSchema() + "].[{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 = _model.GetStoredProcedurePrefix() + "_" + _currentComponent.PascalName + "SelectBy" + column.PascalName + "Range";
sb.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'[" + _currentComponent.GetSQLSchema() + "].[" + storedProcedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
sb.AppendLine(" drop procedure [" + _currentComponent.GetSQLSchema() + "].[" + 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 [" + _currentComponent.GetSQLSchema() + "].[" + 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("\t@paging_PageIndex [Int] = -1, -- page number selected by the user");
sb.AppendLine("\t@paging_RecordsperPage [Int] = -1, -- number of items on the page");
sb.AppendLine("\t@paging_OrderByColumn [Varchar] (100) = '', -- name of column to order things by");
sb.AppendLine("\t@paging_Ascending [Bit] = 1, -- order column ascending or descending");
sb.AppendLine("\t@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("CREATE TABLE #tmpTable");
sb.AppendLine("(");
for (int ii = 0; ii < _currentComponent.Parent.PrimaryKeyColumns.Count; ii++)
{
Column dc = (Column)_currentComponent.Parent.PrimaryKeyColumns[ii];
sb.Append("\t[" + dc.DatabaseName + "] " + dc.GetSQLDefaultType());
if (ii < _currentComponent.Parent.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, _currentComponent.Parent, false));
sb.AppendLine(")");
//SELECT CLAUSE
sb.AppendLine("SELECT");
sb.Append(Globals.BuildPrimaryKeySelectList(_model, _currentComponent.Parent, true));
sb.AppendLine("FROM");
sb.AppendLine(_currentComponent.Parent.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 + "))");
IEnumerable <Column> validColumns = GetValidColumns();
//ORDER BY CLAUSE
sb.AppendLine("ORDER BY");
int index = 0;
foreach (Column column2 in validColumns.OrderBy(x => x.Name))
{
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 (index < validColumns.Count() - 1)
{
sb.Append(", ");
}
sb.AppendLine();
index++;
}
if (_currentComponent.Parent.AllowCreateAudit)
{
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedByColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedByColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedByColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedByColumnName + "] END END");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedDateColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedDateColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedDateColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedDateColumnName + "] END END");
}
if (_currentComponent.Parent.AllowModifiedAudit)
{
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedByColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedByColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedByColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedByColumnName + "] END END");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedDateColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedDateColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedDateColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.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(_currentComponent.Parent.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(_currentComponent, _model));
sb.AppendLine("FROM");
sb.AppendLine(" [#tmpTable]");
sb.Append(" INNER JOIN " + _currentComponent.Parent.GetFullHierarchyTableJoin() + " ON ");
bool pkFirstTime = true;
foreach (Column pkColumn in _currentComponent.Parent.PrimaryKeyColumns.OrderBy(x => x.Name))
{
if (!pkFirstTime)
{
sb.AppendLine(" AND");
}
else
{
pkFirstTime = false;
}
sb.AppendFormat("#tmpTable.[{0}] = [{1}].[{0}]", pkColumn.DatabaseName.ToLower(), _currentComponent.PascalName.ToUpper());
}
sb.AppendLine();
sb.AppendLine("ORDER BY");
index = 0;
foreach (Column column2 in validColumns.OrderBy(x => x.Name))
{
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 (index < validColumns.Count() - 1)
{
sb.Append(", ");
}
sb.AppendLine();
index++;
}
if (_currentComponent.Parent.AllowCreateAudit)
{
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedByColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedByColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedByColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedByColumnName + "] END END");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedDateColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedDateColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedDateColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.CreatedDateColumnName + "] END END");
}
if (_currentComponent.Parent.AllowModifiedAudit)
{
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedByColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedByColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedByColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedByColumnName + "] END END");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedDateColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedDateColumnName + "] END END DESC");
sb.AppendLine(" ,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN '" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.Root)).Database.ModifiedDateColumnName + "' THEN [" + _currentComponent.Parent.DatabaseName + "].[" + ((nHydrate.Generator.Models.ModelRoot)(_currentComponent.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 (!string.IsNullOrEmpty(_model.Database.GrantExecUser))
{
sb.AppendFormat("GRANT EXECUTE ON [" + _currentComponent.GetSQLSchema() + "].[{0}] TO [{1}]", storedProcedureName, _model.Database.GrantExecUser).AppendLine();
sb.AppendLine("GO");
sb.AppendLine();
}
}
#endregion
}
}
catch (Exception ex)
{
throw;
}
}