c# 了解其他ORM框架 SqlSugar, Dapper

柔蘭

174人浏览 · 2026-06-15 19:27:38

柔蘭 · 2026-06-15 19:27:38 发布

SqlSugar官网：
https://www.donet5.com/Home/Doc

SqlSugar github源码：
https://github.com/topics/sqlsugar

Dapper 使用教程：
https://armon.blog.csdn.net/article/details/158917942
https://blog.csdn.net/CSDN2016DDDD/article/details/144368032

Dapper github源码：
https://github.com/topics/dapper
https://github.com/DapperLib/Dapper

市场上ORM框架很多，除了微软官方主流的EF框架外，第三方公司也开发了一些ORM框架，被开发者广泛使用，如：SqlSugar, Dapper

SqlSugar是国产的，开源的，易用的ORM框架，让开发者使用它可以方便的和各种数据库交互。
Dapper是国外的，开源的，简易的ORM框架，它是ADO.NET基础上适当简化了开发者操作数据库的逻辑，性能上有很大提升，它的使用和ADO.NET基本一致。

初学者在学习了EF官方框架后，可以适当了解其他ORM框架的使用，以便在将来的工作中达到举一反三的目的。
学习其他ORM框架时，主要学习技术文档的查找，阅读，理解，根据所学ADO.NET，EF等类比学习。
开源的ORM框架源码一般都会在github网站上开源。

1.Dapper框架

using System;

namespace _1.Dapper框架.Models
{
    public class Publisher
    {
        public int PublisherId { get; set; }
        public string PublisherName { get; set; }
        public string Remark { get; set; }
        public int Status { get; set; }
        public int CreateUserId { get; set; }
        public DateTime CreateTime { get; set; }
        public int? LastUpdateUserId { get; set; }
        public DateTime? LastUpdateTime { get; set; }

    }
}

using _1.Dapper框架.Models;
using Dapper;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Windows.Forms;

namespace _1.Dapper框架
{
    public partial class Form1 : Form
    {
        string connString = "server=.;database=Book;uid=sa;pwd=123456;";
        IDbConnection conn = null;
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            using (conn = new SqlConnection(connString))
            {
                if (conn.State != ConnectionState.Open) conn.Open();

                // Dapper对IDbConnection进行了扩展，扩展了Query，Execute等。
                // 而SqlConnection继承了IDbConnection，所以Dapper对SqlConnection进行了扩展。
                // 使用Dapper之后，再也不需要Command,Adapter这些对象了。
                //var list = conn.Query<Publisher>("select * from Publisher").ToList();
                //var list2 = conn.Query<Models.Publisher>("select * from Publisher where PublisherId=@PublisherId", new { PublisherId = 1}).ToList();
                var list3 = conn.Query<Models.Publisher>("select * from Publisher where PublisherName like @PublisherName order by PublisherId desc", new { PublisherName = $"%{textBox1.Text}%" }).ToList();
                dataGridView1.DataSource = list3;
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            using (conn = new SqlConnection(connString))
            {
                if (conn.State != ConnectionState.Open) conn.Open();
                string sql = "INSERT INTO [dbo].[Publisher]([PublisherName],[Remark],[Status],[CreateUserId],[CreateTime]) VALUES (@PublisherName, @Remark, @Status, @CreateUserId, @CreateTime);";
                int row = conn.Execute(sql, new
                {
                    PublisherName = "hello",
                    Remark = "备注",
                    Status = 0,
                    CreateUserId = 1,
                    CreateTime = DateTime.Now
                });
                if (row > 0)
                {
                    button1_Click(null, null);
                }
            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            using (conn = new SqlConnection(connString))
            {
                if (conn.State != ConnectionState.Open) conn.Open();
                string sql = "INSERT INTO [dbo].[Publisher]([PublisherName],[Remark],[Status],[CreateUserId],[CreateTime]) VALUES (@PublisherName, @Remark, @Status, @CreateUserId, @CreateTime);SELECT SCOPE_IDENTITY();";
                // ExecuteScalar返回第一行的第一列，ExecuteScalar<T>是主键的类型
                int newId = conn.ExecuteScalar<int>(sql, new
                {
                    PublisherName = "hello",
                    Remark = "备注",
                    Status = 0,
                    CreateUserId = 1,
                    CreateTime = DateTime.Now
                });
                if (newId > 0)
                {
                    button1_Click(null, null);
                }
            }
        }

        private void button6_Click(object sender, EventArgs e)
        {
            using (conn = new SqlConnection(connString))
            {
                if (conn.State != ConnectionState.Open) conn.Open();
                string sql = "select * from Publisher where PublisherId=@a";
                //var model = conn.QueryFirstOrDefault<Models.Publisher>(sql, new { a = 1 });
                var model = conn.QuerySingleOrDefault<Models.Publisher>(sql, new { a = 1 });
                MessageBox.Show(model.PublisherName);
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            using (conn = new SqlConnection(connString))
            {
                if (conn.State != ConnectionState.Open) conn.Open();
                string sql = "delete from Publisher where PublisherId=@a";
                int row = conn.Execute(sql, new { a = 1 });  // async和await关键字，后面会讲
                if (row > 0)
                {
                    button1_Click(null, null);
                }
            }
        }
    }
}

2.SqlSugar

using SqlSugar;
using System;

namespace _2.SqlSugar.Models
{
    [SugarTable("Publisher")]//当和数据库名称不一样可以设置表别名 指定表明  [Table("Publisher")]
    public class Publisher
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true, IsNullable = false)]//数据库是自增才配自增   [Column(TypeName="varchar")]  [DatabaseGenerate()]
        public int PublisherId { get; set; }

