標籤:資料庫 key 配置 net 查詢 orm架構 操作 刪除 最大值
1、說明
本項目是一個使用.NET Standard 2.0開發的,基於 Dapper 的輕量級 ORM 架構,包含基本的CRUD以及根據運算式進行一些操作的方法,目前只針對單表,不包含多表串連操作。
github:https://github.com/iamoldli/NetSql
2、使用方法2.2、安裝
Install-Package NetSql
2.2、建立實體
建立Article
實體類,繼承EntityBase
public class Article : EntityBase{ [Column("Title")] public string Title1 { get; set; } public string Summary { get; set; } public string Body { get; set; } public Category Category { get; set; } public int ReadCount { get; set; } public bool IsDeleted { get; set; } public DateTime CreatedTime { get; set; }}public enum Category{ Blog, Movie}
EntityBase
是一個定義好的實體基類,包含一個泛型主鍵標識,預設是 Int 類型的,也可以指定 long 或者 string 類型
public class Article : EntityBase<string>
2.3、定義資料庫上下文(DbContext)
資料庫上下文我是模仿的 EF,IDbContextOptions
是資料庫上下文配置項介面,預設包含了 SqlServer 的實現DbContextOptions
,如果使用的是 MySql 或者 SQLite,需要額外安裝對應的擴充包
Install-Package NetSql.MySql //MySql
Install-Package NetSql.SQLite //SQLite
這裡我定義了一個BlogDbContext
上下文,其中包含一個Articles
資料集
public class BlogDbContext : DbContext{ public BlogDbContext(IDbContextOptions options) : base(options) { } public IDbSet<Article> Articles { get; set; }}
2.4、資料集(DbSet)使用說明2.4.1、建立資料庫上下文執行個體
private readonly BlogDbContext _dbContext;private readonly IDbSet<Article> _dbSet;public DbSetTests(){ _dbContext = new BlogDbContext(new SQLiteDbContextOptions("Filename=./Database/Test.db")); _dbSet = _dbContext.Set<Article>(); //預熱 _dbSet.Find().First();}
2.4.2、插入
[Fact]public async void InsertTest(){ var article = new Article { Title1 = "test", Category = Category.Blog, Summary = "這是一篇測試文章", Body = "這是一篇測試文章這是一篇測試文章這是一篇測試文章這是一篇測試文章這是一篇測試文章這是一篇測試文章這是一篇測試文章這是一篇測試文章", ReadCount = 10, IsDeleted = true, CreatedTime = DateTime.Now }; await _dbSet.InsertAsync(article); Assert.True(article.Id > 0);}
2.4.3、批量插入
[Fact]public void BatchInsertTest(){ var sw = new Stopwatch(); sw.Start(); var tran = _dbContext.BeginTransaction(); for (var i = 0; i < 10000; i++) { var article = new Article { Title1 = "test" + i, Category = i % 3 == 1 ? Category.Blog : Category.Movie, Summary = "這是一篇測試文章", Body = "這是一篇測試文章這是一篇測試文章這是一篇測試文章這是一篇測試文章這是一篇測試文章這是一篇測試文章這是一篇測試文章這是一篇測試文章", ReadCount = 10, IsDeleted = i % 2 == 0, CreatedTime = DateTime.Now }; _dbSet.InsertAsync(article, tran); } tran.Commit(); sw.Stop(); var s = sw.ElapsedMilliseconds; Assert.True(s > 0);}
2.4.4、根據主鍵刪除
[Fact]public void DeleteTest(){ var b = _dbSet.DeleteAsync(3).Result; Assert.True(b);}
2.4.5、根據運算式刪除
[Fact]public async void DeleteWhereTest(){ var b = await _dbSet.Find(m => m.Id > 10).Delete(); Assert.True(b);}[Fact]public async void DeleteWhereTest(){ var b = await _dbSet.Find(m => m.Id > 10) .Where(m => m.CreatedTime > DateTime.Now).Delete(); Assert.True(b);}
2.4.6、修改
[Fact]public async void UpdateTest(){ var article = await _dbSet.Find().First(); article.Title1 = "修改測試"; var b = await _dbSet.UpdateAsync(article); Assert.True(b);}
2.4.7、根據運算式修改實體部分屬性
[Fact]public async void UpdateWhereTest(){ var b = await _dbSet.Find(m => m.Id == 1000).Update(m => new Article { Title1 = "hahahaah", ReadCount = 1000 }); Assert.True(b);}
2.4.8、根據主鍵查詢單個實體
[Fact]public void GetTest(){ var article = _dbSet.GetAsync(100).Result; Assert.NotNull(article);}
2.4.9、根據運算式查詢單條資料
該方法返回結果集中的第一條資料
[Fact]public async void GetWehreTest(){ var article = await _dbSet.Find(m => m.Id > 100).First(); Assert.NotNull(article);}
2.4.10、使用運算式
IDbSet
的Find
方法會返回一個INetSqlQueryable
對象,這個對象是模仿的 EF 裡面的IQueryable
,雖然有些不倫不類,但是是按照適合自己的方式設計的。
INetSqlQueryable
目前包含以下方法:
var query = _dbSet.Find().Where(m => m.Id > 1);
var query = _dbSet.Find().WhereIf(id > 1, m => m.Id > 200);
var query = _dbSet.Find(m => m.Id > 200 && m.Id < 1000).OrderBy(m => m.Id, SortType.Desc);
Limit
:該方法包含兩個參數skip
和take
,標識跳過 skip 條資料,取 take 條資料
var query = _dbSet.Find(m => m.Id > 100 && m.Id < 120).Limit(5, 10);
var query = _dbSet.Find().Select(m => new { m.Id, m.Title1 }).Limit(0, 10);
以上方法都是用於構造INetSqlQueryable
的,下面的方法則是執行:
var maxReadCount = _dbSet.Find().Max(m => m.ReadCount).Result;
var maxReadCount = _dbSet.Find().Min(m => m.ReadCount).Result;
var count = _dbSet.Find(m => m.Id > 1000).Count().Result;
var b = _dbSet.Find(m => m.Id > 1000).Exists().Result;
var article = _dbSet.Find(m => m.Id > 100 && m.Id < 120).First().Result;
var b = _dbSet.Find(m => m.Id > 1000).Delete().Result;
var b = await _dbSet.Find(m => m.Id == 1000).Update(m => new Article{ Title1 = "hahahaah", ReadCount = 1000});
var list = await _dbSet.Find(m => m.Id > 100 && m.Id < 120).ToList();
3、特性表別名以及列名
[Table("blog_article")]public class Article : EntityBase{ [Column("Title")] public string Title1 { get; set; } public string Summary { get; set; } public string Body { get; set; } public Category Category { get; set; } public int ReadCount { get; set; } public bool IsDeleted { get; set; } public DateTime CreatedTime { get; set; }}
指定主鍵
可以通過KeyAttribute
來指定某個欄位為主鍵
4、泛型倉儲(Repository)
平時開發時用到偽 DDD 比較多,所以架構提供了一個泛型倉儲介面IRepository
以及一個抽象實現RepositoryAbstract
/// <summary>/// 判斷是否存在/// </summary>/// <param name="where"></param>/// <param name="transaction"></param>/// <returns></returns>Task<bool> ExistsAsync(Expression<Func<TEntity, bool>> where, IDbTransaction transaction = null);/// <summary>/// 新增/// </summary>/// <param name="entity">實體</param>/// <param name="transaction">事務</param>/// <returns></returns>Task<bool> AddAsync(TEntity entity, IDbTransaction transaction = null);/// <summary>/// 批量新增/// </summary>/// <param name="list"></param>/// <param name="transaction"></param>/// <returns></returns>Task<bool> AddAsync(List<TEntity> list, IDbTransaction transaction = null);/// <summary>/// 刪除/// </summary>/// <param name="id"></param>/// <param name="transaction"></param>/// <returns></returns>Task<bool> DeleteAsync(dynamic id, IDbTransaction transaction = null);/// <summary>/// 更新/// </summary>/// <param name="entity">實體</param>/// <param name="transaction">事務</param>/// <returns></returns>Task<bool> UpdateAsync(TEntity entity, IDbTransaction transaction = null);/// <summary>/// 根據主鍵查詢/// </summary>/// <param name="id"></param>/// <param name="transaction"></param>/// <returns></returns>Task<TEntity> GetAsync(dynamic id, IDbTransaction transaction = null);/// <summary>/// 根據運算式查詢單條記錄/// </summary>/// <param name="where"></param>/// <param name="transaction"></param>/// <returns></returns>Task<TEntity> GetAsync(Expression<Func<TEntity,bool>> where, IDbTransaction transaction = null);/// <summary>/// 分頁查詢/// </summary>/// <param name="paging">分頁</param>/// <param name="where">過濾條件</param>/// <param name="transaction">事務</param>/// <returns></returns>Task<List<TEntity>> PaginationAsync(Paging paging = null, Expression<Func<TEntity, bool>> where = null, IDbTransaction transaction = null);
RepositoryAbstract
中包含實體對應的資料集IDbSet
以及資料上限為IDbContext
protected readonly IDbSet<TEntity> Db;protected readonly IDbContext DbContext;protected RepositoryAbstract(IDbContext dbContext){ DbContext = dbContext; Db = dbContext.Set<TEntity>();}
對於事務,建議使用工作單元IUnitOfWork
public interface IUnitOfWork{ /// <summary> /// 開啟一個事務 /// </summary> /// <returns></returns> IDbTransaction BeginTransaction(); /// <summary> /// 提交 /// </summary> /// <returns></returns> void Commit(); /// <summary> /// 復原 /// </summary> void Rollback();}
項目已經包含了一個實現UnitOfWork
6、倉儲使用方法6.1、定義倉儲
public interface IArticleRepository : IRepository<Article>{}
6.2、建立倉儲執行個體
private readonly IArticleRepository _repository;public RepositoryTest(){ var dbContext = new BlogDbContext(new SQLiteDbContextOptions("Filename=./Database/Test.db")); _repository = new ArticleRepository(dbContext);}
6.3、新增
[Fact]public async void AddTest(){ var article = new Article { Title1 = "test", Category = Category.Blog, Summary = "這是一篇測試文章", Body = "這是一篇測試文章這是一篇測試文章這是一篇測試文章這是一篇測試文章這是一篇測試文章這是一篇測試文章這是一篇測試文章這是一篇測試文章", ReadCount = 10, IsDeleted = true, CreatedTime = DateTime.Now }; await _repository.AddAsync(article); Assert.True(article.Id > 0);}
6.4、批量增加
[Fact]public void BatchInsertTest(){ var list = new List<Article>(); for (var i = 0; i < 10000; i++) { var article = new Article { Title1 = "test" + i, Category = i % 3 == 1 ? Category.Blog : Category.Movie, Summary = "這是一篇測試文章", Body = "這是一篇測試文章這是一篇測試文章這是一篇測試文章這是一篇測試文章這是一篇測試文章這是一篇測試文章這是一篇測試文章這是一篇測試文章", ReadCount = 10, IsDeleted = i % 2 == 0, CreatedTime = DateTime.Now }; list.Add(article); } var sw = new Stopwatch(); sw.Start(); _repository.AddAsync(list); sw.Stop(); var s = sw.ElapsedMilliseconds; Assert.True(s > 0);}
6.5、刪除
[Fact]public async void DeleteTest(){ var b = await _repository.DeleteAsync(2); Assert.True(b);}
6.6、修改
[Fact]public async void UpdateTest(){ var article = await _repository.GetAsync(2); article.Title1 = "修改測試"; var b = await _repository.UpdateAsync(article); Assert.True(b);}
6.7、分頁查詢
[Fact]public async void PaginationTest(){ var paging = new Paging(1, 20); var list = await _repository.PaginationAsync(paging, m => m.Id > 1000); Assert.True(paging.TotalCount > 0);}
未完待續~
分享自己寫的基於Dapper的輕量級ORM架構~