Skip to content

RsCode中对数据库的操作,使用FreeSql,支持国产,并使用它做了一些封装,包括:多数据库管理,分页查询,UnitOfWork扩展等

配置数据库

1.1 appsettings.json中,配置数据库连接

json
{
  //数据库连接 
  "ConnectionStrings": {
    "DefaultConnection": "Server=127.0.0.1;Uid=root;Pwd=123456;Database=test;Port=3306;",
    "DefaultConnection2": "Data Source=rscode.db;"
  } 
}

1.2 添加数据库服务

csharp
builder.Services.AddDatabase(FreeSql.DataType.MySql, "DefaultConnection");
builder.Services.AddDatabase(FreeSql.DataType.Sqlite, "DefaultConnection2");

1.3 添加数据库提供程序

需要访问什么数据库,就安装对应的 FreeSql.Provider.XX,也可直接安装 FreeSql.All

Package Name说明
FreeSql.Provider.MySql基于 MySql.Data(Oracle 官方)
FreeSql.Provider.MySqlConnector基于 MySqlConnector(开源社区,推荐++) MySQL, MariaDB, Percona, Amazon Aurora, Azure Database for MySQL, Google Cloud SQL for MySQL, OceanBase, Doris, Tidb 等等
FreeSql.Provider.PostgreSQL基于 PostgreSQL 9.5+
FreeSql.Provider.SqlServer基于 SqlServer 2005+
FreeSql.Provider.SqlServerForSystem基于 System.Data.SqlClient + SqlServer 2005+
FreeSql.Provider.Sqlite基于 System.Data.SQLite.Core
FreeSql.Provider.SqliteCore基于 Microsoft.Data.Sqlite.Core,需安装 bundle_xxx
FreeSql.Provider.Duckdb基于 DuckDB.NET.Data.Full
FreeSql.Provider.ClickHouse基于 ClickHouse.Client
FreeSql.Provider.QuestDb基于 Npgsql 和 RestApi
FreeSql.Provider.Oracle
FreeSql.Provider.OracleOledb基于 Oledb 解决 US7ASCII 中文乱码问题
FreeSql.Provider.Firebird
FreeSql.Provider.MsAccess
FreeSql.Provider.Dameng基于 达梦数据库
FreeSql.Provider.ShenTong基于 神舟通用数据库
FreeSql.Provider.KingbaseES基于 人大金仓数据库
FreeSql.Provider.GBase基于 南大通用GBase数据库
FreeSql.Provider.Xugu基于 虚谷数据库
FreeSql.Provider.Odbc基于 ODBC
FreeSql.Provider.Custom自定义适配 SqlServer2000, PolarDB, KunDB, 其它国产数据库 等等

操作数据库

2.1使用IApplicationDbContext访问数据

在构造中注入IApplicationDbContextIApplicationDbContext.Current代表当前使用的数据库,默认值是DefaultConnectionIApplicationDbContext.ChangeDatabase("dbConnStr")方法可以更换当前使用的数据库

实例代码:

csharp
//注入IApplicationDbContext实例
IApplicationDbContext dbContext;
//略

//当前数据库连接
dbContext.Current;
//更换数据库
dbContext.ChangeDatabase(connName="DefaultConnection");
//create unitofwork
dbContext.CreateUnitOfWork();
//获取Repository
dbContext.GetRepository<TEntity>();
dbContext.GetRepository<TEntity,TKey>();

2.2 实体特性

csharp
[Table(Name ="rswl_user_info")]
public class UserModel
{
    [Column(IsPrimary =true)]
    //[Column(IsIdentity =true)]//自增长的主键
    public string UserId { get; set; }   
    public string UserName { get; set; }
    
    //导航属性  用户相关文章
     [Navigate(nameof(User.ArticleId))]
    public List<Article> Articles { get; set; }
    
    [Column(IsIgnore=true)]  //是否不映射
    [Column(IsNullable = false)] //手动设置是否为空
    public string age{get;set;}
    
    //乐观锁的原理,是利用实体某字段,如:long version,更新前先查询数据,此时 version 为 1,更新时产生的 SQL 会附加 where version = 1,当修改失败时(即 Affrows == 0)抛出异常(DbUpdateVersionException)。
//每个实体只支持一个乐观锁属性,支持 int/long/string/Guid
//适用 SetSource 更新数据,无论使用什么方法更新 version 的值都会增加 1
     [Column(IsVersion = true)]
    public int Version { get; set; }
}

2.3 常用sql操作

