Creating a T-SQL Query Generator Using Microsoft.Extensions.AI

Get smarter data insights from your own tables: The Microsoft.Extensions.AI package lets us create AI queries of MS-SQL databases.

In this post, I’ll demonstrate a working solution for creating T-SQL queries using the Microsoft.Extensions.AI NuGet package based on the tables and columns from MS-SQL databases transforming the table structure into a system parameter for the AI to build Selects based on it. And it provides some insights about what to ask.

Image created with AI

How It Works

The application reads the local Microsoft Database Servers on your local machine, allowing you to select one from the list.

The tables are listed on a ListView, allowing you to choose which tables you like to use in the selection. Below the table, there is a list of available columns that will be used by AI to create the query.

After selecting the desired tables and when clicking on the prompt, a request is automatically made to the AI with the prompt: “Tell me 5 things you can answer based on these data.”

So, you will have some ideas about what to write a prompt to the AI query data.

Below is the basic use, selecting only some tables:

Image from the main application

In the image above, we can see suggestions from the AI about the selected tables/columns.

A Full Insight Analysis

Below is the result from the prompt “Generate a SQL query that demonstrates revenue by region.”

Pressing the “View Grid” button will open a new window with a grid with the data:

After that, it’s possible to select the data and format it in Microsoft Excel:

Tip: Pressing F10 on the ListView will cause all items to switch on or off.

The Source Code

The logic of the code is pretty simple. We load all databases into a DropDownList from localhost and trigger them to load in a ListView.

1.	 private void LoadDBs()
2.	 {
3.	     string connectionString = "Server=localhost;Integrated Security=true;TrustServerCertificate=True;";
4.	     string query = "SELECT name FROM sys.databases";
5.	
6.	     using var connection = new SqlConnection(connectionString);
7.	
8.	     using var command = new SqlCommand(query, connection);
9.	     connection.Open();
10.	     using var reader = command.ExecuteReader();
11.	
12.	     while (reader.Read())
13.	     {
14.	         this.radDropDownList1.Items.Add(reader["name"].ToString());
15.	     }
16.	
17.	     this.radDropDownList1.SelectedIndexChanged += RadDropDownList1_SelectedIndexChanged;
18.	 }
19.	
20.	 private void RadDropDownList1_SelectedIndexChanged(object sender, Telerik.WinControls.UI.Data.PositionChangedEventArgs e)
21.	 {
22.	     string selectedDatabase = this.radDropDownList1.SelectedItem.Text;
23.	     string connectionString = $"Server=localhost;Database={selectedDatabase};Integrated Security=true;TrustServerCertificate=True;";
24.	     string query = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME";
25.	
26.	     using var connection = new SqlConnection(connectionString);
27.	     using var command = new SqlCommand(query, connection);
28.	     connection.Open();
29.	     using var reader = command.ExecuteReader();
30.	
31.	     this.radListView1.Items.Clear();
32.	     while (reader.Read())
33.	     {
34.	         var schema = reader["TABLE_SCHEMA"].ToString();
35.	         var tableName = reader["TABLE_NAME"].ToString();
36.	         var recordCount = GetTableRecordCount(connectionString, schema + "." + tableName);
37.	         var item = new ListViewDataItem();
38.	         item[0] = schema;
39.	         item[1] = tableName;
40.	         item[2] = recordCount.ToString();
41.	         this.radListView1.Items.Add(item);
42.	     }
43.	     this.radListView1.SelectedIndex = 0;
44.	 }

When the user checks a table, it fires an event to load all columns in another ListView (this helps the user to choose a column for the query), fills the property SystemPrompt with column information for the AI, and resets the Suggestion flag.

