Thursday, July 2, 2026

Build a interactive chatbot using AI modal using SQL database with .NET Semantic Kernel plugin

Build an Interactive AI Chatbot Using SQL Server and .NET Semantic Kernel Plugins

Artificial Intelligence has transformed the way modern applications interact with users. Instead of creating countless APIs for every business operation, developers can now use Large Language Models (LLMs) together with Semantic Kernel plugins to allow AI to retrieve live information directly from enterprise databases.

In this tutorial, you'll learn how to build an Interactive Insurance Chatbot using .NET 8, Microsoft Semantic Kernel, SQL Server, Entity Framework Core, and OpenAI Chat Completion. The chatbot understands natural language questions and automatically calls a Semantic Kernel plugin whenever data needs to be fetched from SQL Server.

What You'll Build

By the end of this tutorial, you'll have an AI-powered console chatbot capable of answering questions such as:

  • Show all insurance customers.
  • Who has Medical Insurance?
  • List all Quarterly premium customers.
  • Show policies started in 2019.
  • What is Rohit Parmar's premium amount?
  • List all Life Insurance policies.

Unlike traditional chatbots that rely on predefined responses, this chatbot retrieves real-time information directly from SQL Server using Semantic Kernel's automatic function calling feature.

Solution Architecture

User
   │
   ▼
OpenAI Chat Model
   │
Semantic Kernel
   │
Insurance Plugin
   │
Insurance Business Layer
   │
Entity Framework Core
   │
SQL Server Database

Whenever the user asks a question related to insurance records, the AI automatically invokes the appropriate plugin, retrieves data from SQL Server, and generates a natural language response based on the returned records.

Technologies Used

  • .NET 8
  • Microsoft Semantic Kernel
  • OpenAI Chat Completion
  • Entity Framework Core
  • SQL Server
  • Dependency Injection
  • C#
  • Semantic Kernel Plugins

Prerequisites

Before starting this project, make sure you have the following installed:

  • Visual Studio 2022
  • .NET 8 SDK
  • SQL Server
  • SQL Server Management Studio (SSMS)
  • OpenAI API Key
  • Microsoft Semantic Kernel NuGet Packages
  • Entity Framework Core SQL Server Provider
Note: This tutorial uses Semantic Kernel Function Calling to automatically invoke database functions whenever the AI determines that external data is required to answer the user's question.

Step 1 – Prepare the Insurance Table with Mock Data

The first step is to create an Insurance table in SQL Server and insert a few sample records. These records will be queried by the chatbot through Entity Framework Core. 


CREATE TABLE Insurance
(
    firstname           VARCHAR(100),
    lastname            VARCHAR(100),
    email               VARCHAR(100),
    contactNumber       VARCHAR(10),
    insurancetype       VARCHAR(100),
    insurancestartdate  DATE,
    premium             DECIMAL,
    premiumfrequency    VARCHAR(100)
);

INSERT INTO Insurance
(firstname, lastname, email, contactNumber, insurancetype, insurancestartdate, premium, premiumfrequency)
VALUES
('Rohit', 'Parmar', 'rohit.parmar@gmail.com', '8454042163', 'Term', '03-12-2016', '5656.00', 'Quaterly');

INSERT INTO Insurance
(firstname, lastname, email, contactNumber, insurancetype, insurancestartdate, premium, premiumfrequency)
VALUES
('Shailash', 'Singh', 'shailash.singh@gmail.com', '8158042151', 'Term', '03-12-2009', '15236.00', 'Half Yearly');

INSERT INTO Insurance
(firstname, lastname, email, contactNumber, insurancetype, insurancestartdate, premium, premiumfrequency)
VALUES
('Chinmay', 'Halankar', 'chinmay.halankar@gmail.com', '6454032121', 'Medical', '03-12-2018', '51626.00', 'Yearly');

INSERT INTO Insurance
(firstname, lastname, email, contactNumber, insurancetype, insurancestartdate, premium, premiumfrequency)
VALUES
('Malay', 'Mondal', 'malay.mondal@gmail.com', '7654048723', 'Term', '03-12-2019', '8923.00', 'Quaterly');

INSERT INTO Insurance
(firstname, lastname, email, contactNumber, insurancetype, insurancestartdate, premium, premiumfrequency)
VALUES
('Rupesh', 'Patil', 'rupesh.patil@gmail.com', '9454043400', 'Life Insurance', '03-12-2010', '3486.00', 'Half Yearly');

