G9DatabaseManagement.SqlServer 1.0.0.9

G9 Database Management Project

The G9 Database Management Project provides a robust framework for managing databases in .NET applications. It features built-in support for SQLite and SQL Server, advanced table caching, encryption, and hashing mechanisms, making it an ideal solution for modern database-driven projects.

Features

• Multi-Database Support:

  • SQLite with password-protected database files (via SQLCipher).
  • SQL Server integration with seamless configuration.

• Advanced Table Caching:

  • Supports caching with configurable refresh intervals.
  • Automatic handling of relationships during caching.

• Encryption and Hashing:

  • AES encryption for sensitive fields.
  • Hashing support with configurable algorithms like MD5, SHA256, and more.

• Customizable Attributes:

  • Table definition attributes for schema, soft delete, and primary key customization.
  • Column-level encryption and hashing attributes.

• Background Services:

  • Automated database backup service.
  • Table cache handler service for real-time data synchronization.

Project Structure

Database Contexts

• CustomSqliteDbContext:

  • SQLite-specific context supporting encrypted databases.
  • Dynamically determines database paths for mobile and desktop environments.

• CustomSqlServerDbContext:

  • SQL Server context with streamlined configuration.

Table Models

• TblTest: Primary table with caching enabled and relationships to TblTest2.
• TblTest2: Intermediate table connecting TblTest and TblTest3.
• TblTest3: Includes encrypted and hashed columns with complex relationships.

Background Services

• G9CSQLServerDatabaseBackupBackgroundService: Automates SQL Server database backups at configured intervals.
• G9CSQLServerTableCacheHandlerBackgroundService: Ensures real-time table cache synchronization.
• G9CSQLiteTableCacheHandlerBackgroundService: Equivalent service for SQLite databases.

How to Use

Setup SQLite Context

1. Ensure SQLitePCLRaw.bundle_sqlcipher is installed for encryption support.
2. Configure the SQLite context:

   var sqliteDb = new CustomSqliteDbContext();
   sqliteDb.Database.EnsureDeleted();
   sqliteDb.Database.EnsureCreated();
   

Setup SQL Server Context

1. Configure the SQL Server connection string in CustomSqlServerDbContext:

   optionsBuilder.UseSqlServer("YourConnectionString");
   

2. Initialize and use the context:

   var db = new CustomSqlServerDbContext();
   db.Database.EnsureDeleted();
   db.Database.EnsureCreated();
   

Add Background Services

Register the database services in the DI container:

builder.Services.AddDbContext<G9ADatabaseContextInitializer, CustomSqlServerDbContext>();
builder.Services.AddHostedService(serviceProvider =>
{
    var logger = serviceProvider.GetRequiredService<ILogger<G9CSQLServerDatabaseBackupBackgroundService>>();
    var config = serviceProvider.GetRequiredService<G9DtBackupServiceConfig>();

    return new G9CSQLServerDatabaseBackupBackgroundService(serviceProvider, logger, config);
});
builder.Services.AddHostedService(serviceProvider =>
{
    var logger = serviceProvider.GetRequiredService<ILogger<G9ATableCacheHandlerBackgroundService>>();
    return new G9CSQLServerTableCacheHandlerBackgroundService(serviceProvider, logger);
});

Define Models

Use attributes like G9AttrEnableTableCache and G9AttrColumnEncryptionAttribute to configure models:

[G9AttrEnableTableCache(20, nameof(TblTest2), $"{nameof(TblTest2)}.{nameof(Tables.TblTest2.TblTest3)}")]
public class TblTest : G9ABaseTable<TblTest, int>
{
    public string Name { set; get; }
    public string Family { set; get; }
    public ICollection<TblTest2> TblTest2 { set; get; }
}

Encryption and Hashing

• Enable AES encryption on a property:

  [G9AttrColumnEncryption("PrivateKey", "IvKey")]
  public string SensitiveData { get; set; }
  

• Enable hashing:

  [G9AttrHash(G9EHashAlgorithm.SHA256)]
  public string HashedData { get; set; }
  

Example Usage

var db = new CustomSqlServerDbContext();
db.Database.EnsureCreated();
var data = db.Table<TblTest>().ToList();
data.ForEach(item => Console.WriteLine($"Name: {item.Name}, Family: {item.Family}"));

Contributions

Contributions are welcome! Feel free to submit issues, feature requests, or pull requests.

License

This project is licensed under the MIT License.