話說C#程式設計師人手一個ORM

語言: CN / TW / HK

話說C#程式設計師人手一個ORM,確實沒有必要再寫ORM了,不過我的ORM並不是新的,是從DBHelper演化過來的,算是DBHelper魔改版。

目前流行的ORM有EF、Dapper、SqlSugar、FreeSql、Chloe等,有經驗的程式設計師總是在這些ORM基礎上或者在DBHelper基礎上再封裝一套,再加上自己寫ORM的,可謂人手一套ORM。可能是因為在框架當中,編寫ORM,入門相對簡單吧,但是做好很難。

本專案來源:專案來源於需求,2014年我在上家公司做ERP、CRM之類的BS管理系統專案,當時公司用的就是OracleHelper、SqlServerHelper之類的;2015年開始嘗試修改,當時支援了事務,但仍是靜態的幫助類;2016年我把OracleHelper、SqlServerHelper、MySqlHelper、SQLiteHelper合成了一個DBHelper,仍是靜態類。2017跳槽到現公司,公司大專案我的DBHelper就用不上了,平時小專案用用;2020年,終於發現靜態DBHelper多執行緒併發,事務這塊有BUG,所以改造成了非靜態的。後來又加入SqlString類,重構以方便擴充套件支援更多資料庫種類;最近簡單支援了一下Lambda表示式;幾經修改,原來DBHelper的那些方法幾乎沒怎麼變;長期不變的穩定的API才是好API。

主要是自己用的,如果別人要用的話,建議從gitee或github上拉原始碼下來,原始碼在手好控制,原始碼相對簡單,有經驗的程式設計師不難看懂,可以自己除錯修改擴充套件,Lambda表示式是新支援的,如果Lambda hold不住,就使用原生SQL。

跟主流ORM相比還是比較欠缺的,它只是一個DBHelper。

原始碼地址:

https://gitee.com/s0611163/DBHelper

https://github.com/0611163/DBHelper

該ORM介紹及使用示例如下:

DBHelper

簡介

一款輕量級ORM,查詢使用原生SQL,查詢結果對映到實體類,增刪改支援實體類,支援Oracle、MSSQL、MySQL、SQLite等多種資料庫,有配套Model生成器,方便自己擴充套件以支援更多資料庫

特點

  1. 支援Oracle、MSSQL、MySQL、SQLite四種資料庫

  2. 方便擴充套件以支援更多關係資料庫

  3. 有配套的Model生成器

  4. insert、update、delete操作無需寫SQL

  5. 查詢使用原生SQL

  6. 查詢結果通過對映轉成實體類或實體類集合

  7. 支援引數化查詢,通過SqlString類提供非常方便的引數化查詢

  8. 支援連線多個數據源

  9. 單表查詢、單表分頁查詢、簡單的聯表分頁查詢支援Lambda表示式

  10. 支援原生SQL和Lambda表示式混寫

優點

  1. 程式碼實現比較簡單,有經驗的程式設計師容易掌控程式碼,自己修改和擴充套件

  2. 查詢使用原生SQL

缺點

  1. 聯表查詢對Lambda表示式的支援比較弱

  2. 複雜查詢不支援Lambda表示式

建議

  1. 單表查詢可以使用Lambda表示式

  2. 聯表查詢以及複雜查詢建議使用原生SQL或原生SQL和Lambda表示式混寫

作者郵箱

[email protected]

示例

定義資料庫物件

public class DBHelper
{
    #region 變數
    private static ISessionHelper _sessionHelper = new SessionHelper(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString(), DBType.MySQL);
    #endregion

    #region 獲取 ISession
    /// <summary>
    /// 獲取 ISession
    /// </summary>
    public static ISession GetSession()
    {
        return _sessionHelper.GetSession();
    }
    #endregion

    #region 獲取 ISession (非同步)
    /// <summary>
    /// 獲取 ISession (非同步)
    /// </summary>
    public static async Task<ISession> GetSessionAsync()
    {
        return await _sessionHelper.GetSessionAsync();
    }
    #endregion

}

使用Model生成器生成實體類

  1. 實體類放在Models資料夾中

  2. 擴充套件實體類放在ExtModels資料夾中

  3. 實體類和擴充套件實體類使用partial修飾,實際上是一個類,放在不同的檔案中

  4. 如果需要新增自定義屬性,請修改ExtModels,不要修改Models

實體類示例

