C# 比Entity Framework輕量的ORM框架Dapper

當不想用肥用大的Entity Framework
其實也可以改用其他款的ORM框架Dapper
你說 Entity Framework 與 Dapper 哪一個好?
小孩子才選擇, 我全都要!
其實就沒有說一定要選擇哪一種
看功能與場合使用最適合的ORM即可
如果需要高效能的查詢就使用Dapper, 例如: 報表查詢等等…

Dapper
Dapper.SqlBuilder

using Dapper;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using static Dapper.SqlBuilder;

namespace Demo.Data.Repository
{
    public class DataRepository
    {
        private string connectString = "資料庫連線字串";

        public List<DataModel> Get(long ID)
        {
            SqlBuilder builder = new SqlBuilder()
                .Select("ID, Name, Value, Memo, CreateTime, UpdateTime")
                .Where("ID = @ID", new { ID });

            Template selector = builder.AddTemplate("SELECT /**select**/ FROM dbo.Data WITH(NOLOCK) /**where**/");

            using (SqlConnection conn = new SqlConnection(connectString))
            {
                return conn.Query<DataModel>(selector.RawSql, selector.Parameters).ToList();
            }
        }

        public int CreateModify(DataModel data)
        {
            string sqlScript = @"
                IF NOT EXISTS(SELECT 1 FROM dbo.Data WHERE ID = @ID)
                INSERT INTO dbo.Data
                    (Name, Value, Memo, CreateTime, UpdateTime)
                VALUES
                    (@Name, @Value, @Memo, GETDATE(), GETDATE())
                ELSE
                UPDATE dbo.Data SET Value = @Value, Memo = @Memo, UpdateTime = GETDATE() WHERE ID = @ID
                ";

            using (SqlConnection conn = new SqlConnection(connectString))
            {
                return conn.Execute(sqlScript, data);
            }
        }

        public int Delete(long ID)
        {
            using (SqlConnection conn = new SqlConnection(connectString))
            {
                return conn.Execute("DELETE FROM dbo.Data WHERE ID = @ID", new { ID });
            }
        }
    }

    public class DataModel
    {
        public long ID { get; set; }
        public string Name { get; set; }
        public string Value { get; set; }
        public string Memo { get; set; }
        public DateTime CreateTime { get; set; }
        public DateTime UpdateTime { get; set; }
    }
}