INSERT INTO Insurance
(firstname, lastname, email, contactNumber, insurancetype, insurancestartdate, premium, premiumfrequency)
VALUES
('Anjali', 'Shinde', 'angali.shinde@gmail.com', '9654090160', 'Endowment Plan', '03-12-2012', '7106.00', 'Quaterly');

Step 2 – Create the Insurance Model

Create Insurance.cs inside the Models folder.


using Microsoft.EntityFrameworkCore;
using System.ComponentModel.DataAnnotations.Schema;

namespace InteractiveChatBot.Models;

[Table("Insurance")]
[Keyless]
public class Insurance
{
    [Column("firstname")]
    public string? FirstName { get; set; }

    [Column("lastname")]
    public string? LastName { get; set; }

    [Column("email")]
    public string? Email { get; set; }

    [Column("contactNumber")]
    public string? ContactNumber { get; set; }

    [Column("insurancetype")]
    public string? InsuranceType { get; set; }

    [Column("insurancestartdate")]
    public DateTime? InsuranceStartDate { get; set; }

    [Column("premium")]
    public decimal? Premium { get; set; }

    [Column("premiumfrequency")]
    public string? PremiumFrequency { get; set; }
}

Step 3 – Create AppDbContext

Create AppDbContext.cs.



using InteractiveChatBot.Models;
using Microsoft.EntityFrameworkCore;

namespace InteractiveChatBot.Data;

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions options)
        : base(options)
    {
    }

    public DbSet Insurance => Set();
}

Step 4 – Create InsuranceBusiness.cs

The business layer retrieves insurance records using Entity Framework Core.


using InteractiveChatBot.Data;
using InteractiveChatBot.Models;
using Microsoft.EntityFrameworkCore;

namespace InteractiveChatBot.Business
{
    public class InsuranceBusiness
    {
        private readonly AppDbContext _appContext;

        public InsuranceBusiness(AppDbContext appContext)
        {
            _appContext = appContext;
        }

        public async Task> ReadInsuranceAsync()
        {
            return await _appContext.Insurance
                .AsNoTracking()
                .ToListAsync();
        }
    }
}

Step 5 – Create InsurancePlugin.cs

The Semantic Kernel plugin exposes the database method as an AI function.


using System.ComponentModel;
using InteractiveChatBot.Business;
using InteractiveChatBot.Models;
using Microsoft.SemanticKernel;

namespace InteractiveChatBot.Plugins;

public class InsurancePlugin
{
    private readonly InsuranceBusiness _business;

    public InsurancePlugin(InsuranceBusiness business)
    {
        _business = business;
    }

    [KernelFunction("read_insurance")]
    [Description("Reads all insurance records from the database. Returns firstname, lastname, email, contact number, insurance type, insurance start date, premium, and premium frequency for every record.")]
    public async Task> ReadInsuranceAsync()
    {
        try
        {
            return await _business.ReadInsuranceAsync();
        }
        catch (Exception ex)
        {
            throw new KernelException($"Failed to read Insurance table: {ex.Message}", ex);
        }
    }
}

Step 6 – Configure Program.cs

Program.cs wires together SQL Server, Dependency Injection, Semantic Kernel, OpenAI Chat Completion, automatic function calling, and the interactive console chat loop. 


using InteractiveChatBot.Business;
using InteractiveChatBot.Data;
using InteractiveChatBot.Plugins;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.SemanticKernel;
using Microsoft.SemanticKernel.ChatCompletion;
using Microsoft.SemanticKernel.Connectors.OpenAI;

try
{
    var config = new ConfigurationBuilder()
        .SetBasePath(AppContext.BaseDirectory)
        .AddJsonFile("appsettings.json", optional: false, reloadOnChange: false)
        .Build();

    var connectionString = config.GetConnectionString("DefaultConnection")
        ?? throw new InvalidOperationException("Connection string 'DefaultConnection' not found.");

    var openAiModelId = config["OpenAI:ModelId"]
        ?? throw new InvalidOperationException("OpenAI:ModelId not found in appsettings.json.");

    var openAiApiKey = config["OpenAI:ApiKey"];
    if (string.IsNullOrWhiteSpace(openAiApiKey))
        throw new InvalidOperationException("OpenAI:ApiKey is missing in appsettings.json.");

    var openAiEndpoint = config["OpenAI:Endpoint"]
        ?? throw new InvalidOperationException("OpenAI:Endpoint not found in appsettings.json.");

    if (!Uri.TryCreate(openAiEndpoint, UriKind.Absolute, out var endpointUri))
        throw new InvalidOperationException($"OpenAI:Endpoint '{openAiEndpoint}' is not a valid absolute URI.");

    var services = new ServiceCollection();
    services.AddDbContext(options => options.UseSqlServer(connectionString));
    services.AddScoped();
    services.AddScoped();
    
services.AddSingleton(_ => new HttpClient
{
    BaseAddress = endpointUri,
    Timeout = TimeSpan.FromMinutes(3)
});

services.AddScoped(sp =>
{
    var builder = Kernel.CreateBuilder();

    builder.AddOpenAIChatCompletion(
        modelId: openAiModelId,
        apiKey: openAiApiKey,
        httpClient: sp.GetRequiredService());

    var kernel = builder.Build();

    kernel.Plugins.AddFromObject(
        sp.GetRequiredService(),
        "Insurance");

    return kernel;
});

await using var provider = services.BuildServiceProvider();
using var scope = provider.CreateScope();

var kernel = scope.ServiceProvider.GetRequiredService();
var chat = kernel.GetRequiredService();

var executionSettings = new OpenAIPromptExecutionSettings
{
    FunctionChoiceBehavior = FunctionChoiceBehavior.Auto()
};

var history = new ChatHistory(
    "You are an insurance assistant. When the user asks anything about policy holders, " +
    "insurance types, premiums, or start dates, call the Insurance.read_insurance function to fetch the data and answer based on the returned rows. Be concise.");
    
  using var cts = new CancellationTokenSource();

Console.CancelKeyPress += (_, e) =>
{
    e.Cancel = true;
    cts.Cancel();
};

Console.WriteLine("Insurance chatbot ready. Type 'exit' to quit (or Ctrl+C).");

while (!cts.IsCancellationRequested)
{
    Console.Write("\nYou: ");
    var input = Console.ReadLine();

    if (input is null) break;
    if (string.IsNullOrWhiteSpace(input)) continue;
    if (input.Trim().Equals("exit", StringComparison.OrdinalIgnoreCase))
        break;

    history.AddUserMessage(input);

    try
    {
        var reply = await chat.GetChatMessageContentAsync(
            history,
            executionSettings,
            kernel,
            cts.Token);

        Console.WriteLine($"\nBot: {reply.Content}");
        history.Add(reply);
    }
    catch (OperationCanceledException)
    {
        Console.WriteLine("\nRequest cancelled.");
        history.RemoveAt(history.Count - 1);
    }
    catch (HttpOperationException ex)
    {
        Console.Error.WriteLine(
            $"\nLLM endpoint error ({(int?)ex.StatusCode}): {ex.Message}");

        history.RemoveAt(history.Count - 1);
    }
}

catch (KernelException ex)
{
    Console.Error.WriteLine($"\nKernel error: {ex.Message}");
    history.RemoveAt(history.Count - 1);
}
catch (Exception ex)
{
    Console.Error.WriteLine($"\nUnexpected error: {ex.Message}");
    history.RemoveAt(history.Count - 1);
}

return 0;
}
catch (InvalidOperationException ex)
{
    Console.Error.WriteLine($"Configuration error: {ex.Message}");
    return 1;
}
catch (FileNotFoundException ex)
{
    Console.Error.WriteLine($"Missing file: {ex.Message}");
    return 1;
}
catch (Exception ex)
{
    Console.Error.WriteLine($"Fatal error: {ex.GetType().Name}: {ex.Message}");
    return 1;
}

Sample Questions

  • Show all insurance records.
  • Who has Medical insurance?
  • Which policies started in 2019?
  • Show Quarterly premium customers.
  • What is Rohit's premium amount?







Conclusion

You have successfully built an AI-powered chatbot that can answer natural language questions using live SQL Server data through .NET Semantic Kernel plugins. This architecture cleanly separates AI reasoning from business logic and can easily be extended for CRM, HR, Healthcare, Banking, and Inventory Management system.

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)