        [SugarColumn(IsNullable = false)]  // [Requried()]
        public string PublisherName { get; set; }

        [SugarColumn(IsNullable = true)]
        public string Remark { get; set; }

        [SugarColumn(IsNullable = false, DefaultValue = "0")]
        public int Status { get; set; }

        [SugarColumn(IsNullable = false)]
        public int CreateUserId { get; set; }

        [SugarColumn(IsNullable = false)]
        public DateTime CreateTime { get; set; }

        public int? LastUpdateUserId { get; set; }
        public DateTime? LastUpdateTime { get; set; }

    }
}

using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace _2.SqlSugar.Models
{
    [SugarTable("UserInfo")]
    public class UserInfo
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true, IsNullable = false)]
        public int UserId { get; set; }
        public string UserName { get; set; }
        public string Password { get; set; }
        public int UserTypeId { get; set; }  // 外键
        public int Status { get; set; }
        public int CreateUserId { get; set; }
        public DateTime CreateTime { get; set; }
        public int? LastUpdateUserId { get; set; }
        public DateTime? LastUpdateTime { get; set; }
    }
}

using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace _2.SqlSugar.Models
{
    [SugarTable("UserType")]
    public class UserType
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true, IsNullable = false)]
        public int UserTypeId { get; set; }
        public string UserTypeName { get; set; }
        public string Remark { get; set; }
        public int Status { get; set; }
        public int CreateUserId { get; set; }
        public DateTime CreateTime { get; set; }
        public int? LastUpdateUserId { get; set; }
        public DateTime? LastUpdateTime { get; set; }

    }
}

using _2.SqlSugar.Models;
using SqlSugar;
using System;
using System.Linq;
using System.Reflection;
using System.Windows.Forms;