1.	   private void RadListView1_ItemCheckedChanged(object sender, ListViewItemEventArgs e)
2.	   {
3.	       SystemPrompt = GetSystemPrompt();
4.	       Suggestion = false;
5.	   }
6.	
7.	   private string GetSystemPrompt()
8.	   {
9.	       this.radLabel2.Text = "";
10.	
11.	       var sb = new StringBuilder();
12.	
13.	       sb.AppendLine("You are a data analyst and will generate T-SQL queries from this structure, response based on the database strucuture that you have access that are:");
14.	
15.	
16.	       this.radListView2.Items.Clear();
17.	
18.	       foreach (ListViewDataItem item in this.radListView1.CheckedItems)
19.	       {
20.	           string schema = item[0].ToString();
21.	           string tableName = item[1].ToString();
22.	           string connectionString = $"Server=localhost;Database={this.radDropDownList1.SelectedItem.Text};Integrated Security=true;TrustServerCertificate=True;";
23.	           string query = $@"
24.	           SELECT 
25.	               TABLE_SCHEMA as SchemaName,
26.	               '{tableName}' AS TableName,
27.	               COLUMN_NAME AS ColumnName,
28.	               DATA_TYPE AS DataType,
29.	               CHARACTER_MAXIMUM_LENGTH AS Size
30.	           FROM INFORMATION_SCHEMA.COLUMNS
31.	           WHERE TABLE_SCHEMA = '{schema}' AND TABLE_NAME = '{tableName}' ";
32.	
33.	           using var connection = new SqlConnection(connectionString);
34.	           using var command = new SqlCommand(query, connection);
35.	           connection.Open();
36.	           using var reader = command.ExecuteReader();
37.	
38.	
39.	           while (reader.Read())
40.	           {
41.	               var col = new ListViewDataItem();
42.	               col[0] = reader["TableName"].ToString();
43.	               col[1] = reader["ColumnName"].ToString();
44.	               col[2] = reader["DataType"].ToString();
45.	
46.	               this.radListView2.Items.Add(col);
47.	
48.	               sb.AppendLine($"{reader["SchemaName"]}.{reader["TableName"]}, {reader["ColumnName"]}, {reader["DataType"]}, {reader["Size"]}");
49.	           }
50.	
51.	       }
52.	
53.	       if (this.radListView2.Items.Count > 0)
54.	           this.radListView2.SelectedIndex = 0;
55.	
56.	       string result = sb.ToString();
57.	       return sb.ToString();
58.	   }

When the user enters focus on the radTextBox1 to write the prompt, the application automatically calls the AI GetResponse with a prompt to generate suggestions about what the selected tables and columns can be made. The Suggestion var is a control to not generate other responses without any changes in the selection.

1.	    private async void radTextBox1_Enter(object sender, EventArgs e)
2.	    {
3.	        const string Prompt = "Tell me 5 things you can answer based on these data.";
4.	        if (!Suggestion && this.radListView2.Items.Count > 0)
5.	        {
6.	            Suggestion = true;
7.	            this.radTextBox2.Text = Prompt;
8.	
9.	            var result = await AI.GetResponse(false, SystemPrompt, Prompt);
10.	
11.	            this.radTextBox2.Text = Prompt + "\r\n" + result.Item2.Replace("\n", "\r\n").Replace("**", "");
12.	        }
13.	    }

In the button “Generate” we fire the event to get the result:

1.	    private async void radButton1_ClickAsync(object sender, EventArgs e)
2.	    {
3.	        if (string.IsNullOrEmpty(SystemPrompt))
4.	        {
5.	            this.radLabel2.Text = "Please select a table";
6.	            return;
7.	        }
8.	
9.	        var result = await AI.GetResponse(this.radCheckBox1.Checked, SystemPrompt, this.radTextBox1.Text);
10.	
11.	        this.radTextBox2.Text = result.Item1.Replace("\n", "\r\n");
12.	        this.radButton2.Visible = true;
13.	    }

Now, we use Microsoft.Extensions.AI which embeds the logic from the OpenAI API and the Azure AI API as well. Using the interface IChatClient it’s possible to use OpenAI, AzureOpenAI or Ollama AI (not implemented in this sample). We set the client interface, and from line 39 ahead the code is the same for all Large Language Models (LLMs). The Microsoft.Extensions.AI will soon support other LLMs.

