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
    }
}

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; }
    }
}

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;
        }
    }
}

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;
        }
    }
}

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
    {
    }
}

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
    {
    }
}

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; }
    }
}

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();
        }
    }
}

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();
        }
    }
}