/// <summary>
/// 訂單表
/// </summary>
[Serializable]
[DBTable("bs_order")]
public partial class BsOrder
{

    /// <summary>
    /// 主鍵
    /// </summary>
    [DBKey]
    [DBField]
    public string Id { get; set; }

    /// <summary>
    /// 訂單時間
    /// </summary>
    [DBField("order_time")]
    public DateTime OrderTime { get; set; }

    /// <summary>
    /// 訂單金額
    /// </summary>
    [DBField]
    public decimal? Amount { get; set; }

    /// <summary>
    /// 下單使用者
    /// </summary>
    [DBField("order_userid")]
    public long OrderUserid { get; set; }

    /// <summary>
    /// 訂單狀態(0草稿 1已下單 2已付款 3已發貨 4完成)
    /// </summary>
    [DBField]
    public int Status { get; set; }

    /// <summary>
    /// 備註
    /// </summary>
    [DBField]
    public string Remark { get; set; }

    /// <summary>
    /// 建立者ID
    /// </summary>
    [DBField("create_userid")]
    public string CreateUserid { get; set; }

    /// <summary>
    /// 建立時間
    /// </summary>
    [DBField("create_time")]
    public DateTime CreateTime { get; set; }

    /// <summary>
    /// 更新者ID
    /// </summary>
    [DBField("update_userid")]
    public string UpdateUserid { get; set; }

    /// <summary>
    /// 更新時間
    /// </summary>
    [DBField("update_time")]
    public DateTime? UpdateTime { get; set; }

}

修改擴充套件實體類

  1. 修改擴充套件實體類,新增自定義屬性

  2. 下面的擴充套件實體類中,查詢時OrderUserRealName會被自動填充,查詢SQL:select t.*, u.real_name as OrderUserRealName from ......

  3. DetailList不會被自動填充,需要手動查詢

擴充套件實體類示例

/// <summary>
/// 訂單表
/// </summary>
public partial class BsOrder
{
    /// <summary>
    /// 訂單明細集合
    /// </summary>
    public List<BsOrderDetail> DetailList { get; set; }

    /// <summary>
    /// 下單使用者姓名
    /// </summary>
    public string OrderUserRealName { get; set; }

    /// <summary>
    /// 下單使用者名稱
    /// </summary>
    public string OrderUserName { get; set; }
}

新增

public void Insert(SysUser info)
{
    using (var session = DBHelper.GetSession())
    {
        session.Insert(info);
    }
}

批量新增

public void Insert(List<SysUser> list)
{
    using (var session = DBHelper.GetSession())
    {
        session.Insert(list);
    }
}

修改

public void Update(SysUser info)
{
    using (var session = DBHelper.GetSession())
    {
        session.Update(info);
    }
}

批量修改

public void Update(List<SysUser> list)
{
    using (var session = DBHelper.GetSession())
    {
        session.Update(list);
    }
}

刪除

public void Delete(string id)
{
    using (var session = DBHelper.GetSession())
    {
        session.DeleteById<SysUser>(id);
    }
}

條件刪除

using (var session = DBHelper.GetSession())
{
    session.DeleteByCondition<SysUser>(string.Format("id>=12"));
}

查詢單個記錄

public SysUser Get(string id)
{
    using (var session = DBHelper.GetSession())
    {
        return session.FindById<SysUser>(id);
    }
}
using (var session = DBHelper.GetSession())
{
    return session.FindBySql<SysUser>("select * from sys_user");
}

簡單查詢

using (var session = DBHelper.GetSession())
{
    string sql = "select * from CARINFO_MERGE";
    List<CarinfoMerge> result = session.FindListBySql<CarinfoMerge>(sql);
}

條件查詢

