Handling queries

The query handler is called once for each request of a connection, with a TdsQueryContext that describes the request, and returns a TdsQueryResult. This page covers what a request contains; Returning results covers the answer.

app.MapTdsHandlers(
    authenticate: (context, cancellationToken) => ValueTask.FromResult(TdsAuthenticationResult.Success()),
    query: (context, cancellationToken) =>
    {
        if (context.RequestType == TdsQueryRequestType.SqlBatch)
        {
            var commandText = context.CommandText; // e.g. "SELECT * FROM Users WHERE Id = @id"
        }
        else if (context.RequestType == TdsQueryRequestType.Rpc)
        {
            var procedureName = context.ProcedureName; // e.g. "sp_executesql" or your procedure name
        }

        foreach (var parameter in context.Parameters)
        {
            var parameterName = parameter.Name;
            var rawValue = parameter.Value; // DBNull.Value for SQL NULL
            var columnType = parameter.Type;
            var stringValue = parameter.AsString();
            var intValue = parameter.AsInt32();
            var json = parameter.AsJson();
            var xml = parameter.AsXml();
        }

        var user = context.UserContext; // ClaimsPrincipal from authentication result, if any

        return ValueTask.FromResult(new TdsQueryResult());
    });

Request types

RequestType tells how the client sent the request:

CommandText is the SQL text as the client wrote it, and is null for RPC requests. The server does not parse or run it: matching it, or parsing it with a SQL parser, is up to the handler, unless you use the query engine.

Read the parameters of a query

SqlClient sends a command that has parameters as a call to the sp_executesql system procedure. Its first parameter is the SQL text, the second one declares the parameters (@id int), and the values of the parameters follow, with their names:

TdsQueryDelegate query = (context, cancellationToken) =>
{
    if (!context.HasCompleteParameters)
    {
        return ValueTask.FromResult(TdsQueryResult.FromError(new TdsQueryError { Message = "A parameter has an unsupported type." }));
    }

    // A command with parameters is sent as a call to sp_executesql: the first parameter is the SQL text,
    // the second one declares the parameters, and the values follow
    if (context.RequestType == TdsQueryRequestType.Rpc && string.Equals(context.ProcedureName, "sp_executesql", StringComparison.OrdinalIgnoreCase))
    {
        var sql = context.Parameters[0].AsString();
        var id = context.Parameters.FirstOrDefault(parameter => parameter.Name == "@id")?.AsInt32();

        var resultSet = new TdsResultSet();
        resultSet.Columns.Add(new TdsColumn("Sql", TdsColumnType.NVarChar));
        resultSet.Columns.Add(new TdsColumn("Id", TdsColumnType.Int32));
        resultSet.Rows.Add([sql, id]);

        var result = new TdsQueryResult();
        result.ResultSets.Add(resultSet);
        return ValueTask.FromResult(result);
    }

    return ValueTask.FromResult(new TdsQueryResult());
};

For the command SELECT Name FROM customers WHERE Id = @id with @id set to 42, this handler returns the SQL text and 42.

Answer a stored procedure call

A stored procedure call has the name of the procedure in ProcedureName and its parameters in Parameters:

TdsQueryDelegate query = (context, cancellationToken) =>
{
    if (context.RequestType == TdsQueryRequestType.Rpc && string.Equals(context.ProcedureName, "GetCustomerName", StringComparison.OrdinalIgnoreCase))
    {
        var id = context.Parameters.FirstOrDefault(parameter => parameter.Name == "@id")?.AsInt32();

        var resultSet = new TdsResultSet();
        resultSet.Columns.Add(new TdsColumn("Name", TdsColumnType.NVarChar));
        resultSet.Rows.Add([id == 1 ? "Alice" : null]);

        var result = new TdsQueryResult();
        result.ResultSets.Add(resultSet);
        return ValueTask.FromResult(result);
    }

    return ValueTask.FromResult(TdsQueryResult.FromError(new TdsQueryError
    {
        Number = 2812,
        Message = $"Could not find stored procedure '{context.ProcedureName}'.",
    }));
};

The client calls it as it would call a SQL Server procedure. See Connecting clients.

Parameters

Each TdsQueryParameter has a Name (with its @ prefix, as the client sent it), a Type (a TdsColumnType) and a Value. A SQL NULL is DBNull.Value, and IsNull is true. IsOutput is true for the parameters the client declares as Output or InputOutput: return their values as shown in Return output parameters and a return value.

The conversion methods return null for a SQL NULL, and for a value they cannot convert:

Incomplete parameters

When the client sends a parameter whose type the server cannot decode, or a malformed request, Parameters stops before that parameter, and HasCompleteParameters is false. Reject such requests, as the first example of Read the parameters of a query does, rather than run them with missing values. The query engine rejects them.

Transactions

The server accepts the transactions of the client: SqlConnection.BeginTransaction, SqlTransaction.Commit, Rollback and Save, so code that runs its commands in a transaction, like the SaveChanges of EF Core, works without a handler. The server tracks the transaction of each connection:

• TransactionId is the identifier of the transaction a request runs in, or null outside a transaction. Use it to stage the changes of a transaction until it is committed.
• Savepoints are tracked by name: a rollback to a savepoint keeps the transaction, and drops the savepoints created after it.
• When a connection closes during a transaction, the transaction is rolled back.

The server does not run the T-SQL statements BEGIN TRANSACTION, COMMIT or ROLLBACK: they reach the query handler as any other SQL text.

To observe the transactions, or to reject a request, set TransactionHandler. Return null to accept the request, or the error to send to the client:

var events = new ConcurrentQueue<string>();
var options = new TdsServerOptions();
options.TransactionHandler = (context, cancellationToken) =>
{
    events.Enqueue($"{context.RequestType} {context.SavepointName} {context.IsolationLevel}".TrimEnd());
    if (context.RequestType is TdsTransactionRequestType.Begin && context.IsolationLevel is IsolationLevel.Snapshot)
    {
        return ValueTask.FromResult<TdsQueryError?>(new TdsQueryError
        {
            Number = 3952,
            Message = "Snapshot isolation transaction failed accessing database 'master' because snapshot isolation is not allowed in this database.",
        });
    }

    return ValueTask.FromResult<TdsQueryError?>(null);
};

The TdsTransactionContext gives the request:

When the handler rejects a request, the transaction is unchanged. The server returns the errors of SQL Server for the requests that do not match the state of the transaction:

Cancellation

The CancellationToken of the handler is canceled when the client cancels the command (SqlCommand.Cancel, or the command timeout), when the client disconnects, and when the server stops. Pass it to the asynchronous calls of the handler.

When the client cancels, the server acknowledges the cancellation at once, even when the handler ignores the token, and the connection stays usable. The result of the abandoned handler is discarded.

Concurrency

A connection runs one request at a time: the handler is not called again for the same connection until it returns. Different connections call the handler concurrently.

A connection with Multiple Active Result Sets (MultipleActiveResultSets=True) is the exception: the client opens several sessions on the connection, one per open SqlDataReader, and each session runs its requests independently of the others. The handler can then be called concurrently for the same connection, and a slow request does not hold up the requests of the other sessions. The server sends the rows of a session as fast as the client reads them, so a reader the client leaves open does not block the other readers.

Exceptions

When the handler throws, the server logs the exception and returns the error 50002 "Unhandled query handler exception" to the client: the message of the exception is not sent. To send a meaningful error, return it. See Return an error.