首页 > 代码库 > 自定义ORMapping—动态生成SQL语句

自定义ORMapping—动态生成SQL语句

概述

       之前在自定义ORMapping——关系表转换为实体或实体集合对象中提到过ORMapping的东西,在那片博客中也有ORMapping实现的一个简单思路,当时只实现了关系表转换为实体或实体集合这个功能,没有实现动态生成SQL这个部分,本片博客就是完善之前的那片博客,实现动态生成SQL语句这么一个功能。


实现思路

       1、创建两个自定义特性,分别为表特性和字段特性,目的就是给相应的实体类的类名和属性名,打上相应的特性,从而创建类名和表名,属性和表字段名之间的对应关系

       2、创建一个特性解析类,用来解析,这个实体类和表之间的对应关系,即获得这种对应关系

       3、创建相应常量类和枚举,常量类用来生成相应的各种运算符或者排序时的关键字,枚举用来说明,生成字段对应的value是否需要添加引号

       4、创建相应的where,order生成器类,用来添加相应的条件

       5、创建一个整合类,将上面说的那些东西,整个为一个整体,生成相应的SQL语句,并且执行,并将返回的DataTable转换为集合对象

       下面的每块内容就是相应的实现


自定义特性类

       a、自定义特性的定义

    /// <summary>
    /// 自定义字段特性
    /// </summary>
    [AttributeUsage(AttributeTargets.Property, AllowMultiple = true, Inherited = false)]
    public class ORFieldMappingAttribute : Attribute
    {
        /// <summary>
        /// 属性和字段的对应
        /// </summary>
        /// <param name="strFieldName">字段名称</param>
        /// <param name="IsAutoIncreate">是否自增</param>
        /// <param name="ORFieldValueHaveCommaEnum">有没有逗号</param>
        public ORFieldMappingAttribute(string strFieldName, bool IsAutoIncreate = false, ORFieldValueHaveCommaEnum ORFieldValueHaveCommaEnum = ORFieldValueHaveCommaEnum.True)
        {
            this.strFieldName = strFieldName;
            this.ORFieldValueHaveCommaEnum = ORFieldValueHaveCommaEnum;
            this.IsAutoIncreate = IsAutoIncreate;
        }

        public string strFieldName { get; set; }
        public ORFieldValueHaveCommaEnum ORFieldValueHaveCommaEnum { get; set; }
        public bool IsAutoIncreate { get; set; }
    }

    /// <summary>
    /// 自定义表特性
    /// </summary>
    [AttributeUsage(AttributeTargets.Class, AllowMultiple = false, Inherited = false)]
    public class ORTableMappingAttribute : Attribute
    {
        /// <summary>
        /// 类名和表明的对应
        /// </summary>
        /// <param name="strTableName">表名</param>
        public ORTableMappingAttribute(string strTableName)
        {
            this.strTableName = strTableName;
        }

        public string strTableName { get; set; }
    }
       b、自定义特性的使用,使用在具体的一个实体类上,具体如下:

<span style="font-size:12px;">    [ORTableMapping("T_Users")]
    public  class User
    {
        [ORFieldMapping("Id",true,ORFieldValueHaveCommaEnum.False)]
        public int UserId { get; set; }

        [ORFieldMapping("Name",false,ORFieldValueHaveCommaEnum.True)]
        public string UserName { get; set; }

        [ORFieldMapping("Sex", false, ORFieldValueHaveCommaEnum.True)]
        public string UserSex { get; set; }

        [ORFieldMapping("Address", false, ORFieldValueHaveCommaEnum.True)]
        public string Addr { get; set; }

        [ORFieldMapping("Contents", false, ORFieldValueHaveCommaEnum.True)]
        public string Content { get; set; }
   }</span>

解析自定义特性类

       a、解析自定义特性类的代码如下

<span style="font-size:12px;">        /// <summary>
        /// 获得实体的表名
        /// </summary>
        /// <param name="obj">实体的type对象</param>
        /// <returns>实体对象对应的表名</returns>
        public static string GetTableName()
        {
            T obj = new T();
            Type type = obj.GetType();

            string strTableName="";
            object[] Attarrs = type.GetCustomAttributes(false);
            for (int i = 0; i < Attarrs.Length; i++)
            {
                if (Attarrs[i] is ORTableMappingAttribute)
                {
                    ORTableMappingAttribute attribute = Attarrs[i] as ORTableMappingAttribute;
                    strTableName = attribute.strTableName;
                }
            }
            return strTableName;
        }

        /// <summary>
        /// 获得实体属性对应的字段,并给字段赋值
        /// </summary>
        /// <param name="obj">实体的type对象</param>
        /// <returns>字典:key=字段名;value=http://www.mamicode.com/字段值>      该类是在生成SQL语句时使用的,只要你的实体类用上相应的特性,只要你把这个类型传递给这个解析类,他就可以给你解决出该类的类名和属性名,与数据库中的表名和字段名的对应关系


