[創意料理] 單純對資料表簡單的 CRUD 讓 Chef.Extensions.DbAccess.SqlServer 來協助我們

Chef.Extensions.DbAccess.SqlServer 這個套件是小弟本人開發的,在 ORM 工具盛行的現在,後端工程師能有一套像是 Entity Framework 的資料庫存取框架來協助開發工作,是件很幸福的事情,但是無奈因為環境的關係、政策的關係、仇恨的關係、...blah blah 的,無法盡如人意,在這種情況之下只好自立自強,於是這個套件就誕生了。

Chef.Extensions.DbAccess.SqlServer 只有簡單陽春的 CRUD,使用上也有一些限制,是無法跟 Entity Framework 相提並論的,等級差太多,如果能使用 Entity Framework 的朋友就繼續使用 Entity Framework 就好了,文章看到這邊可以左轉出去了,但是如果還是跟我一樣需要操著 SQL 跟資料庫奮戰的朋友,說不定這個套件會對各位有點兒幫助。

適合的使用情境是這樣的,很多時候我們需要開發一些對單一資料表做簡單的 CRUD 存取,在分層的機制下,會多出不少簡短又相似的程式碼,我們會想說能不能將這些程式碼進一步做抽象化,讓它們更彈性一點,而這個套件的開發初衷就是希望能解決這方面的問題。

先說明一下我這次使用的範例,我有一個資料表叫 tbl_member,其 Schema 如下圖:

與之對應的類別如下:

[Table("tbl_member")]
public class Member
{
    [Column("No")]
    public int Id { get; set; }

    [StringLength(50)]
    public string Name { get; set; }

    [Column(TypeName = "varchar"), StringLength(20)]
    public string Phone { get; set; }

    public int Age { get; set; }
}

如果資料表名稱跟類別名稱不一樣,可以用 TableAttribute 來標記,而欄位名稱跟屬性名稱不一樣,則是用 ColumnAttribute 來標記,另外,這個套件相依於 Dapper,所以有針對參數型別的語法做調整,如果欄位的資料型態是 varchar 的話,建議特別標記 Column 的 TypeName,而 StringLengthAttribute 則是選擇性的標記。

起手式

首先,建議以資料表對應的類別名稱建立一個 XXXDataAccess,並繼承 SqlServerDataAccess<T>,以 Member 這個類別為例,程式碼如下:

public class MemberDataAccess : SqlServerDataAccess<Member>
{
    public MemberDataAccess()
        : base(@"Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=Test;Integrated Security=True")
    {
    }
}

這個 SqlServerDataAccess<T> 是另一個套件 Chef.Extensions.DbAccessIDataAccess<T> 介面的實作,如果覺得不適用,可以自行實作 IDataAccess<T> 撰寫自己想要的通用性資料表存取類別。

套件中所有的非同步方法都會再搭配一個同步方法,文章中不另外列舉,範例程式也會以非同步方法為主。

C - Create

關於新增資料的方法有 4 個如下:

public interface IDataAccess<T>
{
    ...
    Task<int> InsertAsync(T value);
    Task<int> InsertAsync(IEnumerable<T> values);
    Task<int> InsertAsync(Expression<Func<T>> setter);
    Task<int> InsertAsync(Expression<Func<T>> setter, IEnumerable<T> values);
    ...
}

其中如果要使用 InsertAsync(T value)InsertAsync(IEnumerable<T> values) 這兩個方法的話,需要再覆寫 RequiredColumns 這個唯讀屬性,指定預設的必填欄位。

使用上建議優先選擇 Chef.Extensions.DbAccess.Fluent.Extention 提供的擴充方法,範例程式碼如下,說明寫在註解中。

private static async Task DemoInsert()
{
    var member = new Member { Id = 99, Name = "Kevin", Phone = "0000-000000", Age = 88 };

    var members = new List<Member>
                  {
                      new Member { Id = 98, Name = "Bob", Phone = "0000-000001", Age = 38 },
                      new Member { Id = 97, Name = "Tom", Phone = "0000-000002", Age = 85 }
                  };

    IDataAccess<Member> memberDataAccess = new MemberDataAccess();

    // INSERT 一筆 Member,僅預設的必填欄位。
    await memberDataAccess.InsertAsync(member);

    // INSERT 多筆 Member,僅預設的必填欄位。
    await memberDataAccess.InsertAsync(members);

    // INSERT 一筆 Member,使用 Fluent 語法,僅 Id, Name, Phone 欄位。
    await memberDataAccess
        .Set(() => new Member { Id = 100, Name = "Pass", Phone = "0978-878787" })
        .InsertAsync();

    // INSERT 多筆 Member,使用 Fluent 語法,僅 Id, Name, Age 欄位。
    await memberDataAccess
        .Set(() => new Member { Id = default(int), Name = default(string), Age = default(int) })
        .InsertAsync(
            new List<Member>
            {
                new Member { Id = 11, Name = "Joe", Phone = "0000-000003", Age = 23 },
                new Member { Id = 22, Name = "Steve", Phone = "0000-000004", Age = 45 }
            });
}
運算子右邊的運算式僅支援常數變數屬性,除此之外的運算式是不支援的,這點需要特別注意。

