David Au Yeung

Posted on Jul 18 • Edited on Jul 21

Building a Text-to-SQL AI Assistant with lightweight LLM, and Semantic Kernel in C#: A Fun Experiment

#texttosql #llm #semantickernel #dotnet

Introduction

Have you ever wondered how to bridge the gap between natural language and structured database queries? In this guide, we'll explore how to build a Text-to-SQL AI Assistant using C#, Semantic Kernel, and a lightweight llama3.2-3B model. Yes, you read that right: a low-cost, smaller-scale LLM like llama3.2-3B can achieve this impressive functionality!

If you're new to the concepts of LLM chatbot or building a Database Helper, you can refer to my earlier articles:

How to Write Your First AI Storyteller with Ollama and Semantic Kernel
How to Implement a Database Helper in C# Using the Strategy Design Pattern
(You may create the Chatbot and SQLStrategy helpers first before continuing reading.)

By the end of this guide, you'll have a Text-to-SQL assistant that can:

Understand user queries in natural language.
Dynamically generate optimized SQL statements.
Offer an interactive experience, all powered by llama3.2-3B!

What You’ll Build

The AI Assistant:

Accepts user questions in natural language.
Dynamically generates SQL queries using Semantic Kernel and LLM reasoning.

Example Interaction:

User: What is the best-selling product this year?  
Your SQL:  
SELECT TOP 1 p.ProductName, SUM(o.Quantity) AS TotalQuantity  
FROM dbo.OrderItems o  
JOIN dbo.Orders r ON o.OrderID = r.OrderID  
JOIN dbo.Products p ON o.ProductID = p.ProductID  
WHERE r.OrderDate >= DATEADD(year, -1, GETDATE())  
GROUP BY p.ProductName  
ORDER BY TotalQuantity DESC
User: Exiting... Thank you for using the SQL assistant!

What makes this truly exciting is that we’re doing it with llama3.2-3B, a smaller, more affordable model that proves you don’t need a massive LLM to build practical AI solutions.

Step 1: Setting Up the Tables with Data

CREATE TABLE Customers (
    CustomerID          BIGINT IDENTITY(1,1) NOT NULL,
    CustomerNumber      NVARCHAR(1000) NOT NULL,
    LastName            NVARCHAR(100)    NOT NULL,
    FirstName           NVARCHAR(100)    NOT NULL,
    DOB                 DATE             NOT NULL,
    IsDeleted           BIT              NOT NULL DEFAULT 0,
    CreateBy            NVARCHAR(100)    NOT NULL DEFAULT 'SYSTEM',
    CreateDate          DATETIME         NOT NULL DEFAULT GETDATE(),
    ModifyBy            NVARCHAR(100)    NOT NULL DEFAULT 'SYSTEM',
    ModifyDate          DATETIME         NOT NULL DEFAULT GETDATE(),  
    CHECK (YEAR(DOB) >= 1900),
    PRIMARY KEY (CustomerID)
);

CREATE TABLE Products (
    ProductID           BIGINT IDENTITY(1,1) NOT NULL,
    ProductName         NVARCHAR(1000)   NOT NULL,
    ProductCode         NVARCHAR(1000)   NOT NULL,
    AvailableQuantity   INT              NOT NULL,
    IsDeleted           BIT              NOT NULL DEFAULT 0,
    CreateBy            NVARCHAR(100)    NOT NULL DEFAULT 'SYSTEM',
    CreateDate          DATETIME         NOT NULL DEFAULT GETDATE(),
    ModifyBy            NVARCHAR(100)    NOT NULL DEFAULT 'SYSTEM',
    ModifyDate          DATETIME         NOT NULL DEFAULT GETDATE(),
    CHECK (AvailableQuantity >= 0),
    PRIMARY KEY (ProductID)
);

CREATE TABLE Orders (
    OrderID             BIGINT IDENTITY(1,1) NOT NULL,
    CustomerID          BIGINT,
    OrderNumber         NVARCHAR(1000)   NOT NULL,
    OrderDate           DATETIME         NOT NULL,
    IsDeleted           BIT              NOT NULL DEFAULT 0,
    CreateBy            NVARCHAR(100)    NOT NULL DEFAULT 'SYSTEM',
    CreateDate          DATETIME         NOT NULL DEFAULT GETDATE(),
    ModifyBy            NVARCHAR(100)    NOT NULL DEFAULT 'SYSTEM',
    ModifyDate          DATETIME         NOT NULL DEFAULT GETDATE(),
    PRIMARY KEY (OrderID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

CREATE TABLE OrderItems (
    OrderItemID         BIGINT IDENTITY(1,1) NOT NULL,
    OrderID             BIGINT,
    ProductID           BIGINT,
    Quantity            INT              NOT NULL,
    IsDeleted           BIT              NOT NULL DEFAULT 0,
    CreateBy            NVARCHAR(100)    NOT NULL DEFAULT 'SYSTEM',
    CreateDate          DATETIME         NOT NULL DEFAULT GETDATE(),
    ModifyBy            NVARCHAR(100)    NOT NULL DEFAULT 'SYSTEM',
    ModifyDate          DATETIME         NOT NULL DEFAULT GETDATE(),
    PRIMARY KEY (OrderItemID),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

INSERT INTO Customers (CustomerNumber, LastName, FirstName, DOB) VALUES
('CUST0001', 'Au Yeung', 'David', '1980-12-31'),
('CUST0002', 'Chan', 'Peter', '1982-01-15');

INSERT INTO Products (ProductName, ProductCode, AvailableQuantity) VALUES
('Android Phone', 'A0001', 100),
('iPhone', 'I0001', 100);

INSERT INTO Orders (CustomerID, OrderNumber, OrderDate) VALUES
((SELECT TOP 1 CustomerID FROM Customers WHERE FirstName = 'David' AND IsDeleted = 0),
'ORD0001',
GETDATE());

INSERT INTO OrderItems (OrderID, ProductID, Quantity) VALUES
((SELECT TOP 1 OrderID FROM Orders WHERE OrderNumber = 'ORD0001' AND IsDeleted = 0),
(SELECT TOP 1 ProductID FROM Products WHERE ProductCode = 'I0001' AND IsDeleted = 0),
10);

SELECT * FROM Customers;
SELECT * FROM Products;
SELECT * FROM Orders;
SELECT * FROM OrderItems;

Step 2: Setting Up the Environment

Prerequisites

.NET SDK (Version 8 or higher)
Visual Studio Code

NuGet Packages:

Microsoft.SemanticKernel
Microsoft.SemanticKernel.ChatCompletion
OllamaSharp
OllamaSharp.Models.Chat

Step 3: Implementing the Text-to-SQL Assistant

3.1 Create the Project

mkdir TextToSqlAssistant
cd TextToSqlAssistant
dotnet new console

Run the following commands to install the required packages:

dotnet add package Microsoft.SemanticKernel
dotnet add package Microsoft.SemanticKernel.ChatCompletion
dotnet add package OllamaSharp
dotnet add package OllamaSharp.Models.Chat
dotnet add package Microsoft.Extensions.Configuration.Json

3.2 Writing the Core Code

Replace the contents of Program.cs with:

using TextToSqlAssistant.Utils;
using System.Data;
using System.Text;

namespace TextToSqlAssistant
{
    public class Program
    {
        public static async Task Main()
        {
            string endPoint = "http://localhost:11434";

            string systemMessage = @$"""
                # ROLE:
                - You are a highly intelligent SQL assistant.
                - Your task is to generate precise and efficient T-SQL queries based on the provided database schema and user requirements.

                # OUTPUT:
                - Return only the T-SQL query without any explanation, comments, or additional text.
                - Ensure the SQL query is optimized and adheres to best practices.

                # NOTE:
                - If the user request is unclear or ambiguous, request clarification.
            """;

            // Initialize the SQL helper chatbot
            Chatbot sqlAI = new Chatbot(endPoint, systemMessage);

            string databaseSchema = await FetchDatabaseSchema();

            // Chat loop
            do
            {
                Console.Write("User: ");
                string userMessage = Console.ReadLine();

                if (string.IsNullOrWhiteSpace(userMessage))
                {
                    Console.WriteLine("Exiting... Thank you for using the SQL assistant!");
                    break;
                }

                string formattedMessage = $"""
                    User Message: {userMessage}

                    Table Schema: 
                    {databaseSchema}
                """;

                try
                {
                    string response = await sqlAI.AskQuestion(formattedMessage);
                    Console.WriteLine($"Your SQL: \n{response}");
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"An error occurred: {ex.Message}");
                }
            }
            while (true);
        }

        /// <summary>
        /// Fetches the database schema and formats it for use in user prompts.
        /// </summary>
        private static async Task<string> FetchDatabaseSchema()
        {
            string query = @"
                SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
                FROM INFORMATION_SCHEMA.COLUMNS
                ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;
            ";

            SQLStrategy sqlHelper = new SQLStrategy(new TSqlQuery());
            DataTable result = sqlHelper.GetDataTable(query);

            if (result == null || result.Rows.Count == 0)
            {
                throw new Exception("Failed to fetch database schema or schema is empty.");
            }

            StringBuilder schemaBuilder = new StringBuilder();

            foreach (DataRow row in result.Rows)
            {
                Console.WriteLine($"{row["TABLE_SCHEMA"]}.{row["TABLE_NAME"]}.{row["COLUMN_NAME"]} ({row["DATA_TYPE"]})");
                schemaBuilder.AppendLine($"{row["TABLE_SCHEMA"]}.{row["TABLE_NAME"]}.{row["COLUMN_NAME"]} ({row["DATA_TYPE"]})");
            }

            return schemaBuilder.ToString();
        }
    }
}

Code Explanation
This program demonstrates how to build a Text-to-SQL Assistant in C#. It uses a chatbot interface to convert user queries into SQL statements dynamically. Below is a breakdown of the major components:

1. Setting Up the SQL Assistant

string endPoint = "http://localhost:11434";

string systemMessage = @$"""
    # ROLE:
    - You are a highly intelligent SQL assistant.
    - Your task is to generate precise and efficient T-SQL queries based on the provided database schema and user requirements.

    # OUTPUT:
    - Return only the T-SQL query without any explanation, comments, or additional text.
    - Ensure the SQL query is optimized and adheres to best practices.

    # NOTE:
    - If the user request is unclear or ambiguous, request clarification.
""";

Purpose: Configures the chatbot with a role and clear instructions.
System Message: Defines the assistant as a SQL generator, specifying that it should only return SQL queries without extra explanations.
Endpoint: Points to the local LLM service running at http://localhost:11434.

2. Fetching the Database Schema

private static async Task<string> FetchDatabaseSchema()
{
    string query = @"
        SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
        FROM INFORMATION_SCHEMA.COLUMNS
        ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;
    ";

    SQLStrategy sqlHelper = new SQLStrategy(new TSqlQuery());
    DataTable result = sqlHelper.GetDataTable(query);

    if (result == null || result.Rows.Count == 0)
    {
        throw new Exception("Failed to fetch database schema or schema is empty.");
    }

    StringBuilder schemaBuilder = new StringBuilder();

    foreach (DataRow row in result.Rows)
    {
        Console.WriteLine($"{row["TABLE_SCHEMA"]}.{row["TABLE_NAME"]}.{row["COLUMN_NAME"]} ({row["DATA_TYPE"]})");
        schemaBuilder.AppendLine($"{row["TABLE_SCHEMA"]}.{row["TABLE_NAME"]}.{row["COLUMN_NAME"]} ({row["DATA_TYPE"]})");
    }

    return schemaBuilder.ToString();
}

Purpose: Retrieves the database schema to help the assistant generate accurate SQL queries.
Implementation: Executes a query on the INFORMATION_SCHEMA.COLUMNS table to list all table schemas, names, columns, and their data types.

3. Chat Loop

do
{
    Console.Write("User: ");
    string userMessage = Console.ReadLine();

    if (string.IsNullOrWhiteSpace(userMessage))
    {
        Console.WriteLine("Exiting... Thank you for using the SQL assistant!");
        break;
    }

    string formattedMessage = $"""
        User Message: {userMessage}

        Table Schema: 
        {databaseSchema}
    """;

    try
    {
        string response = await sqlAI.AskQuestion(formattedMessage);
        Console.WriteLine($"Your SQL: \n{response}");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"An error occurred: {ex.Message}");
    }
}
while (true);

Purpose: Handles user interaction by accepting natural language queries and generating SQL responses.
Implementation: Prompts the user for input. Formats the input alongside the database schema into a message for the chatbot. Sends the formatted message to the chatbot (sqlAI.AskQuestion). Displays the SQL response generated by the chatbot.
Exit Condition: Stops the loop if the user enters an empty or whitespace message.

4. The SQL Chatbot

Chatbot sqlAI = new Chatbot(endPoint, systemMessage);

SQL Chatbot: Initializes an AI-powered chatbot with the specified endpoint and system message.
Task: The chatbot converts user queries into SQL statements, leveraging the database schema for context.

Step 4: Running the Assistant

Start the Assistant and ask question:

dotnet run

What is the best-selling product this year?

Test the SQL:

Insights and Lessons Learned

Using a Lightweight LLM:
llama3.2-3B is a budget-friendly yet powerful model that can handle text-to-SQL tasks effectively.

Semantic Kernel Integration:
The Semantic Kernel provides a structured framework to interact with the LLM and generate SQL dynamically.

Practicality:
This experiment demonstrates that you don’t need a massive model or expensive infrastructure to build a functional AI assistant.

Conclusion

This Text-to-SQL Assistant is a fun experiment and an excellent proof of concept. It shows how even a low-B llama3.2-3B model can be leveraged to create practical AI applications.

Feel free to try the code, adapt it to your needs, and share your feedback! Let’s make querying databases fun and accessible for everyone.