[創意料理] 單純對資料表簡單的 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_membertbl_department,其結構如下圖:

與之對應的類別如下:

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

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

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

    public int Age { get; set; }
    
    public int DepartmentId { get; set; }

    public Department Department { get; set; }

    public int ManagerId { get; set; }

    public Member Manager { get; set; }
    
    [NotMapped]
    public string Secret { get; set; }
}

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

    public string Name { get; set; }

    public int Age { get; set; }

    public decimal AverageAge { get; set; }
}

[Table("tbl_department")]
public class Department
{
    public int Id { get; set; }

    public string Name { get; set; }
}

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

適用版本

  • Chef.Extensions.DbAccess.SqlServer v2.3.8+

起手式

首先,類別建好後要為它指定連線字串,標記 [ConnectionString] 告訴套件說資料表是在哪一個資料庫內,這樣基本上就可以開始下 Query 了,既然是自己寫的,當然要弄得越方便越好。

[ConnectionString("nameOrConnectionString")]
[Table("tbl_member")]
public class Member
{
    ...
}

[ConnectionString("nameOrConnectionString")]
[Table("tbl_member")]
public class MemberStatistics
{
    ...
}

[ConnectionString("nameOrConnectionString")]
[Table("tbl_department")]
public class Department
{
    ...
}

連線字串可以直接是 SQL Server 的連線字串,也可以是連線字串的名稱,而透過連線字串名稱取得連線字串的方式,需要先操作 SqlServerDataAccessFactory.Instance.AddConnectionString() 方法,從設定檔中將連線字串加進來,這樣才會對應得到。

internal class Program
{
    private static void Main(string[] args)
    {
        var configurationRoot = new ConfigurationBuilder().SetBasePath(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location))
            .AddJsonFile("appsettings.json")
            .AddJsonFile("appsettings.Debug.json", true)
            .AddJsonFile("appsettings.Release.json", true)
            .Build();

        // 將設定檔中的連線字串加到 SqlServerDataAccessFactory
        foreach (var configurationSection in configurationRoot.GetSection("ConnectionStrings").GetChildren())
        {
            SqlServerDataAccessFactory.Instance.AddConnectionString(configurationSection.Key, configurationSection.Value);
        }
    }
}

弄好之後,直接呼叫 IDataAccessFactory.Create<T>() 就可以建立 Data Access Object。

internal class Program
{
    private static void Main(string[] args)
    {
        ...

        IDataAccessFactory dataAccessFactory = SqlServerDataAccessFactory.Instance;

        var memberDataAccess = dataAccessFactory.Create<Member>();
    }
}

[ConnectionString] 可以標記多個,也就是說一個類別可以對應不同資料庫相同結構的資料表,只是在建立 Data Access Object 時需要指定連線字串名稱。

[ConnectionString("MemberDB")]
[ConnectionString("AnotherMemberDB")]
[Table("tbl_member")]
public class Member
{
    ...
}

internal class Program
{
    private static void Main(string[] args)
    {
        ...

        IDataAccessFactory dataAccessFactory = SqlServerDataAccessFactory.Instance;

        var memberDataAccess = dataAccessFactory.Create<Member>("AnotherMemberDB");
    }
}

建好 Data Access Object 之後就可以開始 Query 了,Query 的方法會在下面介紹,而套件中所有的非同步方法都會再搭配一個同步方法,文章中不另外列舉,範例程式也會以非同步方法為主。

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) 這兩個方法的話,需要到對應的類別裡面,為指定的必填欄位標上 [Required]

使用上建議優先選擇 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 }
                  };

    IDataAccessFactory dataAccessFactory = SqlServerDataAccessFactory.Instance;

    var memberDataAccess = dataAccessFactory.Create<Member>();
    
    // 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

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

public interface IDataAccess<T>
{
    ...
    Task<int> CountAsync(Expression<Func<T, bool>> predicate);
    Task<bool> ExistsAsync(Expression<Func<T, bool>> predicate);
    Task<List<T>> QueryAsync(Expression<Func<T, bool>> predicate, IEnumerable<(Expression<Func<T, object>>, Sortord)> orderings = null, Expression<Func<T, object>> selector = null, Expression<Func<T, object>> groupingColumns = null, Expression<Func<Grouping<T>, T>> groupingSelector = null, int? skipped = null, int? taken = null);
    Task<List<T>> QueryAsync<TSecond>((Expression<Func<T, TSecond>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, Expression<Func<T, TSecond, bool>> predicate, IEnumerable<(Expression<Func<T, TSecond, object>>, Sortord)> orderings = null, Expression<Func<T, TSecond, object>> selector = null, Expression<Func<T, TSecond, object>> groupingColumns = null, Expression<Func<Grouping<T, TSecond>, T>> groupingSelector = null, int? skipped = null, int? taken = null);
    Task<List<T>> QueryAsync<TSecond, TThird>((Expression<Func<T, TSecond>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, Expression<Func<T, TSecond, TThird, bool>> predicate, IEnumerable<(Expression<Func<T, TSecond, TThird, object>>, Sortord)> orderings = null, Expression<Func<T, TSecond, TThird, object>> selector = null, Expression<Func<T, TSecond, TThird, object>> groupingColumns = null, Expression<Func<Grouping<T, TSecond, TThird>, T>> groupingSelector = null, int? skipped = null, int? taken = null);
    Task<List<T>> QueryAsync<TSecond, TThird, TFourth, TFifth, TSixth, TSeventh>((Expression<Func<T, TSecond>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>>, JoinType) fifthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, bool>>, JoinType) sixthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, bool>>, JoinType) seventhJoin, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, bool>> predicate, IEnumerable<(Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, object>>, Sortord)> orderings = null, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, object>> selector = null, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, object>> groupingColumns = null, Expression<Func<Grouping<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh>, T>> groupingSelector = null, int? skipped = null, int? taken = null);
    Task<List<T>> QueryAsync<TSecond, TThird, TFourth, TFifth>((Expression<Func<T, TSecond>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>>, JoinType) fifthJoin, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>> predicate, IEnumerable<(Expression<Func<T, TSecond, TThird, TFourth, TFifth, object>>, Sortord)> orderings = null, Expression<Func<T, TSecond, TThird, TFourth, TFifth, object>> selector = null, Expression<Func<T, TSecond, TThird, TFourth, TFifth, object>> groupingColumns = null, Expression<Func<Grouping<T, TSecond, TThird, TFourth, TFifth>, T>> groupingSelector = null, int? skipped = null, int? taken = null);
    Task<List<T>> QueryAsync<TSecond, TThird, TFourth>((Expression<Func<T, TSecond>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, Expression<Func<T, TSecond, TThird, TFourth, bool>> predicate, IEnumerable<(Expression<Func<T, TSecond, TThird, TFourth, object>>, Sortord)> orderings = null, Expression<Func<T, TSecond, TThird, TFourth, object>> selector = null, Expression<Func<T, TSecond, TThird, TFourth, object>> groupingColumns = null, Expression<Func<Grouping<T, TSecond, TThird, TFourth>, T>> groupingSelector = null, int? skipped = null, int? taken = null);
    Task<List<T>> QueryAsync<TSecond, TThird, TFourth, TFifth, TSixth>((Expression<Func<T, TSecond>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>>, JoinType) fifthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, bool>>, JoinType) sixthJoin, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, bool>> predicate, IEnumerable<(Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, object>>, Sortord)> orderings = null, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, object>> selector = null, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, object>> groupingColumns = null, Expression<Func<Grouping<T, TSecond, TThird, TFourth, TFifth, TSixth>, T>> groupingSelector = null, int? skipped = null, int? taken = null);
    Task<T> QueryOneAsync(Expression<Func<T, bool>> predicate, IEnumerable<(Expression<Func<T, object>>, Sortord)> orderings = null, Expression<Func<T, object>> selector = null, Expression<Func<T, object>> groupingColumns = null, Expression<Func<Grouping<T>, T>> groupingSelector = null, int? skipped = null, int? taken = null);
    Task<T> QueryOneAsync<TSecond, TThird, TFourth, TFifth, TSixth, TSeventh>((Expression<Func<T, TSecond>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>>, JoinType) fifthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, bool>>, JoinType) sixthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, bool>>, JoinType) seventhJoin, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, bool>> predicate, IEnumerable<(Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, object>>, Sortord)> orderings = null, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, object>> selector = null, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh, object>> groupingColumns = null, Expression<Func<Grouping<T, TSecond, TThird, TFourth, TFifth, TSixth, TSeventh>, T>> groupingSelector = null, int? skipped = null, int? taken = null);
    Task<T> QueryOneAsync<TSecond, TThird, TFourth, TFifth, TSixth>((Expression<Func<T, TSecond>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>>, JoinType) fifthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, bool>>, JoinType) sixthJoin, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, bool>> predicate, IEnumerable<(Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, object>>, Sortord)> orderings = null, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, object>> selector = null, Expression<Func<T, TSecond, TThird, TFourth, TFifth, TSixth, object>> groupingColumns = null, Expression<Func<Grouping<T, TSecond, TThird, TFourth, TFifth, TSixth>, T>> groupingSelector = null, int? skipped = null, int? taken = null);
    Task<T> QueryOneAsync<TSecond>((Expression<Func<T, TSecond>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, Expression<Func<T, TSecond, bool>> predicate, IEnumerable<(Expression<Func<T, TSecond, object>>, Sortord)> orderings = null, Expression<Func<T, TSecond, object>> selector = null, Expression<Func<T, TSecond, object>> groupingColumns = null, Expression<Func<Grouping<T, TSecond>, T>> groupingSelector = null, int? skipped = null, int? taken = null);
    Task<T> QueryOneAsync<TSecond, TThird>((Expression<Func<T, TSecond>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, Expression<Func<T, TSecond, TThird, bool>> predicate, IEnumerable<(Expression<Func<T, TSecond, TThird, object>>, Sortord)> orderings = null, Expression<Func<T, TSecond, TThird, object>> selector = null, Expression<Func<T, TSecond, TThird, object>> groupingColumns = null, Expression<Func<Grouping<T, TSecond, TThird>, T>> groupingSelector = null, int? skipped = null, int? taken = null);
    Task<T> QueryOneAsync<TSecond, TThird, TFourth, TFifth>((Expression<Func<T, TSecond>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, (Expression<Func<T, TSecond, TThird, TFourth, TFifth>>, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>>, JoinType) fifthJoin, Expression<Func<T, TSecond, TThird, TFourth, TFifth, bool>> predicate, IEnumerable<(Expression<Func<T, TSecond, TThird, TFourth, TFifth, object>>, Sortord)> orderings = null, Expression<Func<T, TSecond, TThird, TFourth, TFifth, object>> selector = null, Expression<Func<T, TSecond, TThird, TFourth, TFifth, object>> groupingColumns = null, Expression<Func<Grouping<T, TSecond, TThird, TFourth, TFifth>, T>> groupingSelector = null, int? skipped = null, int? taken = null);
    Task<T> QueryOneAsync<TSecond, TThird, TFourth>((Expression<Func<T, TSecond>>, Expression<Func<T, TSecond, bool>>, JoinType) secondJoin, (Expression<Func<T, TSecond, TThird>>, Expression<Func<T, TSecond, TThird, bool>>, JoinType) thirdJoin, (Expression<Func<T, TSecond, TThird, TFourth>>, Expression<Func<T, TSecond, TThird, TFourth, bool>>, JoinType) fourthJoin, Expression<Func<T, TSecond, TThird, TFourth, bool>> predicate, IEnumerable<(Expression<Func<T, TSecond, TThird, TFourth, object>>, Sortord)> orderings = null, Expression<Func<T, TSecond, TThird, TFourth, object>> selector = null, Expression<Func<T, TSecond, TThird, TFourth, object>> groupingColumns = null, Expression<Func<Grouping<T, TSecond, TThird, TFourth>, T>> groupingSelector = null, int? skipped = null, int? taken = null);
    ...
}

查詢語法目前除了必要的 SELECT ... FROM ... WHERE 之外,還支援底下幾個語法:

  • 比較運算子:=<>><>=<=LIKEINIS NULLIS NOT NULL
  • 字串欄位比大小:"strA".CompareTo("strB") [>, >=, <, <=] 0
  • TOP
  • ORDER BY
  • GROUP BY:GroupBy 有支援的函式為 MAXMINSUMAVG
  • OFFSET | FETCH
  • INNER JOINLEFT JOIN:Join 最多可以到 7 個資料表

另外這邊不鼓勵 SELECT * 語法,所以一定要 SELECT 欄位,還有任何加入 SQL 運算的屬性都不可以被標記 [NotMapped],範例程式碼如下,說明寫在註解中。

private static async Task DemoQuery()
{
    IDataAccessFactory dataAccessFactory = SqlServerDataAccessFactory.Instance;

    var memberDataAccess = dataAccessFactory.Create<Member>();

    // SELECT 一筆 Id = 1 的 Member
    var member = await memberDataAccess
                     .Where(x => x.Id == 1)
                     .Select(x => new { x.Name, x.Age })
                     .QueryOneAsync();

    // SELECT 多筆 Id IN (1, 2, 3) 的 Member
    var members = await memberDataAccess
                      .Where(x => new[] { 1, 2, 3 }.Contains(x.Id))
                      .Select(x => new { x.Name, x.Age })
                      .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)
                  .Take(10)
                  .Select(x => new { x.Name, x.Age })
                  .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)
                  .Select(x => new { x.Name, x.Age })
                  .QueryAsync();

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

    // 回傳 Id = 1 的 Member 是否存在?
    memberCount = await memberDataAccess
                      .Where(x => x.Id == 1)
                      .ExistsAsync();

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

    // 使用 Or() 方法 SELECT 多筆 Id IN (1, 2, 3) 的 Member
    members = await memberDataAccess
                  .Where(x => x.Id == 1)
                  .Or(x => x.Id == 2)
                  .Or(x => x.Id == 3)
                  .Select(x => new { x.Name, x.Age })
                  .QueryAsync();

    // 使用 Skip() + Take() 方法跳過 20 筆取 10 筆資料,可以用做分頁查詢。
    members = await memberDataAccess
                  .OrderBy(x => x.Id)
                  .Select(x => new { x.Name, x.Age })
                  .Skip(20)
                  .Take(10)
                  .QueryAsync();

    // 使用 InnerJoin() 找出年紀大於 20 歲的 Member,以及 Member 的 Department 和 Manager。
    members = await memberDataAccess.InnerJoin(x => x.Department, (x, y) => x.DepartmentId == y.Id)
                  .InnerJoin((x, y) => x.Manager, (x, y, z) => x.ManagerId == z.Id)
                  .Where((x, y, z) => x.Age > 20)
                  .Select(
                      (x, y, z) => new
                                   {
                                       x.Id,
                                       x.Name,
                                       DepartmentId = y.Id,
                                       DepartmentName = y.Name,
                                       ManagerId = z.Id,
                                       ManagerName = z.Name
                                   })
                  .QueryAsync();

    var memberStatisticsDataAccess = dataAccessFactory.Create<MemberStatistics>();

    // 使用 GroupBy 找出 Member 名字叫 Johnny 的平均年齡
    var statistics = await memberStatisticsDataAccess
                         .Where(x => x.Name == "Johnny")
                         .GroupBy(
                             x => new { x.Name },
                             g => new MemberStatistics
                                  {
                                      Name = g.Select(x => x.Name),
                                      AverageAge = g.Avg(x => x.Age)
                                  })
                         .QueryOneAsync();
}
運算子右邊的運算式僅支援常數變數屬性,除此之外的運算式是不支援的,這點需要特別注意。

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()
{
    IDataAccessFactory dataAccessFactory = SqlServerDataAccessFactory.Instance;

    var memberDataAccess = dataAccessFactory.Create<Member>();

    // 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()
{
    IDataAccessFactory dataAccessFactory = SqlServerDataAccessFactory.Instance;

    var memberDataAccess = dataAccessFactory.Create<Member>();

    // 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()
{
    IDataAccessFactory dataAccessFactory = SqlServerDataAccessFactory.Instance;

    var memberDataAccess = dataAccessFactory.Create<Member>();

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

Bulk 系列

Bulk 系列是使用 Table-Valued Parameter 來向資料庫傳遞參數的,所以需要先在 SQL Server 建立 User Defined Table Type,應用程式這邊需要透過 SqlServerDataAccessFactory.Instance.AddUserDefinedTable() 方法預先做設定,然後在有需要使用 User Defined Table Type 的資料類別上標記 UserDefinedAttribute

internal class Program
{
    private static void Main(string[] args)
    {
        ...

        SqlServerDataAccessFactory.Instance.AddUserDefinedTable(
            "MemberType",
            new Dictionary<string, Type>
            {
                ["Id"] = typeof(int),
                ["Name"] = typeof(string),
                ["Phone"] = typeof(string),
                ["Age"] = typeof(int)
            });

        ...
    }
}

[UserDefined(TableType = "MemberType")]
[Table("tbl_member")]
public class Member
{
    ...
}

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

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()
{
    IDataAccessFactory dataAccessFactory = SqlServerDataAccessFactory.Instance;

    var memberDataAccess = dataAccessFactory.Create<Member>();

    // 批次 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 教學