[转载].net下开源轻量级ORM框架Dapper扩展系列1 - 树上的蜗牛 - 博客园

baacloud免费翻墙vpn注册使用

[转载].net下开源轻量级ORM框架Dapper扩展系列1 – 树上的蜗牛 – 博客园.

轻量级ORM框架Dapper相信很多人了解,也用过,可能也有很多人可能还不知道
Dapper官网:https://code.google.com/p/dapper-dot-net/
我在网上复制一下别人对Dapper的描述:

Dapper是一个轻型的ORM类。代码就一个SQLMapper.cs文件。文件见下。编译后就40K的一个很小的Dll.

Dapper很快,有多快。实验下就知道了。官方给了点测试包,想玩的时候就去测试下。Dapper的速度接近与IDataReader,取列表的数据超过了DataTable。

Dapper支持什么数据库。Dapper支持MySQL,SQLLite,Mssql2000,Mssql2005,Oracle等一系列的数据库,当然如果你知道原理也可以让它支持Mongo db.

Dapper的r支持多表并联的对象。支持一对多 多对多的关系。并且没侵入性,想用就用,不想用就不用。无XML无属性。代码以前怎么写现在还怎么写。

Dapper原理通过Emit反射IDataReader的序列队列,来快速的得到和产生对象。性能实在高。

Dapper支持net2.0,3.0,3.5,4.0。

Dapper的语法是这样的。语法十分简单。并且无须迁就数据库的设计。

为什么要扩展Dapper:
了解Dapper都知道,在书写代码时,我们还是会手动写SQL,扩展的目的就是在完全不改变dapper源代码和使用基础上,进行一次封闭,达到零SQL,实现完全对象操作。
接下来,我们开始Dapper的扩展之旅第一章。。。
下载Dapper后,我们新建类库项目:DapperEx ,并把Dapper项目加载到项目中:

1.在DapperEx 中添加Dapper引用 ,如下:

2.为了存储数据库类型,以及根据数据库类型,使用不同的参数化操作数据库,添加一个DbBase类:

复制代码
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;

namespace Dapper
{
    public class DbBase : IDisposable
    {
        private string paramPrefix = "@";
        private string providerName = "System.Data.SqlClient";
        private IDbConnection dbConnecttion;
        private DbProviderFactory dbFactory;
        private DBType _dbType = DBType.SqlServer;
        public IDbConnection DbConnecttion
        {
            get
            {
                return dbConnecttion;
            }
        }
        public string ParamPrefix
        {
            get
            {
                return paramPrefix;
            }
        }
        public string ProviderName
        {
            get
            {
                return providerName;
            }
        }
        public DBType DbType
        {
            get
            {
                return _dbType;
            }
        }
        public DbBase(string connectionStringName)
        {
            var connStr = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
            if (!string.IsNullOrEmpty(ConfigurationManager.ConnectionStrings[connectionStringName].ProviderName))
                providerName = ConfigurationManager.ConnectionStrings[connectionStringName].ProviderName;
            else
                throw new Exception("ConnectionStrings中没有配置提供程序ProviderName!");
            dbFactory = DbProviderFactories.GetFactory(providerName);
            dbConnecttion = dbFactory.CreateConnection();
            dbConnecttion.ConnectionString = connStr;
            dbConnecttion.Open();
            SetParamPrefix();
        }

