public int UspCountPerson()
{
MethodInfo info = (MethodInfo)(MethodInfo.GetCurrentMethod());
StoredProcedureResult result = this.ExecuteStoredProcedure(info);
return(result.ReturnValue.Value);
}
public async Task ExecuteStoredProcedureWithInputMultipleOutputAndTableResult()
{
string outputParameterName1 = "SampleOutputInt";
string outputParameterName2 = "SampleOutputVarChar";
StoredProcedureResult result = await _databaseCommander.BuildCommand()
.ForStoredProcedure("[dbo].[usp_BigIntInput_MultipleOutput_TableResult]")
.AddInputParameter("SampleTableID", 1)
.AddOutputParameter(outputParameterName1, SqlDbType.Int)
.AddOutputParameter(outputParameterName2, SqlDbType.VarChar, 1000)
.Timeout(TimeSpan.FromSeconds(30))
.ExecuteAsync(new CancellationToken());
int outputParameter1 = result.GetOutputParameter <int>(outputParameterName1);
string outputParameter2 = result.GetOutputParameter <string>(outputParameterName2);
int count = result.Count;
bool hasData = result.HasData;
DataTable dataTable = result.DataTable;
Console.WriteLine("Output parameter: {0}", outputParameter1);
Console.WriteLine("Output parameter: {0}", outputParameter2);
Console.WriteLine("Row count: {0}", count);
Console.WriteLine("Has Data: {0}", hasData);
Console.WriteLine("DataTable: {0}", Print(dataTable));
}
public void ExecuteStoredProcedure_WithInputMultipleOutputAndTableResult_ShouldReturnEverythingAsExpected()
{
// Arrange
string outputParameterName1 = "SampleOutputInt";
string outputParameterName2 = "SampleOutputVarChar";
// Act
StoredProcedureResult result = SUT.BuildCommand()
.ForStoredProcedure("[dbo].[usp_BigIntInput_MultipleOutput_TableResult]")
.AddInputParameter("SampleTableID", 1)
.AddOutputParameter(outputParameterName1, SqlDbType.Int)
.AddOutputParameter(outputParameterName2, SqlDbType.VarChar, 1000)
.Execute();
// Assert
result.ShouldNotBeNull();
result.HasData.ShouldBeTrue();
result.GetOutputParameter <int>(outputParameterName1).ShouldBeGreaterThan(0);
result.GetOutputParameter <string>(outputParameterName2).ShouldNotBeNullOrEmpty();
// Print result
WriteLine("Output Parameter 1: {0}", result.GetOutputParameter <int>(outputParameterName1));
WriteLine("Output Parameter 2: {0}", result.GetOutputParameter <string>(outputParameterName2));
WriteLine(result.DataTable);
}
public StoredProcedureResultViewModel(StoredProcedureResult result)
{
Text = result.Body?.ToString(Formatting.Indented) ?? string.Empty;
ScriptLog = result.ScriptLog;
Error = result.Error?.Message ?? string.Empty;
if (result.Error != null)
{
SelectedTab = ResultTab.Error;
}
}
public int UspGetTotalSalaryAmountPerYear(
[Parameter(DBType = "Int")] System.Nullable <int> year,
[Parameter(DBType = "Money")] ref System.Nullable <decimal> amount)
{
MethodInfo info = (MethodInfo)(MethodInfo.GetCurrentMethod());
StoredProcedureResult result = this.ExecuteStoredProcedure(info, year, amount);
amount = ((System.Nullable <decimal>)(result.GetParameterValue(1)));
return(result.ReturnValue.Value);
}
public async Task <ActionResult> DisplayStoredProcedure()
{
var result = await _repository.GetStoredProcedureResult();
var model = new StoredProcedureResult
{
Result = result
};
return(View(model));
}
public async Task ExecuteStoredProcedureWithOptionalInputParameter()
{
StoredProcedureResult result = await _databaseCommander.BuildCommand()
.ForStoredProcedure("[dbo].[usp_OptionalInput_NoOutput_ReturnInt]")
.AddInputParameter("SampleTableID", 1)
.WithReturnParameter()
.ExecuteAsync(new CancellationToken());
int returnParameter = result.GetReturnParameter <int>();
Console.WriteLine("Return parameter: {0}", returnParameter);
}
public async Task ExecuteStoredProcedureWithInputOutputParameterSpecifyingType()
{
string inputOutputParameterName = "SampleInputOutputVarChar";
StoredProcedureResult result = await _databaseCommander.BuildCommand()
.ForStoredProcedure("[dbo].[usp_BigIntInput_VarCharOutput_TableResult]")
.AddInputParameter("SampleTableID", 1)
.AddInputOutputParameter(inputOutputParameterName, 1, SqlDbType.VarChar, 50)
.ExecuteAsync(new CancellationToken());
string inputOutputParameter = result.GetOutputParameter <string>(inputOutputParameterName);
Console.WriteLine("Output parameter: {0}", inputOutputParameter);
}
public async Task ExecuteStoredProcedureWithOutput()
{
string outputParameterName = "SampleOutputInt";
StoredProcedureResult result = await _databaseCommander.BuildCommand()
.ForStoredProcedure("[dbo].[usp_BigIntInput_IntOutput_NoResult]")
.AddInputParameter("SampleTableID", 1)
.AddOutputParameter(outputParameterName, SqlDbType.Int)
.ExecuteAsync(new CancellationToken());
int outputParameter = result.GetOutputParameter <int>(outputParameterName);
Console.WriteLine("Output parameter: {0}", outputParameter);
}
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 ExecuteStoredProcedureWithInputSpecifyingType()
{
StoredProcedureResult result = await _databaseCommander.BuildCommand()
.ForStoredProcedure("[dbo].[usp_OptionalInput_NoOutput_ReturnInt]")
.AddInputParameter("SampleTableID", 1, SqlDbType.BigInt)
.ExecuteAsync(new CancellationToken());
int count = result.Count;
bool hasData = result.HasData;
DataTable dataTable = result.DataTable;
Console.WriteLine("Row count: {0}", count);
Console.WriteLine("Has Data: {0}", hasData);
Console.WriteLine("DataTable: {0}", Print(dataTable));
}
public async Task ExecuteStoredProcedureAsync_WithVarCharInput_ShouldPassTheSizeAsExpected()
{
// Arrange & Act
StoredProcedureResult result = await SUT.BuildCommand()
.ForStoredProcedure("[dbo].[usp_VarCharInput_NoOutput_TableResult]")
.AddInputParameter("SampleVarChar", "Row 1", SqlDbType.VarChar, 1000)
.ExecuteAsync(new CancellationToken());
// Assert
result.ShouldNotBeNull();
result.HasData.ShouldBeTrue();
// Print result
WriteLine(result.DataTable);
}
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 void ExecuteStoredProcedureAsync_WithInputOutputParameterSpecifyingTypeAndSize_ShouldReturnTheOutputAsExpected()
{
// Arrange & Act
StoredProcedureResult result = SUT.BuildCommand()
.ForStoredProcedure("[dbo].[usp_VarCharInput_NoOutput_TableResult]")
.AddInputParameter("SampleVarChar", "Row 1", SqlDbType.VarChar, 1000)
.Execute();
// Assert
result.ShouldNotBeNull();
result.HasData.ShouldBeTrue();
// Print result
WriteLine(result.DataTable);
}
public void ExecuteStoredProcedure_WithOptionalInputParameterSpecifyingType_ShouldReturnAsExpected()
{
// Arrange & Act
StoredProcedureResult result = SUT.BuildCommand()
.ForStoredProcedure("[dbo].[usp_OptionalInput_NoOutput_ReturnInt]")
.AddInputParameter("SampleTableID", 1, SqlDbType.BigInt)
.WithReturnParameter()
.Execute();
// Assert
result.ShouldNotBeNull();
result.GetReturnParameter <int>().ShouldBeGreaterThan(0);
// Print result
WriteLine("Return Parameter: {0}", result.GetReturnParameter <int>());
}
public async Task ExecuteStoredProcedureAsync_WithOptionalInputParameter_ShouldReturnAsExpected()
{
// Arrange & Act
StoredProcedureResult result = await SUT.BuildCommand()
.ForStoredProcedure("[dbo].[usp_OptionalInput_NoOutput_ReturnInt]")
.AddInputParameter("SampleTableID", 1)
.WithReturnParameter()
.ExecuteAsync(new CancellationToken());
// Assert
result.ShouldNotBeNull();
result.GetReturnParameter <int>().ShouldBeGreaterThan(0);
// Print result
WriteLine("Return Parameter: {0}", result.GetReturnParameter <int>());
}
public async Task ExecuteStoredProcedureWithBehaviors()
{
StoredProcedureResult result = await _databaseCommander.BuildCommand()
.ForStoredProcedure("[dbo].[usp_VarCharInput_NoOutput_TableResult]")
.AddInputParameter("SampleVarChar", "Row 1", SqlDbType.VarChar, 1000)
.Behaviors(behavior => behavior.SingleResult().KeyInfo())
.ExecuteAsync(new CancellationToken());
int count = result.Count;
bool hasData = result.HasData;
DataTable dataTable = result.DataTable;
Console.WriteLine("Row count: {0}", count);
Console.WriteLine("Has Data: {0}", hasData);
Console.WriteLine("DataTable: {0}", Print(dataTable));
}
public void ExecuteStoredProcedureAsync_WithBehaviorsSet_ShouldRespectBehaviorSettings()
{
// Arrange & Act
StoredProcedureResult result = SUT.BuildCommand()
.ForStoredProcedure("[dbo].[usp_VarCharInput_NoOutput_TableResult]")
.AddInputParameter("SampleVarChar", "Row 1", SqlDbType.VarChar, 1000)
.Behaviors(behavior => behavior.SingleResult().KeyInfo())
.Execute();
// Assert
result.ShouldNotBeNull();
result.HasData.ShouldBeTrue();
// Print result
WriteLine(result.DataTable);
}
public async Task ExecuteStoredProcedureWithInputOutputAndReturn()
{
string outputParameterName = "SampleOutputBigInt";
StoredProcedureResult result = await _databaseCommander.BuildCommand()
.ForStoredProcedure("[dbo].[usp_Input_Output_ReturnBigInt]")
.AddInputParameter("SampleTableID", 0)
.AddInputOutputParameter(outputParameterName, 2)
.WithReturnParameter()
.ExecuteAsync(new CancellationToken());
int outputParameter = result.GetOutputParameter <int>(outputParameterName);
int returnParameter = result.GetReturnParameter <int>();
Console.WriteLine("Output parameter: {0}", outputParameter);
Console.WriteLine("Return parameter: {0}", returnParameter);
}
public void ExecuteStoredProcedure_WithOutputParameter_ShouldHaveOutputInResult()
{
// Arrange
string outputParameterName = "SampleOutputInt";
// Act
StoredProcedureResult result = SUT.BuildCommand()
.ForStoredProcedure("[dbo].[usp_BigIntInput_IntOutput_NoResult]")
.AddInputParameter("SampleTableID", 1)
.AddOutputParameter(outputParameterName, SqlDbType.Int)
.Execute();
// Assert
result.ShouldNotBeNull();
result.GetOutputParameter <int>(outputParameterName).ShouldBeGreaterThan(0);
// Print result
WriteLine("Output Parameter: {0}", result.GetOutputParameter <int>(outputParameterName));
}
public async Task <StoredProcedureResult> ExecuteStoredProcedureAsync(CosmosStoredProcedure storedProcedure, object?partitionKey, object?[] parameters, CancellationToken cancellationToken)
{
var result = new StoredProcedureResult();
var stopwatch = new Stopwatch();
try
{
stopwatch.Start();
var response = await _containerGetter().Scripts.ExecuteStoredProcedureAsync <JToken>(
storedProcedure.Id,
PartitionKeyHelper.Create(partitionKey),
parameters,
new StoredProcedureRequestOptions {
EnableScriptLogging = true
},
cancellationToken);
stopwatch.Stop();
result.Body = response.Resource;
result.ScriptLog = response.ScriptLog ?? string.Empty;
result.RequestCharge = response.RequestCharge;
result.StatusCode = response.StatusCode;
}
catch (CosmosException ex)
{
result.Error = ex;
result.StatusCode = ex.StatusCode;
var scriptLog = ex.Headers["x-ms-documentdb-script-log-results"] ?? string.Empty;
result.ScriptLog = Uri.UnescapeDataString(scriptLog);
}
catch (Exception ex)
{
result.Error = ex;
}
finally
{
stopwatch.Stop();
}
result.TimeElapsed = stopwatch.Elapsed;
return(result);
}
public void ExecuteStoredProcedure_WithInputOutputParameterSpecifyingTypeAndSize_ShouldReturnTheOutputAsExpected()
{
// Arrange
string outputParameterName = "SampleInputOutputVarChar";
// Act
StoredProcedureResult result = SUT.BuildCommand()
.ForStoredProcedure("[dbo].[usp_BigIntInput_VarCharOutput_TableResult]")
.AddInputParameter("SampleTableID", 1)
.AddInputOutputParameter(outputParameterName, 1, SqlDbType.VarChar, 50)
.Execute();
// Assert
result.ShouldNotBeNull();
result.HasData.ShouldBeTrue();
result.GetOutputParameter <string>(outputParameterName).ShouldNotBeNullOrEmpty();
// Print result
WriteLine("Output Parameter: {0}", result.GetOutputParameter <string>(outputParameterName));
WriteLine(result.DataTable);
}
public void ExecuteStoredProcedure_WithAllInputTypesAndTableResult_ShouldReturnListOfEntities()
{
// Arrange & Act
StoredProcedureResult <SampleEntity> result = SUT.BuildCommand()
.ForStoredProcedure <SampleEntity>("[dbo].[usp_VarCharInput_NoOutput_TableResult]")
.AddInputParameter("SampleVarChar", "Row 1")
.Project(sample =>
{
sample.Property(s => s.SampleId).MapFrom("SampleTableID");
sample.Property(s => s.SampleInt).MapFrom("SampleInt");
sample.Property(s => s.SampleSmallInt).MapFrom("SampleSmallInt");
sample.Property(s => s.SampleTinyInt).MapFrom("SampleTinyInt");
sample.Property(s => s.SampleBit).MapFrom("SampleBit");
sample.Property(s => s.SampleDecimal).MapFrom("SampleDecimal");
sample.Property(s => s.SampleFloat).MapFrom("SampleFloat");
sample.Property(s => s.SampleDateTime).MapFrom("SampleDateTime");
sample.Property(s => s.SampleUniqueIdentifier).MapFrom("SampleUniqueIdentifier");
sample.Property(s => s.SampleVarChar).MapFrom("SampleVarChar");
sample.Property(s => s.CreatedBy).MapFrom("CreatedBy");
sample.Property(s => s.CreatedDate).MapFrom("CreatedDate");
sample.Property(s => s.ModifiedBy).MapFrom("ModifiedBy");
sample.Property(s => s.ModifiedDate).MapFrom("ModifiedDate");
})
.Execute();
// Assert
result.ShouldNotBeNull();
result.HasData.ShouldBeTrue();
result.Data.ShouldNotBeNull();
result.Data.Count.ShouldBeGreaterThan(0);
result.Data.First().SampleId.ShouldBeGreaterThan(0);
result.Data.First().SampleDateTime.ShouldNotBe(DateTime.MinValue);
result.Data.First().SampleUniqueIdentifier.ShouldNotBe(Guid.Empty);
result.Data.First().SampleVarChar.ShouldNotBeNullOrEmpty("SampleVarChar");
// Print result
WriteLine(result.Data);
}
public async Task ProcessAsync(CancellationToken cancellationToken)
{
//await DatabaseCommander.ExecuteNonQueryAsync($"DELETE FROM {Settings.TableNameQualified}", cancellationToken);
DataTable dataTable = _delimitedFileHandler.GetFileAsDataTable(Settings.FilePath, '~');
await DatabaseCommander.BuildCommand()
.ForBulkCopy()
.Into(Settings.TableNameQualified)
.From(dataTable)
.Mapping(mapping => mapping.UseAutoMap())
.ExecuteAsync(cancellationToken);
StoredProcedureResult result = await DatabaseCommander.BuildCommand()
.ForStoredProcedure("[dbo].[LoadData]")
.AddInputParameter("DataFile", Settings.FilePath)
.ExecuteAsync(cancellationToken);
if (result.GetReturnParameter <string>() == "-1")
{
throw new Exception("[dbo].[LoadData] encountered error");
}
}
public void ExecuteStoredProcedure_WithInputOutputParameter_ShouldReturnTheOutputAsExpected()
{
// Arrange
string outputParameterName = "SampleInputOutputInt";
int inputValue = 1;
// Act
StoredProcedureResult result = SUT.BuildCommand()
.ForStoredProcedure("[dbo].[usp_BigIntInput_IntInputOutput_TableResult]")
.AddInputParameter("SampleTableID", 1)
.AddInputOutputParameter(outputParameterName, inputValue)
.Execute();
// Assert
result.ShouldNotBeNull();
result.HasData.ShouldBeTrue();
result.GetOutputParameter <int>(outputParameterName).ShouldBeGreaterThan(0);
result.GetOutputParameter <int>(outputParameterName).ShouldNotBe(inputValue);
// Print result
WriteLine("Output Parameter: {0}", result.GetOutputParameter <int>(outputParameterName));
WriteLine(result.DataTable);
}
public async Task ExecuteStoredProcedureWithAllInputTypesAndTableResult()
{
StoredProcedureResult result = await _databaseCommander.BuildCommand()
.ForStoredProcedure("[dbo].[usp_AllInputTypes_NoOutput_TableResult]")
.AddInputParameter("SampleTableID", 1)
.AddInputParameter("SampleInt", 0)
.AddInputParameter("SampleSmallInt", 0)
.AddInputParameter("SampleTinyInt", 0)
.AddInputParameter("SampleBit", 0)
.AddInputParameter("SampleDecimal", 0)
.AddInputParameter("SampleFloat", 0)
.AddInputParameter("SampleDateTime", DateTime.Now)
.AddInputParameter("SampleUniqueIdentifier", Guid.NewGuid())
.AddInputParameter("SampleVarChar", "Row 1")
.ExecuteAsync(new CancellationToken());
int count = result.Count;
bool hasData = result.HasData;
DataTable dataTable = result.DataTable;
Console.WriteLine("Row count: {0}", count);
Console.WriteLine("Has Data: {0}", hasData);
Console.WriteLine("DataTable: {0}", Print(dataTable));
}
public void ExecuteStoredProcedure_WithAllInputTypesAndTableResult_ShouldReturnDataTable()
{
// Arrange & Act
StoredProcedureResult result = SUT.BuildCommand()
.ForStoredProcedure("[dbo].[usp_AllInputTypes_NoOutput_TableResult]")
.AddInputParameter("SampleTableID", 1)
.AddInputParameter("SampleInt", 0)
.AddInputParameter("SampleSmallInt", 0)
.AddInputParameter("SampleTinyInt", 0)
.AddInputParameter("SampleBit", false)
.AddInputParameter("SampleDecimal", 0)
.AddInputParameter("SampleFloat", 0)
.AddInputParameter("SampleDateTime", DateTime.Now)
.AddInputParameter("SampleUniqueIdentifier", Guid.NewGuid())
.AddInputParameter("SampleVarChar", "Row 1")
.Execute();
// Assert
result.ShouldNotBeNull();
result.HasData.ShouldBeTrue();
// Print result
WriteLine(result.DataTable);
}
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 void ExecuteStoredProcedure_WithInputOutputAndReturn_ShouldReturnAsExpected()
{
// Arrange
string outputParameterName = "SampleOutputBigInt";
int inputValue = 2;
// Act
StoredProcedureResult result = SUT.BuildCommand()
.ForStoredProcedure("[dbo].[usp_Input_Output_ReturnBigInt]")
.AddInputParameter("SampleTableID", 0)
.AddInputOutputParameter(outputParameterName, inputValue)
.WithReturnParameter()
.Execute();
// Assert
result.ShouldNotBeNull();
result.GetOutputParameter <int>(outputParameterName).ShouldBeGreaterThan(0);
result.GetOutputParameter <int>(outputParameterName).ShouldNotBe(inputValue);
result.GetReturnParameter <int>().ShouldBeGreaterThan(0);
// Print result
WriteLine("Output Parameter: {0}", result.GetOutputParameter <int>(outputParameterName));
WriteLine("Return Parameter: {0}", result.GetReturnParameter <int>());
}
