Thomas.Database 3.1.0

.NET 5.0 This package targets .NET 5.0. The package is compatible with this framework or higher. .NET Standard 2.0 This package targets .NET Standard 2.0. The package is compatible with this framework or higher. .NET Framework 4.7.2 This package targets .NET Framework 4.7.2. The package is compatible with this framework or higher.
There is a newer version of this package available.
See the version list below for details. 
dotnet add package Thomas.Database --version 3.1.0
                    


                    

                
NuGet\Install-Package Thomas.Database -Version 3.1.0
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package. 
<PackageReference Include="Thomas.Database" Version="3.1.0" />
For projects that support PackageReference, copy this XML node into the project file to reference the package. 
<PackageVersion Include="Thomas.Database" Version="3.1.0" />
                    


                            Directory.Packages.props
                        

                    

                
<PackageReference Include="Thomas.Database" />
                    


                            Project file
For projects that support Central Package Management (CPM), copy this XML node into the solution Directory.Packages.props file to version the package. 
paket add Thomas.Database --version 3.1.0
                    


                    

                
#r "nuget: Thomas.Database, 3.1.0"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package. 
#:package Thomas.Database@3.1.0
#:package directive can be used in C# file-based apps starting in .NET 10 preview 4. Copy this into a .cs file before any lines of code to reference the package. 
#addin nuget:?package=Thomas.Database&version=3.1.0
                    


                            Install as a Cake Addin
                        

                    

                
#tool nuget:?package=Thomas.Database&version=3.1.0
                    


                            Install as a Cake Tool

alternate text is missing from this package README image ThomasDataAdapter

Simplified setup for robust database operations by relief having to manage connection, commands or transactions

🌟 Join Us in Shaping the Future of ThomasDataAdapter! 🌟

Join us in refining a library designed to streamline database interactions effortlessly. Your insights, tests, and small enhancements can make a big difference. Feel free to fix bugs and suggest features.

🎯 Features:

  • Support for NET CORE and NET FRAMEWORK
  • Enhanced integration of both typed and anonymous types with query parameters for a more streamlined process.
  • Expanded transaction support through the use of lambda expressions.
  • Attribute configuration for DbParameter, including parameter direction, size, and precision.
  • Unique signature creation for database settings, enabling static instantiation of database contexts on demand.
  • Advanced optional caching layer designed to improve application performance by managing storage, updates, and deletions of cache, including the ability to refresh cache dynamically through a key identifier based on specific queries, expressions, and parameters.
  • Full support for record types.

Supported database providers

  • Microsoft.Data.SqlClient
  • Oracle.ManagedDataAccess.Core
  • Npgsql
  • Mysql.Data
  • Microsoft.Data.Sqlite

🚀 Quick start

Basic configuration in startup.cs

Benefits of persisting the configuration with its string connection you may have different versions of it for different purposes like pooling or Packet Size:

DbConfigurationFactory.Register(new DbSettings(
    signature: "db1",
    provider: SqlProvider.SqlServer,
    stringConnection: $"Data Source={SourceName};Initial Catalog={database};User ID={User};Password={Pass}"
));

DbConfigurationFactory.Register(new DbSettings(
    signature: "db2",
    provider: SqlProvider.Sqlite,
    stringConnection: $"Data Source={filePath}"
));

Custom configuration

DbConfigurationFactory.Register(new DbSettings { 
    Signature = "db1",
    StringConnection = $"Data Source={SourceName};Initial Catalog={database};User ID={User};Password={Pass}",
    DetailErrorMessage = true,
    ConnectionTimeout = 300,
    SensitiveDataLog = true
 });

Expression configuration

   var builder = new TableBuilder();
   builder.Configure<User>(key: x => x.Id).AddFieldsAsColumns<User>().DbName("system_user");
   DbFactory.AddDbBuilder(builder);

Expressions calls

public class UserRepository
{
    public List<User> GetUsers() => DbFactory.GetDbContext("db1").ToList<User>();
    public List<User> GetInvalidUsers() => DbFactory.GetDbContext("db1").ToList<User>(
        user => 
        SqlExpression.IsNull<Person>(x => x.LastName) &&
        x.Age <= 0 && x.Age > 100 &&
        x.Salary <= 0
    );