        private void SetParamPrefix()
        {
            string dbtype = (dbFactory == null ? dbConnecttion.GetType() : dbFactory.GetType()).Name;

            // 使用类型名判断
            if (dbtype.StartsWith("MySql")) _dbType = DBType.MySql;
            else if (dbtype.StartsWith("SqlCe")) _dbType = DBType.SqlServerCE;
            else if (dbtype.StartsWith("Npgsql")) _dbType = DBType.PostgreSQL;
            else if (dbtype.StartsWith("Oracle")) _dbType = DBType.Oracle;
            else if (dbtype.StartsWith("SQLite")) _dbType = DBType.SQLite;
            else if (dbtype.StartsWith("System.Data.SqlClient.")) _dbType = DBType.SqlServer;
            // else try with provider name
            else if (providerName.IndexOf("MySql", StringComparison.InvariantCultureIgnoreCase) >= 0) _dbType = DBType.MySql;
            else if (providerName.IndexOf("SqlServerCe", StringComparison.InvariantCultureIgnoreCase) >= 0) _dbType = DBType.SqlServerCE;
            else if (providerName.IndexOf("Npgsql", StringComparison.InvariantCultureIgnoreCase) >= 0) _dbType = DBType.PostgreSQL;
            else if (providerName.IndexOf("Oracle", StringComparison.InvariantCultureIgnoreCase) >= 0) _dbType = DBType.Oracle;
            else if (providerName.IndexOf("SQLite", StringComparison.InvariantCultureIgnoreCase) >= 0) _dbType = DBType.SQLite;

            if (_dbType == DBType.MySql && dbConnecttion != null && dbConnecttion.ConnectionString != null && dbConnecttion.ConnectionString.IndexOf("Allow User Variables=true") >= 0)
                paramPrefix = "?";
            if (_dbType == DBType.Oracle)
                paramPrefix = ":";
        }

        public void Dispose()
        {
            if (dbConnecttion != null)
            {
                try
                {
                    dbConnecttion.Dispose();
                }
                catch { }
            }
        }
    }
    public enum DBType
    {
        SqlServer,
        SqlServerCE,
        MySql,
        PostgreSQL,
        Oracle,
        SQLite
    }
}
复制代码

此类功能利用DbProviderFactories实现数据源连接接口IDbConnection,通过数据库判断设置参数前缀为@、?、:的一种。
3.和大多数实体映射一样,为了更方便的操作数据库,我们添加几个特性,来实现实体与数据库表的映射关系:

BaseAttribute:特性基类

复制代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Dapper
{
    public class BaseAttribute:Attribute
    {
        /// <summary>
        /// 别名,对应数据里面的名字
        /// </summary>
        public string Name { get; set; }
    }
}
复制代码

ColumnAttribute:字段列特性

复制代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Dapper
{
    /// <summary>
    /// 列字段
    /// </summary>
    [AttributeUsage(AttributeTargets.Field | AttributeTargets.Property)]
    public class ColumnAttribute : BaseAttribute
    {
        /// <summary>
        /// 自增长
        /// </summary>
        public bool AutoIncrement { get; set; }
        public ColumnAttribute()
        {
            AutoIncrement = false;
        }
        /// <summary>
        /// 是否是自增长
        /// </summary>
        /// <param name="autoIncrement"></param>
        public ColumnAttribute(bool autoIncrement)
        {
            AutoIncrement = autoIncrement;
        }
    }
}
复制代码

IdAttribute:主键列特性

