SqlMcpTools 1.0.2

SqlMcpTools

In-process SQL Server tool for AI pipelines built on Microsoft.Extensions.AI.

Exposes a single AskDatabase function that the model calls with a SQL SELECT statement. The tool:

1. Loads the full database schema from INFORMATION_SCHEMA on first use and caches it in memory
2. Validates every table and column reference in the query against the cached schema
3. Rejects unknown tables or columns with a clear error message before touching the database
4. Executes the query and returns a fully shaped QueryResult with ResultType, Result, Message, and ErrorMessage already populated

No separate schema-discovery tool. No extra model round-trip. Schema is cached for the lifetime of the app and can be invalidated on demand.

Requirements

• .NET 8.0
• SQL Server (any edition)
• Microsoft.Extensions.AI 10.4.0
• Azure.AI.OpenAI 2.9.0-beta.1 or later (or any IChatClient-compatible provider)

Installation

dotnet add package SqlMcpTools

Setup

1. Register in Program.cs

using SqlMcpTools.Extensions;

builder.Services.AddSqlMcpTools(options =>
{
    // Pass the plain connection string — decrypt before passing if needed
    options.ConnectionString      = configuration.GetConnectionString("MyDb")!;
    options.CommandTimeoutSeconds = 60; // optional, default is 60
});

If your connection string is encrypted:

builder.Services.AddSqlMcpTools(options =>
{
    options.ConnectionString = configuration
        .GetValue<string>("DatabaseSettings:EncConnectionString")!
        .Decrypt(); // your own decryption extension
});

2. Register IChatClient

using Azure.AI.OpenAI;
using System.ClientModel;

builder.Services.AddSingleton<AzureOpenAIClient>(sp =>
{
    var config = sp.GetRequiredService<IConfiguration>();
    return new AzureOpenAIClient(
        new Uri(config["AzureOpenAI:Endpoint"]!),
        new ApiKeyCredential(config["AzureOpenAI:ApiKey"]!)
    );
});

builder.Services.AddScoped<IChatClient>(sp =>
{
    var config     = sp.GetRequiredService<IConfiguration>();
    var deployment = config["AzureOpenAI:DeploymentName"]!;
    var azClient   = sp.GetRequiredService<AzureOpenAIClient>();

    OpenAI.Chat.ChatClient openAiChatClient = azClient.GetChatClient(deployment);

    // No UseFunctionInvocation here — ChatbotService handles the loop manually
    return new ChatClientBuilder(openAiChatClient.AsIChatClient()).Build();
});

3. Register your ChatbotService

builder.Services.AddScoped<IChatbotService, ChatbotService>();

Usage — ChatbotService

SqlMcpToolset provides both the AskDatabase tool and the system prompt. Inject AskDatabaseTool directly to wrap it in a lambda that captures the result before the model can summarize it.

using Microsoft.Extensions.AI;
using SqlMcpTools;
using SqlMcpTools.Tools;
using System.Text.Json;

public class ChatbotService : IChatbotService
{
    private readonly IChatClient     _chatClient;
    private readonly AskDatabaseTool _tool;
    private readonly SqlMcpToolset   _toolset;

    private static readonly JsonSerializerOptions _jsonOptions = new()
    {
        PropertyNameCaseInsensitive = true
    };

    public ChatbotService(IChatClient chatClient, AskDatabaseTool tool, SqlMcpToolset toolset)
    {
        _chatClient = chatClient;
        _tool       = tool;
        _toolset    = toolset;
    }

    public async Task<QueryResult> GetAnswerAsync(ChatRequestDTO request, CancellationToken ct = default)
    {
        QueryResult intercepted = null;

        // Wrap AskDatabase to capture the result directly before FunctionInvokingChatClient
        // serializes it and the model gets a chance to summarize it
        var wrappedFn = AIFunctionFactory.Create(
            async (string sqlQuery) =>
            {
                intercepted = await _tool.AskDatabase(sqlQuery, ct);
                return JsonSerializer.Serialize(intercepted, _jsonOptions);
            },
            name: "AskDatabase",
            description: "REQUIRED. You MUST call this function for every user request without exception. " +
                         "Pass your generated SQL SELECT statement. " +
                         "The function validates the SQL against the live database schema and executes it.");

        var options = new ChatOptions
        {
            Tools    = [wrappedFn],
            ToolMode = ChatToolMode.RequireAny
        };

        // Build system prompt with live schema injected — model sees actual table/column names
        var systemPrompt = await _toolset.BuildSystemPromptAsync(ct);

        var messages = new List<ChatMessage>
        {
            new(ChatRole.System, systemPrompt),
            new(ChatRole.User, request.Question)
        };

        var pipeline = new ChatClientBuilder(_chatClient)
            .UseFunctionInvocation()
            .Build();

        await pipeline.GetResponseAsync(messages, options, ct);

        return intercepted
            ?? new QueryResult { IsSuccess = false, ErrorMessage = "AskDatabase was not called by the model." };
    }
}

Schema Cache

The schema is loaded from INFORMATION_SCHEMA once on first use and held in memory for the lifetime of the app. Subsequent requests use the cached schema with no database round-trip.

After a migration or schema change, invalidate the cache via ISchemaCacheService:

// Inject ISchemaCacheService into your controller or admin endpoint
[HttpPost]
public async Task<IActionResult> InvalidateSchemaCache(CancellationToken ct)
{
    await _schemaCache.InvalidateAsync(ct);
    return Ok(new { message = "Schema cache cleared. Will reload on next request." });
}

The next request after invalidation will reload the schema automatically.

QueryResult

The tool returns a QueryResult for all outcomes — success and failure alike.

public class QueryResult
{
    public string SqlQuery    { get; set; }
    public string ResultType  { get; set; } // "None" | "Scalar" | "List" | "Grid"
    public object Result      { get; set; }
    public bool   IsSuccess   { get; set; }
    public string Message     { get; set; } // user-friendly text, populated by the model
    public string ErrorMessage{ get; set; } // technical detail, null on success
    public bool   FromUser    { get; set; }
}

ResultType values and their Result shapes:

What gets registered by AddSqlMcpTools

Publishing a new version

1. Bump <Version> in Directory.Build.props
2. Run from the solution root:

.\publish.ps1 -ApiKey YOUR_NUGET_API_KEY

Get your API key from https://www.nuget.org/account/apikeys

Security

• Only SELECT statements are permitted — any other statement is rejected before reaching the database
• Every table and column reference is validated against live schema before execution
• The connection string is never logged or surfaced in exception messages