/// <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); }