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