复制代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Dapper
{
    /// <summary>
    /// 主键
    /// </summary>
    [AttributeUsage(AttributeTargets.Field | AttributeTargets.Property)]
    public class IdAttribute : BaseAttribute
    {
        /// <summary>
        /// 是否为自动主键
        /// </summary>
        public bool CheckAutoId { get; set; }

        public IdAttribute()
        {
            this.CheckAutoId = false;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="checkAutoId">是否为自动主键</param>
        public IdAttribute(bool checkAutoId)
        {
            this.CheckAutoId = checkAutoId;
        }
    }
}
复制代码

IgnoreAttribute:忽略列特性

复制代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Dapper
{
    /// <summary>
    /// 忽略字段
    /// </summary>
    [AttributeUsage(AttributeTargets.Field | AttributeTargets.Property)]
    public class IgnoreAttribute:BaseAttribute
    {
    }
}
复制代码

TableAttribute:数据库表特性

复制代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Dapper
{
    /// <summary>
    /// 数据库表
    /// </summary>
    [AttributeUsage(AttributeTargets.Field | AttributeTargets.Property)]
    public class TableAttribute : BaseAttribute
    {
    }
}
复制代码

4.接下来建立一个生成SQL时参数里面的列名和对应值名称的对应类:ParamColumnModel

复制代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Dapper
{
    /// <summary>
    /// 生成SQL时参数里面的列名和对应值名称
    /// </summary>
    public class ParamColumnModel
    {
        /// <summary>
        /// 数据库列名
        /// </summary>
        public string ColumnName { get; set; }
        /// <summary>
        /// 对应类属性名
        /// </summary>
        public string FieldName { get; set; }
    }
}
复制代码

 

5.建立一个公共类,方便对特性、属性进行操作和取值等操作Common

复制代码
using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Text.RegularExpressions;

namespace DapperEx
{
    public class Common
    {

        /// <summary>
        /// 获取对象对应数据库表名
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static string GetTableName<T>()
        {
            var ty = typeof(T);
            var arri = ty.GetCustomAttributes(typeof(BaseAttribute), true).FirstOrDefault();
            if (arri is TableAttribute && (!string.IsNullOrEmpty((arri as BaseAttribute).Name)))
            {
                return (arri as BaseAttribute).Name;
            }
            return ty.Name;
        }
        /// <summary>
        /// 在没有指定排序时,获取一个默认的排序列
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static string GetDefaultOrderField<T>()
        {
            var name = "";
            foreach (var propertyInfo in typeof(T).GetProperties())
            {
                var arri = propertyInfo.GetCustomAttributes(typeof(BaseAttribute), true).FirstOrDefault();
                if (arri is IgnoreAttribute)
                {
                    arri = null;
                    continue;
                }
                name = (arri == null || string.IsNullOrEmpty((arri as BaseAttribute).Name)) ? propertyInfo.Name : (arri as BaseAttribute).Name;
                break;
            }
            return name;
        }
        /// <summary>
        /// 获取要执行SQL时的列,添加和修改数据时
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public static IList<ParamColumnModel> GetExecColumns<T>() where T : class
        {
            var columns = new List<ParamColumnModel>();
            foreach (var propertyInfo in typeof(T).GetProperties())
            {
                var arri = propertyInfo.GetCustomAttributes(typeof(BaseAttribute), true).FirstOrDefault();
                if (arri is IgnoreAttribute)
                {
                    arri = null;
                    continue;
                }
                else if (arri is IdAttribute)
                {
                    if ((arri as IdAttribute).CheckAutoId)
                    {
                        arri = null;
                        continue;
                    }
                }
                else if (arri is ColumnAttribute)
                {
                    if ((arri as ColumnAttribute).AutoIncrement)
                    {
                        arri = null;
                        continue;
                    }
                }
                string name = (arri == null || string.IsNullOrEmpty((arri as BaseAttribute).Name)) ? propertyInfo.Name : (arri as BaseAttribute).Name;
                columns.Add(new ParamColumnModel() { ColumnName = name, FieldName = propertyInfo.Name });
            }
            return columns;
        }
        /// <summary>
        /// 获取对象的主键标识列
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="PropertyName">对应实体属性名</param>
        /// <returns></returns>
        public static string GetPrimaryKey<T>(out string PropertyName) where T : class
        {
            string name = "";
            PropertyName = "";
            foreach (var propertyInfo in typeof(T).GetProperties())
            {
                var arri = propertyInfo.GetCustomAttributes(typeof(BaseAttribute), true).FirstOrDefault();
                if (arri is IdAttribute)
                {
                    name = (arri == null || string.IsNullOrEmpty((arri as BaseAttribute).Name)) ? propertyInfo.Name : (arri as BaseAttribute).Name;
                    PropertyName = propertyInfo.Name;
                    break;
                }

            }
            if (string.IsNullOrEmpty(PropertyName))
            {
                throw new Exception("没有任何列标记为主键特性");
            }
            return name;
        }
        /// <summary>
        /// 通过属性名获取对应的数据列名
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="propertyName"></param>
        /// <returns></returns>
        public static string GetExecCloumName<T>(string propertyName) where T : class
        {
            var propertyInfo = typeof(T).GetProperty(propertyName);

            var arri = propertyInfo.GetCustomAttributes(typeof(BaseAttribute), true).FirstOrDefault();
            if (arri is IgnoreAttribute)
            {
                arri = null;
            }
            string name = (arri == null || string.IsNullOrEmpty((arri as BaseAttribute).Name)) ? propertyInfo.Name : (arri as BaseAttribute).Name;
            return name;
        }
        /// <summary>
        /// 通过表达示树获取属性名对应列名
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="expr"></param>
        /// <returns></returns>
        public static string GetNameByExpress<T>(Expression<Func<T, object>> expr) where T : class
        {
            var pname = "";
            if (expr.Body is UnaryExpression)
            {
                var uy = expr.Body as UnaryExpression;
                pname = (uy.Operand as MemberExpression).Member.Name;
            }
            else
            {
                pname = (expr.Body as MemberExpression).Member.Name;
            }
            var propertyInfo = typeof(T).GetProperty(pname);
            var arri = propertyInfo.GetCustomAttributes(typeof(BaseAttribute), true).FirstOrDefault();
            if (arri is IgnoreAttribute)
            {
                throw new Exception(string.Format("{0}不能进行SQL处理", pname));
            }
            string name = (arri == null || string.IsNullOrEmpty((arri as BaseAttribute).Name)) ? propertyInfo.Name : (arri as BaseAttribute).Name;
            return name;
        }
        /// <summary>
        /// 字符串中连续多个空格合并成一个空格
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static string UnitMoreSpan(string str)
        {
            Regex replaceSpace = new Regex(@"\s{1,}", RegexOptions.IgnoreCase);
            return replaceSpace.Replace(str, " ").Trim();
        }
    }
}
复制代码

6.有了以上基础,现在开始进行扩展操作,新建DapperEx类:

本系列,我们先扩展两个方法:添加一个实体 和 批量添加
代码比较简单:

复制代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Dapper;
using System.Data;
using DapperEx;

namespace Dapper
{
    public static class DapperEx
    {
        /// <summary>
        /// 扩展插入数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="db"></param>
        /// <param name="t"></param>
        /// <param name="useTransaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public static bool Insert<T>(this DbBase dbs, T t, bool useTransaction = false, int? commandTimeout = null) where T : class,new()
        {
            var db = dbs.DbConnecttion;
            IDbTransaction tran = null;
            if (useTransaction)
                tran = db.BeginTransaction();
            var result = false;
            var tbName = Common.GetTableName<T>();
            var columns = Common.GetExecColumns<T>();

            var flag = db.Execute(CreateInertSql(tbName, columns, dbs.ParamPrefix), t, tran, commandTimeout);
            if (tran != null)
            {
                try
                {
                    tran.Commit();
                    result = true;
                }
                catch
                {
                    tran.Rollback();
                }
            }
            else
            {
                return flag == 1;
            }
            return result;
        }
        /// <summary>
        /// 批量插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="db"></param>
        /// <param name="lt"></param>
        /// <param name="useTransaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static bool InsertBatch<T>(this DbBase dbs, IList<T> lt, bool useTransaction = false, int? commandTimeout = null) where T : class,new()
        {
            var db = dbs.DbConnecttion;
            IDbTransaction tran = null;
            if (useTransaction)
                tran = db.BeginTransaction();
            var result = false;
            var tbName = Common.GetTableName<T>();
            var columns = Common.GetExecColumns<T>();
            var flag = db.Execute(CreateInertSql(tbName, columns, dbs.ParamPrefix), lt, tran, commandTimeout);
            if (tran != null)
            {
                try
                {
                    tran.Commit();
                    result = true;
                }
                catch
                {
                    tran.Rollback();
                }
            }
            else
            {
                return flag == lt.Count;
            }
            return result;
        }
        /// <summary>
        /// 组装插入语句
        /// </summary>
        /// <param name="tbName"></param>
        /// <param name="colums"></param>
        /// <returns></returns>
        private static string CreateInertSql(string tbName, IList<ParamColumnModel> colums, string ParamPrefix)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(string.Format("INSERT INTO {0}(", tbName));
            for (int i = 0; i < colums.Count; i++)
            {
                if (i == 0) sql.Append(colums[i].ColumnName);
                else sql.Append(string.Format(",{0}", colums[i].ColumnName));
            }
            sql.Append(")");
            sql.Append(" VALUES(");
            for (int i = 0; i < colums.Count; i++)
            {
                if (i == 0) sql.Append(string.Format("{0}{1}", ParamPrefix, colums[i].FieldName));
                else sql.Append(string.Format(",{0}{1}", ParamPrefix, colums[i].FieldName));
            }
            sql.Append(") ");
            return sql.ToString();
        }
    }
}
复制代码

