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.

The failures you will actually hit

Two of them are loud, and the loud ones are the good kind.

The first is a hallucinated name. The model asks for a column that is not there and SQL Server answers with Invalid column name 'CustomerRegion', or reaches for a view you never created and gets Invalid object name 'ai.Invoices'. The second is dialect drift: the model produces LIMIT 20 against T-SQL and you get Incorrect syntax near 'LIMIT', or emits TOP against PostgreSQL. Both are more likely the vaguer your schema description is, and both are worth handling the same way — hand the error back and let the model try once more.

catch (SqlException ex) when (ex.Number is 207 or 208 or 102 && attempt == 1)
{
    // 207 invalid column, 208 invalid object, 102 syntax.
    // One repair attempt, with the real error text as the correction signal.
    repairPrompt = $"""
        That query failed with: {ex.Message}
        Rewrite it using only the columns and tables in the schema above.
        """;
}

One retry, not a loop. A repair attempt roughly doubles the latency and the token cost of the request, and if the second attempt fails the third almost never succeeds — the schema genuinely does not contain what the question needs, and the right answer is to tell the user so. Explicitly do not retry a timeout (SqlException.Number of -2): the query was legal and expensive, and running it again just spends another fifteen seconds proving it.

The quiet failure is the one that costs you. Consider “what did we sell to German customers in Q2”, answered with a join from ai.Orders to ai.OrderLines and then SUM(TotalAmount). That statement is valid, runs instantly, and is wrong: the join fans out one order row per line item, so a three-line order contributes its total three times. Revenue comes back inflated by whatever your average basket size is, and it looks entirely plausible. Nothing in the pipeline catches it — not the validator, not the read-only login, not the timeout. Only a human reading the query does.

Date boundaries produce the same shape of error. BETWEEN '2026-04-01' AND '2026-06-30' against a datetime column silently drops everything after midnight on the last day, which is most of a day’s trading. So do implicit NULL semantics, where a filter on a nullable column quietly excludes rows the user assumed were included.

This is the real argument for the eval set, and for showing the SQL. You cannot prompt your way out of arithmetic that is wrong in a way the database considers correct — you can only make it visible and measure it.

Cost, latency and caching

The schema context is on every single request, and unlike the question it never changes. On a curated surface of a dozen views with comments it is a few hundred tokens, which is fine; on a surface of eighty views it is not, and that is the point at which per-question retrieval stops being an accuracy optimisation and starts being a cost one. If your provider supports caching a stable prompt prefix, the schema block is close to the ideal candidate for it — identical across every call, and sitting at the front.

Expect a normal request to be one model call plus a query, so somewhere in the low seconds. What breaks the budget is the repair loop, which adds a full round trip, and a question that returns two hundred rows the model then has to summarise. If you are rendering a table rather than prose, skip the summarisation call entirely and put the rows straight into the grid — the model has already done its job by the time the SQL is written.

Caching on the question text is tempting and mostly disappointing. With Temperature = 0 the same wording produces the same SQL, but people do not repeat wordings; they ask the same question forty different ways. Cache the generated plan keyed on a normalised question if you like, but check the hit rate before you build anything around it.

When not to build this at all

Three situations where the honest recommendation is to build something else.

If the questions people ask are actually the same five questions, build five parameterised reports. They are faster, exactly correct every time, and cost nothing per run. Natural language earns its keep on the long tail of unpredictable questions, not on the head of predictable ones — and if you have not yet checked which of those you have, that is the first thing to find out.

If nobody owns the schema, you cannot build the query surface, and without the query surface this feature is a confident wrong-number generator. The curation work in Step 1 is not preliminary to the project; it substantially is the project, and a team that will not fund it should not start.

If the numbers are audited, regulated or externally reported, do not put a probabilistic writer in that path. A figure that goes to a regulator or into published accounts needs a derivation somebody signed off, and “the model wrote this join” is not that. Text-to-SQL belongs in exploration and internal analysis, where a wrong answer is caught by the person who asked the question.

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.