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