public async Task <IEnumerable <TEntity> > GetStoredProcedureResult(StoredProcedureModel sql)
{
var parameters = new DynamicParameters(sql.Parameters);
var result = await _connectionFactory.DbConnection().QueryAsync <TEntity>(sql.SqlScript, parameters, commandType: sql.SqlCommandType);
return(result);
}
public void Write(StoredProcedureModel procedure)
{
Builder
.WriteIndentation()
.Write("public Result ExecuteResult(SqlCommand command");
if (procedure.Parameters.Count > 0)
{
Builder.Write(", Parameters parameters");
}
Builder
.Write(")")
.WriteNewLine();
WriteBlockStart();
{
Builder
.WriteIndentation()
.Write("command.CommandText = \"")
.Write(procedure.DatabaseName.EscapedFullName)
.Write("\";")
.WriteNewLine();
Builder
.WriteIndentation()
.Write("command.CommandType = CommandType.StoredProcedure;")
.WriteNewLine()
.WriteNewLine();
if (procedure.Parameters.Count > 0)
{
Builder.WriteIndentedLine("SqlParameter parameter = null;");
WriteAddParameters(procedure);
}
Builder.WriteIndentedLine("using(var reader = command.ExecuteReader())");
WriteBlockStart();
{
Builder.WriteIndentedLine("var result = new Result();");
WriteReadingResults(procedure);
WriteOutputParameters(procedure);
Builder.WriteIndentedLine("return result;");
}
WriteBlockEnd();
}
WriteBlockEnd();
}
public void Write(StoredProcedureModel procedure)
{
if (procedure.Parameters.Count > 0)
{
Builder
.WriteNewLine()
.WriteIndentedLine("public partial class Parameters");
WriteBlockStart();
{
WriteConstructor(procedure);
Builder.WriteNewLine();
WriteProperties(procedure);
}
WriteBlockEnd();
}
}
private void WriteConstructorArguments(StoredProcedureModel procedure)
{
int lastIndex = procedure.Parameters.Count - 1;
for (int i = 0; i < procedure.Parameters.Count; i++)
{
ParameterModel parameter = procedure.Parameters[i];
Builder
.Write(parameter.Column.ClrType.TypeName)
.Write(" ")
.Write(parameter.Column.ParameterName);
if (i != lastIndex)
{
Builder.Write(", ");
}
}
}
private void WriteConstructor(StoredProcedureModel procedure)
{
Builder
.WriteIndentation()
.Write("public Parameters(");
WriteConstructorArguments(procedure);
Builder
.Write(")")
.WriteNewLine();
WriteBlockStart();
{
WriteConstructorBody(procedure);
}
WriteBlockEnd();
}
private void WriteOutputProperties(StoredProcedureModel procedure)
{
int lastIndex = procedure.Parameters.Count - 1;
for (int i = 0; i < procedure.OutputParameters.Count; i++)
{
var parameter = procedure.OutputParameters[i];
Builder
.WriteIndentation()
.Write("public ")
.Write(parameter.Column.ClrType.TypeName)
.Write(" ")
.Write(parameter.Column.PropertyName)
.Write(" { get; set; }")
.WriteNewLine();
if(i != lastIndex)
{
Builder.WriteNewLine();
}
}
}
public void Write(StoredProcedureModel procedure)
{
Builder
.WriteIndentedLine("public partial class Result");
WriteBlockStart();
{
if (procedure.Results.Count == 0)
{
Builder.WriteIndentedLine("public int AffectedRows { get; set; }");
}
WriteOutputProperties(procedure);
if (procedure.Results.Count > 0)
{
WriteResults(procedure);
}
}
WriteBlockEnd();
}
private void WriteReadingRows(StoredProcedureModel procedure, int i)
{
var result = procedure.Results[i];
string indexString = (i + 1).ToString(CultureInfo.CurrentCulture);
Builder
.WriteIndentation()
.Write("result.Rows")
.Write(indexString)
.Write("= new List<Result.Row")
.Write(indexString)
.Write(">();")
.WriteNewLine();
Builder
.WriteIndentedLine("while (reader.Read())");
WriteBlockStart();
{
Builder
.WriteIndentation()
.Write("result.Rows")
.Write(indexString)
.Write(".Add(new Result.Row")
.Write(indexString)
.Write("()")
.WriteNewLine();
WriteBlockStart();
{
WriteExecuteMethodReadValues(result);
}
Builder.Indent--;
Builder.WriteIndentedLine("});");
}
WriteBlockEnd();
}
private void WriteReadingResults(StoredProcedureModel procedure)
{
int lastIndex = procedure.Results.Count - 1;
for (int i = 0; i < procedure.Results.Count; i++)
{
WriteReadingRows(procedure, i);
if (i != lastIndex)
{
Builder.WriteIndentedLine("reader.NextResult();");
}
}
}
private void WriteResults(StoredProcedureModel procedure)
{
WriteRowsProperties(procedure);
WriteRowsClasses(procedure);
}
private List<StoredProcedureResultModel> GetResults(SqlConnection connection, StoredProcedureModel model)
{
var list = new List<StoredProcedureResultModel>();
using (var command = new SqlCommand())
{
var sb = new StringBuilder(128);
sb.Append("set fmtonly on;exec ");
sb.Append(model.DatabaseName.EscapedFullName);
foreach (var parameter in model.Parameters)
{
sb.Append(" ");
if (parameter.IncludeInFmtOnlyQuery())
{
sb.Append(parameter.Column.DatabaseName);
sb.Append("=null,");
}
}
if (model.Parameters.Count > 0)
{
sb.Remove(sb.Length - 1, 1);
}
sb.Append(";");
sb.Append("set fmtonly off;");
command.Connection = connection;
command.CommandType = CommandType.Text;
command.CommandText = sb.ToString();
using (var reader = command.ExecuteReader())
{
//In case there is no result from the SP, the first result is null
StoredProcedureResultModel result = GetResult(reader);
if (result != null)
{
list.Add(result);
}
while (reader.NextResult())
{
list.Add(GetResult(reader));
}
}
}
return list;
}
public StoredProcedure(StoredProcedureModel storedProcedure, Schema schema)
{
_storedProcedure = storedProcedure;
_schema = schema;
}
private void WriteRowsClasses(StoredProcedureModel procedure)
{
int lastIndex = procedure.Parameters.Count - 1;
for (int i = 0; i < procedure.Results.Count; i++)
{
Builder
.WriteIndentation()
.Write("public partial class Row");
if (procedure.Results.Count > 1)
{
Builder.Write((i + 1).ToString(CultureInfo.CurrentCulture));
}
Builder.WriteNewLine();
StoredProcedureResultModel result = procedure.Results[i];
WriteRowClassProperties(result);
if(i != lastIndex)
{
Builder.WriteNewLine();
}
}
}
private void WriteConstructorBody(StoredProcedureModel procedure)
{
foreach (var parameter in procedure.Parameters)
{
Builder
.WriteIndentation()
.Write("this.")
.Write(parameter.Column.PropertyName)
.Write(" = ")
.Write(parameter.Column.ParameterName)
.Write(";")
.WriteNewLine();
}
}
/// <summary>
/// Generates stored procedure model from schema data.
/// </summary>
/// <param name="dataContext">Data context model.</param>
/// <param name="func">Function schema.</param>
/// <param name="defaultSchemas">List of default database schema names.</param>
private void BuildStoredProcedure(DataContextModel dataContext, StoredProcedure func, ISet <string> defaultSchemas)
{
var(name, isNonDefaultSchema) = ProcessObjectName(func.Name, defaultSchemas);
var method = new MethodModel(
_namingServices.NormalizeIdentifier(_options.DataModel.ProcedureNameOptions,
(func.Name.Package != null ? $"{func.Name.Package}_" : null) + name.Name))
{
Modifiers = Modifiers.Public | Modifiers.Static | Modifiers.Extension,
Summary = func.Description,
};
var funcModel = new StoredProcedureModel(name, method)
{
Error = func.SchemaError?.Message
};
BuildParameters(func.Parameters, funcModel.Parameters);
switch (func.Result.Kind)
{
case ResultKind.Void:
break;
case ResultKind.Tuple:
// no support from db (maybe pgsql could do it?) and schema API now
throw new NotImplementedException($"Tuple return type support not implemented for stored procedures");
case ResultKind.Scalar:
{
var scalarResult = (ScalarResult)func.Result;
var typeMapping = MapType(scalarResult.Type);
var paramName = _namingServices.NormalizeIdentifier(_options.DataModel.ProcedureParameterNameOptions, scalarResult.Name ?? "return");
funcModel.Return = new FunctionParameterModel(
new ParameterModel(paramName, typeMapping.CLRType.WithNullability(scalarResult.Nullable),
CodeParameterDirection.Out),
System.Data.ParameterDirection.ReturnValue)
{
Type = scalarResult.Type,
DataType = typeMapping.DataType,
DbName = scalarResult.Name,
IsNullable = scalarResult.Nullable
};
break;
}
}
FunctionResult?resultModel = null;
if (func.ResultSets?.Count > 1)
{
// TODO: to support multi-result sets we need at least one implementation in schema provider
throw new NotImplementedException($"Multi-set stored procedures not supported");
}
else if (func.ResultSets?.Count == 1)
{
funcModel.Results.Add(resultModel = PrepareResultSetModel(func.Name, func.ResultSets[0]));
}
// prepare async result class descriptor if needed
var returningParameters = funcModel.Parameters.Where(p => p.Direction != System.Data.ParameterDirection.Input).ToList();
if (funcModel.Return != null)
{
returningParameters.Add(funcModel.Return);
}
if (returningParameters.Count > 0)
{
var asyncResult = new AsyncProcedureResult(
new ClassModel(
_namingServices.NormalizeIdentifier(
_options.DataModel.AsyncProcedureResultClassNameOptions,
func.Name.Name))
{
Modifiers = Modifiers.Public
}, new PropertyModel("Result")
{
Modifiers = Modifiers.Public,
IsDefault = true,
HasSetter = true
});
foreach (var parameter in returningParameters)
{
asyncResult.ParameterProperties.Add(
parameter,
new PropertyModel(_namingServices.NormalizeIdentifier(_options.DataModel.AsyncProcedureResultClassPropertiesNameOptions, parameter.Parameter.Name), parameter.Parameter.Type)
{
Modifiers = Modifiers.Public,
IsDefault = true,
HasSetter = true
});
}
// TODO: next line will need refactoring if we add multi-set support
funcModel.Results.Clear();
funcModel.Results.Add(new FunctionResult(resultModel?.CustomTable, resultModel?.Entity, asyncResult));
}
_interceptors.PreprocessStoredProcedure(_languageProvider.TypeParser, funcModel);
if (isNonDefaultSchema && _options.DataModel.GenerateSchemaAsType)
{
GetOrAddAdditionalSchema(dataContext, func.Name.Schema !).StoredProcedures.Add(funcModel);
}
else
{
dataContext.StoredProcedures.Add(funcModel);
}
}
private StoredProcedureModel ToModel(SqlConnection connection, DatabaseConfiguration configuration, StoredProcedure procedure)
{
var model = new StoredProcedureModel();
model.DatabaseName = CreateDatabaseName(procedure);
model.TypeName = _typeNameBuilder.Build(configuration.StoredProcedures.Namespace, procedure.Name);
model.Parameters = GetParameters(configuration, procedure);
model.OutputParameters = model.Parameters.Where(p => p.IsOutput).ToList();
model.Results = GetResults(connection, model);
return model;
}
public static StoredProcedure ForStoredProcedure(StoredProcedureModel storedProcedure, Schema schema)
{
return(new StoredProcedure(storedProcedure, schema));
}
private void WriteRowsProperties(StoredProcedureModel procedure)
{
for (int i = 0; i < procedure.Results.Count; i++)
{
Builder
.WriteIndentation()
.Write("public List<Row")
.Write((i + 1).ToString(CultureInfo.CurrentCulture))
.Write("> Rows");
if (procedure.Results.Count > 1)
{
Builder.Write((i + 1).ToString(CultureInfo.CurrentCulture));
}
Builder
.Write(" { get; set; }")
.WriteNewLine()
.WriteNewLine();
}
}
/// <summary>
/// Using this method user could modify stored procedure code generation options:
/// <list type="bullet">
/// <item>Return parameter descriptor: <see cref="StoredProcedureModel.Return"/></item>
/// <item>Return tables (data sets) descriptor: <see cref="StoredProcedureModel.Results"/></item>
/// <item>Error, returned by data set schema load procedure: <see cref="TableFunctionModelBase.Error"/></item>
/// <item>Metadata (procedure name): <see cref="TableFunctionModelBase.Name"/></item>
/// <item>Method code-generation options: <see cref="FunctionModelBase.Method"/></item>
/// <item>Parameters: <see cref="FunctionModelBase.Parameters"/></item>
/// </list>
/// </summary>
/// <param name="typeParser">Type parser service to create type tokens.</param>
/// <param name="procedureModel">Stored procedure model descriptor.</param>
public virtual void PreprocessStoredProcedure(ITypeParser typeParser, StoredProcedureModel procedureModel)
{
}
public async Task <object> GetStoredProcedureResult([FromBody] StoredProcedureModel storredProcedureModel)
{
var result = await _genericStoredProcedure.GetStoredProcedureResult(storredProcedureModel);
return(result);
}