You’ll need to add the information from your account for the keys OPENAI_API_KEY and AZURE_OPENAI_ENDPOINT (and others).

1.	using Azure;
2.	using Microsoft.Extensions.AI;
3.	using OpenAI;
4.	using System;
5.	using System.Collections.Generic;
6.	using System.Linq;
7.	using System.Threading.Tasks;
8.	
9.	namespace TelerikWinFormsApp1;
10.	
11.	class AI
12.	{
13.	    public static async Task<(string, string)> GetResponse(bool useAzure, string systemPrompt, string prompt)
14.	    {
15.	        IChatClient client;
16.	
17.	        if (useAzure)
18.	        {
19.	            var endpoint = Environment.GetEnvironmentVariable("AZURE_OPENAI_ENDPOINT") ?? "";
20.	            var key = Environment.GetEnvironmentVariable("AZURE_OPENAI_KEY") ?? "";
21.	            string deploymentName = Environment.GetEnvironmentVariable("AZURE_OPENAI_DEPLOYMENT_NAME") ?? "";
22.	
23.	            var aoaiClient = new OpenAIClient(
24.	                    new AzureKeyCredential(key),
25.	                    new OpenAIClientOptions
26.	                    {
27.	                        Endpoint = new Uri(endpoint)
28.	                    }
29.	                );
30.	
31.	            var chatClient = aoaiClient.GetChatClient(deploymentName);
32.	            client = chatClient.AsIChatClient();
33.	        }
34.	        else
35.	        {
36.	            var apiKey = Environment.GetEnvironmentVariable("OPENAI_API_KEY") ?? "";
37.	            var chatClient = new OpenAI.Chat.ChatClient("gpt-4o-mini", apiKey);
38.	            client = chatClient.AsIChatClient();
39.	        }
40.	
41.	        var messages = new List<ChatMessage>
42.	            {
43.	                new(ChatRole.System, systemPrompt),
44.	                new(ChatRole.User, prompt)
45.	            };
46.	
47.	        var response = await client.GetResponseAsync(messages);
48.	        string responseText = response?.Text ?? string.Empty;
49.	
50.	        string[] responseParts = responseText.Split(new[] { "\n\n" }, StringSplitOptions.None);
51.	        var explanationText = responseParts.FirstOrDefault(part => !part.Contains("```sql", StringComparison.OrdinalIgnoreCase)) ?? string.Empty;
52.	
53.	        var sqlQuery = string.Join("\r\n", responseParts);
54.	        var sql = Tools.ClearSql(sqlQuery);
55.	
56.	        if (string.IsNullOrEmpty(sql))
57.	        {
58.	            explanationText = sqlQuery;
59.	        }
60.	
61.	        return (sql, explanationText);
62.	    }
63.	}

The ClearSql function gets the SQL code from the response using RegEx:

1.	    public static string ClearSql(string queryTSql)
2.	    {
3.	        var match = Regex.Match(queryTSql, @"```sql\s*(.*?)\s*```", RegexOptions.Singleline);
4.	        if (!match.Success) return "";
5.	        string tsql = match.Groups[1].Value;
6.	        return tsql;
7.	    }

The secret of this application is to pass a system prompt. We do this at: new ChatMessage(ChatRole.System, systemPrompt),.

The systemPrompt, as seen in the other code above, is:

"You are a data analyst and will generate T-SQL queries from this structure, response based on the database structure that you have access that are:"

And the structure is the ChatRole.System.

ChatRole.System is the engine that prepares the user prompt to respond correctly to the user prompt. If you will write this code for a specific database, you can add more information here to help the AI understand the context and relations of the data.

Conclusion

This application can be improved to help developers and data analysts create queries with AI assistance. On my tests with my databases, sometimes the AI returns incomplete results, but adjusting the prompt returns a correct query.

Microsoft Extensions AI is a powerful API to avoid creating a specific code to implement the AI API and soon we will have implementations for Claude AI, Gemini and other AIs from the global market.

The complete source code is available at my GitHub https://github.com/jssmotta/MsExtensionsAI25/. You will need a Telerik license to use this sample.