sjh37
commented
1 year ago Closed sjh37 closed 1 year ago
sjh37
commented
1 year ago sjh37
commented
1 year ago Investigate this helper file from github.com/dotnet/efcore/issues/8127
Consume it like this
_context.LoadStoredProc("get_user_by_lastupdateddate]")
.WithSqlParam("@lastupdatedfrom", lastUpdatedFrom)
.WithSqlParam("@startwithuserid", startWithUserId)
.ExecuteStoredProc((handler) =>
{
usersData.AddRange(handler.ReadToList<Users>());
handler.NextResult();
usersData.AddRange(handler.ReadToList<Users2>());
handler.NextResult();
usersData.AddRange(handler.ReadToList<Users3>());
handler.NextResult();
});public static class SqlHelper
{
/// <summary>
/// Creates an initial DbCommand object based on a stored procedure name
/// </summary>
/// <param name="context">target database context</param>
/// <param name="storedProcName">target procedure name</param>
/// <param name="prependDefaultSchema">Prepend the default schema name to <paramref name="storedProcName"/> if explicitly defined in <paramref name="context"/></param>
/// <returns></returns>
public static DbCommand LoadStoredProc(this DbContext context, string storedProcName, bool prependDefaultSchema = true)
{
var cmd = context.Database.GetDbConnection().CreateCommand();
if (prependDefaultSchema)
{
var schemaName = context.Model.Relational().DefaultSchema;
if (schemaName != null)
{
storedProcName = $"{schemaName}.{storedProcName}";
}
}
cmd.CommandText = storedProcName;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
return cmd;
}
/// <summary>
/// Creates a DbParameter object and adds it to a DbCommand
/// </summary>
/// <param name="cmd"></param>
/// <param name="paramName"></param>
/// <param name="paramValue"></param>
/// <returns></returns>
public static DbCommand WithSqlParam(this DbCommand cmd, string paramName, object paramValue, Action<DbParameter> configureParam = null)
{
if (string.IsNullOrEmpty(cmd.CommandText) && cmd.CommandType != System.Data.CommandType.StoredProcedure)
throw new InvalidOperationException("Call LoadStoredProc before using this method");
var param = cmd.CreateParameter();
param.ParameterName = paramName;
param.Value = (paramValue != null ? paramValue : DBNull.Value);
configureParam?.Invoke(param);
cmd.Parameters.Add(param);
return cmd;
}
/// <summary>
/// Creates a DbParameter object and adds it to a DbCommand
/// </summary>
/// <param name="cmd"></param>
/// <param name="paramName"></param>
/// <param name="paramValue"></param>
/// <returns></returns>
public static DbCommand WithSqlParam(this DbCommand cmd, string paramName, Action<DbParameter> configureParam = null)
{
if (string.IsNullOrEmpty(cmd.CommandText) && cmd.CommandType != System.Data.CommandType.StoredProcedure)
throw new InvalidOperationException("Call LoadStoredProc before using this method");
var param = cmd.CreateParameter();
param.ParameterName = paramName;
configureParam?.Invoke(param);
cmd.Parameters.Add(param);
return cmd;
}
/// <summary>
/// Creates a DbParameter object based on the SqlParameter and adds it to a DbCommand.
/// This enabled the ability to provide custom types for SQL-parameters.
/// </summary>
/// <param name="cmd"></param>
/// <param name="paramName"></param>
/// <param name="paramValue"></param>
/// <returns></returns>
public static DbCommand WithSqlParam(this DbCommand cmd, string paramName, SqlParameter parameter)
{
if (string.IsNullOrEmpty(cmd.CommandText) && cmd.CommandType != System.Data.CommandType.StoredProcedure)
throw new InvalidOperationException("Call LoadStoredProc before using this method");
//var param = cmd.CreateParameter();
//param.ParameterName = paramName;
//configureParam?.Invoke(param);
cmd.Parameters.Add(parameter);
return cmd;
}
public class SprocResults
{
// private DbCommand _command;
private DbDataReader _reader;
public SprocResults(DbDataReader reader)
{
// _command = command;
_reader = reader;
}
public IList<T> ReadToList<T>()
{
return MapToList<T>(_reader);
}
public T? ReadToValue<T>() where T : struct
{
return MapToValue<T>(_reader);
}
public Task<bool> NextResultAsync()
{
return _reader.NextResultAsync();
}
public Task<bool> NextResultAsync(CancellationToken ct)
{
return _reader.NextResultAsync(ct);
}
public bool NextResult()
{
return _reader.NextResult();
}
/// <summary>
/// Retrieves the column values from the stored procedure and maps them to <typeparamref name="T"/>'s properties
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dr"></param>
/// <returns>IList<<typeparamref name="T"/>></returns>
private IList<T> MapToList<T>(DbDataReader dr)
{
var objList = new List<T>();
var props = typeof(T).GetRuntimeProperties().ToList();
var colMapping = dr.GetColumnSchema()
.Where(x => props.Any(y => y.Name.ToLower() == x.ColumnName.ToLower()))
.ToDictionary(key => key.ColumnName.ToLower());
if (dr.HasRows)
{
while (dr.Read())
{
T obj = Activator.CreateInstance<T>();
foreach (var prop in props)
{
if (colMapping.ContainsKey(prop.Name.ToLower()))
{
var column = colMapping[prop.Name.ToLower()];
if (column?.ColumnOrdinal != null)
{
var val = dr.GetValue(column.ColumnOrdinal.Value);
prop.SetValue(obj, val == DBNull.Value ? null : val);
}
}
}
objList.Add(obj);
}
}
return objList;
}
/// <summary>
/// Attempts to read the first value of the first row of the resultset.
/// </summary>
private T? MapToValue<T>(DbDataReader dr) where T : struct
{
if (dr.HasRows)
{
if (dr.Read())
{
return dr.IsDBNull(0) ? new T?() : new T?(dr.GetFieldValue<T>(0));
}
}
return new T?();
}
}
/// <summary>
/// Executes a DbDataReader and returns a list of mapped column values to the properties of <typeparamref name="T"/>
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="command"></param>
/// <returns></returns>
public static void ExecuteStoredProc(this DbCommand command, Action<SprocResults> handleResults, System.Data.CommandBehavior commandBehaviour = System.Data.CommandBehavior.Default, bool manageConnection = true)
{
if (handleResults == null)
{
throw new ArgumentNullException(nameof(handleResults));
}
using (command)
{
if (manageConnection && command.Connection.State == System.Data.ConnectionState.Closed)
command.Connection.Open();
try
{
using (var reader = command.ExecuteReader(commandBehaviour))
{
var sprocResults = new SprocResults(reader);
handleResults(sprocResults);
}
}
finally
{
if (manageConnection)
{
command.Connection.Close();
}
}
}
}
/// <summary>
/// Executes a DbDataReader asynchronously and returns a list of mapped column values to the properties of <typeparamref name="T"/>.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="command"></param>
/// <returns></returns>
public async static Task ExecuteStoredProcAsync(this DbCommand command, Action<SprocResults> handleResults, System.Data.CommandBehavior commandBehaviour = System.Data.CommandBehavior.Default, CancellationToken ct = default(CancellationToken), bool manageConnection = true)
{
if (handleResults == null)
{
throw new ArgumentNullException(nameof(handleResults));
}
using (command)
{
if (manageConnection && command.Connection.State == System.Data.ConnectionState.Closed)
await command.Connection.OpenAsync(ct).ConfigureAwait(false);
try
{
using (var reader = await command.ExecuteReaderAsync(commandBehaviour, ct).ConfigureAwait(false))
{
var sprocResults = new SprocResults(reader);
handleResults(sprocResults);
}
}
finally
{
if (manageConnection)
{
command.Connection.Close();
}
}
}
}
}The solution was to move
if (IsSqlParameterNull(isApplicationCompleteParam))
isApplicationComplete = null;
else
isApplicationComplete = (bool) isApplicationCompleteParam.Value;to the bottom of the function like so:
public CheckIfApplicationIsCompleteReturnModel CheckIfApplicationIsComplete(int? applicationId, out bool? isApplicationComplete)
{
var applicationIdParam = new SqlParameter { ParameterName = "@ApplicationId", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Input, Value = applicationId.GetValueOrDefault(), Precision = 10, Scale = 0 };
if (!applicationId.HasValue)
applicationIdParam.Value = DBNull.Value;
var isApplicationCompleteParam = new SqlParameter { ParameterName = "@IsApplicationComplete", SqlDbType = SqlDbType.Bit, Direction = ParameterDirection.Output };
var procResultData = new CheckIfApplicationIsCompleteReturnModel();
var cmd = Database.Connection.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "[dbo].[CheckIfApplicationIsComplete]";
cmd.Parameters.Add(applicationIdParam);
cmd.Parameters.Add(isApplicationCompleteParam);
try
{
DbInterception.Dispatch.Connection.Open(Database.Connection, new DbInterceptionContext());
var reader = cmd.ExecuteReader();
var objectContext = ((IObjectContextAdapter) this).ObjectContext;
procResultData.ResultSet1 = objectContext.Translate<CheckIfApplicationIsCompleteReturnModel.ResultSetModel1>(reader).ToList();
reader.NextResult();
procResultData.ResultSet2 = objectContext.Translate<CheckIfApplicationIsCompleteReturnModel.ResultSetModel2>(reader).ToList();
reader.Close();
}
finally
{
DbInterception.Dispatch.Connection.Close(Database.Connection, new DbInterceptionContext());
}
if (IsSqlParameterNull(isApplicationCompleteParam))
isApplicationComplete = null;
else
isApplicationComplete = (bool) isApplicationCompleteParam.Value;
return procResultData;
}
This issue has been resolved and will be in the next release.