Connecting clients

The server speaks the protocol of SQL Server, so clients connect with their usual SQL Server API. The library is tested with Microsoft.Data.SqlClient; this page uses it.

Connection strings

var login = new TaskCompletionSource<TdsAuthenticationContext>(TaskCreationOptions.RunContinuationsAsynchronously);
var options = new TdsServerOptions();
options.AddTcpListener(port: 0, bindAddress: IPAddress.Loopback);
using var server = new TdsServer(
    options,
    authenticationHandler: (context, cancellationToken) =>
    {
        login.TrySetResult(context);
        return ValueTask.FromResult(TdsAuthenticationResult.Success());
    },
    queryHandler: (context, cancellationToken) => ValueTask.FromResult(new TdsQueryResult()));
await server.StartAsync();

var connectionString = new SqlConnectionStringBuilder
{
    DataSource = $"127.0.0.1,{server.Ports[0]}", // host,port
    UserID = "app",
    Password = "Password123!",
    InitialCatalog = "Sales",
    ApplicationName = "Reporting",
    Encrypt = SqlConnectionEncryptOption.Optional,
}.ConnectionString;

await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();

var context = await login.Task;
Assert.Equal("app", context.UserName);
Assert.Equal("Password123!", context.Password);
Assert.Equal("Sales", context.Database);
Assert.Equal("Reporting", context.ApplicationName);

Do not set an Authentication mode other than SQL Password: the server does not support Microsoft Entra ID authentication.

Connect with an instance name

A client given an instance name (host\instance) instead of a port asks the SQL Server Browser service of the host for the port, on UDP port 1434. Call EnableSqlServerBrowser to answer these requests with the port of the first TCP endpoint:

var options = new TdsServerOptions();
options.AddTcpListener(port: 0, bindAddress: IPAddress.Loopback);
options.EnableSqlServerBrowser("SALES"); // UDP port 1434, on 127.0.0.1

using var server = new TdsServer(
    options,
    authenticationHandler: (context, cancellationToken) => ValueTask.FromResult(TdsAuthenticationResult.Success()),
    queryHandler: (context, cancellationToken) => ValueTask.FromResult(new TdsQueryResult()));
await server.StartAsync();

var connectionString = new SqlConnectionStringBuilder
{
    DataSource = @"127.0.0.1\SALES", // host\instance: the client asks the SQL Server Browser for the port
    UserID = "app",
    Password = "Password123!",
    Encrypt = SqlConnectionEncryptOption.Optional,
}.ConnectionString;

await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();

In an ASP.NET Core application, the responder starts and stops with the application:

builder.AddTdsServer(options =>
{
    options.AddTcpListener(port: 14330, bindAddress: IPAddress.Any);
    options.EnableSqlServerBrowser("SALES", bindAddress: IPAddress.Any); // UDP port 1434
});

• The responder answers the requests for one instance name, without case sensitivity, and the requests that list the instances of the host. It ignores the requests for the dedicated administrator connection (DAC).
• Clients only send their requests to port 1434, and one program can listen on it per address. It fails to start when the port is in use, for instance by the SQL Server Browser service of a local SQL Server.
• Like the TCP endpoints, it binds to the loopback address by default. Pass bindAddress to answer the clients of other machines.
• For more settings (the server name, an existing TdsSqlServerBrowserOptions), set SqlServerBrowser. With TdsServer, port 0 binds a free UDP port, which SqlServerBrowserPort returns, to test the protocol without the client.

Run queries

A command without parameters reaches the query handler as a SQL batch, a command with parameters as a call to sp_executesql. See Handling queries. The results are read as usual, including several result sets and informational messages:

var messages = new List<string>();
connection.InfoMessage += (sender, e) => messages.Add(e.Message);
await connection.OpenAsync();

await using var command = connection.CreateCommand();
command.CommandText = "SELECT * FROM products";
await using var reader = await command.ExecuteReaderAsync();

var names = new List<string>();
while (await reader.ReadAsync())
{
    names.Add(reader.GetString(reader.GetOrdinal("Name")));
}

Assert.True(await reader.NextResultAsync()); // move to the second result set
Assert.True(await reader.ReadAsync());
Assert.Equal(2, reader.GetInt32(0));

Assert.Equal(["Keyboard", "Mouse"], names);
Assert.Equal(["2 products found"], messages);

Call stored procedures

Set CommandType.StoredProcedure, and the name of the procedure as the command text (connection is an open SqlConnection):

await using var command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "GetCustomerName";
command.Parameters.Add(new SqlParameter("@id", SqlDbType.Int) { Value = 1 });

var name = await command.ExecuteScalarAsync();
Assert.Equal("Alice", name);

The query handler receives the name in ProcedureName and the parameters in Parameters. With the query engine, the call runs the delegate registered under that name.

What clients see

• ExecuteNonQuery returns -1: the server does not report the number of affected rows.
• Output parameters and return values are not set: the server does not return them.
• After an error returned by the server, SqlClient closes the connection. See Return an error.
• BeginTransaction fails with "Unsupported packet type": the server has no transactions.