Important
This project has already stopped development. Please consider using QLimitive as an alternative.
This library provides attribute-based table mapping and simple database access. It mainly contains following features.
- Unified connection to the some databases (SQL Server / Oracle / MySQL / SQLite etc.)
- Attribute-based simple SQL generation
- Super easy CRUD access based Dapper
- Automatically set
CreatedAt/ModifiedAtcolumn. - High availability connection support by master/slave approach
- .NET Framework 4.6.1+
- .NET Standard 2.0+
- .NET 5.0+
DeclarativeSql that is also inspired by Entity Framework provides attribute-based database mapping. Following code is its sample. Generates SQL and performs O/R mapping that based on these attributes and types.
using System;
using DeclarativeSql.Annotations;
namespace SampleApp
{
[Table(DbKind.SqlServer, "T_Person", Schema = "dbo")] // Customize table name per database
public class Person
{
[PrimaryKey] // Primary key constraint
[AutoIncrement] // Automatically numbering
public int Id { get; set; }
[Unique(0)] // Unique constraint by index
public string Email { get; set; }
[Column(DbKind.SqlServer, "名前")] // Customize column name per database
public string Name { get; set; }
[AllowNull] // Nullable
public int? Age { get; set; }
[CreatedAt] // Set datetime when row is inserted
[DefaultValue(DbKind.SqlServer, "SYSDATETIME()")]
public DateTimeOffset CreatedOn { get; set; }
[ModifiedAt] // Set datetime when row is updated
[DefaultValue(DbKind.SqlServer, "SYSDATETIME()")]
public DateTimeOffset UpdatedOn { get; set; }
}
}This library also provides automatic sql generation feature using above meta data. You can get very simple and typical sql using QueryBuilder class. Of course it's completely type-safe.
//--- Query records with specified columns that matched specified condition
var sql
= QueryBuilder.Select<Person>
(
this.DbProvider,
x => x.Age >= 30,
x => new { x.Name, x.Age }
)
.Statement;
/*
select
[名前] as [Name],
[Age] as [Age]
from [dbo].[T_Person]
where
[Age] >= @p1
*///--- If wants OrderBy / ThenBy, allows you to write like following
using (var builder = new QueryBuilder<Person>(DbProvider.SqlServer))
{
builder.Select(x => new { x.Id, x.Name });
builder.Where(x => x.Name == "xin9le");
builder.OrderByDescending(x => x.Name);
builder.ThenBy(x => x.CreatedOn);
var sql = builder.Build().Statement;
}
/*
select
[Id] as [Id],
[名前] as [Name]
from [dbo].[T_Person]
where
[名前] = @p1
order by
[名前] desc,
[CreatedOn]
*///--- Insert record to SQL Server
var sql = QueryBuilder.Insert<Person>(DbProvider.SqlServer).Statement;
/*
insert into [dbo].[T_Person]
(
[Email],
[名前],
[Age],
[CreatedOn],
[UpdatedOn]
)
values
(
@Email,
@Name,
@Age,
SYSDATETIME(),
SYSDATETIME()
)
*///--- Update records with specified columns that matched specified condition
var sql
= QueryBuilder.Update<Person>
(
DbProvider.SqlServer,
x => x.Age < 35 || x.Name == "xin9le",
x => new { x.Name, x.Age }
)
.Statement;
/*
update [dbo].[T_Person]
set
[名前] = @Name,
[Age] = @Age,
[UpdatedOn] = SYSDATETIME()
where
[Age] < @p1 or [名前] = @p2
*/QueryBuilder class also provides some other overload functions and Count / Delete / Truncate methods, and so on.
This library automates typical CRUD operations completely using above sql generation feature and Dapper. By using expression tree, you can specify target column and filter records. Provided method names are directly described the CRUD operations, so you can understand and use them easily.
//--- Query all records
var p1 = connection.Select<Person>();
//--- Query all records with specified columns
var p2 = connection.Select<Person>(x => new { x.Id, x.Name });
//--- Query 'ID = 3' records only
var p3 = connection.Select<Person>(x => x.Id == 3);
//--- Query 'ID = 3' records with specified columns
var p4 = connection.Select<Person>
(
x => x.Id == 3,
x => new { x.Id, x.Name }
);//--- Insert specified data
var p5 = connection.Insert(new Person { Name = "xin9le", Age = 30 });
//--- Insert collection
var p6 = connection.InsertMulti(new []
{
new Person { Name = "yoshiki", Age= 49, },
new Person { Name = "suzuki", Age= 30, },
new Person { Name = "anders", Age= 54, },
});
//--- Super easy bulk insert
var p7 = connection.BulkInsert(new []
{
new Person { Id = 1, Name = "yoshiki", Age= 49, },
new Person { Id = 2, Name = "suzuki", Age= 30, },
new Person { Id = 3, Name = "anders", Age= 54, },
});
//--- Insert and get generated auto incremented id
var p8 = connection.InsertAndGetId(new Person { Name = "xin9le", Age = 30 });//--- Update records which is matched specified condition
var p9 = connection.Update
(
new Person { Name = "test", Age = 23 },
x => x.Age == 30
);//--- Delete all records
var p10 = connection.Delete<Person>();
//--- Delete records which is matched specified condition
var p11 = connection.Delete<Person>(x => x.Age != 30);//--- Truncate table
var p12 = connection.Truncate<Person>();//--- Count all records
var p13 = connection.Count<Person>();
//--- Count records which is matched specified condition
var p14 = connection.Count<Person>(x => x.Name == "xin9le");These CRUD methods are provided not only synchronous but also asynchronous.
BulkInsert / InsertAndGetId (etc.) methods are environment specific feature. If you want to use them, please install following package and call follows once.
dotnet add package DeclarativeSql.MicrosoftSqlClient
dotnet add package DeclarativeSql.SystemSqlClient
MicrosoftSqlClientInitializer.Initialize();
SystemSqlClientInitializer.Initialize();If you want to create a highly available database configuration, you can use HighAvailabilityConnection. This provides the simple
master/slave pattern. High availability can be achieved simply by writing to the master database and reading from the slave database.
public class FooConnection : HighAvailabilityConnection
{
public FooConnection()
: base("ConnectionString-ToMasterServer", "ConnectionString-ToSlaveServer")
{}
protected override IDbConnection CreateConnection(string connectionString, AvailabilityTarget target)
=> new SqlConnection(connectionString);
}using (var connection = new FooConnection())
{
//--- Read from slave database
var p = connection.Slave.Select<Person>();
//--- Write to master database
connection.Master.Insert(new Person { Name = "xin9le" });
}Of course, by using the same connection string for the master database and for the slave database, a single database environment can be also supported.
Getting started from downloading NuGet package.
dotnet add package DeclarativeSql
This library is provided under MIT License.
Takaaki Suzuki (a.k.a @xin9le) is software developer in Japan who awarded Microsoft MVP for Developer Technologies (C#) since July 2012.
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent