Query Analyst with Semantic.Kernel

In this post, I’ll demonstrate Semantic.Kernel, an open-source AI SDK provided by Microsoft, which makes it easy to interact with large language models (LLMs) and their services.

This post demonstrates a powerful console chat, based on the entire SQL Server database schema, which maintains the session and chat history, displaying the results of executed queries and allowing users to ask questions about the data.

How This Sample App Works

I started with a template generated from the Claude AI prompt and customized it to meet my needs. It’s an app idealized mainly for IT people that reads the structure of a database and replies with data or T-SQL commands. It requires understanding the data stored and how it is structured in the database.

This app is like using the integrated Microsoft Copilot in the last version of Microsoft SQL Server Management Studio without a subscription to Azure AI.

The app starts reading the databases from the current setting ConnectionStrings/SqlServer and allows the user to select one by navigating with arrow keys and pressing ENTER. The connection strings contain the property ApplicationIntent=ReadOnly, so the generated queries will not change the database.

After the selection, it generates a cache from the current schema to save resources in the next session with the same database.

The console will allow the user to chat in natural language and ask to build queries. If a query is generated, it’s possible to execute it. When it’s executed, the data is added to the history of the chat, so the next question you ask will consider the last query data.

Semantic.Kernal Key Steps

“Semantic Kernel is a lightweight, open-source development kit that lets you easily build AI agents and integrate the latest AI models into your C#, Python, or Java codebase. It serves as an efficient middleware that enables rapid delivery of enterprise-grade solutions.”

https://learn.microsoft.com/en-us/semantic-kernel/overview/

Semantic.Kernel uses a modular plugin that can be reused in several solutions; for example, we can call our plugins and arguments dynamically. There is too much code involved to share everything in this post, so I’ll just demo the main parts here.

I built a class to be a plugin, SqlQueryPlugin, for example:

