Connecting to the Database

Every call your application makes to the Rule Repository, Audit or the MCP server takes a database provider: an object that knows how to open a connection to your repository database and which SQL dialect it speaks. This page explains the providers that ship, how to configure them, and how to write your own when you need a different way of connecting.

You need a provider because the libraries never read a connection string from configuration files themselves. Your application decides where the database is and how to sign in to it, builds one provider, and hands it to every call.

The Providers That Ship

Database	Class	Package	Default schema
SQL Server 2012+	`CodeEffects.Rule.Connection.SqlServer.DbProvider`	`CodeEffects.Rule.Connection.SqlServer` (uses `Microsoft.Data.SqlClient`)	`dbo`
PostgreSQL 14+	`CodeEffects.Rule.Connection.Postgres.DbProvider`	`CodeEffects.Rule.Connection.Postgres` (uses `Npgsql`)	`public`

Both implement IDbProvider from CodeEffects.Rule.Connection, and both take a connection string and, optionally, a schema name:

using CodeEffects.Rule.Connection;

IDbProvider sqlServer = new CodeEffects.Rule.Connection.SqlServer.DbProvider(
	"Server=sql01;Database=CodeEffectsRules;Integrated Security=True;Encrypt=True");

IDbProvider postgres = new CodeEffects.Rule.Connection.Postgres.DbProvider(
	"Host=pg01;Database=codeeffects_rules;Username=rules_app;Password=...;SSL Mode=Require",
	schemaName: "rr");

The schema name must be the schema your tables were created in (see The Database). The provider quotes it, so give it exactly as the database stored it.

One Provider for the Whole Application

A provider holds only the connection string, the schema name and the dialect. It opens nothing when you create it: each library opens a connection for each piece of work, and closes it when done. So:

Create it once at startup and share it: register it as a singleton, pass it to Context.GetResponse, Context.SyncSources, AuditRecorder and the MCP server's options.
Creating one per request is also harmless, if that suits your code better.
Connection pooling is the ADO.NET driver's own and is on by default for both databases. Keep it on.
Providers are safe to use from many requests at once.

// Program.cs (ASP.NET Core)
builder.Services.AddSingleton<IDbProvider>(_ =>
	new CodeEffects.Rule.Connection.SqlServer.DbProvider(
		builder.Configuration.GetConnectionString("Rules")));

// Global.asax.cs (ASP.NET on .NET Framework)
public static IDbProvider RulesDb { get; private set; }

protected void Application_Start()
{
	RulesDb = new CodeEffects.Rule.Connection.SqlServer.DbProvider(
		ConfigurationManager.ConnectionStrings["Rules"].ConnectionString);
}

Connection Strings

Everything a connection string can say is up to you and your security policies: the server, the authentication method, encryption, timeouts, application name. A few settings are worth setting deliberately:

Setting	SQL Server	PostgreSQL	Why
Encryption	`Encrypt=True` (and a trusted certificate)	`SSL Mode=Require` or `VerifyFull`	Rules and recorded evaluations may hold business data.
Application name	`Application Name=RulesPortal`	`Application Name=RulesPortal`	Lets your DBAs see the repository's connections.
Command timeout	`Command Timeout=60`	`Command Timeout=60`	The libraries set no timeout of their own, so the driver's default (30 seconds) applies. Raise it only if very large grids or source syncs time out.
Pool size	`Max Pool Size=...`	`Maximum Pool Size=...`	Defaults are fine for most sites.

Keep the connection string in your secret store or in configuration that is protected, never in source control.

What the Libraries Do with Failures

Each library classifies a database error through the provider's dialect:

Kind (`DbFailureKind`)	Examples	What happens
`Deadlock`	SQL Server 1205, PostgreSQL deadlock	The statement is retried once.
`ConnectionLost`	A dropped connection, an Azure SQL failover	Retried once.
`Timeout`	The command timed out	Not retried; the request fails with CE19003.
`MissingObject`	A table or column not found	Not retried; the library checks the schema version and, if the database is behind, fails with CE19004 naming the update scripts to run.
`Other`	Anything else	Not retried; the request fails with CE19002.

The user sees a general message with the control number; your application's log gets the whole exception (see The Server Endpoint).

Customizing the Connection

The most common customization is how the connection is opened: a managed identity, an access token from your identity platform, a connection string read from a vault on every open. You do not need a new dialect for that. Implement IDbProvider and IConnectionProvider yourself and reuse the shipped dialect:

using System.Data;
using CodeEffects.Rule.Connection;
using Microsoft.Data.SqlClient;

// A SQL Server provider that opens every connection with an access token from your own token source
public sealed class TokenSqlProvider : IDbProvider, IConnectionProvider
{
	private readonly string connectionString;
	private readonly Func<string> getAccessToken;

	public TokenSqlProvider(string connectionString, Func<string> getAccessToken, string schemaName = "dbo")
	{
		this.connectionString = connectionString;
		this.getAccessToken = getAccessToken;
		this.SchemaName = schemaName;

		// The shipped SQL Server dialect, taken from the shipped provider
		this.Dialect = new CodeEffects.Rule.Connection.SqlServer.DbProvider(connectionString).Dialect;
	}

	public IConnectionProvider ConnectionProvider => this;
	public IDialect Dialect { get; }
	public string SchemaName { get; }

	public IDbConnection CreateConnection() =>
		new SqlConnection(this.connectionString) { AccessToken = this.getAccessToken() };
}

CreateConnection must return a new, closed connection each time; the libraries open, use and dispose of it.

(With Microsoft.Data.SqlClient you can often avoid this altogether: Authentication=Active Directory Managed Identity or Active Directory Default in the connection string signs in with Microsoft Entra ID.)

Writing Your Own Dialect

A dialect (IDialect) holds everything about SQL that differs between databases. You write one only to run the repository on a database the shipped providers do not cover. That also means translating every schema and update script to that database yourself, and testing the result; Code Effects supports SQL Server and PostgreSQL.

Member	What it returns
`QuoteIdentifier(name)`	An identifier quoted for the database: `[name]`, `"name"` or `name`. Used for the schema name.
`Table(schemaName, tableName)`	A table name qualified with the schema, or the bare name when the schema is empty.
`ParameterMarker(name)`	How a parameter is written in statement text: `@name`, or `:name`.
`UtcNow`	An expression for the current UTC date and time, at least to the millisecond.
`InsertReturningId(table, columns, values, idColumn)`	An `INSERT` of one row that returns the new identity value as its single result.
`ContainsPattern(value)`	A `LIKE` pattern matching the value anywhere, with every wildcard escaped by `!`.
`FormatLimit(n)`, `FormatPaging(skip, take)`	The clause that limits or pages a query; appended after `ORDER BY`.
`Classify(exception)`	The `DbFailureKind` of a database exception (walk the inner exceptions). Return `MissingObject` for "no such table or column", or the schema-behind diagnosis never happens.
`CreateParameter(name, value)`, `CreateLargeTextParameter(name, value)`	A `ParameterInfo` for an ordinary value, and for a value of an unlimited text column.
`NewSequentialId()`	A new GUID that sorts after every one this process made before, in the database's own GUID order. Audit keys evaluations by it so they append to the clustered index.

The statements the libraries send follow fixed rules so a dialect stays small: table and column names are unquoted and never reserved words, one statement per command with no trailing semicolon, no UPDATE ... FROM, no MERGE, and affected-row counts come from the driver, not from a SQL function.

Ask on Stack Overflow - Yes, it's still active
Ask in GitHub Discussions

p102

l107

l097 --