public async Task It_can_connect_and_query_data()
{
var connectionString = MsSqlFactAttribute.GetConnectionStringForTests();
using var kernel = await CreateKernelAsync();
var result = await kernel.SubmitCodeAsync(
$"#!connect --kernel-name adventureworks mssql \"{connectionString}\"");
result.KernelEvents
.ToSubscribedList()
.Should()
.NotContainErrors();
result = await kernel.SubmitCodeAsync(@"
#!sql-adventureworks
SELECT TOP 100 * FROM Person.Person
");
var events = result.KernelEvents.ToSubscribedList();
events.Should()
.NotContainErrors()
.And
.ContainSingle <DisplayedValueProduced>(e =>
e.FormattedValues.Any(f => f.MimeType == PlainTextFormatter.MimeType));
events.Should()
.ContainSingle <DisplayedValueProduced>(e =>
e.FormattedValues.Any(f => f.MimeType == HtmlFormatter.MimeType));
}
public async Task sending_query_to_sql_will_generate_suggestions()
{
var connectionString = MsSqlFactAttribute.GetConnectionStringForTests();
using var kernel = await CreateKernelAsync();
var result = await kernel.SubmitCodeAsync(
$"#!connect --kernel-name adventureworks mssql \"{connectionString}\"");
result.KernelEvents
.ToSubscribedList()
.Should()
.NotContainErrors();
result = await kernel.SubmitCodeAsync(@"
#!sql
SELECT TOP 100 * FROM Person.Person
");
var events = result.KernelEvents.ToSubscribedList();
events.Should()
.NotContainErrors()
.And
.ContainSingle <DisplayedValueProduced>(e =>
e.FormattedValues.Any(f => f.MimeType == HtmlFormatter.MimeType))
.Which.FormattedValues.Single(f => f.MimeType == HtmlFormatter.MimeType)
.Value
.Should()
.Contain("#!sql-adventureworks")
.And
.Contain(" SELECT TOP * FROM");
}
public async Task Multiple_shared_variable_can_be_used_to_parameterize_a_sql_query()
{
using var kernel = await CreateKernelAsync();
var result = await kernel.SubmitCodeAsync(
$"#!connect --kernel-name adventureworks mssql \"{MsSqlFactAttribute.GetConnectionStringForTests()}\"");
result.KernelEvents
.ToSubscribedList()
.Should()
.NotContainErrors();
var csharpCode = "string x = \"Hello world!\";";
await kernel.SendAsync(new SubmitCode(csharpCode));
csharpCode = "int y = 123;";
await kernel.SendAsync(new SubmitCode(csharpCode));
var code = @"
#!sql-adventureworks
#!share --from csharp x
#!share --from csharp y
select @x, @y";
result = await kernel.SendAsync(new SubmitCode(code));
var events = result.KernelEvents.ToSubscribedList();
events.ShouldDisplayTabularDataResourceWhich().Data
.Should()
.ContainSingle()
.Which
.Should()
.ContainValues(new object[] { "Hello world!", 123 });
}
public async Task It_can_store_result_set_with_a_name()
{
var connectionString = MsSqlFactAttribute.GetConnectionStringForTests();
using var kernel = await CreateKernelAsync();
await kernel.SubmitCodeAsync(
$"#!connect --kernel-name adventureworks mssql \"{connectionString}\"");
// Run query with result set
await kernel.SubmitCodeAsync($@"
#!sql-adventureworks --name my_data_result
select * from sys.databases
");
// Use share to fetch result set
var csharpResults = await kernel.SubmitCodeAsync($@"
#!csharp
#!share --from sql-adventureworks my_data_result
my_data_result");
// Verify the variable loaded is of the correct type and has the expected number of result sets
var csharpEvents = csharpResults.KernelEvents.ToSubscribedList();
csharpEvents
.Should()
.ContainSingle <ReturnValueProduced>()
.Which
.Value
.Should()
.BeAssignableTo <IEnumerable <TabularDataResource> >()
.Which.Count()
.Should()
.Be(1);
}
public async Task Empty_results_are_displayed_correctly()
{
var connectionString = MsSqlFactAttribute.GetConnectionStringForTests();
using var kernel = await CreateKernelAsync();
var result = await kernel.SubmitCodeAsync(
$"#!connect --kernel-name adventureworks mssql \"{connectionString}\"");
result.KernelEvents
.ToSubscribedList()
.Should()
.NotContainErrors();
result = await kernel.SubmitCodeAsync($@"
#!sql-adventureworks
use tempdb;
create table dbo.EmptyTable(column1 int, column2 int, column3 int);
select * from dbo.EmptyTable;
drop table dbo.EmptyTable;
");
var events = result.KernelEvents.ToSubscribedList();
events.Should()
.NotContainErrors()
.And
.ContainSingle <DisplayedValueProduced>(e =>
e.FormattedValues.Any(f => f.MimeType == PlainTextFormatter.MimeType && f.Value.ToString().StartsWith("Info")));
}
public async Task query_produces_expected_formatted_values()
{
var connectionString = MsSqlFactAttribute.GetConnectionStringForTests();
using var kernel = await CreateKernelAsync();
var result = await kernel.SubmitCodeAsync(
$"#!connect --kernel-name adventureworks mssql \"{connectionString}\"");
result.KernelEvents
.ToSubscribedList()
.Should()
.NotContainErrors();
result = await kernel.SubmitCodeAsync($@"
#!sql-adventureworks
select * from sys.databases
");
var events = result.KernelEvents.ToSubscribedList();
events.Should().NotContainErrors();
events.Should()
.ContainSingle <DisplayedValueProduced>(fvp => fvp.Value is DataExplorer <TabularDataResource>)
.Which
.FormattedValues.Select(fv => fv.MimeType)
.Should()
.BeEquivalentTo(HtmlFormatter.MimeType, CsvFormatter.MimeType);
}
public async Task Field_types_are_deserialized_correctly()
{
var connectionString = MsSqlFactAttribute.GetConnectionStringForTests();
using var kernel = await CreateKernelAsync();
var result = await kernel.SubmitCodeAsync(
$"#!connect --kernel-name adventureworks mssql \"{connectionString}\"");
result.KernelEvents
.ToSubscribedList()
.Should()
.NotContainErrors();
result = await kernel.SubmitCodeAsync($@"
#!sql-adventureworks
select * from sys.databases
");
var events = result.KernelEvents.ToSubscribedList();
events.ShouldDisplayTabularDataResourceWhich()
.Schema
.Fields
.Should()
.ContainSingle(f => f.Name == "database_id")
.Which
.Type
.Should()
.Be(TableSchemaFieldType.Integer);
}
public async Task It_can_scaffold_a_DbContext_in_a_CSharpKernel()
{
var connectionString = MsSqlFactAttribute.GetConnectionStringForTests();
using var kernel = await CreateKernelAsync();
var result = await kernel.SubmitCodeAsync(
$"#!connect --kernel-name adventureworks mssql \"{connectionString}\" --create-dbcontext");
var events = result.KernelEvents.ToSubscribedList();
events.Should().NotContainErrors();
result = await kernel.SubmitCodeAsync("adventureworks.AddressTypes.Count()");
events = result.KernelEvents.ToSubscribedList();
events.Should().NotContainErrors();
events.Should()
.ContainSingle <ReturnValueProduced>()
.Which
.Value
.As <int>()
.Should()
.Be(6);
}
[InlineData("var testVar = new List<int>();")] // Unsupported type
public async Task Invalid_shared_variables_are_handled_correctly(string csharpVariableDeclaration, bool isCSharpError = false)
{
using var kernel = await CreateKernelAsync();
var result = await kernel.SubmitCodeAsync(
$"#!connect --kernel-name adventureworks mssql \"{MsSqlFactAttribute.GetConnectionStringForTests()}\"");
result.KernelEvents
.ToSubscribedList()
.Should()
.NotContainErrors();
var cSharpResult = await kernel.SendAsync(new SubmitCode(csharpVariableDeclaration));
var cSharpEvents = cSharpResult.KernelEvents.ToSubscribedList();
if (isCSharpError)
{
cSharpEvents
.Should()
.ContainSingle <CommandFailed>();
}
var code = @"
#!sql-adventureworks
#!share --from csharp testVar
select @testVar";
result = await kernel.SendAsync(new SubmitCode(code));
var events = result.KernelEvents.ToSubscribedList();
var assertion = events
.Should()
.ContainSingle <CommandFailed>();
if (!isCSharpError)
{
// Errors that occurred in the csharp block will result in this failing, but not with an inner exception
assertion
.Which
.Exception
.Should()
.BeOfType <InvalidOperationException>();
}
}
[InlineData("short testVar = 123;", (short)123)] // short
public async Task Shared_variable_can_be_used_to_parameterize_a_sql_query(string csharpVariableDeclaration, object expectedValue, Type changeType = null)
{
using var kernel = await CreateKernelAsync();
var result = await kernel.SubmitCodeAsync(
$"#!connect --kernel-name adventureworks mssql \"{MsSqlFactAttribute.GetConnectionStringForTests()}\"");
result.KernelEvents
.ToSubscribedList()
.Should()
.NotContainErrors();
await kernel.SendAsync(new SubmitCode(csharpVariableDeclaration));
var code = @"
#!sql-adventureworks
#!share --from csharp testVar
select @testVar";
result = await kernel.SendAsync(new SubmitCode(code));
var events = result.KernelEvents.ToSubscribedList();
var data = events.ShouldDisplayTabularDataResourceWhich();
if (changeType != null)
{
// Decimals can't be made constants so need to convert at runtime
expectedValue = Convert.ChangeType(expectedValue, changeType);
}
data.Data
.Should()
.ContainSingle()
.Which
.Should()
.ContainValue(expectedValue);
}
static MsSqlTheoryAttribute()
{
_skipReason = MsSqlFactAttribute.TestConnectionAndReturnSkipReason();
}
