private void SaveProcedureToFile(StoredProcedure procedure) { var dir = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Stored Procedures"); if(!Directory.Exists(dir)) { Directory.CreateDirectory(dir); } var path = Path.Combine(dir, procedure.Name + ".sql"); File.WriteAllText(path, procedure.Script, Encoding.UTF8); }
public StoredProcedure GenerateSp(DBTableInfo tableInfo, List<DBTableColumnInfo> selectedFields, List<DBTableColumnInfo> whereConditionFields) { var name = GetSpName(tableInfo.TableName); var statementBuilder = new StringBuilder(); statementBuilder.AppendLine("CREATE PROCEDURE " + string.Format("[{0}].[{1}]", tableInfo.Schema, name)); //GenerateErrorNumberOutParameter(statementBuilder); var inputs = GenerateInputParameters(selectedFields); var wheres = GenerateWhereParameters(whereConditionFields); if (!string.IsNullOrWhiteSpace(inputs)) { statementBuilder.Append(inputs); if (!string.IsNullOrWhiteSpace(wheres)) { statementBuilder.Append(','); } statementBuilder.AppendLine(); } if (!string.IsNullOrWhiteSpace(wheres)) { statementBuilder.AppendLine(wheres); } statementBuilder.AppendLine("AS"); //GenerateStartTryBlock(statementBuilder); statementBuilder.AppendLine(GenerateStatement(tableInfo, selectedFields, whereConditionFields)); //GenerateEndTryBlock(statementBuilder); //GenerateCatchBlock(statementBuilder); var res = new StoredProcedure { Name = name, DropScript = GenerateDropScript(name), Script = statementBuilder.ToString() }; return res; }