    public int Add(User user) => DbFactory.GetDbContext("db2").Add<User, int>(user);
    public void Update(User user) => DbFactory.GetDbContext("db2").Update(user);
    public void Delete(User user) => DbFactory.GetDbContext("db2").Delete(user);
}

Direct calls

    public void DisableUser(int id) => DbFactory.GetDbContext("db2").ExecuteOp(new { id }, "disable_user");
    public Tuple<IEnumerable<User>, IEnumerable<Office>> ProcessData() {
        return DbFactory.GetDbContext("db1").ToTupleOp<User, Office>("sp_process");
    }

    public bool ActiveOffice(decimal officeId)
    {
        return DbFactory.GetDbContext("db2").ExecuteTransaction((db) =>
        {
            db.Execute("UPDATE User SET Active = 1 WHERE OfficeId = $officeId", new { officeId });
            db.Execute("UPDATE Office SET Active = 1 WHERE OfficeId = $officeId", new { officeId });
            return db.Commit();
        });
    }

    //batching
    public void Export()
    {
        var (dispose, data) = sqlServerContext.FetchData<Order>(script: "SELECT * FROM Order", parameters: null, batchSize: 10000);
        foreach (var batch in data)
        {
            //process batch
        }
        dispose();
    }

    //records
    public record User(int Id, string Name, byte[] photo);
    public List<User> Export()
    {
        return sqlServerContext.ToList<User>("SELECT * FROM User"); 
    }

Perfomance

Experience really good performance without the hassle of managing DbConnection. Here's a fair test where we consistently dispose of connections after each use.

Note: Tested with Docker MSSQL locally

BenchmarkDotNet v0.13.12, Windows 11 (10.0.22631.3527/23H2/2023Update/SunValley3)
13th Gen Intel Core i9-13900H, 1 CPU, 20 logical and 14 physical cores
.NET SDK 8.0.101
  [Host]     : .NET 8.0.1 (8.0.123.58001), X64 RyuJIT AVX2
  Job-VNBJGA : .NET 8.0.1 (8.0.123.58001), X64 RyuJIT AVX2


| Detailed Runtime           | Type                       | Method                      | Mean     | StdDev   | Error    | Op/s    | Gen0   | Allocated |
|--------------------------- |--------------------------- |---------------------------- |---------:|---------:|---------:|--------:|-------:|----------:|
| .NET 8.0.1 (8.0.123.58001) | ThomasDataAdapterBenckmark | Single<>                    | 432.8 us | 22.58 us |  8.15 us | 2,310.6 |      - |   8.04 KB |
| .NET 8.0.1 (8.0.123.58001) | DapperBenckmark            | QuerySingle<T>              | 500.0 us | 25.82 us |  9.93 us | 2,000.1 |      - |  11.54 KB |
| .NET 8.0.1 (8.0.123.58001) | ThomasDataAdapterBenckmark | 'ToListRecord<> Expression' | 563.7 us | 44.90 us | 15.23 us | 1,774.0 | 1.9531 |  26.76 KB |
| .NET 8.0.1 (8.0.123.58001) | ThomasDataAdapterBenckmark | ToList<>                    | 572.7 us | 12.89 us | 10.83 us | 1,746.2 | 1.9531 |  26.47 KB |
| .NET 8.0.1 (8.0.123.58001) | DapperBenckmark            | 'Query<T> (buffered)'       | 602.0 us | 38.75 us | 13.36 us | 1,661.1 | 1.9531 |  26.45 KB |

Best practices

  • For fastest data retrieve prefer use Store procedure over Sql text queries
  • Prefer use Records over Anemic class to retrieve data
  • Try different bufferSize values when need read streams for optimal performance
  • Columns defined as NOT NULL will enhance the generate algorithm because will avoid IsDBNull branching
  • Use latest database provider library version as much as possible for security and performance concerns
  • Use nullable Datatype for classes used to write operations or store procedures object filter for more natural interpretation of DBNull when is possible, here are more DbNull implicit values:
Type implicit DbNull
String null
Datetime MinValue
TimeSpan MinValue
Guid default
StringBuilder null
XmlDocument null
Byte[] null or empty array
Nullable<> not(HasValue)

Considerations

Library's purpose is to make easy DB interactions with a simple configuration. Obviously it doesn't attempt to solve every problem. There are some considerations at development time:

  • Ensure that the specific database provider library is installed in your project, as it will be accessed via reflection by this library.
  • Configuration for write operations (insert, update and delete) requires TableBuilder preferring at application startup.

Db Types

  • Nullable versions has the same corresponding DbType
  • DateOnly and TimeOnly available in NET 6 or greater
  • C# types will transform to their specific dbTypes to setup parameters command
  • SQLite dbtypes won't setup so when create dynamically parameters the library itself to infer their type
C# Type SQL Server Oracle PostgreSQL MySQL
string NVARCHAR VARCHAR2 VARCHAR VARCHAR
short / ushort SMALLINT INT16 SMALLINT INT16
int / uint INT INT32 INTEGER INT32
long / ulong BIGINT INT64 BIGINT INT64
byte / sbyte TINYINT BYTE SMALLINT BYTE
decimal DECIMAL DECIMAL NUMERIC DECIMAL
double FLOAT DOUBLE DOUBLE DOUBLE
float FLOAT FLOAT REAL FLOAT
bool BIT INT32 (0 or 1) BIT BIT
DateTime DATETIME DATE TIMESTAMP DATETIME
TimeSpan TIME IntervalDS INTERVAL -
Guid UNIQUEIDENTIFIER RAW UUID GUID
byte[] VARBINARY BLOB BYTEA MEDIUMBLOB
SqlBinary BINARY - - -
XmlDocument XML XMLTYPE XML XML
StringBuilder TEXT CLOB TEXT -
DateOnly DATE DATE DATE DATE
TimeOnly TIME - TIME TIME

Limitations

Be aware of the following limitations:

  • Currently limited to specific DB library providers, with plans to expand support in the future.
  • Depend on Sigil a powerful reflection.emit ILGenerator. However, I consider remove it.
Product Compatible and additional computed target framework versions.
.NET net5.0 is compatible.  net5.0-windows was computed.  net6.0 is compatible.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 is compatible.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 is compatible.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed.  net9.0 was computed.  net9.0-android was computed.  net9.0-browser was computed.  net9.0-ios was computed.  net9.0-maccatalyst was computed.  net9.0-macos was computed.  net9.0-tvos was computed.  net9.0-windows was computed.  net10.0 was computed.  net10.0-android was computed.  net10.0-browser was computed.  net10.0-ios was computed.  net10.0-maccatalyst was computed.  net10.0-macos was computed.  net10.0-tvos was computed.  net10.0-windows was computed. 
.NET Core netcoreapp2.0 was computed.  netcoreapp2.1 was computed.  netcoreapp2.2 was computed.  netcoreapp3.0 was computed.  netcoreapp3.1 was computed. 
.NET Standard netstandard2.0 is compatible.  netstandard2.1 was computed. 
.NET Framework net461 was computed.  net462 was computed.  net463 was computed.  net47 was computed.  net471 was computed.  net472 is compatible.  net48 was computed.  net481 was computed. 
MonoAndroid monoandroid was computed. 
MonoMac monomac was computed. 
MonoTouch monotouch was computed. 
Tizen tizen40 was computed.  tizen60 was computed. 
Xamarin.iOS xamarinios was computed. 
Xamarin.Mac xamarinmac was computed. 
Xamarin.TVOS xamarintvos was computed. 
Xamarin.WatchOS xamarinwatchos was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages (2)

Showing the top 2 NuGet packages that depend on Thomas.Database:

Package Downloads
ThomasDataAdapter.SqlServer

Simple library to get data from Database SQL Server specially high load and low memory consum.
Thomas.Cache

ThomasDataAdapter Cache Layer to boost performance with dynamic cache refresh capabilities.

GitHub repositories

This package is not used by any popular GitHub repositories.

Features released 3.0.0:

- Enhanced integration of both typed and anonymous types with query parameters for a more streamlined process.
- Expanded transaction support through the use of lambda expressions.
- Enhanced attribute configuration for DbParameter, including parameter direction, size, and precision.
- Unique signature creation for database settings, enabling static instantiation of database contexts on demand.
- Advanced optional caching layer designed to improve application performance by managing storage, updates, and deletions of cache, including the ability to refresh cache dynamically through a key identifier based on specific queries, expressions, and parameters.
- Full support for record types.