自定義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; }
}
b、自定義特性的使用，使用在具體的一個實體類上，具體如下：
[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;
}
}
該類是在生成SQL語句時使用的，只要你的實體類用上相應的特性，只要你把這個類型傳遞給這個解析類，他就可以給你解決出該類的類名和屬性名，與數據庫中的表名和字段名的對應關系
常量和枚舉
定義如下常量和枚舉類型
///
/// 邏輯運算符
///
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
};
定義這些常量和枚舉是為了幫助我們生成相應SQL語句時，使用
定義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();
}
} 使用這些對象幫助我們生成相應的SQL語句中的where和order部分，當然，也可以定義其他的
定義整合類
整合類是將上述運用起來形成的一個整體，從而實現增刪該查這些功能，具體如下
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);
});
#endregion
Console.ReadKey();
}
總結
自定義ORMapping，主要完成兩個功能，第一：SQL語句的生成和執行；第二：DataTable轉換為相應的集合，本片博客和之前的那篇博客只是簡單的完成了這個功能，具體的代碼大家可以在這裡下載。
