public void Execute_Create_Procedure_And_Read_Results()
{
using var connection = new SqlConnection(fixture.ConnectionString);
var results = NormExtensions.SelectDictionaries(ConnectionExtensions.Read(ConnectionExtensions.AsProcedure(ConnectionExtensions.Execute(connection, @"
create procedure TestStoredProcedure(@id int)
as
select * from (
values
(1, 'foo1', cast('1977-05-19' as date)),
(2, 'foo2', cast('1978-05-19' as date)),
(3, 'foo3', cast('1979-05-19' as date))
) t(first, bar, day)
where first = @id
")), "TestStoredProcedure", ("id", 1)))
.ToList();
Assert.Single(results);
Assert.Equal(1, results[0].Values.First());
Assert.Equal("foo1", results[0]["bar"]);
Assert.Equal(new DateTime(1977, 5, 19), results[0]["day"]);
}
public void Execute_Create_Empty_Proc_Execute_And_Drop_Procedure()
{
using var connection = new SqlConnection(fixture.ConnectionString);
ConnectionExtensions.Execute(ConnectionExtensions.AsText(ConnectionExtensions.Execute(ConnectionExtensions.AsProcedure(ConnectionExtensions.Execute(connection, "create procedure EmptyStoreProc as /* empty */")), "EmptyStoreProc")), "drop procedure EmptyStoreProc");
var procMissing = false;
try
{
ConnectionExtensions.Execute(ConnectionExtensions.As(connection, CommandType.StoredProcedure), "EmptyStoreProc");
}
catch (SqlException)
{
procMissing = true;
}
Assert.True(procMissing);
}