R - Retrieve

關於查詢資料的方法有 3 個如下:

public interface IDataAccess<T>
{
    ...
    Task<T> QueryOneAsync(Expression<Func<T, bool>> predicate, IEnumerable<(Expression<Func<T, object>>, Sortord)> orderings = null, Expression<Func<T, object>> selector = null, int? top = null);
    Task<List<T>> QueryAsync(Expression<Func<T, bool>> predicate, IEnumerable<(Expression<Func<T, object>>, Sortord)> orderings = null, Expression<Func<T, object>> selector = null, int? top = null);
    Task<int> CountAsync(Expression<Func<T, bool>> predicate)
    ...
}

查詢語法目前除了必要的 SELECT ... FROM ... WHERE 之外,還支援 TOPORDER BY,而可以使用的比較運算子為 =<>><>=<=LIKEINIS NULLIS NOT NULL,另外這邊不鼓勵 SELECT * 語法,所以如果不指定回傳欄位的話,必須覆寫 DefaultSelector 這個唯讀屬性。

範例程式碼如下,說明寫在註解中。

private static async Task DemoQuery()
{
    IDataAccess<Member> memberDataAccess = new MemberDataAccess();

    // SELECT 一筆 Id = 1 的 Member,並傳回預設選取的欄位。
    var member = await memberDataAccess
                     .Where(x => x.Id == 1)
                     .QueryOneAsync();

    // SELECT 多筆 Id IN (1, 2, 3) 的 Member,並傳回預設選取的欄位。
    var members = await memberDataAccess
                      .Where(x => new[] { 1, 2, 3 }.Contains(x.Id))
                      .QueryAsync();

    // SELECT TOP 10 筆 Age >= 30 AND Name LIKE 'J%' ORDER BY Age 的 Member,並傳回預設選取的欄位。
    members = await memberDataAccess
                  .Where(x => x.Age >= 30 && x.Name.StartsWith("J"))
                  .OrderBy(x => x.Age)
                  .Top(10)
                  .QueryAsync();

    // SELECT 多筆 Age = 18 AND Name LIKE '%y' 的 Member,並傳回 Name, Age 欄位。
    members = await memberDataAccess
                  .Where(x => x.Age == 18 && x.Name.EndsWith("y"))
                  .Select(x => new { x.Name, x.Age })
                  .QueryAsync();

    // SELECT 多筆 Name LIKE '%e%' ORDER BY Id, Name DESC 的 Member,並傳回預設選取的欄位。
    members = await memberDataAccess
                  .Where(x => x.Name.Contains("e"))
                  .OrderBy(x => x.Id)
                  .ThenByDescending(x => x.Name)
                  .QueryAsync();

    // 計算 Age = 20 的 Member 數量
    memberCount = await memberDataAccess
                      .Where(x => x.Age == 20)
                      .CountAsync();
}

U - Update

關於更新資料的方法有 2 個如下:

public interface IDataAccess<T>
{
    ...
    Task<int> UpdateAsync(Expression<Func<T, bool>> predicate, Expression<Func<T>> setter);
    Task<int> UpdateAsync(Expression<Func<T, bool>> predicateTemplate, Expression<Func<T>> setterTemplate, IEnumerable<T> values);
    ...
}

範例程式碼如下,說明寫在註解中。

private static async Task DemoUpdate()
{
    IDataAccess<Member> memberDataAccess = new MemberDataAccess();

    // UPDATE 一筆 Id = 1 的 Member Phone 欄位
    await memberDataAccess
        .Set(() => new Member { Phone = "02-87871234" })
        .Where(x => x.Id == 1)
        .UpdateAsync();

    // UPDATE 多筆 Member 的 Age, Phone 欄位
    await memberDataAccess
        .Set(() => new Member { Age = default(int), Phone = default(string) })
        .Where(x => x.Id == default(int))
        .UpdateAsync(new List<Member>
                     {
                         new Member { Id = 44, Phone = "0000-000006", Age = 63 },
                         new Member { Id = 55, Phone = "0000-000007", Age = 75 }
                     });
}

U - Upsert

Upsert 是 UpdateOrInsert 的縮寫,顧名思義,它們作用就如同它的名稱,方法有 2 個如下:

public interface IDataAccess<T>
{
    ...
    Task<int> UpsertAsync(Expression<Func<T, bool>> predicate, Expression<Func<T>> setter);
    Task<int> UpsertAsync(Expression<Func<T, bool>> predicateTemplate, Expression<Func<T>> setterTemplate, IEnumerable<T> values);
    ...
}

範例程式碼如下,說明寫在註解中。

private static async Task DemoUpsert()
{
    IDataAccess<Member> memberDataAccess = new MemberDataAccess();

    // UPDATE or INSERT 一筆 Id = 1 的 Member,包含 Name, Phone 欄位
    await memberDataAccess
        .Set(() => new Member { Name = "Black", Phone = "02-56567788" })
        .Where(x => x.Id == 1)
        .UpsertAsync();

    // UPDATE or INSERT 多筆 Member,包含 Age, Phone 欄位
    await memberDataAccess
        .Set(() => new Member { Name = default(string), Age = default(int), Phone = default(string) })
        .Where(x => x.Id == default(int))
        .UpdateAsync(new List<Member>
                     {
                         new Member { Id = 77, Phone = "0000-000066", Age = 36 },
                         new Member { Id = 88, Phone = "0000-000077", Age = 57 }
                     });
}

D - Delete

關於刪除資料的方法目前只有 1 個如下:

public interface IDataAccess<T>
{
    ...
    Task<int> DeleteAsync(Expression<Func<T, bool>> predicate);
    ...
}

範例程式碼如下,說明寫在註解中。

private static async Task DemoDelete()
{
    IDataAccess<Member> memberDataAccess = new MemberDataAccess();

    // DELETE Name LIKE '%A%' 的 Member
    await memberDataAccess
        .Where(x => x.Name.Contains("A"))
        .DeleteAsync();
}

Bulk 系列

Bulk 系列是使用 Table-Valued Parameter 來向資料庫傳遞參數的,所以這邊需要預先建立 User Defined Table Type,並且覆寫 ConvertToTableValuedParameters() 方法。

Bulk 系列操作資料的方法有 3 個如下

public interface IDataAccess<T>
{
    ...
    Task<int> BulkInsertAsync(IEnumerable<T> values);
    Task<int> BulkInsertAsync(Expression<Func<T>> setterTemplate, IEnumerable<T> values);
    Task<int> BulkUpdateAsync(Expression<Func<T, bool>> predicateTemplate, Expression<Func<T>> setterTemplate, IEnumerable<T> values);
    Task<int> BulkUpsertAsync(Expression<Func<T, bool>> predicateTemplate, Expression<Func<T>> setterTemplate, IEnumerable<T> values);
    ...
}

範例程式碼如下,說明寫在註解中。

private static async Task DemoBulk()
{
    IDataAccess<Member> memberDataAccess = new MemberDataAccess();

    // 批次 INSERT,只有預設的必填欄位。
    await memberDataAccess.BulkInsertAsync(
        new List<Member>
        {
            new Member { Id = 345, Name = "Logi" },
            new Member { Id = 678, Name = "Alucky" }
        });

    // 批次 INSERT,新增 Id, Name, Phone, Age 欄位。
    await memberDataAccess
        .Set(() => new Member
                   {
                       Id = default(int),
                       Name = default(string),
                       Phone = default(string),
                       Age = default(int)
                   })
        .BulkInsertAsync(
            new List<Member>
            {
                new Member { Id = 324, Name = "Cruzer", Phone = "0000-000123", Age = 12 },
                new Member { Id = 537, Name = "Slice", Phone = "0000-000456", Age = 21 }
            });

    // 批次 UPDATE Name, Phone 欄位
    await memberDataAccess
        .Where(x => x.Id == default(int))
        .Set(() => new Member
                   {
                       Name = default(string),
                       Phone = default(string),
                   })
        .BulkUpdateAsync(
            new List<Member>
            {
                new Member { Id = 324, Name = "Lawson", Phone = "0000-000523" },
                new Member { Id = 537, Name = "Marlon", Phone = "0000-000756" }
            });

    // 批次 UPDATE or INSERT Name, Phone 欄位
    await memberDataAccess
        .Where(x => x.Id == default(int))
        .Set(() => new Member
                   {
                       Name = default(string),
                       Phone = default(string),
                   })
        .BulkUpsertAsync(
            new List<Member>
            {
                new Member { Id = 324, Name = "Ramone", Phone = "0000-000623" },
                new Member { Id = 657, Name = "Chris", Phone = "0000-000835" }
            });
}

以上,針對 Chef.Extensions.DbAccess.SqlServer 這個套件的使用方式做基本的介紹,分享給大家,希望能有一丁點兒的幫助。

C# 指南 ASP.NET 教學 ASP.NET MVC 指引
Azure SQL Database 教學 SQL Server 教學 Xamarin.Forms 教學