使用Dapper对MySQL进行增删改查

首先使用NuGet添加Dapper和Mysql.Data的引用；

using Dapper;
using MySql.Data.MySqlClient;

创建数据库表：

建立对应的实体类：

    public class TestEntity
    {
        public int ID { get; set; }
        public DateTime DateTime { get; set; }
        public string Name { get; set; }
        public string PhoneNumber { get; set; }
        public string Address { get; set; }
        public string Role { get; set; }
    }

建立DapperTool类，用以对数据库进行操作：

private static string conStr = "server=localhost;database=test;uid=root;pwd=123456";

增删改的函数：

        /// <summary>
        /// 增删改均可
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="t"></param>
        /// <returns></returns>
        public static bool Excute<T>(string sql, T t)
            where T : class
        {
            using (IDbConnection conn = new MySqlConnection(conStr))
            {
                return conn.Execute(sql, t) > 0;
            }
        }

批量增删改的函数：

        /// <summary>
        /// 增删改均可[批量]
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="tlist"></param>
        /// <returns></returns>
        public static bool Excute<T>(string sql, List<T> tlist)
            where T : class
        {
            using (IDbConnection conn = new MySqlConnection(conStr))
            {
                return conn.Execute(sql, tlist) > 0;
            }
        }

查询函数：

        /// <summary>
        /// 查找
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static List<T> Query<T>(string sql)
            where T : class
        {
            using (IDbConnection conn = new MySqlConnection(conStr))
            {
                return conn.Query<T>(sql).ToList();
            }
        }

计数函数：

        /// <summary>
        /// 计数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int GetCountRow(string sql)
        {
            using (var connection = new MySqlConnection(conStr))
            {
                return connection.ExecuteScalar<int>(sql);
            }
        }

分页查询：

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="orderBy"></param>
        /// <param name="start"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public static IEnumerable<T> GetPagedData<T>(string sql, string orderBy, int start, int count)
            where T : class
        {
            using (var connection = new MySqlConnection(conStr))
            {
                string pageSql = null;
                orderBy = string.IsNullOrEmpty(orderBy) ? " " : string.Format(@" ORDER BY {0} ", orderBy);
                pageSql = string.Format(@"{0} {1} LIMIT @StartNum, @RetCount", sql, orderBy);
                return connection.Query<T>(pageSql, new { StartNum = start, RetCount = count });
            }
        }

建立库表类，用以对表进行具体操作：

private static string tableName = "personalfile";

添加数据：

        public static void AddMembers()
        {
            var list = new List<TestEntity>();
            for (int i = 0; i < 5; i++)
            {
                list.Add(new TestEntity()
                {
                    ID = i,
                    DateTime = DateTime.Now.AddDays(0 - i),
                    Name = "测试" + i,
                    Address = "地址" + i,
                    Role = "身份" + i,
                    PhoneNumber = Math.Pow(2, i).ToString(),
                    Added = "多余数据"
                });
            }
            string sql = "Insert into " + tableName + @" (`ID`,`Name`,Role,PhoneNumber,Address,DateTime)
                        Values
                        (@ID,@Name,@Role,@PhoneNumber,@Address,@DateTime)";
            DapperTool.Excute(sql, list);
        }

删除数据：

        public static void Delete()
        {
            TestEntity entity = new TestEntity() { ID = 2 };
            string sql = "Delete From " + tableName + " where `ID`>=@ID";
            DapperTool.Excute(sql, entity);
        }

修改数据：

        public static void Update()
        {
            List<TestEntity> list = new List<TestEntity>();
            list.Add(new TestEntity() { ID = 1, Name = "修改Name1" });
            list.Add(new TestEntity() { ID = 0, Name = "修改Name0" });
            string sql = "Update " + tableName + " set `Name`=@Name Where `ID`=@ID";
            DapperTool.Excute(sql, list);
        }

查询数据：

        public static List<TestEntity> Query()
        {
            string sql = "Select * From " + tableName + " Where 1=1";
            return DapperTool.Query<TestEntity>(sql);
        }

查询数量：

        public static int GetCount()
        {
            string sql = "Select Count(*) From " + tableName + " where 1=1";
            return DapperTool.GetCountRow(sql);
        }

调用进行验证：

    static void Main(string[] args)
    {
        //*********************
        int count = TestTable.GetCount();
        Console.WriteLine("表初始数据数量:" + count);
        //*********************
        TestTable.AddMembers();
        Console.WriteLine("添加5条数据:");
        var list = TestTable.Query();
        list.ForEach(l =>
        {
            Console.WriteLine(string.Format("ID:{0} Name:{1} Role:{2} Number:{3} Address:{4}", l.ID, l.Name, l.Role, l.PhoneNumber, l.Address));
        });
        //*********************
        TestTable.Delete();
        Console.WriteLine("删除数据>=2后:");
        list = TestTable.Query();
        list.ForEach(l =>
        {
            Console.WriteLine(string.Format("ID:{0} Name:{1} Role:{2} Number:{3} Address:{4}", l.ID, l.Name, l.Role, l.PhoneNumber, l.Address));
        });
        //*********************
        TestTable.Update();
        Console.WriteLine("更改数据后:");
        list = TestTable.Query();
        list.ForEach(l =>
        {
            Console.WriteLine(string.Format("ID:{0} Name:{1} Role:{2} Number:{3} Address:{4}", l.ID, l.Name, l.Role, l.PhoneNumber, l.Address));
        });
        //*********************
        Console.ReadLine();
    }

结果：