1.	public class SqlQueryPlugin
2.	{
3.	    private readonly DatabaseSchema _databaseSchema;
4.	    public SqlQueryPlugin(DatabaseSchema databaseSchema)
5.	    {
6.	        _databaseSchema = databaseSchema;
7.	    }
8.	
9.	    [KernelFunction]
10.	    [Description("Generates a SQL query based on the user's natural language request and database schema")]
11.	    public async Task<string> GenerateSqlQuery(
12.	     [Description("User's natural language request for a SQL query")] string input,
13.	     Kernel kernel,
14.	     [Description("Optional chat history for context")] ChatHistory? existingHistory = null)
15.	    {

And initialize the Semantic.Kernel:

1.	var builder = Kernel.CreateBuilder();
2.	
3.	string apiKey = Environment.GetEnvironmentVariable("OPENAI_API_KEY") ?? throw new Exception("OpenAI API Key not found");
4.	
5.	string modelId = "gpt-4o-mini";
6.	
7.	builder.AddOpenAIChatCompletion(
8.	    modelId: modelId,
9.	    apiKey: apiKey
10.	);            
11.	
12.	var kernel = builder.Build();

… and activate it:

1.	var sqlQueryPlugin = new SqlQueryPlugin(databaseSchema);
2.	
3.	kernel.Plugins.Add(KernelPluginFactory.CreateFromObject(sqlQueryPlugin, "SqlQueryPlugin"));

Note: We can select a list of services to be used according to our needs:

Context menu from VS 2022

In the code above, we are using AddOpenAIChatCompletion service.

To invoke the service, we pass the arguments on the KernelArguments:

1.	var arguments = new KernelArguments
2.	{
3.	    ["input"] = userInput,
4.	    ["existingHistory"] = chatHistory
5.	};
6.	
7.	var result = await kernel.InvokeAsync<string>("SqlQueryPlugin", "GenerateSqlQuery", arguments);

The first parameter is our plugin name, SqlQueryPlugin, and the second is the method we like to use.

This is so powerful because your logic can load the parameters from settings and load them as needed. For example, instead of input parameter, we can load it from a memory var with any other value:

1.	var inputParam = isBasic ? “input” : "inputArg2";
2.	
3.	var arguments = new KernelArguments
4.	{
5.	    [inputParam] = userInput,
6.	    ["existingHistory"] = chatHistory
7.	};

In the sample, instead of the static "input", we changed "input" to "inputArg2" according to a boolean var isBasic. inputArg2 did not exist in the code; it’s just a sample of use.

Also, the string for the plugin name "SqlQueryPlugin" can be a setting, and we can load another plugin according to our needs, for example, "MySqlQueryPlugin" or another database type—not in this sample, which is for Microsoft SqlServer only.

Working Sample

When executing the console app, we will have this result:

Console with the AdventureWorks selected

I asked: “What are the total sales by region in 2004?”

Prompt

And the prompt returned this:

Note: The purple SQL Query was extracted by a script filtering the sql … from the output.

We can ask what we can prompt:

Prompt

If you don’t know about it, the prompt will reply with some insights:

Prompt output

I tried: “What is the average sales per salesperson?”

Prompt

This is the result: Sales Person ID and amount:

Query result

And I asked for more information:

Prompt

Now the output returns the FirstName and LastName:

Query result

Now, we format the data:

Prompt

The result is now formatted:

Query result

These prompt samples demonstrate that the Kernel recognizes the history that we created together with the Kernel object:

1.	var chatHistory = new ChatHistory();
2.	
3.	chatHistory.AddSystemMessage($"You are a Data Analyst and SQL expert assistant for the database: {schemaExtractor.DatabaseName}");

And updated after query execution:

1.	 if (executeResponse.ToLower() == "yes" || executeResponse.ToLower() == "y")
2.	 {
3.	     WriteColorLine("Executing query...", ConsoleColors.Prompt);
4.	     var queryResults = await schemaExtractor.ExecuteQueryAsync(resultSql);
5.	     DisplayQueryResults(queryResults);
6.	
7.	     chatHistory.AddUserMessage(userInput);
8.	     chatHistory.AddAssistantMessage(result);
9.	     chatHistory.AddUserMessage($"I executed the query and it returned {queryResults.Rows.Count} rows.");
10.	 }
11.	 else
12.	 {
13.	     chatHistory.AddUserMessage(userInput);
14.	     chatHistory.AddAssistantMessage(result);
15.	     chatHistory.AddUserMessage("I chose not to execute this query.");
16.	 }

And Finally

GenerateSqlQuery is the endpoint where things are really done. Set the database schema to the history at line 12. Set the configuration for the service at line 31. In lines 37, 38 and 40, we force the AI to return a more deterministic output. And return the result at line 44.

1.	public async Task<string> GenerateSqlQuery(
2.	    [Description("User's natural language request for a SQL query")] string input,
3.	    Kernel kernel,
4.	    [Description("Optional chat history for context")] ChatHistory? existingHistory = null)
5.	{
6.	    string schemaDescription = GetDatabaseSchemaDescription();
7.	         
8.	    var chatHistory = existingHistory ?? new ChatHistory();
9.	         
10.	    if (existingHistory == null || chatHistory.Count()==1)
11.	    {
12.	        chatHistory.AddSystemMessage(@$"
13.	# SQL Query Assistant
14.	
15.	You are a Data Analyst and SQL expert who helps translate natural language questions about databases into SQL queries.
16.	
17.	## Database Schema
18.	{schemaDescription}
19.	
20.	## Guidelines
21.	- Use proper T-SQL syntax for SQL Server
22.	- Include appropriate JOINs when data from multiple tables is needed
23.	- Format your output as a clear explanation followed by the SQL query in ```sql``` code blocks
24.	- Always use schema name in the query (example: dbo.TableName)
25.	- For queries that involve aggregations, add appropriate GROUP BY clauses
26.	- Handle NULLs appropriately");
27.	    }
28.	         
29.	    chatHistory.AddUserMessage(input);
30.	         
31.	    var chatCompletionService = kernel.GetRequiredService<IChatCompletionService>();
32.	         
33.	    var completionResult = await chatCompletionService.GetChatMessageContentAsync(
34.	        chatHistory,
35.	        new OpenAIPromptExecutionSettings
36.	        {
37.	            MaxTokens = 2000,
38.	            Temperature = 0.0,
39.	            TopP = 0.95
40.	        });
41.	         
42.	    chatHistory.AddAssistantMessage(completionResult.ToString());
43.	
44.	    return completionResult.ToString().Trim();
45.	}

Conclusion

The app returned mostly with success, but it’s necessary that the user understands how a database works, and the system arguments need to explain specific details about how the database schema is designed.

For developers, data analysts and DBAs, it’s a great tool that can examine, create queries, retrieve information about the database and extract data.

Semantic.Kernel helps to create a clean and organized source code that can be shared across our projects. This app can be upgraded to work with other database types by changing a little bit of the basic code.

References

Microsoft Learn
GitHub Source Code