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

概述

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

实现思路

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

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

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

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

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

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

自定义特性类

       a、自定义特性的定义

    /// 
/// 自定义字段特性/// 
[AttributeUsage(AttributeTargets.Property, AllowMultiple = true, Inherited = false)]public class ORFieldMappingAttribute : Attribute{/// 
/// 属性和字段的对应/// 
/// 字段名称/// 是否自增/// 有没有逗号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; }}/// 
/// 自定义表特性/// 
[AttributeUsage(AttributeTargets.Class, AllowMultiple = false, Inherited = false)]public class ORTableMappingAttribute : Attribute{/// 
/// 类名和表明的对应/// 
/// 表名public ORTableMappingAttribute(string strTableName){this.strTableName = strTableName;}public string strTableName { get; set; }}

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

解析自定义特性类

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

        /// 
/// 获得实体的表名/// 
/// 实体的type对象/// 实体对象对应的表名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;}/// 
/// 获得实体属性对应的字段,并给字段赋值/// 
/// 实体的type对象/// 字典：key=字段名；value=字段值public static Dictionary GetFieldName(T obj){Dictionary dic = new Dictionary();Type type = obj.GetType();PropertyInfo[] pis = type.GetProperties();  for (int i = 0; i < pis.Length; i++){object[] Attarrs = pis[i].GetCustomAttributes(false);for (int j = 0; j < Attarrs.Length; j++){if (Attarrs[j] is ORFieldMappingAttribute){ORFieldMappingAttribute fn = Attarrs[j] as ORFieldMappingAttribute;if (fn.IsAutoIncreate != true){if (fn.ORFieldValueHaveCommaEnum.ToString() == "True"){dic.Add(fn.strFieldName, "'" + pis[i].GetValue(obj, null).ToString() + "'");}else{dic.Add(fn.strFieldName, pis[i].GetValue(obj, null).ToString());}}}}}return dic;}}

常量和枚举

       定义如下常量和枚举类型

    /// 
/// 逻辑运算符/// 
public class LogicOperatorConst{public const string And = "and";public const string Or = "or";public const string None = "";}/// 
/// 字段的排序方向定义/// 
public class FieldSortConst{public const string Asc = "asc";public const string Desc = "desc";}/// 
/// 比较运算符/// 
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";}/// 
/// 是否有单引号/// 
public enum ORFieldValueHaveCommaEnum{False = 0, True = 1};

定义Where和Order生成对象

    public abstract class SqlClauseBuilder{//便于之后的扩展：ToSqlString(ISqlBuilder sqlBuilder)public abstract string ToSqlString();}/// 
/// 拼接Where后的条件语句/// 
public class WhereSqlClauseBuilder : SqlClauseBuilder{private Dictionary dicSqlConditions = new Dictionary();/// 
/// 添加条件/// 
/// 字段名/// 字段值/// 比较运算符/// 连接符(and or none)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);}/// 
/// 生成Sql语句/// 
/// public override string ToSqlString(){StringBuilder sb = new StringBuilder();foreach (var item in dicSqlConditions){sb.Append(item.Key.GetOperationItem() + " " + item.Value);}return sb.ToString();}}/// 
/// 单个where项/// 
public class SqlConditionItem{private string strFieldName;private string strFieldValue;private string strCompareOperation;/// 
/// 以字符串的形式获得条件/// 
/// 单个条件的字符串public string GetOperationItem(){StringBuilder sb = new StringBuilder();sb.Append(" " + strFieldName + " " + strCompareOperation + " " + strFieldValue);return sb.ToString();}/// 
/// 赋值/// 
/// 字段名/// 字段值/// 比较运算符public void SetOperationItem(string strFieldName, string strFieldValue, string strCompareOperation){this.strFieldName = strFieldName;this.strCompareOperation = strCompareOperation;this.strFieldValue = strFieldValue;}}/// 
/// 拼接OrderBy后的条件语句/// 
public class OrderBySqlClauseBuilder : SqlClauseBuilder{private Dictionary dicOrderConditions = new Dictionary();public void AppendItem(string strDataField, string strFieldSort = FieldSortConst.Asc){if (dicOrderConditions.Count > 0){dicOrderConditions.Add(", " + strDataField, strFieldSort);}else {dicOrderConditions.Add(strDataField, strFieldSort);}}public override string ToSqlString(){StringBuilder sb = new StringBuilder();foreach (var item in dicOrderConditions){sb.Append(item.Key + " " + item.Value);}return sb.ToString();}}

定义整合类

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

    public class DataManager where T : class,new(){#region 增加/// 
/// 添加/// 
/// 实体对象/// SQL语句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 删除/// 
/// 全部删除/// 
/// 这个类型的对象/// 操作影响行数public static int Delete(T obj){return Delete(obj, null);}/// 
/// 带有条件的删除/// 
/// 被删除对象/// 条件/// 操作影响行数public static int Delete(T obj, Action 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 查询/// 
/// 无条件，无排序的查询/// 
/// lambda表达式/// 集合public static List Load(){return Load(null,null);}/// 
/// 有条件，无排序的查询/// 
/// lambda表达式/// 集合public static List Load(Action whereSqlClauseBuilder){return Load(whereSqlClauseBuilder, null);}/// 
/// 无条件，有排序的查询/// 
/// lambda表达式/// 集合public static List Load(Action orderBySqlClauseBuilder){return Load(null, orderBySqlClauseBuilder);}/// 
/// 有条件，有排序的查询/// 
/// whereSqlClauseBuilder/// orderBySqlClauseBuilder/// 集合public static List Load(Action whereSqlClauseBuilder, Action 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(dt);}#endregion#region 修改/// 
/// 更新/// 
/// 更新的对象/// 条件/// public static int Update(T obj, Action 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.GetTableName();return strTableName;}//insert所用的字段名和相应值private static void GetInsertFieldAndValue(T obj, out string strFieldNames, out string strFieldValues){Dictionary dic = AttributeManager.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 dic = AttributeManager.GetFieldName(obj);strFieldNameAndValue = "";foreach (var item in dic){strFieldNameAndValue = strFieldNameAndValue + item.Key + " = " + item.Value + " ,";}if (strFieldNameAndValue.Length > 0){strFieldNameAndValue = strFieldNameAndValue.Substring(0, strFieldNameAndValue.Length-1);}}#endregion}

测试 

        static void Main(string[] args){//Father father1 = DataManager.Load().First();List father2 = DataManager.Load(p =>{p.Append("Id", "2", CompareOperationConst.EqualTo, LogicOperatorConst.None);});//List 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 userList = ORMapping.ToList(dt);#endregion#region insert语句int insertCount = DataManager.Add(user);#endregion#region delete删除int intDelete1 = DataManager.Delete(user);int intDelete2 = DataManager.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 userList1 = DataManager.Load();List userList2 = DataManager.Load(p =>{p.Append("Id", "2", CompareOperationConst.EqualTo, LogicOperatorConst.And);p.Append("Name", "女", CompareOperationConst.Like, LogicOperatorConst.None);});#endregion#region update语句int intUpdateCount1 = DataManager.Update(user);int intUpdateCount2 = DataManager.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);});#endregionConsole.ReadKey();}

总结

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

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！