This is a simple library to generate SQL based on POCO (or better PORO 🎉). This is the C# version of my F# library FSharpDapper.
It maps collections, records and anonymous types to SQL so this can be used with Dapper.
This library is reflection based to generate SQL - but queries are cached then making it quite efficient after warmup.
Implementation is provided for:
- Generic SQL (
Select
,Insert
,Update
,Delete
,DapperConnection<T>
) - Specialized dialect MSSQL (
Upsert
)
Package | Status | Description |
---|---|---|
SqlDapper | Core package | |
SqlDapper.SqlServer | SqlServer provider |
First you have to use SqlDapper
namespace before proceeding:
using SqlDapper;
In order to read/write data, you will need to define a record first (or a class with properties):
[Table("StatusEx")]
public record DbStatusEx([property: Key] string Name,
int Status,
string? Comment);
Available attributes are:
Table(name)
: force table name at class level. Default value is record/class nameKey
: specify a property as a key (or part of key composite if multiple declarations)
You have first to import an ADO.Net provider (SQLServer, SQlite...). A connection can then be created (for exemple for SQlite
):
var conn = new DapperConnection<SQLiteConnection>("Data Source=sqldapper.db");
var status = conn.Select<DbStatusEx>(new { Name = "toto" });
Following operations are available (methods on IDapperConnection
). Those operations are same as Dapper and basically allow unit testing the library:
Operation | Description |
---|---|
Execute |
Run provided sql query using the parameter and returns the result from the query (an int) |
QueryScalar<T> |
Run provided sql query using the parameter and returns the single result of T |
Query<T> |
Run provided sql query using the parameter and returns a list of result of T |
TransactionScope |
Create a transaction scope - transaction must be disposed |
Following operations are available as extension methods on IDapperConnection
:
Operation | Description |
---|---|
Select<Table> |
Run select query using the conditions and returns a list of result of Table |
SelectAll<Table> |
Select all results from table Table |
Insert<Table> |
Insert values into table Table |
Update<Table> |
Update table Table` with values |
Delete<Table> |
Delete table Table using the conditions |
Insert
, Update
, Delete
support either a single value or a list. A value is either a record or an anonymous record.
Following operations are available as extension methods on IDapperConnection
:
Operation | Description |
---|---|
Upsert<Table> |
Upsert values into table Table |
Upsert
support either a single value or a list. A value is either a record or an anonymous record.
using SqlDapper;
[Table("Status")]
public record DbStatus([property: Key] string Name, int Status);
var prm = new { Name = "tagada", Status = 42 };
using var tx = conn.TransactionScope();
conn.Upsert<DbStatus>(prm);
tx.Complete();
This generates following SQL:
MERGE INTO [Status] as TARGET
USING (VALUES(@Name,@Status)) AS SOURCE ([Name],[Status]) ON SOURCE.[Name]=TARGET.[Name]
WHEN MATCHED THEN UPDATE SET [Status]=SOURCE.[Status]
WHEN NOT MATCHED THEN INSERT ([Name],[Status]) VALUES (SOURCE.[Name],SOURCE.[Status]);