Meziantou.Framework

Getting started

edit

Install the package

Add the Meziantou.Framework.TdsServer package to a project:

dotnet package add Meziantou.Framework.TdsServer

The package depends on the ASP.NET Core shared framework (Microsoft.AspNetCore.App), which the .NET SDK provides, also in console and test projects.

The examples connect with Microsoft.Data.SqlClient, the SQL Server client for .NET:

dotnet package add Microsoft.Data.SqlClient

The examples of this documentation omit the using directives. They use the namespaces listed in Namespaces, plus System.Net, System.Security.Claims and Microsoft.Data.SqlClient.

Start a server and query it

This example starts a server on a free port of the local machine, connects to it, and runs a query:

var options = new TdsServerOptions();
options.AddTcpListener(port: 0, bindAddress: IPAddress.Loopback); // 0: the system picks a free port

using var server = new TdsServer(
    options,
    authenticationHandler: (context, cancellationToken) =>
    {
        var result = context.UserName == "app" && context.Password == "Password123!"
            ? TdsAuthenticationResult.Success()
            : TdsAuthenticationResult.Fail("Login failed.");
        return ValueTask.FromResult(result);
    },
    queryHandler: (context, cancellationToken) =>
    {
        var resultSet = new TdsResultSet();
        resultSet.Columns.Add(new TdsColumn("Message", TdsColumnType.NVarChar));
        resultSet.Rows.Add([$"You sent: {context.CommandText}"]);

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

var port = server.Ports[0];
await using var connection = new SqlConnection($"Server=127.0.0.1,{port};User ID=app;Password=Password123!;Encrypt=Optional");
await connection.OpenAsync();

await using var command = connection.CreateCommand();
command.CommandText = "SELECT 1";
var message = await command.ExecuteScalarAsync();
Assert.Equal("You sent: SELECT 1", message);
  • AddTcpListener adds an endpoint. Port 0 lets the system choose a free port, and Ports gives the port the server bound, which is what tests need. bindAddress defaults to the loopback address, so the server only accepts local connections.
  • The authentication handler accepts or rejects each login. See Authentication.
  • The query handler answers every request. Here it returns one result set with one row, whatever the SQL text. See Handling queries and Returning results.
  • Encrypt=Optional in the connection string lets the client connect without TLS, since this server has no certificate. See Encryption.
  • Disposing the server stops listening and cancels the requests in progress.

Host the server in an ASP.NET Core application

In an application, host the server with the generic host, so it starts and stops with the application and logs through its logging configuration:

var builder = WebApplication.CreateBuilder(args);
builder.AddTdsServer(options =>
{
    options.AddTcpListener(port: 14330, bindAddress: IPAddress.Loopback);
});

var app = builder.Build();
app.MapTdsHandlers(
    authenticate: (context, cancellationToken) =>
    {
        if (context.UserName == "sa" && context.Password == "Password123!")
        {
            var identity = new ClaimsIdentity(
            [
                new Claim(ClaimTypes.NameIdentifier, "42"),
                new Claim(ClaimTypes.Name, context.UserName ?? "sa"),
            ],
            authenticationType: "password");
            return ValueTask.FromResult(TdsAuthenticationResult.Success(userContext: new ClaimsPrincipal(identity)));
        }

        return ValueTask.FromResult(TdsAuthenticationResult.Fail("Login failed"));
    },
    query: (context, cancellationToken) =>
    {
        var user = context.UserContext;

        var resultSet = new TdsResultSet();
        resultSet.Columns.Add(new TdsColumn("Message", TdsColumnType.NVarChar));
        resultSet.Rows.Add(["Hello from TDS server"]);

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

app.Run();

Clients connect with Server=127.0.0.1,14330;User ID=sa;Password=Password123!;Encrypt=Optional. See Hosting the server for the options.

Let the query engine answer the queries

Instead of parsing the SQL text yourself, map table names to IQueryable sources and let the built-in query engine run the SELECT statements:

var products = new[]
{
    new { Id = 1, Name = "Keyboard", Price = 49.90m },
    new { Id = 2, Name = "Mouse", Price = 19.50m },
    new { Id = 3, Name = "Monitor", Price = 189.00m },
}.AsQueryable();

var queryEngineOptions = new TdsQueryEngineOptions();
queryEngineOptions.AddQueryRoot("products", context => products);

var options = new TdsServerOptions();
options.AddTcpListener(port: 0, bindAddress: IPAddress.Loopback);
using var server = new TdsServer(
    options,
    authenticationHandler: (context, cancellationToken) => ValueTask.FromResult(TdsAuthenticationResult.Success()),
    queryHandler: TdsQueryEngine.CreateQueryHandler(queryEngineOptions));
await server.StartAsync();

await using var connection = new SqlConnection($"Server=127.0.0.1,{server.Ports[0]};User ID=app;Password=Password123!;Encrypt=Optional");
await connection.OpenAsync();

await using var command = connection.CreateCommand();
command.CommandText = "SELECT Name, Price FROM dbo.products WHERE Price < @maxPrice ORDER BY Name";
command.Parameters.Add(new SqlParameter("@maxPrice", SqlDbType.Decimal) { Value = 50m });

var names = new List<string>();
await using (var reader = await command.ExecuteReaderAsync())
{
    while (await reader.ReadAsync())
    {
        names.Add(reader.GetString(0));
    }
}

Assert.Equal(["Keyboard", "Mouse"], names);

See The query engine.

Next steps