using System;
|
using System.Collections;
|
using System.Collections.Generic;
|
using System.Data.Common;
|
using System.Linq;
|
using System.Reflection;
|
using System.Text;
|
using CSFramework.DB.Common;
|
|
namespace CSFramework.DB
|
{
|
/// <summary>
|
/// DbCommand命令生成器，根据对象模型(Type)自动生成三种DbCommand,支持MsSQL,MySQL,Oracle
|
/// </summary>
|
public class DbCommandGenerator
|
{
|
private IDatabase _db;
|
|
/// <summary>
|
/// 构造器
|
/// </summary>
|
/// <param name="db">CSFramework.DB数据库底层通用接口</param>
|
public DbCommandGenerator(IDatabase db)
|
{
|
_db = db;
|
}
|
|
/// <summary>
|
/// 创建Insert命令 - 处理实体对象数据
|
/// </summary>
|
/// <param name="model">对象类型</param>
|
/// <param name="ignoreFields">自增字段(或不支持更新的字段)数组</param>
|
/// <returns></returns>
|
public DbCommand GetInsertCommand<T>(T obj,
|
params string[] ignoreFields) where T : new()
|
{
|
var model = obj.GetType();
|
var tableName = GetTableName(model); //获取类对应的表名
|
|
var cmd = _db.CreateCommand("", System.Data.CommandType.Text);
|
|
if (ignoreFields == null || ignoreFields.Length == 0)
|
ignoreFields =
|
GetAttributeFields(obj.GetType(), typeof(IgnoreField));
|
|
#region 主表信息==========================
|
|
var strSql = new StringBuilder();
|
var str1 = new StringBuilder(); //数据字段
|
var str2 = new StringBuilder(); //数据参数
|
|
//利用反射获得属性的所有公共属性
|
var pros =
|
model.GetProperties(BindingFlags.Instance |
|
BindingFlags.Public);
|
|
strSql.Append("INSERT INTO " + tableName + "(");
|
|
foreach (var pi in pros)
|
{
|
//跳过被忽略的字段(不生成SQL脚本)，如：自增字段。必须忽略大小写匹配！
|
if (ignoreFields != null &&
|
ignoreFields.Any(e => e.ToLower() == pi.Name.ToLower()))
|
continue;
|
|
//主键字段或IList数据类型不生成Update-SQL脚本
|
//IList数据类型不生成Insert-SQL脚本
|
if (!IsObjectListType(pi.PropertyType))
|
{
|
//判断属性值是否为空
|
str1.Append(pi.Name + ","); //拼接字段
|
str2.Append(_db.ParamSymboName + pi.Name + ","); //声明参数
|
|
var p = _db.CreateParameter(pi.Name, GetValue(obj, pi));
|
cmd.Parameters.Add(p); //对参数赋值
|
}
|
}
|
|
strSql.Append(str1.ToString().Trim(','));
|
strSql.Append(") VALUES (");
|
strSql.Append(str2.ToString().Trim(','));
|
strSql.Append("); ");
|
|
#endregion
|
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(strSql.ToString());
|
return cmd;
|
}
|
|
/// <summary>
|
/// 获取标识有特性(Attribute)的字段
|
/// </summary>
|
/// <typeparam name="T"></typeparam>
|
/// <param name="objType"></param>
|
/// <param name="attributeType"></param>
|
/// <returns></returns>
|
public string[] GetAttributeFields(Type objType, Type attributeType)
|
{
|
var result = new List<string>();
|
|
//获取所有字段属性名称
|
var pros =
|
objType.GetProperties(BindingFlags.Instance |
|
BindingFlags.Public);
|
foreach (var pi in pros)
|
{
|
var list = pi.GetCustomAttributes(attributeType);
|
if (list.Count() > 0) result.Add(pi.Name);
|
}
|
|
if (result.Count > 0)
|
return result.ToArray();
|
else
|
return null;
|
}
|
|
/// <summary>
|
/// 创建Insert命令
|
/// </summary>
|
/// <param name="model">对象类型</param>
|
/// <param name="ignoreFields">自增字段(或不支持更新的字段)数组</param>
|
/// <returns></returns>
|
public DbCommand GetInsertCommand(Type model,
|
params string[] ignoreFields)
|
{
|
var tableName = GetTableName(model); //获取类对应的表名
|
|
var cmd = _db.CreateCommand("", System.Data.CommandType.Text);
|
|
if (ignoreFields == null || ignoreFields.Length == 0)
|
ignoreFields = GetAttributeFields(model, typeof(IgnoreField));
|
|
#region 主表信息==========================
|
|
var strSql = new StringBuilder();
|
var str1 = new StringBuilder(); //数据字段
|
var str2 = new StringBuilder(); //数据参数
|
|
//利用反射获得属性的所有公共属性
|
var pros =
|
model.GetProperties(BindingFlags.Instance |
|
BindingFlags.Public);
|
|
strSql.Append("INSERT INTO " + tableName + "(");
|
|
foreach (var pi in pros)
|
{
|
//跳过被忽略的字段(不生成SQL脚本)，如：自增字段。必须忽略大小写匹配！
|
if (ignoreFields != null &&
|
ignoreFields.Any(e => e.ToLower() == pi.Name.ToLower()))
|
continue;
|
|
//主键字段或IList数据类型不生成Update-SQL脚本
|
//IList数据类型不生成Insert-SQL脚本
|
if (!typeof(IList).IsAssignableFrom(pi.PropertyType))
|
{
|
//判断属性值是否为空
|
str1.Append(pi.Name + ","); //拼接字段
|
str2.Append(_db.ParamSymboName + pi.Name + ","); //声明参数
|
cmd.Parameters.Add(_db.CreateParameter(pi.Name,
|
DataTypeConverter.DotNetType2DbType(
|
pi.PropertyType.Name), 0, pi.Name,
|
DBNull.Value)); //对参数赋值
|
}
|
}
|
|
strSql.Append(str1.ToString().Trim(','));
|
strSql.Append(") VALUES (");
|
strSql.Append(str2.ToString().Trim(','));
|
strSql.Append(") ");
|
|
#endregion
|
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(strSql.ToString());
|
return cmd;
|
}
|
|
/// <summary>
|
/// 创建Delete命令
|
/// </summary>
|
/// <param name="model">对象类型</param>
|
/// <param name="PKs">主键字段名数组</param>
|
/// <returns></returns>
|
public DbCommand GetDeleteCommand(Type model, params string[] PKs)
|
{
|
if (PKs == null || PKs.Length == 0)
|
PKs = GetAttributeFields(model, typeof(KeyField));
|
|
if (PKs == null || PKs.Length == 0)
|
throw new Exception("没有指定主键字段！");
|
|
var tableName = GetTableName(model); //获取类对应的表名
|
|
var cmd = _db.CreateCommand("", System.Data.CommandType.Text);
|
var PKTypeNames = new Dictionary<string, string>();
|
|
#region 主表信息==========================
|
|
var strSql = new StringBuilder();
|
|
//利用反射获得属性的所有公共属性
|
var pros =
|
model.GetProperties(BindingFlags.Instance |
|
BindingFlags.Public);
|
|
strSql.Append("DELETE FROM " + tableName + " WHERE ");
|
|
foreach (var pi in pros)
|
if (PKs.Any(e => e.ToLower() == pi.Name.ToLower()))
|
PKTypeNames[pi.Name] = pi.PropertyType.Name;
|
|
//组合主键 WHERE 条件
|
foreach (var pk in PKs)
|
{
|
if (Array.IndexOf(PKs, pk) == 0)
|
strSql.Append(pk + "=" + _db.ParamSymboName + pk); //声明参数
|
else
|
strSql.Append(" AND " + pk + "=" + _db.ParamSymboName +
|
pk); //声明参数
|
|
cmd.Parameters.Add(_db.CreateParameter(pk,
|
DataTypeConverter.DotNetType2DbType(PKTypeNames[pk]), 0, pk,
|
DBNull.Value)); //对参数赋值
|
}
|
|
#endregion
|
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(strSql.ToString());
|
return cmd;
|
}
|
|
|
/// <summary>
|
/// 创建Delete命令-处理实体对象数据
|
/// </summary>
|
/// <param name="model">对象类型</param>
|
/// <param name="PKs">主键字段名数组</param>
|
/// <returns></returns>
|
public DbCommand GetDeleteCommand<T>(T obj, params string[] PKs)
|
where T : new()
|
{
|
if (PKs == null || PKs.Length == 0)
|
PKs = GetAttributeFields(obj.GetType(), typeof(KeyField));
|
|
if (PKs == null || PKs.Length == 0)
|
throw new Exception("没有指定主键字段！");
|
|
var model = obj.GetType();
|
var tableName = GetTableName(model); //获取类对应的表名
|
|
var cmd = _db.CreateCommand("", System.Data.CommandType.Text);
|
|
var PKTypeNames = new Dictionary<string, string>();
|
var PKValues = new Dictionary<string, object>();
|
|
#region 主表信息==========================
|
|
var strSql = new StringBuilder();
|
|
//利用反射获得属性的所有公共属性
|
var pros =
|
model.GetProperties(BindingFlags.Instance |
|
BindingFlags.Public);
|
|
strSql.Append("DELETE FROM " + tableName + " WHERE ");
|
|
foreach (var pi in pros)
|
if (PKs.Any(e => e.ToLower() == pi.Name.ToLower()))
|
{
|
PKTypeNames[pi.Name] = pi.PropertyType.Name;
|
PKValues[pi.Name] = GetValue(obj, pi);
|
}
|
|
//组合主键 WHERE 条件
|
foreach (var pk in PKs)
|
{
|
if (Array.IndexOf(PKs, pk) == 0)
|
strSql.Append(pk + "=" + _db.ParamSymboName + pk); //声明参数
|
else
|
strSql.Append(" AND " + pk + "=" + _db.ParamSymboName +
|
pk); //声明参数
|
|
cmd.Parameters.Add(_db.CreateParameter(pk,
|
PKValues[pk])); //对参数赋值
|
}
|
|
#endregion
|
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(strSql.ToString());
|
return cmd;
|
}
|
|
/// <summary>
|
/// 创建Update命令 - 处理实体对象数据
|
/// </summary>
|
/// <param name="model">对象类型</param>
|
/// <param name="PKs">主键字段名数组</param>
|
/// <param name="ignoreFields">自增字段(或不支持更新的字段)数组</param>
|
/// <param name="updateFields">指定更新的字段</param>
|
/// <returns></returns>
|
public DbCommand GetUpdateCommand<T>(T obj,
|
string[] PKs = null,
|
string[] ignoreFields = null,
|
string[] updateFields = null) where T : new()
|
{
|
if (PKs == null)
|
PKs = GetAttributeFields(obj.GetType(), typeof(KeyField));
|
if (ignoreFields == null)
|
ignoreFields =
|
GetAttributeFields(obj.GetType(), typeof(IgnoreField));
|
if (PKs == null || PKs.Length == 0)
|
throw new Exception("没有指定主键字段！");
|
|
var model = obj.GetType();
|
var tableName = GetTableName(model); //获取类对应的表名
|
|
var cmd = _db.CreateCommand("", System.Data.CommandType.Text);
|
|
var PKTypeNames = new Dictionary<string, string>();
|
var PKValues = new Dictionary<string, object>();
|
|
#region 主表信息==========================
|
|
var strSql = new StringBuilder();
|
var str1 = new StringBuilder(); //数据字段
|
var str2 = new StringBuilder(); //数据参数
|
|
//利用反射获得属性的所有公共属性
|
var pros =
|
model.GetProperties(BindingFlags.Instance |
|
BindingFlags.Public);
|
|
strSql.Append("UPDATE " + tableName + " SET ");
|
|
var isPK = false;
|
|
foreach (var pi in pros)
|
{
|
isPK = PKs.Any(e => e.ToLower() == pi.Name.ToLower());
|
|
//获取主键的数据类型
|
if (isPK)
|
{
|
PKTypeNames[pi.Name] = pi.PropertyType.Name;
|
PKValues[pi.Name] = GetValue(obj, pi);
|
}
|
|
//跳过被忽略的字段(不生成SQL脚本)，如：自增字段。必须忽略大小写匹配！
|
if (ignoreFields != null &&
|
ignoreFields.Any(e => e.ToLower() == pi.Name.ToLower()))
|
continue;
|
|
//指定更新的字段
|
if (updateFields != null &&
|
updateFields.Any(e => e.ToLower() == pi.Name.ToLower()) ==
|
false) continue;
|
|
//主键字段或IList数据类型不生成Update-SQL脚本
|
//IList数据类型不生成Insert-SQL脚本
|
if (!isPK && !typeof(IList).IsAssignableFrom(pi.PropertyType))
|
{
|
//判断属性值是否为空
|
str1.Append(pi.Name + "=" + _db.ParamSymboName + pi.Name +
|
","); //声明参数
|
cmd.Parameters.Add(
|
_db.CreateParameter(pi.Name,
|
GetValue(obj, pi))); //对参数赋值
|
}
|
}
|
|
strSql.Append(str1.ToString().Trim(','));
|
strSql.Append(" WHERE ");
|
foreach (var pk in PKs)
|
{
|
if (Array.IndexOf(PKs, pk) == 0)
|
strSql.Append(pk + "=" + _db.ParamSymboName + pk); //声明参数
|
else
|
strSql.Append(" AND " + pk + "=" + _db.ParamSymboName +
|
pk); //声明参数
|
cmd.Parameters.Add(_db.CreateParameter(pk,
|
PKValues[pk])); //对参数赋值
|
}
|
|
#endregion
|
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(strSql.ToString());
|
return cmd;
|
}
|
|
|
/// <summary>
|
/// 创建Update命令
|
/// </summary>
|
/// <param name="model">对象类型</param>
|
/// <param name="PKs">主键字段名数组</param>
|
/// <param name="ignoreFields">自增字段(或不支持更新的字段)数组</param>
|
/// <param name="updateFields">指定更新的字段</param>
|
/// <returns></returns>
|
public DbCommand GetUpdateCommand(Type model,
|
string[] PKs = null,
|
string[] ignoreFields = null,
|
string[] updateFields = null)
|
{
|
if (PKs == null) PKs = GetAttributeFields(model, typeof(KeyField));
|
if (ignoreFields == null)
|
ignoreFields = GetAttributeFields(model, typeof(IgnoreField));
|
if (PKs == null || PKs.Length == 0)
|
throw new Exception("没有指定主键字段！");
|
|
var tableName = GetTableName(model); //获取类对应的表名
|
|
var cmd = _db.CreateCommand("", System.Data.CommandType.Text);
|
var PKTypeNames = new Dictionary<string, string>();
|
|
#region 主表信息==========================
|
|
var strSql = new StringBuilder();
|
var str1 = new StringBuilder(); //数据字段
|
var str2 = new StringBuilder(); //数据参数
|
//利用反射获得属性的所有公共属性
|
var pros =
|
model.GetProperties(BindingFlags.Instance |
|
BindingFlags.Public);
|
|
strSql.Append("UPDATE " + tableName + " SET ");
|
var isPK = false;
|
|
foreach (var pi in pros)
|
{
|
isPK = PKs.Any(e => e.ToLower() == pi.Name.ToLower());
|
|
//获取主键的数据类型
|
if (isPK) PKTypeNames[pi.Name] = pi.PropertyType.Name;
|
|
//跳过被忽略的字段(不生成SQL脚本)，如：自增字段。必须忽略大小写匹配！
|
if (ignoreFields != null &&
|
ignoreFields.Any(e => e.ToLower() == pi.Name.ToLower()))
|
continue;
|
|
//指定更新的字段
|
if (updateFields != null &&
|
updateFields.Any(e => e.ToLower() == pi.Name.ToLower()) ==
|
false) continue;
|
|
//主键字段或IList数据类型不生成Update-SQL脚本
|
//IList数据类型不生成Insert-SQL脚本
|
if (!isPK && !typeof(IList).IsAssignableFrom(pi.PropertyType))
|
{
|
//判断属性值是否为空
|
str1.Append(pi.Name + "=" + _db.ParamSymboName + pi.Name +
|
","); //声明参数
|
cmd.Parameters.Add(_db.CreateParameter(pi.Name,
|
DataTypeConverter.DotNetType2DbType(
|
pi.PropertyType.Name), 0, pi.Name,
|
DBNull.Value)); //对参数赋值
|
}
|
}
|
|
strSql.Append(str1.ToString().Trim(','));
|
strSql.Append(" WHERE ");
|
foreach (var pk in PKs)
|
{
|
if (Array.IndexOf(PKs, pk) == 0)
|
strSql.Append(pk + "=" + _db.ParamSymboName + pk); //声明参数
|
else
|
strSql.Append(" AND " + pk + "=" + _db.ParamSymboName +
|
pk); //声明参数
|
cmd.Parameters.Add(_db.CreateParameter(pk,
|
DataTypeConverter.DotNetType2DbType(PKTypeNames[pk]), 0, pk,
|
DBNull.Value)); //对参数赋值
|
}
|
|
#endregion
|
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(strSql.ToString());
|
return cmd;
|
}
|
|
|
/// <summary>
|
/// 判断属性的数据类型是对象列表(对象列表不支持生成SQL,必须转换为byte[])
|
/// </summary>
|
/// <param name="propertyType">属性类型</param>
|
/// <returns></returns>
|
private bool IsObjectListType(Type propertyType)
|
{
|
if (propertyType == null) return false;
|
|
//特殊处理，数组类型byte[]允许存储数据库
|
if (propertyType == typeof(byte[])) return false;
|
|
return typeof(IList).IsAssignableFrom(propertyType);
|
}
|
|
/// <summary>
|
/// 获取对象属性的值
|
/// </summary>
|
/// <param name="obj"></param>
|
/// <param name="pi"></param>
|
/// <returns></returns>
|
private object GetValue(object obj, PropertyInfo pi)
|
{
|
var o = pi.GetValue(obj, null);
|
|
//转换DBNull,非常重要！！！
|
if (o == null) return DBNull.Value;
|
|
if (pi.PropertyType == typeof(DateTime))
|
//处理空日期
|
if (DateTime.MinValue == DateTime.Parse(o.ToString()))
|
return DBNull.Value;
|
|
return o;
|
}
|
|
|
/// <summary>
|
/// 获取类对应的表名
|
/// </summary>
|
/// <param name="model">实体模型对应的类</param>
|
/// <returns></returns>
|
public string GetTableName(Type model)
|
{
|
//从类的特性获取
|
var list =
|
model.GetCustomAttributes(typeof(TableInfoAttribute), false);
|
if (list != null && list.Length > 0)
|
return (list[0] as TableInfoAttribute).TableName;
|
else
|
return model.Name; //类名既表名
|
}
|
}
|
}
|
