/// <summary>
/// Initializes a new instance of the StoredProcedure class.
/// </summary>
public StoredProcedureParameter(SMO.StoredProcedureParameter storedProcedureParameter)
{
AddinHelper.Common.WriteToDebugWindow(string.Format("SMOH.{0}({1})", "StoredProcedureParameter", storedProcedureParameter));
DataType = storedProcedureParameter.DataType.ToString();
DefaultValue = storedProcedureParameter.DefaultValue;
MaximumLength = storedProcedureParameter.DataType.MaximumLength.ToString();
Name = storedProcedureParameter.Name;
NumericPrecision = storedProcedureParameter.DataType.NumericPrecision.ToString();
NumericScale = storedProcedureParameter.DataType.NumericScale.ToString();
}
public void generate(String ProcedurePath)
{
String name = string.Format("ssp_{0}", _table.Name);
String FullObjectName = this._database.Name + "." + this._table.Schema + "." + this._table.Name;
Match match;
TSQL.StoredProcedure storedProcedure = new TSQL.StoredProcedure(this._database, name);
storedProcedure.Schema = this._table.Schema;
storedProcedure.TextMode = false;
TSQL.StoredProcedureParameter storedProcedureParameter = new TSQL.StoredProcedureParameter(storedProcedure, "@Operation");
storedProcedureParameter.DataType = TSQL.DataType.Int;
storedProcedureParameter.DefaultValue = "0";
storedProcedure.Parameters.Add(storedProcedureParameter);
TSQL.StoredProcedureParameter parameter2 = new TSQL.StoredProcedureParameter(storedProcedure, "@User");
parameter2.DataType = TSQL.DataType.NVarChar(0x80);
parameter2.DefaultValue = "NULL";
storedProcedure.Parameters.Add(parameter2);
IDictionary <TSQL.Column, TSQL.StoredProcedureParameter> source = new SortedDictionary <TSQL.Column, TSQL.StoredProcedureParameter>(new ColumnComparer());
foreach (TSQL.Column column in this._table.Columns)
{
TSQL.StoredProcedureParameter parameter3 = new TSQL.StoredProcedureParameter(storedProcedure, string.Format("@{0}", column.Name));
parameter3.DataType = column.DataType;
parameter3.DefaultValue = string.IsNullOrEmpty(column.Default) ? "NULL" : column.Default;
source.Add(column, parameter3);
storedProcedure.Parameters.Add(parameter3);
}
String SqlSearchBody = @"";
String SqlSearchHeader = @"-- =============================================================================
-- Name: #PROCEDURENAME#
-- Created By: MGenerator.Database.Generation.CrudProcedureGenerator (Generated)
-- Created On: ##GENDATE##
-- Description: Provides enhanced Search to Table [##DATBASE##].[##SCHEMA##].[##TABLE##]
--
-- This procedure was generated by a tool.
--
-- Changes to this file may cause incorrect behavior and will be
-- lost if the procedure is regenerated.
-- ============================================================================= \n\n".Replace("#PROCEDURENAME#", name).Replace("##GENDATE##", DateTime.Now.ToLongDateString()).Replace("##DATBASE##", _database.Name).Replace("##SCHEMA##", _table.Schema).Replace("##TABLE##", _table.Name);
SqlSearchBody = (char)9 + "\n CREATE PROCEDURE ##NAME##" + "\n";
SqlSearchBody = SqlSearchBody + (char)9 + (char)9 + "##PARAMETERS## \n";
SqlSearchBody = SqlSearchBody + (char)9 + "AS \n";
SqlSearchBody = SqlSearchBody + "BEGIN \n";
SqlSearchBody = SqlSearchBody + (char)9 + (char)9 + "SELECT \n";
SqlSearchBody = SqlSearchBody + "\t\t ##COLUMNS## \n";
SqlSearchBody = SqlSearchBody + (char)9 + (char)9 + "FROM \n";
SqlSearchBody = SqlSearchBody + (char)9 + (char)9 + (char)9 + "##TABLE## \n";
SqlSearchBody = SqlSearchBody + (char)9 + (char)9 + "WHERE \n";
SqlSearchBody = SqlSearchBody + (char)9 + (char)9 + (char)9 + "\t ##CONTRAINTS## \n";
SqlSearchBody = SqlSearchBody + " END ";
String Constraints = @"";
String ConstraintLine = "\t\t\t(##COLUMN## LIKE ISNULL(##PARAMETER##,NULL))";
String ColumnList = @"";
int i = 0;
int ic = _table.Columns.Count - 1;
while (i <= ic)
{
if (i == ic)
{
ColumnList = ColumnList + "[" + _table.Columns[i].Name + "]";
Constraints = Constraints + (char)9 + (ConstraintLine.Replace("##COLUMN##", "[" + _table.Columns[i].Name + "]").Replace("##PARAMETER##", "@" + _table.Columns[i].Name) + "\n");
}
else
{
ColumnList = ColumnList + "[" + _table.Columns[i].Name + "],";
Constraints = Constraints + (char)9 + (ConstraintLine.Replace("##COLUMN##", "[" + _table.Columns[i].Name + "]").Replace("##PARAMETER##", "@" + _table.Columns[i].Name) + " OR \n");
}
i = i + 1;
}
// build paramter scritp list
String ParametersList = @"";
foreach (TSQL.StoredProcedureParameter spp in storedProcedure.Parameters)
{
if (!(spp.Name == "Operation"))
{
ParametersList = ParametersList + "," + spp.Name + " " + spp.DataType.ToString() + " = NULL " + "\n";
}
}
SqlSearchBody = SqlSearchBody.Replace("##PARAMETERS##", "");
SqlSearchBody = SqlSearchBody.Replace("##NAME##", name);
SqlSearchBody = SqlSearchBody.Replace("##TABLE##", _table.Name);
SqlSearchBody = SqlSearchBody.Replace("##COLUMNS##", ColumnList);
SqlSearchBody = SqlSearchBody.Replace("##CONTRAINTS##", Constraints);
storedProcedure.TextMode = true;
storedProcedure.TextHeader = SqlSearchHeader;
//storedProcedure.TextBody = SqlSearchBody;
String Output = @"";
storedProcedure.TextMode = false;
Output = Output + SqlSearchHeader + "\n";
Output = Output + SqlSearchBody;
Output = Output + "\n GO";
File.WriteAllText(ProcedurePath + @"\" + name + ".sql", Output);
}
public TSQL.StoredProcedure Generate(String ProcedureRepositoryPath)
{
TSQL.StoredProcedure procedure = null;
if (CheckRequiredObjects())
{
procedure = new Microsoft.SqlServer.Management.Smo.StoredProcedure(this._database, "ssp_" + this._table.Name, _table.Schema);
// Set the text mode to false and then set other properties;
procedure.TextMode = false;
#region [ Operation 1 - Search by Any Criteria ]
// Add Parameters;
System.Text.StringBuilder SelectSql = new StringBuilder();
SelectSql.AppendLine("SELECT ");
int Count = _table.Columns.Count - 1;
int Index = 0;
while (Index <= Count)
{
MemberGraph mGraph = new MemberGraph(_table.Columns[Index]);
TSQL.StoredProcedureParameter parameter = new Microsoft.SqlServer.Management.Smo.StoredProcedureParameter(procedure, "@" + mGraph.PropertyName(), _table.Columns[Index].DataType);
parameter.DefaultValue = "NULL";
procedure.Parameters.Add(parameter);
// ntext Data types cna not be searched
if (!(mGraph.SqlColumn.DataType.SqlDataType == TSQL.SqlDataType.NText))
{
if (Index == Count)
{
SelectSql.AppendLine("[" + _table.Columns[Index].Name + "]");
}
else
{
SelectSql.AppendLine("[" + _table.Columns[Index].Name + "],");
}
}
Index = Index + 1;
}
SelectSql.AppendLine("FROM " + _table.Name);
SelectSql.AppendLine("WHERE ");
Count = _table.Columns.Count - 1;
Index = 0;
while (Index <= Count)
{
MemberGraph mGraph = new MemberGraph(_table.Columns[Index]);
String ParameterName = "@" + mGraph.PropertyName();
if (!(mGraph.SqlColumn.DataType.SqlDataType == TSQL.SqlDataType.NText))
{
if (Index == 0)
{ // This is the last column
if (_table.Columns[Index].DataType.SqlDataType == Microsoft.SqlServer.Management.Smo.SqlDataType.Text)
{
SelectSql.AppendLine("(" + ParameterName + " IS NULL OR " + _table.Columns[Index].Name + " LIKE " + ParameterName + ")");
}
else
{
SelectSql.AppendLine("(" + ParameterName + " IS NULL OR " + _table.Columns[Index].Name + " =" + ParameterName + ")");
}
}
else
{
SelectSql.AppendLine("AND");
if (_table.Columns[Index].DataType.SqlDataType == Microsoft.SqlServer.Management.Smo.SqlDataType.Text)
{
SelectSql.AppendLine("(" + ParameterName + " IS NULL OR " + _table.Columns[Index].Name + " LIKE " + ParameterName + ")");
}
else
{
SelectSql.AppendLine("(" + ParameterName + " IS NULL OR " + _table.Columns[Index].Name + " = " + ParameterName + ")");
}
}
}
Index = Index + 1;
}
SelectSql.AppendLine("\n");
#endregion
#region [ Build Script ]
string totalScript = SelectSql.ToString();
procedure.TextBody = totalScript;
TSQL.ScriptingOptions Options = new Microsoft.SqlServer.Management.Smo.ScriptingOptions();
StringBuilder sbSql = new StringBuilder();
foreach (var s in procedure.Script())
{
sbSql.AppendLine(s);
}
#endregion
File.WriteAllText(ProcedureRepositoryPath + @"\" + procedure.Name + ".sql", sbSql.ToString());
}
return(procedure);
}