今天要讲的都已经完成,接下来,我们进行使用测试:
在解决方案中添加一个测试库:DapperExTest,并在测试库中添加一个本地数据库:dbSqlCeEx.sdf,并在数据库中中添加一个表:Account

注意最后个一字段。Flag是一个字增长列.
在测试库中添加一个应用程序配置文件:App.config,并修改成自己相应的连接数据库字符串

在测试库中添加一个相应的实体类:Account

复制代码
namespace DapperExTest
{
    public class Account
    {
        [Id]
        public virtual string Id { get; set; }
        public virtual string Name { get; set; }
        public virtual string Password { get; set; }
        public virtual string Email { get; set; }
        public virtual DateTime CreateTime { get; set; }
        public virtual int Age { get; set; }
        [Column(true)]
        public virtual int Flag { get; set; }
        [Ignore]
        public virtual string AgeStr
        {
            get
            {
                return "年龄:" + Age;
            }
        }
    }
}
复制代码

 

 


上述工作完成后,在测试类UnitTest1中添加如下代码:

 public string connectionName = "strSqlCe";
        public DbBase CreateDbBase()
        {
            return new DbBase(connectionName);
        }

现在我们对添加功能,进行测试,添加方法:

复制代码
[TestMethod]
        public void Insert()//插入一条数据
        {
            var model = new Account()
            {
                Id = "1",
                Name = "张三1",
                Password = "123456",
                Email = "123@qq.com",
                CreateTime = DateTime.Now,
                Age = 15
            };
            using (var db = CreateDbBase())
            {
                var result = db.Insert<Account>(model);
                if (result)
                    Console.WriteLine("添加成功");
                else
                    Console.WriteLine("添加失败");
            }
        }
复制代码

右键运行测试,测试成功,成功添加一条数据:

现在测试批量添加:

复制代码
[TestMethod]
        public void InsertBatch()//插入多条数据
        {
            var list = new List<Account>();
            for (int i = 2; i < 21; i++)
            {
                var model = new Account()
                {
                    Id = i.ToString(),
                    Name = "张三" + i.ToString(),
                    Password = "123456",
                    Email = "123@qq.com",
                    CreateTime = DateTime.Now,
                    Age = 15
                };
                list.Add(model);
            }
            using (var db = CreateDbBase())
            {
                var result = db.InsertBatch<Account>(list, true);
                if (result)
                    Console.WriteLine("添加成功");
                else
                    Console.WriteLine("添加失败");
            }
        }
复制代码

运行完成后,查看数据库:

成功!!!

OK,今天的扩展到此结束,后续系列将会对修改、批量修改、删除、批量删除、查询、分页进行扩展
希望大家多多关注,觉得对自己有所帮助或有意见的,欢迎留言,觉得不错的,不要吝啬你的鼠标,点点支持,点点推荐,谢谢啦!!!

本系列源码:http://pan.baidu.com/s/1dDh4T7F

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