ORM框架Dapper详细教程

1,为什么要使用dapper:

性能优越: 其实在各大网站上,我们大概都会看到这样的一个对比效果图,在超过500次poco serialization的过程中所表现的性能,我们发现dapper是第二名

支持多数据库: 支持多数据库的本质是因为Dapper是对IDBConnection接口进行了方法扩展,比如我下面声明的扩展类 ,SqlConnection,MysqlConnection,OracleConnection都是继承于DBConnection,而DBConnection又是实现了IDBConnection的接口 下面实例是以sql server开发。

轻量级:通过实现IDBConnection的扩展方法 所有实例方法需要自己写 它是通过sql与表的映射功能

2 实例

首先创建一个.net core项目

Nuget安装dapper

ORM框架Dapper详细教程

新加一个类 调用dapper的帮助类

 public class DapperHelper
    {
        private string connectionStr;
        public DapperHelper()
        {
            //连接数据库的字符串 这里是sql server数据库
            connectionStr = "Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=LocalDB;Data Source=.";
        }
        public SqlConnection GetSqlConnection()
        {
            //连接 连接字符串 开启连接
            SqlConnection conn = new SqlConnection(connectionStr);
            conn.Open();
            return conn;
        }

        public List<T> Query<T>(string sql, List<T> model = null)
        {
            using (IDbConnection connection = GetSqlConnection())
            {
                return connection.Query<T>(sql, model).ToList();
            }
        }

        public List<T> QueryInid<T>(string sql, string[] c_ids)
        {
            var maxParametersSize = 2000;
            var c_idsReplace = c_ids.ToList().Distinct().ToArray();
            using (IDbConnection connection = GetSqlConnection())
            {
                if (c_ids.Length > maxParametersSize)
                {
                    List<T> res = new List<T>();

                    var roundNumber = c_ids.Length / maxParametersSize + 1;
                    for (int i = 0; i < roundNumber; i++)
                    {
                        c_ids = c_idsReplace.Skip(maxParametersSize * i).Take(maxParametersSize).ToArray();
                        res.AddRange(connection.Query<T>(sql, new { c_ids }));
                    }

                    return res;
                }
                else
                {
                    return connection.Query<T>(sql, new { c_ids }).ToList();
                }
            }
        }

        /// <summary>
        ///根据sql查询 
        /// </summary>
        /// <typeparam name="T">查询语句需要映射的类</typeparam>
        /// <param name="sql">查询语句</param>
        /// <returns></returns>
        public T Select<T>(string sql)
        {
            using (IDbConnection connection = GetSqlConnection())
            {
                //前面的控制器使用了该方法 为什么可以  直接使用QueryFirstOrDefault  
                //是因为dapper方法继承了this IDbConnection connection的参数
                //该dapper声明的方法 属于IDbConnection的扩展方法
                return connection.QueryFirstOrDefault<T>(sql);
            }
        }
        /// <summary>
        /// 查询sql返回的行数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int Exec(string sql)
        {
            using (IDbConnection connection = GetSqlConnection())
            {
                return connection.Execute(sql);
            }
        }

        public int Exec(string sql, object model)
        {
            using (IDbConnection connection = GetSqlConnection())
            {
                return connection.Execute(sql, model);
            }
        }
        /// <summary>
        /// 是否需要执行事务 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="model"></param>
        /// <param name="useTransaction"></param>
        /// <returns></returns>
        public int Exec(string sql, object model, bool useTransaction = false)
        {
            using (IDbConnection connection = GetSqlConnection())
            {
                if (useTransaction)
                {
                    var tran = BeginTransaction(connection);
                    return connection.Execute(sql, model, tran);
                }
                else
                {
                    return connection.Execute(sql, model);
                }

            }
        }


        /// <summary>
        /// 必须是  string @c_ids
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="c_id"></param>
        /// <returns></returns>
        public List<T> QueryIn<T>(string sql, string[] c_ids)
        {
            using (IDbConnection connection = GetSqlConnection())
            {
                return connection.Query<T>(sql, new { c_ids }).ToList();
            }
        }

        /// <summary>
        /// 必须是 int @ids
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<T> QueryIn<T>(string sql, int[] ids)
        {
            using (IDbConnection connection = GetSqlConnection())
            {
                return connection.Query<T>(sql, new { ids }).ToList();
            }
        }
        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        private IDbTransaction BeginTransaction(IDbConnection conn)
        {
            IDbTransaction tran = conn.BeginTransaction();
            return tran;
        }
        /// <summary>
        /// 扩展方法 插入表  在进行数据批量导库的时候 比单次一条条存库 (单次导入 需要每次都开启数据库连接 )速度提高
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="tablename"></param>
        public void InsertTable(DataTable dt, string tablename)
        {
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionStr))
            {
                bulkCopy.DestinationTableName = tablename;
                bulkCopy.BatchSize = dt.Rows.Count;
                bulkCopy.WriteToServer(dt);
            }
        }
    }

使用 在控制器中实现的代码块 下面主要举例了两种方法 一个是基础查询 还有一个是批量插入库

           #region 调用dapper
            //数据库  普通查询
            var Orm = new DapperHelper();
            var locallist = Orm.Select<Local>("select * from Local");


            //调用扩展 使用SqlBulkCopy 批量存库
            List<Local> ts = new List<Local>();
            Local locala = new Local();
            locala.ID = Guid.NewGuid();
            locala.sendtime = "kanfkasf";
            locala.backtime = "kanfkasf";
            locala.status = 0;
            Local localb = new Local();
            localb.ID = Guid.NewGuid();
            localb.sendtime = "dfafaf";
            localb.backtime = "dfssfd";
            localb.status = 2;
            ts.Add(locala);
            ts.Add(localb);
            Orm.InsertTable(ListToDataTable(ts), "Local");
            #endregion

上面的批量入库的方法中调用了 List转datatable的方法

     /// <summary>
        /// list转table
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entitys"></param>
        /// <returns></returns>
        public static DataTable ListToDataTable<T>(List<T> entitys)
        {

            //检查实体集合不能为空
            if (entitys == null || entitys.Count < 1)
            {
                return new DataTable();
            }

            //取出第一个实体的所有Propertie
            Type entityType = entitys[0].GetType();
            PropertyInfo[] entityProperties = entityType.GetProperties();

            //生成DataTable的structure
            //生产代码中,应将生成的DataTable结构Cache起来,此处略
            DataTable dt = new DataTable("dt");
            for (int i = 0; i < entityProperties.Length; i++)
            {
                Type colType = entityProperties[i].PropertyType;
                if (colType.IsGenericType && colType.GetGenericTypeDefinition() == typeof(Nullable<>))
                {
                    colType = colType.GetGenericArguments()[0];
                }
                //dt.Columns.Add(entityProperties[i].Name, entityProperties[i].GetMethod);
                dt.Columns.Add(entityProperties[i].Name, colType);
            }

            //将所有entity添加到DataTable中
            foreach (object entity in entitys)
            {
                //检查所有的的实体都为同一类型
                if (entity.GetType() != entityType)
                {
                    throw new Exception("要转换的集合元素类型不一致");
                }
                object[] entityValues = new object[entityProperties.Length];
                for (int i = 0; i < entityProperties.Length; i++)
                {
                    entityValues[i] = entityProperties[i].GetValue(entity, null);

                }
                dt.Rows.Add(entityValues);
            }
            return dt;
        }
上一篇:Node.js 操作MySQL


下一篇:Sprinboot 整合 RestTemplate 调用 REST 服务