public List<BsOrder> GetList(int? status, string remark, DateTime? startTime, DateTime? endTime)
{
    using (var session = DBHelper.GetSession())
    {
        SqlString sql = session.CreateSqlString(@"
            select t.*, u.real_name as OrderUserRealName
            from bs_order t
            left join sys_user u on t.order_userid=u.id
            where 1=1");

        sql.AppendIf(status.HasValue, " and t.status=@status", status);

        sql.AppendIf(!string.IsNullOrWhiteSpace(remark), " and t.remark like concat('%',@remark,'%')", remark);

        sql.AppendIf(startTime.HasValue, " and t.order_time>=STR_TO_DATE(@startTime, '%Y-%m-%d %H:%i:%s') ", startTime.Value.ToString("yyyy-MM-dd HH:mm:ss"));

        sql.AppendIf(endTime.HasValue, " and t.order_time<=STR_TO_DATE(@endTime, '%Y-%m-%d %H:%i:%s') ", endTime.Value.ToString("yyyy-MM-dd HH:mm:ss"));

        sql.Append(" order by t.order_time desc, t.id asc ");

        List<BsOrder> list = session.FindListBySql<BsOrder>(sql.SQL, sql.Params);
        return list;
    }
}

分頁查詢

public List<BsOrder> GetListPage(ref PageModel pageModel, int? status, string remark, DateTime? startTime, DateTime? endTime)
{
    using (var session = DBHelper.GetSession())
    {
        SqlString sql = session.CreateSqlString(@"
            select t.*, u.real_name as OrderUserRealName
            from bs_order t
            left join sys_user u on t.order_userid=u.id
            where 1=1");

        sql.AppendIf(status.HasValue, " and t.status=@status", status);

        sql.AppendIf(!string.IsNullOrWhiteSpace(remark), " and t.remark like concat('%',@remark,'%')", remark);

        sql.AppendIf(startTime.HasValue, " and t.order_time>=STR_TO_DATE(@startTime, '%Y-%m-%d %H:%i:%s') ", startTime.Value.ToString("yyyy-MM-dd HH:mm:ss"));

        sql.AppendIf(endTime.HasValue, " and t.order_time<=STR_TO_DATE(@endTime, '%Y-%m-%d %H:%i:%s') ", endTime.Value.ToString("yyyy-MM-dd HH:mm:ss"));

        string orderby = " order by t.order_time desc, t.id asc ";
        pageModel = session.FindPageBySql<BsOrder>(sql.SQL, orderby, pageModel.PageSize, pageModel.CurrentPage, sql.Params);
        return pageModel.GetResult<BsOrder>();
    }
}

事務

public string Insert(BsOrder order, List<BsOrderDetail> detailList)
{
    using (var session = DBHelper.GetSession())
    {
        try
        {
            session.BeginTransaction();

            order.Id = Guid.NewGuid().ToString("N");
            order.CreateTime = DateTime.Now;

            decimal amount = 0;
            foreach (BsOrderDetail detail in detailList)
            {
                detail.Id = Guid.NewGuid().ToString("N");
                detail.OrderId = order.Id;
                detail.CreateTime = DateTime.Now;
                amount += detail.Price * detail.Quantity;
                session.Insert(detail);
            }
            order.Amount = amount;

            session.Insert(order);

            session.CommitTransaction();

            return order.Id;
        }
        catch (Exception ex)
        {
            session.RollbackTransaction();
            Console.WriteLine(ex.Message + "\r\n" + ex.StackTrace);
            throw ex;
        }
    }
}

非同步查詢

public async Task<List<BsOrder>> GetListPageAsync(PageModel pageModel, int? status, string remark, DateTime? startTime, DateTime? endTime)
{
    using (var session = await DBHelper.GetSessionAsync())
    {
        SqlString sql = session.CreateSqlString(@"
            select t.*, u.real_name as OrderUserRealName
            from bs_order t
            left join sys_user u on t.order_userid=u.id
            where 1=1");

        sql.AppendIf(status.HasValue, " and t.status=@status", status);

        sql.AppendIf(!string.IsNullOrWhiteSpace(remark), " and t.remark like concat('%',@remark,'%')", remark);

        sql.AppendIf(startTime.HasValue, " and t.order_time>=STR_TO_DATE(@startTime, '%Y-%m-%d %H:%i:%s') ", startTime.Value.ToString("yyyy-MM-dd HH:mm:ss"));

        sql.AppendIf(endTime.HasValue, " and t.order_time<=STR_TO_DATE(@endTime, '%Y-%m-%d %H:%i:%s') ", endTime.Value.ToString("yyyy-MM-dd HH:mm:ss"));

        string orderby = " order by t.order_time desc, t.id asc ";
        pageModel = await session.FindPageBySqlAsync<BsOrder>(sql.SQL, orderby, pageModel.PageSize, pageModel.CurrentPage, sql.Params);
        return pageModel.GetResult<BsOrder>();
    }
}

條件查詢(使用 ForContains、ForStartsWith、ForEndsWith、ForDateTime、ForList 等輔助方法)

public List<BsOrder> GetListExt(int? status, string remark, DateTime? startTime, DateTime? endTime, string ids)
{
    using (var session = DBHelper.GetSession())
    {
        SqlString sql = session.CreateSqlString(@"
            select t.*, u.real_name as OrderUserRealName
            from bs_order t
            left join sys_user u on t.order_userid=u.id
            where 1=1");

        sql.AppendIf(status.HasValue, " and t.status=@status", status);

        sql.AppendIf(!string.IsNullOrWhiteSpace(remark), " and t.remark like @remark", sql.ForContains(remark));

        sql.AppendIf(startTime.HasValue, " and t.order_time >= @startTime ", sql.ForDateTime(startTime.Value));

        sql.AppendIf(endTime.HasValue, " and t.order_time <= @endTime ", sql.ForDateTime(endTime.Value));

        sql.Append(" and t.id in @ids ", sql.ForList(ids.Split(',').ToList()));

        sql.Append(" order by t.order_time desc, t.id asc ");

        List<BsOrder> list = session.FindListBySql<BsOrder>(sql.SQL, sql.Params);
        return list;
    }
}

使用Lambda表示式單表查詢

單表分頁查詢使用ToPageList替換ToList即可

public void TestQueryByLambda6()
{
    using (var session = DBHelper.GetSession())
    {
        SqlString<BsOrder> sql = session.CreateSqlString<BsOrder>();

        string remark = "測試";

        List<BsOrder> list = sql.Query()

            .WhereIf(!string.IsNullOrWhiteSpace(remark),
                t => t.Remark.Contains(remark)
                && t.CreateTime < DateTime.Now
                && t.CreateUserid == "10")

            .OrderByDescending(t => t.OrderTime).OrderBy(t => t.Id)
            .ToList();

        foreach (BsOrder item in list)
        {
            Console.WriteLine(ModelToStringUtil.ToString(item));
        }
    }
}

使用Lambda表示式聯表分頁查詢(簡單的聯表查詢,複雜情況請使用原生SQL或原生SQL和Lambda表示式混寫)

public void TestQueryByLambda7()
{
    using (var session = DBHelper.GetSession())
    {
        SqlString<BsOrder> sql = session.CreateSqlString<BsOrder>();

        int total;
        List<string> idsNotIn = new List<string>() { "100007", "100008", "100009" };

        List<BsOrder> list = sql.Query()
            .Select<SysUser>(u => u.UserName, t => t.OrderUserName)
            .Select<SysUser>(u => u.RealName, t => t.OrderUserRealName)
            .LeftJoin<SysUser>((t, u) => t.OrderUserid == u.Id)
            .LeftJoin<BsOrderDetail>((t, d) => t.Id == d.OrderId)
            .Where<SysUser, BsOrderDetail>((t, u, d) => t.Remark.Contains("訂單") && u.CreateUserid == "1" && d.GoodsName != null)
            .WhereIf<BsOrder>(true, t => t.Remark.Contains("測試"))
            .WhereIf<BsOrder>(true, t => !idsNotIn.Contains(t.Id))
            .WhereIf<SysUser>(true, u => u.CreateUserid == "1")
            .OrderByDescending(t => t.OrderTime).OrderBy(t => t.Id)
            .ToPageList(1, 20, out total);

        foreach (BsOrder item in list)
        {
            Console.WriteLine(ModelToStringUtil.ToString(item));
        }
    }
}

原生SQL和Lambda表示式混寫

public void TestQueryByLambda9()
{
    using (var session = DBHelper.GetSession())
    {
        SqlString<BsOrder> sql = session.CreateSqlString<BsOrder>(@"
            select t.*, u.real_name as OrderUserRealName
            from bs_order t
            left join sys_user u on t.order_userid=u.id
            where 1=1");

        List<BsOrder> list = sql.Where(t => t.Status == int.Parse("0")
            && t.Status == new BsOrder().Status
            && t.Remark.Contains("訂單")
            && t.Remark != null
            && t.OrderTime >= new DateTime(2010, 1, 1)
            && t.OrderTime <= DateTime.Now.AddDays(1))
            .WhereIf<SysUser>(true, u => u.CreateTime < DateTime.Now)
            .OrderByDescending(t => t.OrderTime).OrderBy(t => t.Id)
            .ToList();

        foreach (BsOrder item in list)
        {
            Console.WriteLine(ModelToStringUtil.ToString(item));
        }
    }
}