Text-to-SQL in .NET: Letting Users Query a Database in Plain English (Safely)

“Can we just let people ask the database questions in English?” is one of the most requested AI features in any business application, and one of the easiest to build badly.

The naive version takes about twenty minutes: send the schema and the question to a model, get SQL back, execute it. It demos beautifully. It is also a remote-code-execution feature wearing a nice hat, and it starts producing confidently wrong numbers the moment someone asks something with a join in it.

Here is the version that survives contact with production.

The architecture in one line

The model writes SQL. The database decides what SQL is allowed to do.

Everything below follows from that split. The model is untrusted — not because it is malicious, but because it is a text generator and you cannot prove what it will emit. So you never rely on prompt instructions for safety; you rely on a permission boundary that holds even when the model gets it completely wrong.

Step 1: build the schema context (not the whole schema)

The single biggest driver of accuracy is what schema you show the model. Two rules:

Do not dump the whole database. A real schema does not fit in a context window, and even where it does, irrelevant tables measurably hurt accuracy — the model finds a plausible-looking table that is not the right one.

Do not expose raw normalised tables. Your TBL_CUST_MSTR with a STAT_CD column encoding six business states is hostile to a model in exactly the way it is hostile to a new hire.

Instead, curate a query surface: a handful of views designed to be queried, with names and columns that read like the vocabulary your users actually use.

CREATE VIEW ai.Orders AS
SELECT  o.OrderId          AS OrderId,
        c.CompanyName      AS CustomerName,
        o.OrderDate        AS OrderDate,
        o.TotalAmount      AS TotalAmount,
        CASE o.StatusCode
            WHEN 'P' THEN 'Pending'
            WHEN 'S' THEN 'Shipped'
            WHEN 'C' THEN 'Cancelled'
        END                AS Status
FROM    dbo.TBL_ORD_HDR o
JOIN    dbo.TBL_CUST_MSTR c ON c.CustId = o.CustId
WHERE   o.IsDeleted = 0;

Two wins at once: the model sees clean names, and IsDeleted = 0 is enforced in the view rather than hoped for in a prompt. Any business rule you would otherwise have to ask the model to remember belongs in the view instead.

Then describe that surface compactly, with the annotations a model needs and DDL does not carry:

const string SchemaContext = """
    ai.Orders(OrderId int, CustomerName nvarchar, OrderDate date,
              TotalAmount decimal, Status nvarchar)
      -- One row per order. Status is one of: Pending, Shipped, Cancelled.
      -- TotalAmount is in GBP, inclusive of tax.

    ai.OrderLines(OrderId int, ProductName nvarchar, Quantity int, UnitPrice decimal)
      -- Join to ai.Orders on OrderId.

    ai.Customers(CustomerName nvarchar, Country nvarchar, SignedUpOn date)
      -- One row per customer. CustomerName is unique.
    """;

Those comments are doing real work. “TotalAmount is inclusive of tax” is the difference between a right and a wrong revenue number, and there is nowhere in the DDL for it to live.

If your surface grows past a few dozen views, stop putting all of it in every prompt and retrieve the relevant subset per question with embeddings — same technique as any other RAG problem, applied to schema.

Step 2: generate the SQL as structured output

Ask for a typed object, not a blob of text you then have to scrape fences off:

public sealed record SqlPlan(
    string Sql,
    string Explanation,
    bool CanAnswer,
    string? Reason);

CanAnswer matters more than it looks. Without an explicit way to decline, a model asked something the schema cannot support will invent a column rather than admit defeat. Giving it a legitimate exit removes most of that class of failure.

ChatOptions options = new()
{
    Temperature = 0,   // determinism matters more than variety here
    ResponseFormat = ChatResponseFormat.ForJsonSchema<SqlPlan>()
};

var prompt = $"""
    You translate questions into a single T-SQL SELECT statement.

    Schema:
    {SchemaContext}

    Rules:
    - Exactly one statement, and it must be a SELECT.
    - Always include TOP (200).
    - Use only the tables and columns listed above.
    - If the schema cannot answer the question, set CanAnswer to false
      and explain what is missing. Do not guess at column names.

    Question: {question}
    """;

ChatResponse<SqlPlan> response =
    await chatClient.GetResponseAsync<SqlPlan>(prompt, options);

SqlPlan plan = response.Result;

Temperature = 0 because there is no upside to creative variation in a query, and a stable output makes caching and debugging tractable. More on the general pattern in structured output for .NET agents.

Those rules in the prompt improve the odds. They are not the safety mechanism. That is next.

Step 3: the execution boundary (this is the important part)

If you take one thing from this article: the generated SQL runs on a connection that is physically incapable of doing damage.

Create a login that exists only for this feature:

CREATE USER [ai_reader] WITHOUT LOGIN;

