private void AppendFullTemplate(StringBuilder sb)
{
try
{
sb.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + this.StoredProcedureName + "]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
sb.AppendLine(" drop procedure [dbo].[" + this.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].[" + this.StoredProcedureName + "]" );
if(_currentView.Parameters.Count > 0)
{
sb.AppendLine("(" );
sb.Append(this.BuildParameterList());
sb.AppendLine(")" );
}
sb.AppendLine("AS" );
sb.AppendLine("SET NOCOUNT ON;" );
sb.AppendLine();
sb.AppendLine("SELECT " );
int index = 0;
foreach (Reference reference in _currentView.Columns)
{
CustomViewColumn column = (CustomViewColumn)reference.Object;
sb.Append("CONVERT(" + column.DatabaseType + ", [view_" + _currentView.DatabaseName + "].[" + column.DatabaseName + "]) AS [" + column.DatabaseName + "]");
if (index < _currentView.Columns.Count - 1)
sb.Append(",");
sb.AppendLine();
index++;
}
sb.AppendLine("FROM" );
sb.AppendLine("[view_" + _currentView.DatabaseName + "]" );
sb.AppendLine();
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" );
sb.AppendLine();
if (_model.Database.GrantExecUser != string.Empty)
{
sb.AppendFormat("GRANT EXECUTE ON [dbo].[{0}] TO [{1}]", this.StoredProcedureName, _model.Database.GrantExecUser).AppendLine();
sb.AppendLine("GO" );
sb.AppendLine();
}
}
catch (Exception ex)
{
throw;
}
}