public void ExecuteStoredProcedure_ShouldReturnDataTable_WithNoParameters()
{
// Arrange
var storedProcedureRequest = new StoredProcedureRequest("[dbo].[usp_NoInput_NoOutput_TableResult]");
// Act
StoredProcedureResult result = SUT.ExecuteStoredProcedure(storedProcedureRequest);
// Assert
result.DataTable.ShouldNotBeNull();
result.DataTable.Rows.Count.ShouldBeGreaterThan(0);
// Print
WriteLine(result.DataTable);
}
public async Task ExecuteStoredProcedureAsync_ShouldReturnDataTable_WithNoParameters()
{
// Arrange
var storedProcedureRequest = new StoredProcedureRequest("[dbo].[usp_NoInput_NoOutput_TableResult]");
// Act
StoredProcedureResult result = await SUT.ExecuteStoredProcedureAsync(storedProcedureRequest, new CancellationToken());
// Assert
result.DataTable.ShouldNotBeNull();
result.DataTable.Rows.Count.ShouldBeGreaterThan(0);
// Print
WriteLine(result.DataTable);
}
public async Task Invoke(HttpContext httpContext)
{
string procedure = httpContext.Request.Query[nameof(procedure)].First() !;
var request = new StoredProcedureRequest(procedure);
httpContext.Request.Query
.If(query => !query.Key.Is(nameof(procedure)))
.Do(query => request.Parameters.Add(query.Key, query.Value.First()));
var json = await JsonSerializer.DeserializeAsync <JsonElement>(httpContext.Request.Body);
if (json.ValueKind == JsonValueKind.Object)
{
using var enumerator = json.EnumerateObject();
while (enumerator.MoveNext())
{
var property = enumerator.Current;
request.Parameters.Add(property.Name, property.Value.GetValue());
}
}
await this.HandleRequest <StoredProcedureRequest, RowSet[]>(request, httpContext);
}
/*==================================================================================================================================
* Public Methods
*=================================================================================================================================*/
public questStatus ExecuteStoredProdecure(StoredProcedureRequest storedProcedureRequest)
{
// Initialize
questStatus status = null;
try
{
using (SqlConnection conn = new SqlConnection(storedProcedureRequest.Database.ConnectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
SqlCommandBuilder.DeriveParameters(cmd);
List <StoredProcedureParameter> storedProcedureParameterList = new List <StoredProcedureParameter>();
foreach (SqlParameter sqlParameter in cmd.Parameters)
{
StoredProcedureParameter storedProcedureParameter = new StoredProcedureParameter();
BufferMgr.TransferBuffer(sqlParameter, storedProcedureParameter, true);
storedProcedureParameter.DbType = Enum.GetName(typeof(DbType), sqlParameter.DbType);
storedProcedureParameter.SqlDbType = Enum.GetName(typeof(SqlDbType), sqlParameter.SqlDbType);
storedProcedureParameter.Precision[0] = sqlParameter.Precision;
storedProcedureParameter.Scale[0] = sqlParameter.Scale;
storedProcedureParameterList.Add(storedProcedureParameter);
}
}
}
}
catch (System.Exception ex)
{
status = new questStatus(Severity.Fatal, String.Format("EXCEPTION: {0}.{1}: {2}",
this.GetType().ToString(), MethodInfo.GetCurrentMethod().Name, ex.Message));
return(status);
}
return(new questStatus(Severity.Success));
}
public StoredProcedureResult <TEntity> ExecuteStoredProcedure <TEntity>(StoredProcedureRequest request) where TEntity : class
{
List <TEntity> data;
SqlParameter[] parameters = ToSqlParameters(request.Parameters);
if (parameters == null)
{
data = _dbContext.Set <TEntity>().FromSqlRaw(request.StoredProcedureName).ToList();
}
else
{
string sqlCommand = $"{request.StoredProcedureName} {string.Join(", ", parameters.Select(p => $"@{p.ParameterName}"))}";
data = _dbContext.Set <TEntity>().FromSqlRaw(sqlCommand, parameters).ToList();
foreach (DatabaseCommandParameter parameter in request.Parameters.Where(dp => dp.Direction == ParameterDirection.Output || dp.Direction == ParameterDirection.InputOutput || dp.Direction == ParameterDirection.ReturnValue))
{
parameter.Value = parameters.Single(sp => sp.ParameterName == parameter.Name).Value;
}
}
return(new StoredProcedureResult <TEntity>(data, request.Parameters));
}
public void StoredProcedureCommand_ShouldReturnAsExpected_WhenDataTableIsNotNull()
{
// Arrange
var input = new StoredProcedureRequest();
var connectionProviderMock = AutoMocker.GetMock <ISqlServerConnectionProvider>();
var commandExecutorMock = AutoMocker.GetMock <ISqlServerCommandExecutor>();
connectionProviderMock
.Setup(mock => mock.GetConnection(new CommandOptions()))
.Returns(new SqlConnection("Server=(local)\\SQL2017;Integrated security=SSPI;"));
commandExecutorMock
.Setup(mock => mock.Execute(It.IsAny <SqlCommand>()))
.Returns(new DataTable());
var cut = new SqlServerStoredProcedureCommand(connectionProviderMock.Object, commandExecutorMock.Object);
// Act
StoredProcedureResult output = cut.Execute(input);
// Assert
output.ShouldNotBeNull();
}
public override StoredProcedureResult ExecuteStoredProcedure(StoredProcedureRequest request)
{
return(new SqlServerStoredProcedureCommand(ConnectionProvider, _commandExecutor).Execute(request));
}
public override async Task <StoredProcedureResult> ExecuteStoredProcedureAsync(StoredProcedureRequest request, CancellationToken cancellationToken)
{
return(await new SqlServerStoredProcedureCommand(ConnectionProvider, _commandExecutor).ExecuteAsync(request, cancellationToken));
}
/// <summary> /// Executes a stored procedure against the database this <see cref="IDatabaseCommander"/> is connected to /// </summary> /// <param name="request">The data needed to execute the stored procedure command</param> /// <param name="cancellationToken">The CancellationToken from the caller</param> /// <returns>The result of the stored procedure</returns> public abstract Task <StoredProcedureResult> ExecuteStoredProcedureAsync(StoredProcedureRequest request, CancellationToken cancellationToken);
/// <summary> /// Executes a stored procedure against the database this <see cref="IDatabaseCommander"/> is connected to /// </summary> /// <param name="request">The data needed to execute the stored procedure command</param> /// <returns>The result of the stored procedure</returns> public abstract StoredProcedureResult ExecuteStoredProcedure(StoredProcedureRequest request);
