I.S.M. Database SQLServer

This project is a .NET 10.0 class library that provides a database access layer for SQL Server. It includes methods for connecting to the database, executing queries, and managing transactions. The library is designed to be used in applications that require efficient and reliable data access to SQL Server databases. It supports asynchronous operations and is compatible with the latest features of C# 14.0.

Configuration

To configure the database connection, you can use the following connection string format in your application's configuration file (e.g., appsettings.json):

{
  "ConnectionStrings": {
    "SqlConnection": "Server=your_server;Database=your_database;User Id=your_username;Password=your_password;"
  }
}

[!TIP] The list of additional SQL error numbers to consider transient can be used to specify any custom error codes that your application may encounter and want to treat as transient errors for retry logic.

And to configure SQL Server specific settings, you can add the following section to your configuration file:

{
  "SQLServerSettings": {
    "MigrationAssembly": "YourProject.Migrations",
    "Environment": "Development", // or "Staging" or "Production"
    "CompatibilityLevel": 160, // SQL Server 2022
    "CommandTimeout": 180, // in seconds
    "MaxRetryCount": 5, // number of retry attempts for transient failures
    "MaxRetryDelay": "00:00:10", // maximum delay between retries (10 seconds)
    "ErrorNumbersToAdd": [] // list of additional SQL error numbers to consider transient
  }
}

[!NOTE] The list is available in this Microsoft learn article and can be used to enhance the robustness of your application's database interactions by allowing you to specify additional error codes that should trigger retry logic.

In your application's startup code (e.g., Program.cs), you can retrieve the connection string and SQL Server settings from the configuration and create the SQL Server configuration object as follows:

var connectionString = builder.Configuration.GetConnectionString("SqlConnection")!;
var sqlServerSettings = builder.Services.ConfigureAndGet<SQLServerSettings>(builder.Configuration, nameof(SQLServerSettings));

var sqlServerConfiguration = GetSQLServerConfiguration(builder, connectionString, sqlServerSettings);

Finally in the DbContext configuration it is necessary to add the relevant configuration.

builder.Services.AddDbContext<YourDbContext>(options =>
{
    options.UseSQLServerConfiguration(sqlServerConfiguration);
});