-- Deny everything, then grant back exactly the query surface.
DENY  SELECT, INSERT, UPDATE, DELETE, EXECUTE, ALTER ON SCHEMA::dbo TO [ai_reader];
GRANT SELECT ON SCHEMA::ai TO [ai_reader];

Now DROP TABLE Orders fails at the database. Not because the model was well-behaved, but because that user cannot do it. Same for reading the dbo tables you deliberately did not expose, and for the Users table with the password hashes in it.

This is the control. Everything else is defence in depth:

static void Validate(string sql)
{
    // One statement only — kills the "; DROP TABLE" shape outright.
    if (sql.TrimEnd().TrimEnd(';').Contains(';'))
        throw new InvalidOperationException("Multiple statements are not allowed.");

    var normalised = sql.TrimStart();
    if (!normalised.StartsWith("SELECT", StringComparison.OrdinalIgnoreCase) &&
        !normalised.StartsWith("WITH", StringComparison.OrdinalIgnoreCase))
        throw new InvalidOperationException("Only SELECT statements are allowed.");

    // Constructs with no business appearing in a read-only reporting query.
    string[] banned = ["xp_", "sp_", "OPENROWSET", "OPENQUERY", "BULK", "INTO "];
    foreach (var token in banned)
        if (sql.Contains(token, StringComparison.OrdinalIgnoreCase))
            throw new InvalidOperationException($"Disallowed construct: {token.Trim()}");
}

Be honest about what this is: keyword filtering is a speed bump, not a wall. It catches the obvious and buys you nothing against a determined adversary. It is worth having precisely because the permission boundary is doing the real work underneath.

Then bound the blast radius of an expensive-but-legal query:

await using var conn = new SqlConnection(_readOnlyConnectionString);
await conn.OpenAsync(ct);

await using var cmd = conn.CreateCommand();
cmd.CommandText = plan.Sql;
cmd.CommandTimeout = 15;                 // a cartesian join should fail, not hang

await using var reader = await cmd.ExecuteReaderAsync(ct);

var rows = new List<Dictionary<string, object?>>();
while (rows.Count < 200 && await reader.ReadAsync(ct))
{
    var row = new Dictionary<string, object?>();
    for (var i = 0; i < reader.FieldCount; i++)
        row[reader.GetName(i)] = reader.IsDBNull(i) ? null : reader.GetValue(i);
    rows.Add(row);
}

A timeout and a hard row cap in code, regardless of what the model put in the query. TOP (200) in the prompt is a request; the while condition is the guarantee.

Row-level security is the piece most teams miss. If different users are allowed to see different rows, the model cannot be trusted to add the WHERE TenantId = @x clause — it will forget, and the failure is silent and severe. Enforce it in the view or with the database’s row-level security so the filter applies whether or not the query asks for it. The general principle is covered in securing AI agents in .NET.

Step 4: show your work

Return the SQL alongside the answer. Always.

A SQL-literate user glances at the query and catches a wrong join in a second — that is the cheapest accuracy control available to you, and it costs one UI element. For non-technical users, show plan.Explanation in plain English instead: “Total value of orders placed by German customers in Q2 2026, excluding cancelled orders.”

The failure this prevents is the expensive one. Text-to-SQL rarely blows up loudly; it returns a number that is plausible and wrong — the join dropped rows, or the date range was off by a boundary, or Cancelled orders got counted. Nobody notices until that number is in a board deck. Visible derivation is how it gets caught while it is still cheap.

Where it works and where it does not

Set expectations honestly, because this feature’s reputation is decided by which questions people try first.

Works well: single-table filters and aggregates; simple joins across a clean surface; “top N by X”; date-range questions; anything a competent analyst would write in one glance.

Struggles: three-plus-table joins with ambiguous relationships; window functions; questions whose answer depends on a business rule that lives in someone’s head; anything where two columns could plausibly mean the same thing.

Two practical mitigations. First, few-shot examples of your five most common questions with their correct SQL, in the prompt — this is the highest-return single change you can make, because it teaches conventions no schema description conveys. Second, build an eval set of thirty real questions with known-correct results and run it on every prompt change, so “did that help?” is a measurement instead of an argument. The method is in testing and evaluating AI agents in .NET.

Takeaway

Text-to-SQL is a twenty-minute demo and a genuinely demanding production feature, and the gap between them is almost entirely about boundaries. Curate a small query surface of views rather than exposing raw tables, so business rules live in SQL rather than in a prompt. Run generated SQL on a deny-by-default read-only login so correctness bugs cannot become data-loss incidents, and enforce row-level filtering in the database rather than trusting the model to add a WHERE clause. Cap rows and time in code, and show the user the query. Get those right and the model’s occasional wrong join is a visible, recoverable annoyance instead of a silent wrong number in a report.

---

Have a correction or a topic you want covered? Email mani.bc72@gmail.com.