SafeConnection 1.0.11

About

SafeConnection is a robust library designed to facilitate secure and efficient database connections. It provides a comprehensive set of features including:

• Secure Connections: Ensures all database connections are secure and encrypted. Additionally, it closes the passed connection explicitly.
• Caching: Implements in-memory caching to reduce database load and improve performance.
• Logging: Integrated logging capabilities to track database operations and errors.
• Compatibility: Supports multiple .NET versions including .NET Standard 3.1 and .NET 6.0.
• Ease of Use: Simple and intuitive API for quick integration into your projects.

This library is ideal for applications that require reliable and secure database interactions with minimal overhead.

Usage Instructions

To use SafeConnection, simply install the package via NuGet and add the following code to your project:

1. Install the Package: Add the SafeConnection package to your project using NuGet:

    dotnet add package SafeConnection
   

2. Inject the Service: Inject the SafeConnection service into your application as Singleton:

    builder.Services.AddSingleton<SafeConnection>(_ => new SafeConnection(
    	appName: builder.Environment.ApplicationName, // It will be used in the logs.
    	environment: builder.Environment.EnvironmentName, // It will be used in the logs.
    	sqlConnectionString: builder.Configuration.GetConnectionString("Your_Sql_log_connectionString"), // Where the logs will be stored.
    	sqlTable: "App_Logs", // The table where the logs will be stored.
    	cacheExpirationMinutes: builder.Configuration.GetValue<int>("CacheExpirationInMinutes") // The expiration time of the cache in minutes.
    ));
   

3. Use the Service: Use the SafeConnection service in your application to interact with the database:

    public class YourService
    {
    	private readonly SafeConnection _safeConnection;
   
    	// Inject SafeConnection service
    	public YourService(SafeConnection safeConnection)
    	{
    		_safeConnection = safeConnection;
    	}
   
    	// Use SafeConnection to execute SQL queries
    	public async Task<IEnumerable<YourModel>> GetYourData()
    	{
    		var query = @"SELECT field1, field2 from your_table";
   
           var connection = new OleDbConnection("Your__query_string");		
    		return await _safeConnection.QueryAsync<YourModel>(connection, query, "YourCacheKey", queryParams: null, specificCache: null, commandTimeout: 30);
    	}
    }
   

4. The SQL table structure:

    CREATE TABLE App_Logs (
    	Id int IDENTITY(1,1) NOT NULL,
    	[DateTime] datetime NOT NULL,
    	App varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	Env varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	Query varchar(500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    	Cached bit NOT NULL,
    	Duration real NOT NULL,
    	[Rows] int NOT NULL,
    	Columns int NOT NULL,
    	Kb int NULL,
    	ServerName varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	ConnectionType varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    	CONSTRAINT App_Logs_PK PRIMARY KEY (Id)
    );
   

Methods:

QueryAsync<T> Executes a query asynchronously and returns the result set.

Type Parameters

• T: The type of the result set elements.

Parameters

• IDbConnection dbConnection: The database connection to use for the query.
• string query: The SQL query to execute.
• string queryKey: A unique key identifying the query, used for caching the result and identifying the query in the logs.
• object queryParams: The parameters to pass to the query. Default is null.
• int? specificCache: The expiration time of the cache in minutes for this query. Default is null and uses the default cache expiration time. Use 0 to disable caching.
• int? commandTimeout: The command timeout in seconds. Default is null.

Returns

• Task<IEnumerable<T>>: An enumerable of the result set elements.

Example

var parameters = new { Id = 1 }; // Optional parameter
var query = @"SELECT field1, field2 from your_table WHERE Id = @Id";
var specificCache = 0; // Optional: 0 to disable cache, null for default, or specific minutes
var commandTimeout = 30; // Optional: timeout in seconds, null for default
var connection = new OleDbConnection("Your__query_string");
return await _safeConnection.QueryAsync<YourModel>(connection, query, "YourCacheKey", parameters, specificCache, commandTimeout);

Query<T> Executes a query and returns the result set.

Type Parameters

• T: The type of the result set elements.

Parameters

• IDbConnection dbConnection: The database connection to use for the query.
• string query: The SQL query to execute.
• string queryKey: A unique key identifying the query, used for caching the result and identifying the query in the logs.
• object queryParams: The parameters to pass to the query. Default is null.
• int? specificCache: The expiration time of the cache in minutes for this query. Default is null and uses the default cache expiration time. Use 0 to disable caching.
• int? commandTimeout: The command timeout in seconds. Default is null.

Returns

• IEnumerable<T>: An enumerable of the result set elements.

Example

var parameters = new { Id = 1 }; // Optional parameter
var query = @"SELECT field1, field2 from your_table WHERE Id = @Id";
var specificCache = 0; // Optional: 0 to disable cache, null for default, or specific minutes
var commandTimeout = 30; // Optional: timeout in seconds, null for default
var connection = new OleDbConnection("Your__query_string");
return _safeConnection.Query<YourModel>(connection, query, "YourCacheKey", parameters, specificCache, commandTimeout);

LogInformation Manually logs information.

Parameters

• string queryKey: A unique key identifying the query, it will be used to identify the query in the logs.
• long elapsedMilliseconds: The time taken to execute the query, in milliseconds.
• int rowCount: The number of rows returned by the query.
• int columnCount: The number of columns in the result set.
• int kb: The size of the result set in kilobytes. Default is -1 if not specified.
• string serverName: The name of the server where the query was executed. Default is an empty string if not specified.
• string connectionType: The type of the connection used to execute the query. Default is an empty string if not specified.

Example

var queryKey = "YourCacheKey";
var elapsedMilliseconds = 100;
var rowCount = 10;
var columnCount = 2;
var kb = 1;
var serverName = "YourServerName"; // Optional parameter, default is an empty string.
var connectionType = "YourConnectionType"; // Optional parameter, default is an empty string.
_safeConnection.LogInformation(queryKey, elapsedMilliseconds, rowCount, columnCount, kb, serverName, connectionType);