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