csharp
public class Demo
{
    IFreeSql db;
    public void Demo(IApplicationDbContext dbContext)
    {
        db=dbContext.Current;
    }
    
    public async Task Test()
    {
        await db.Select<UserModel>().FirstAsync();
         //OneToOne、ManyToOne
    		db.Select<Tag>().Where(a => a.Parent.Parent.Name == "English").ToList();

        //OneToMany
        db.Select<Tag>().IncludeMany(a => a.Tags, then => then.Where(sub => sub.Name == "foo")).ToList();
         //ManyToMany
        db.Select<Song>()
            .IncludeMany(a => a.Tags, then => then.Where(sub => sub.Name == "foo"))
            .Where(s => s.Tags.Any(t => t.Name == "Chinese"))
            .ToList();

        //Other
        db.Select<YourType>()
            .Where(a => a.IsDelete == 0)
            .WhereIf(keyword != null, a => a.UserName.Contains(keyword))
            .WhereIf(role_id > 0, a => a.RoleId == role_id)
            .Where(a => a.Nodes.Any(t => t.Parent.Id == t.UserId))
            .Count(out var total)
            .Page(page, size)
            .OrderByDescending(a => a.Id)
            .ToList();
    }
     
}
csharp
//OneToOne、ManyToOne
db.Select<Tag>().Where(a => a.Parent.Parent.Name == "English").ToList();

//OneToMany
db.Select<Tag>().IncludeMany(a => a.Tags, then => then.Where(sub => sub.Name == "foo")).ToList();

//ManyToMany
db.Select<Song>()
  .IncludeMany(a => a.Tags, then => then.Where(sub => sub.Name == "foo"))
  .Where(s => s.Tags.Any(t => t.Name == "Chinese"))
  .ToList();

//Other
db.Select<YourType>()
  .Where(a => a.IsDelete == 0)
  .WhereIf(keyword != null, a => a.UserName.Contains(keyword))
  .WhereIf(role_id > 0, a => a.RoleId == role_id)
  .Where(a => a.Nodes.Any(t => t.Parent.Id == t.UserId))
  .Count(out var total)
  .Page(page, size)
  .OrderByDescending(a => a.Id)
  .ToList();

未设置导航属性关系的,临时指定关系(只支持一对多):

csharp
db.Select<Goods>().IncludeMany(a => a.Comment.Where(b => b.TagId == a.Id));

只查询每项子集合的前几条数据,避免像 EfCore 加载所有数据导致 IO 性能低下(比如某商品下有 2000 条评论):

csharp
db.Select<Goods>().IncludeMany(a => a.Comment.Take(10));

更多操作查看freesql文档

2.4仓储模式

RsCode自定义封装了一些数据库常用操作,使用FreeSql做为orm

开启UnitOfWork服务

csharp
//必要步骤:添加动态代理
builder.Host.UseServiceProviderFactory(new DynamicProxyServiceProviderFactory());

builder.Services.AddDatabase(FreeSql.DataType.MySql, "DefaultConnection");
builder.Services.AddUnitOfWork();

定义UserInfo实体类

csharp
[TableName("rswl_user_info")]
public partial class UserModel
{
    [Column(Identity=true)]
    public long Id { get; set; }
    public string UserName { get; set; } 
}

实现Repository

csharp
public interface IUserRepository
{
}

public class UserRepository:IUserRepository 
{      
    IRepository<UserModel> repository;
    public UserRepository(IRepository<UserModel> repository)
    {
        this.repository=repository;
    }
    []
    public async Task<UserModel> CreateAndGetUserByRepository(string name)
     {
         repository.Insert(new UserModel()
         {
              UserId=Guid.NewGuid().ToString("N"),
              UserName=name
         });
         return await  repository.Select.Where(x => x.UserName == name).FirstAsync();
     }
    
    public async Task<PageData<UserModel>> PageInfoAsync(int page,int pageSize)
    {
        //分页查询
    		ISelect<UserModel> select=repository.Page(1,10).Where(x=>x.UserId=="xxx");
        return await repository.PageAsync(page,pageSize,select);
    }
}

//或者
public interface IUserRepository:IRepository<UserModel>
{}
public class UserRepository:Repository<UserModel>,IUserRepository
{
    public UserRepository(IApplicationDbContext applicationDbContext):base(applicationDbContext){}
    //其它资源可通过属性注入
    [FromServiceContext]
    public IRepository<UserExtInfo> userExtRepository { get; set; }
}

参考文档:

Freesql文档
Freesql官方文档