常量和枚举

       定义如下常量和枚举类型

<span style="font-size:12px;">    /// <summary>
    /// 逻辑运算符
    /// </summary>
    public class LogicOperatorConst
    {
        public const string And = "and";
        public const string Or = "or";
        public const string None = "";
    }

    /// <summary>
    /// 字段的排序方向定义
    /// </summary>
    public class FieldSortConst
    {
        public const string Asc = "asc";
        public const string Desc = "desc";
    }

    /// <summary>
    /// 比较运算符
    /// </summary>
    public class CompareOperationConst
    {
        //条件项的运算符常量定义
        public const string EqualTo = "=";
        public const string GreaterThanOrEqualTo = ">=";
        public const string GreaterThan = ">";
        public const string LessThanOrEqualTo = "<=";
        public const string LessThan = "<";
        public const string NotEqualTo = "<>";
        public const string Like = "LIKE";
        public const string Is = "IS";
        public const string In = "IN";
    }

    /// <summary>
    /// 是否有单引号
    /// </summary>
    public enum ORFieldValueHaveCommaEnum
    {
        False = 0, True = 1
    };</span>
        定义这些常量和枚举是为了帮助我们生成相应SQL语句时,使用


定义Where和Order生成对象

<span style="font-size:12px;">    public abstract class SqlClauseBuilder
    {
        //便于之后的扩展:ToSqlString(ISqlBuilder sqlBuilder)
        public abstract string ToSqlString();
    }

    /// <summary>
    /// 拼接Where后的条件语句
    /// </summary>
    public class WhereSqlClauseBuilder : SqlClauseBuilder
    {
        private Dictionary<SqlConditionItem, string> dicSqlConditions = new Dictionary<SqlConditionItem, string>();

        /// <summary>
        /// 添加条件
        /// </summary>
        /// <param name="strFieldName">字段名</param>
        /// <param name="strFieldValue">字段值</param>
        /// <param name="strCompareOperation">比较运算符</param>
        /// <param name="strLogicOperation">连接符(and or none)</param>
        public void Append(string strFieldName, string strFieldValue, string strCompareOperation = CompareOperationConst.EqualTo, string strLogicOperation = LogicOperatorConst.None)
        {
            SqlConditionItem item = new SqlConditionItem();
            item.SetOperationItem(strFieldName, strFieldValue, strCompareOperation);
            dicSqlConditions.Add(item, strLogicOperation);
        }

        /// <summary>
        /// 生成Sql语句
        /// </summary>
        /// <returns></returns>
        public override string ToSqlString()
        {
            StringBuilder sb = new StringBuilder();
            foreach (var item in dicSqlConditions)
            {
                sb.Append(item.Key.GetOperationItem() + " " + item.Value);
            }
            return sb.ToString();
        }
    }

    /// <summary>
    /// 单个where项
    /// </summary>
    public class SqlConditionItem
    {
        private string strFieldName;
        private string strFieldValue;
        private string strCompareOperation;

        /// <summary>
        /// 以字符串的形式获得条件
        /// </summary>
        /// <returns>单个条件的字符串</returns>
        public string GetOperationItem()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" " + strFieldName + " " + strCompareOperation + " " + strFieldValue);
            return sb.ToString();
        }

        /// <summary>
        /// 赋值
        /// </summary>
        /// <param name="strFieldName">字段名</param>
        /// <param name="strFieldValue">字段值</param>
        /// <param name="strCompareOperation">比较运算符</param>
        public void SetOperationItem(string strFieldName, string strFieldValue, string strCompareOperation)
        {
            this.strFieldName = strFieldName;
            this.strCompareOperation = strCompareOperation;
            this.strFieldValue = http://www.mamicode.com/strFieldValue;>        使用这些对象帮助我们生成相应的SQL语句中的where和order部分,当然,也可以定义其他的


定义整合类

       整合类是将上述运用起来形成的一个整体,从而实现增删该查这些功能,具体如下

<span style="font-size:12px;">    public class DataManager<T> where T : class,new()
    {
        #region 增加
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="obj">实体对象</param>
        /// <returns>SQL语句</returns>
        public static int Add(T obj)
        {
            StringBuilder sb = new StringBuilder();
            string strTableName = GetTableName();

            string strFieldNames;
            string strFieldValues;
            GetInsertFieldAndValue(obj, out strFieldNames, out strFieldValues);
            sb.AppendFormat("insert into {0}({1}) values({2})", strTableName, strFieldNames, strFieldValues);

            return SQLHelper.GetInstance().ExecuteNonQuery(sb.ToString(), CommandType.Text);
        }
        #endregion

        #region 删除
        /// <summary>
        /// 全部删除
        /// </summary>
        /// <param name="obj">这个类型的对象</param>
        /// <returns>操作影响行数</returns>
        public static int Delete(T obj)
        {
            return Delete(obj, null);
        }

        /// <summary>
        /// 带有条件的删除
        /// </summary>
        /// <param name="obj">被删除对象</param>
        /// <param name="whereSqlClauseBuilder">条件</param>
        /// <returns>操作影响行数</returns>
        public static int Delete(T obj, Action<WhereSqlClauseBuilder> whereSqlClauseBuilder)
        {
            StringBuilder sb = new StringBuilder();
            string strTableName = GetTableName();

            WhereSqlClauseBuilder w = new WhereSqlClauseBuilder();

            if (whereSqlClauseBuilder != null)
            {
                whereSqlClauseBuilder(w);
                sb.AppendFormat("delete from {0} where {1}", strTableName, w.ToSqlString());
            }
            else
            {
                sb.AppendFormat("delete from {0}", strTableName);
            }

            return SQLHelper.GetInstance().ExecuteNonQuery(sb.ToString(), CommandType.Text);
        }
        #endregion

        #region 查询
        /// <summary>
        /// 无条件,无排序的查询
        /// </summary>
        /// <param name="whereSqlClauseBuilder">lambda表达式</param>
        /// <returns>集合</returns>
        public static List<T> Load()
        {
            return Load(null,null);
        }

        /// <summary>
        /// 有条件,无排序的查询
        /// </summary>
        /// <param name="whereSqlClauseBuilder">lambda表达式</param>
        /// <returns>集合</returns>
        public static List<T> Load(Action<WhereSqlClauseBuilder> whereSqlClauseBuilder)
        {
            return Load(whereSqlClauseBuilder, null);
        }

        /// <summary>
        /// 无条件,有排序的查询
        /// </summary>
        /// <param name="whereSqlClauseBuilder">lambda表达式</param>
        /// <returns>集合</returns>
        public static List<T> Load(Action<OrderBySqlClauseBuilder> orderBySqlClauseBuilder)
        {
            return Load(null, orderBySqlClauseBuilder);
        }

        /// <summary>
        /// 有条件,有排序的查询
        /// </summary>
        /// <param name="whereSqlClauseBuilder">whereSqlClauseBuilder</param>
        /// <param name="orderBySqlClauseBuilder">orderBySqlClauseBuilder</param>
        /// <returns>集合</returns>
        public static List<T> Load(Action<WhereSqlClauseBuilder> whereSqlClauseBuilder, Action<OrderBySqlClauseBuilder> orderBySqlClauseBuilder)
        {
            StringBuilder sb = new StringBuilder();
            string strTableName = GetTableName();

            WhereSqlClauseBuilder w = new WhereSqlClauseBuilder();
            OrderBySqlClauseBuilder o = new OrderBySqlClauseBuilder();

            if (whereSqlClauseBuilder != null)
            {
                whereSqlClauseBuilder(w);
                sb.AppendFormat("select * from {0} where {1}", strTableName, w.ToSqlString());
            }
            else
            {
                sb.AppendFormat("select * from {0}", strTableName);
            }

            if (orderBySqlClauseBuilder != null) {
                orderBySqlClauseBuilder(o);
                sb.Append(" order by " + w.ToSqlString());
            }

            DataTable dt = SQLHelper.GetInstance().ExecuteQuery(sb.ToString(), CommandType.Text);
            return ORMapping.ToList<T>(dt);
        }
        #endregion

        #region 修改
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="obj">更新的对象</param>
        /// <param name="whereSqlClauseBuilder">条件</param>
        /// <returns></returns>
        public static int Update(T obj, Action<WhereSqlClauseBuilder> whereSqlClauseBuilder = null)
        {
            StringBuilder sb = new StringBuilder();
            string strTableName = GetTableName();

            string strFieldValues;
            GetUpdateFieldAndValue(obj, out strFieldValues);

            WhereSqlClauseBuilder w = new WhereSqlClauseBuilder();

            if (whereSqlClauseBuilder != null)
            {
                whereSqlClauseBuilder(w);
                sb.AppendFormat("update {0} set {1} Where {2}", strTableName, strFieldValues, w.ToSqlString());
            }
            else
            {
                sb.AppendFormat("update {0} set {1}", strTableName, strFieldValues);
            }

            return SQLHelper.GetInstance().ExecuteNonQuery(sb.ToString(), CommandType.Text);
        }
        #endregion

        #region 内部方法
        //获得表名
        private static string GetTableName()
        {
            T obj = new T();
            string strTableName = AttributeManager<T>.GetTableName();
            return strTableName;
        }

        //insert所用的字段名和相应值
        private static void GetInsertFieldAndValue(T obj, out string strFieldNames, out string strFieldValues)
        {
            Dictionary<string, string> dic = AttributeManager<T>.GetFieldName(obj);
            strFieldNames = "";
            strFieldValues = "";
            foreach (var item in dic)
            {
                strFieldNames = strFieldNames + "," + item.Key;
                strFieldValues = strFieldValues + "," + item.Value;
            }
            if (strFieldNames.Length > 0)
            {
                strFieldNames = strFieldNames.Substring(1);
                strFieldValues = strFieldValues.Substring(1);
            }
        }

        //insert所用的字段名和相应值
        private static void GetUpdateFieldAndValue(T obj, out string strFieldNameAndValue)
        {
            Dictionary<string, string> dic = AttributeManager<T>.GetFieldName(obj);
            strFieldNameAndValue = http://www.mamicode.com/"";>

测试 

<span style="font-size:12px;"></span><span style="font-size:12px;">   </span><span style="font-size:12px;">     static void Main(string[] args)
        {
            //Father father1 = DataManager<Father>.Load().First();

            List<Father> father2 = DataManager<Father>.Load(p =>
            {
                p.Append("Id", "2", CompareOperationConst.EqualTo, LogicOperatorConst.None);
            });

            //List<Son> sonList = father1.ListSon;

            User user = new User() {UserName="青山111",UserSex="男111", Addr="地址", Content="内容" };

            #region Table转换为实体
            //DataTable dt = new DataTable("T_Users");
            //dt.Columns.Add(new DataColumn("Id", typeof(string)));
            //dt.Columns.Add(new DataColumn("Name", typeof(string)));
            //dt.Columns.Add(new DataColumn("Sex", typeof(string)));
            
            ////1、创建行
            //DataRow dr = dt.NewRow();
            ////2、赋值行
            //dr["Id"] = "10040242041";
            //dr["Name"] = "青山";
            //dr["Sex"] = "青山";

            //dt.Rows.Add(dr);
            //List<User> userList = ORMapping.ToList<User>(dt);
            #endregion

            #region insert语句
            int insertCount = DataManager<User>.Add(user);
            #endregion

            #region delete删除
            int intDelete1 = DataManager<User>.Delete(user);

            int intDelete2 = DataManager<User>.Delete(user, p =>
            {
                p.Append("Id", "1", CompareOperationConst.EqualTo, LogicOperatorConst.And);
                p.Append("Name", "qingshan", CompareOperationConst.Like, LogicOperatorConst.Or);
                p.Append("Sex", "男", CompareOperationConst.Like, LogicOperatorConst.None);
            });
            #endregion

            #region select语句
            List<User> userList1 = DataManager<User>.Load();

            List<User> userList2 = DataManager<User>.Load(p =>
            {
                p.Append("Id", "2", CompareOperationConst.EqualTo, LogicOperatorConst.And);
                p.Append("Name", "女", CompareOperationConst.Like, LogicOperatorConst.None);
            });
            #endregion

            #region update语句
            int intUpdateCount1 = DataManager<User>.Update(user);

            int intUpdateCount2 = DataManager<User>.Update(user, p => {
                p.Append("Id", "1", CompareOperationConst.EqualTo, LogicOperatorConst.And);
                p.Append("Name", "qingshan", CompareOperationConst.Like, LogicOperatorConst.Or);
                p.Append("Sex", "男", CompareOperationConst.Like, LogicOperatorConst.None);
            });
            #endregion

            Console.ReadKey();
        }</span>

总结

       自定义ORMapping,主要完成两个功能,第一:SQL语句的生成和执行;第二:DataTable转换为相应的集合,本片博客和之前的那篇博客只是简单的完成了这个功能,具体的代码大家可以在这里下载。



自定义ORMapping—动态生成SQL语句