namespace _2.SqlSugar
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            // 表达式。 Where条件
            var exp = Expressionable.Create<Publisher>();
            exp.AndIF(!string.IsNullOrWhiteSpace(textBox1.Text), p => p.PublisherName.Contains(textBox1.Text));

            var list = SqlSugarHelper.Db
                 .Queryable<Models.Publisher>()  // SQLSugar查询
                 //.Where(x => x.PublisherName.Contains(textBox1.Text))
                 .Where(exp.ToExpression())
                 .OrderByDescending(x => x.PublisherId)
                 .ToList();

            dataGridView1.DataSource = list;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            var model = new Models.Publisher()
            {
                PublisherName = "XXXX",
                Remark = "备注",
                Status = 0,
                CreateUserId = 1,
                CreateTime = DateTime.Now
            };
            int row = SqlSugarHelper.Db.Insertable<Publisher>(model).ExecuteCommand();
            if (row > 0)
            {
                button1.PerformClick();
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            var model1 = SqlSugarHelper.Db.Queryable<Publisher>().First(x => x.PublisherId == 2);
            var model2 = SqlSugarHelper.Db.Queryable<Publisher>().Single(x => x.PublisherId == 2);
            var model3 = SqlSugarHelper.Db.Queryable<Publisher>().InSingle(2);


            model1.PublisherName = "ZZZZ";
            model1.LastUpdateUserId = 1;
            model1.LastUpdateTime = DateTime.Now;

            int row = SqlSugarHelper.Db.Updateable<Publisher>(model1).ExecuteCommand();
            if (row > 0)
            {
                button1.PerformClick();
            }
        }

        // 删除remove, delete  插入add insert   修改edit, update, 查询query, search
        private void button4_Click(object sender, EventArgs e)
        {
            var model1 = SqlSugarHelper.Db.Queryable<Publisher>().First(x => x.PublisherId == 2);
            int row = SqlSugarHelper.Db.Deleteable<Publisher>(model1).ExecuteCommand();
            int row2 = SqlSugarHelper.Db.Deleteable<Publisher>(2).ExecuteCommand();

            if (row > 0)
            {
                button1.PerformClick();
            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            var list = SqlSugarHelper.Db.Queryable<UserInfo>()
                 .LeftJoin<UserType>((u, ut) => u.UserTypeId == ut.UserTypeId)
                 .LeftJoin<UserInfo>((u, ut, u1) => u.CreateUserId == u1.UserId)
                 .LeftJoin<UserInfo>((u, ut, u1, u2) => u.LastUpdateUserId == u2.UserId)
                 .Select((u, ut, u1, u2) => new
                 {
                     u.UserId,
                     u.UserName,
                     ut.UserTypeName,
                     CreateUserName = u1.UserName,
                     LastUpdateUserName = u2.UserName
                 })
                 .ToList();

            dataGridView1.DataSource = list;
        }
    }
}

using SqlSugar;
using System;

namespace _2.SqlSugar
{
    // sqlsugar中的Db理解成EF中的上下文。
    public static class SqlSugarHelper
    {
        //创建数据库对象 (用法和EF Dappper一样通过new保证线程安全)
       public static SqlSugarClient Db = new SqlSugarClient(
           // 参数1：连接配置，连接字符串，数据的类型，是否自动关闭连接
           new ConnectionConfig()
           {
               ConnectionString = "server=.;database=Book;uid=sa;pwd=123456;",
               DbType = DbType.SqlServer,
               IsAutoCloseConnection = true
           },
           //参数2：主要把sqlSugar执行的sql语句翻译出来打印到控制台上
           // AOP 面向切片编程。
           db => {
                db.Aop.OnLogExecuting = (sql, pars) =>
                {
                    //获取原生SQL推荐 5.1.4.63  性能OK
                    Console.WriteLine(UtilMethods.GetNativeSql(sql, pars));

                    //获取无参数化SQL 对性能有影响，特别大的SQL参数多的，调试使用
                    //Console.WriteLine(UtilMethods.GetSqlString(DbType.SqlServer,sql,pars))
                };
                //注意多租户 有几个设置几个
                //db.GetConnection(i).Aop
            }
        );
    }
}

亚马逊云科技技术品牌专区

更多推荐

Kiro Editor 开发实战：使用 Cargo 构建、测试与性能优化指南

欢迎来到这篇终极指南，我们将深入探索如何使用Rust构建高性能的终端文本编辑器Kiro Editor。无论你是Rust新手还是经验丰富的开发者，这篇完整教程将带你了解如何利用Cargo工具链进行高效的开发、测试和性能优化，打造一款快速、轻量且功能强大的UTF-8文本编辑器。## 什么是Kiro Editor？Kiro Editor是一款使用Rust编写的极简终端文本编辑器，它最初是著名编辑