/// <summary>
/// 使用SQL语句查询，获取结果集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public override IEnumerable <T> GetListBySql <T>(string sql, dynamic parameters = null)
{
using (IDbConnection connection = OpenConnection(SqlExecuteType.Read))
{
Type entityType = typeof(T);
if (CheckListEnableCache(entityType))
{
//找到主键
var pk = GetPrimaryKey(entityType);
//找到第一个FROM关键字，替换前面的SELECT
var tableInfo = TableInfoAttribute.GetAttribute(entityType);
var tableName = tableInfo == null ? entityType.Name : tableInfo.TableName;
var index = sql.IndexOf(" FROM ", StringComparison.OrdinalIgnoreCase);
var pkSql = "SELECT " + tableName + "." + pk.Name + " FROM " + sql.Substring(index + " FROM ".Length);
var entityIds = FetchListBySql <int>(pkSql);
return(GetListByEntityIds <T>(entityIds));
}
OnExecutingCommand(sql);
var result = connection.Query <T>(sql, parameters as object);
OnExecutedCommand(sql);
return(result);
}
}
/// <summary>
/// 根据Id获取实体
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="id">主键Id</param>
/// <param name="primaryKeyField"></param>
/// <returns></returns>
public T Get <T>(string id, string primaryKeyField = "") where T : BaseEntity
{
//find key
Type type = typeof(T);
T entity;
PropertyInfo pk;
PropertyInfo[] ps = type.GetProperties();
if (string.IsNullOrEmpty(primaryKeyField))
{
pk = GetPrimaryKey(type);
}
else
{
pk = ps.FirstOrDefault(p => p.Name == primaryKeyField);
}
if (pk == null)
{
throw new Exception(string.Format("实体{0}没有设置主键", type.FullName));
}
TableInfoAttribute tableInfo = TableInfoAttribute.GetAttribute(type);
string where = string.Format("{0}.{1}='{2}'", tableInfo == null ? type.Name : tableInfo.TableName, pk.Name, id);
entity = GetList <T>(where).FirstOrDefault();
//if (entity != null)
// foreach (var item in ps)
// {
// entity.Dbvalue.Add(new KeyValuePair<string, object>(item.Name, item.GetValue(entity, null)));
// }
return(entity);
}
private static TableInformation GetInformation(Type t)
{
TableInfoAttribute tableInfo = TableInfoAttribute.GetAttribute(t);
var information = new TableInformation();
if (tableInfo == null)
{
information.TableName = t.Name;
}
else
{
information.TableName = tableInfo.TableName;
}
//主键
var ps = t.GetProperties();
foreach (var info in ps)
{
IdentityAttribute id = IdentityAttribute.GetAttribute(info);
if (id != null)
{
information.PrimaryKey = info.Name;
information.AutoIncrement = true;
break;
}
}
return(information);
}
private string GenerateUpdateSql(Type type)
{
//判断缓存中存在已经生成的Sql语句，则直接返回
if (_updateSqlCaches.ContainsKey(type))
{
return(_updateSqlCaches[type]);
}
PropertyInfo[] propertyInfos = type.GetProperties();
TableInfoAttribute tableInfo = TableInfoAttribute.GetAttribute(type);
string tableName = tableInfo == null ? type.Name : tableInfo.TableName;
var updateSql = new StringBuilder();
updateSql.AppendFormat("UPDATE {0} SET ", tableName);
int columnCount = 0;
foreach (PropertyInfo info in propertyInfos)
{
ExtendedAttribute extended = ExtendedAttribute.GetAttribute(info);
if (extended != null)
{
continue;
}
ExcludeFieldAttribute exclude = ExcludeFieldAttribute.GetAttribute(info);
if (exclude != null)
{
continue;
}
IdentityAttribute identity = IdentityAttribute.GetAttribute(info);
if (identity != null)
{
continue;
}
RefFieldAttribute refField = RefFieldAttribute.GetAttribute(info);
if (refField != null)
{
continue;
}
GuidIdentityAttribute guidIdentity = GuidIdentityAttribute.GetAttribute(info);
if (guidIdentity != null)
{
continue;
}
if (columnCount != 0)
{
updateSql.Append(",");
}
updateSql.AppendFormat("{0}=@{0}", info.Name);
columnCount++;
}
string updateString = updateSql.ToString();
_updateSqlCaches[type] = updateString;
return(updateString);
}
private string GenerateUpdateSql(Type type, BaseEntity entity)
{
PropertyInfo[] propertyInfos = type.GetProperties();
TableInfoAttribute tableInfo = TableInfoAttribute.GetAttribute(type);
string tableName = tableInfo == null ? type.Name : tableInfo.TableName;
var updateString = new StringBuilder();
updateString.AppendFormat("UPDATE {0} SET ", tableName);
int columnCount = 0;
foreach (PropertyInfo info in propertyInfos)
{
ExtendedAttribute extended = ExtendedAttribute.GetAttribute(info);
if (extended != null)
{
continue;
}
ExcludeFieldAttribute exclude = ExcludeFieldAttribute.GetAttribute(info);
if (exclude != null)
{
continue;
}
IdentityAttribute identity = IdentityAttribute.GetAttribute(info);
if (identity != null)
{
continue;
}
RefFieldAttribute refField = RefFieldAttribute.GetAttribute(info);
if (refField != null)
{
continue;
}
GuidIdentityAttribute guidIdentity = GuidIdentityAttribute.GetAttribute(info);
if (guidIdentity != null)
{
continue;
}
//var dbv = entity.Dbvalue.FirstOrDefault(p => p.Key == info.Name);
//var newvalue = info.GetValue(entity, null);
//if (dbv.Value != null && newvalue != null)
// if (dbv.ToString() == newvalue.ToString()) continue;
if (columnCount != 0)
{
updateString.Append(",");
}
updateString.AppendFormat("{0}=@{0}", info.Name);
columnCount++;
}
if (columnCount == 0)
{
return("");
}
return(updateString.ToString());
}
private string GenerateUpdateSql(BaseEntity entity)
{
var type = entity.GetType();
PropertyInfo[] propertyInfos = type.GetProperties();
TableInfoAttribute tableInfo = TableInfoAttribute.GetAttribute(type);
string tableName = tableInfo == null ? type.Name : tableInfo.TableName;
var updateString = new StringBuilder();
updateString.AppendFormat("UPDATE {0} SET ", tableName);
int columnCount = 0;
foreach (PropertyInfo info in propertyInfos)
{
ExtendedAttribute extended = ExtendedAttribute.GetAttribute(info);
if (extended != null)
{
continue;
}
ExcludeFieldAttribute exclude = ExcludeFieldAttribute.GetAttribute(info);
if (exclude != null)
{
continue;
}
IdentityAttribute identity = IdentityAttribute.GetAttribute(info);
if (identity != null)
{
continue;
}
RefFieldAttribute refField = RefFieldAttribute.GetAttribute(info);
if (refField != null)
{
continue;
}
if (!object.Equals(entity.Dbvalue[info.Name], info.GetValue(entity, null)))
{
if (columnCount != 0)
{
updateString.Append(",");
}
updateString.AppendFormat("{0}=@{0}", info.Name);
columnCount++;
}
}
if (columnCount == 0)
{
return("");
}
return(updateString.ToString());
}
/// <summary>
/// 根据主键Id获取实体集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entityIds"></param>
/// <returns></returns>
public List <T> GetListByEntityIds <T>(IEnumerable <string> entityIds) where T : BaseEntity
{
T[] tArray = new T[entityIds.Count()];
IDictionary <string, int> objs = new Dictionary <string, int>();
for (int i = 0; i < entityIds.Count(); i++)
{
tArray[i] = default(T);
objs[entityIds.ElementAt(i)] = i;
}
if (objs.Any())
{
var entityType = typeof(T);
var tableInfoAttr = TableInfoAttribute.GetAttribute(entityType);
var pk = entityType.GetProperties().FirstOrDefault(p => IdentityAttribute.GetAttribute(p) != null);
var tableName = tableInfoAttr == null ? entityType.Name : tableInfoAttr.TableName;
if (pk == null)
{
pk = entityType.GetProperties().FirstOrDefault(p => GuidIdentityAttribute.GetAttribute(p) != null);
}
if (pk != null)
{
var idsString = "";
for (int i = 0; i < objs.Keys.ToArray().Length; i++)
{
idsString = idsString +
(i == 0
? ("'" + objs.Keys.ToArray()[i] + "'")
: (",'" + objs.Keys.ToArray()[i] + "'"));
}
var datalist = FetchEntities <T>(tableName + "." + pk.Name + " IN(" + idsString + ")");
foreach (var entity1 in datalist)
{
tArray[objs[entity1.EntityId]] = entity1;
}
}
}
List <T> tEntities = new List <T>();
T[] tArray1 = tArray;
for (int i = 0; i < tArray1.Length; i++)
{
T entity2 = tArray1[i];
if (entity2 != null)
{
tEntities.Add(entity2);
}
}
return(tEntities);
}
/// <summary>
/// 根据主键Id获取实体集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entityIds"></param>
/// <returns></returns>
public override List <T> GetListByEntityIds <T>(IEnumerable <int> entityIds)
{
T[] tArray = new T[entityIds.Count()];
IDictionary <int, int> objs = new Dictionary <int, int>();
for (int i = 0; i < entityIds.Count(); i++)
{
T entity =
CacheHelper.CacheService.Get <T>(EntityUpdateTrackHelper.GetEntityKey(typeof(T),
entityIds.ElementAt(i)));
if (entity == null)
{
tArray[i] = default(T);
objs[entityIds.ElementAt(i)] = i;
}
else
{
tArray[i] = entity;
}
}
if (objs.Any())
{
var entityType = typeof(T);
var tableInfoAttr = TableInfoAttribute.GetAttribute(entityType);
var pk = entityType.GetProperties().FirstOrDefault(p => IdentityAttribute.GetAttribute(p) != null);
var tableName = tableInfoAttr == null ? entityType.Name : tableInfoAttr.TableName;
if (pk != null)
{
var datalist = FetchEntities <T>(tableName + "." + pk.Name + " IN(" + objs.Keys.ToArray().GetString() + ")");
foreach (var entity1 in datalist)
{
tArray[objs[entity1.EntityId]] = entity1;
CacheHelper.CacheService.Add(EntityUpdateTrackHelper.GetEntityKey(entity1), entity1, CachingExpirationType.SingleObject);
}
}
}
List <T> tEntities = new List <T>();
T[] tArray1 = tArray;
for (int i = 0; i < tArray1.Length; i++)
{
T entity2 = tArray1[i];
if (entity2 != null)
{
tEntities.Add(entity2);
}
}
return(tEntities);
}
private static Type GetTypeByTableName(string virtualTableName)
{
string sqlAssembly = typeof(Creative).Assembly.FullName;
string classNamespace = typeof(Creative).Namespace;
Type type = Type.GetType(string.Format("{0}.{1},{2}", classNamespace, virtualTableName, sqlAssembly));
//Get type from Attribute table name
#region Try Get Type from Attribute
/************************************************/
if (type == null) // Unrecognized type name ( ex. TargetKeyword )
{
var assemblyNestedTypes = from t in Assembly.GetAssembly(typeof(Creative)).GetTypes()
where t.IsClass && t.Namespace == classNamespace
select t;
foreach (Type t in assemblyNestedTypes)
{
TableInfoAttribute tableInfo = (TableInfoAttribute)Attribute.GetCustomAttribute(t, typeof(TableInfoAttribute));
if (tableInfo != null && tableInfo.Name == virtualTableName)
{
type = t;
}
}
}
/************************************************/
#endregion
//Get type from Meta Property Name
#region Try Get Type from Meta Property Table
/************************************************/
if (type == null) // still Unrecognized type name ( ex. color )
{
Int32 metaPropertyID = 0;
string baseValueType = GetMetaPropertyBaseValueType(virtualTableName, SqlInt32.Null, out metaPropertyID);
if (!string.IsNullOrEmpty(baseValueType))
{
type = Type.GetType(string.Format("{0}.{1},{2}", classNamespace, baseValueType, sqlAssembly));
}
/************************************************/
#endregion
}
return(type);
}
/// <summary>
/// 通过限制条件，批量数据库记录删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="where"></param>
/// <returns></returns>
public override int DeleteEntities <T>(string where)
{
if (string.IsNullOrEmpty(where))
{
throw new Exception("删除条件不能为空");
}
if (!where.Trim().StartsWith("where", StringComparison.InvariantCultureIgnoreCase))
{
where = " WHERE " + where;
}
Type type = typeof(T);
TableInfoAttribute tableInfo = TableInfoAttribute.GetAttribute(type);
string tableName = tableInfo == null ? type.Name : tableInfo.TableName;
string deleteSql = "DELETE FROM " + tableName + " " + where;
return(ExecuteSql(deleteSql));
}
/// <summary>
/// 根据Id获取实体
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="id">主键Id</param>
/// <param name="primaryKeyField"></param>
/// <returns></returns>
public override T Get <T>(int id, string primaryKeyField = "")
{
//find key
Type type = typeof(T);
T entity;
if (CheckEnableCache(type))
{
entity = CacheHelper.CacheService.Get <T>(EntityUpdateTrackHelper.GetEntityKey(type, id));
if (entity != null)
{
return(entity);
}
}
PropertyInfo pk;
PropertyInfo[] ps = type.GetProperties();
if (string.IsNullOrEmpty(primaryKeyField))
{
pk = GetPrimaryKey(type);
}
else
{
pk = ps.FirstOrDefault(p => p.Name == primaryKeyField);
}
if (pk == null)
{
throw new Exception(string.Format("实体{0}没有设置主键", type.FullName));
}
TableInfoAttribute tableInfo = TableInfoAttribute.GetAttribute(type);
string where = string.Format("{0}.{1}={2}", tableInfo == null ? type.Name : tableInfo.TableName, pk.Name, id);
entity = GetList <T>(where).FirstOrDefault();
if (entity != null)
{
CacheHelper.CacheService.Add(EntityUpdateTrackHelper.GetEntityKey(type, id), entity, CachingExpirationType.SingleObject);
}
//if (entity != null)
// foreach (var item in ps)
// {
// entity.Dbvalue.Add(new KeyValuePair<string, object>(item.Name, item.GetValue(entity, null)));
// }
return(entity);
}
/// <summary>
/// 生成更新的Sql
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
protected string GenerateUpdateSql(Type type)
{
PropertyInfo[] propertyInfos = type.GetProperties();
TableInfoAttribute tableInfo = TableInfoAttribute.GetAttribute(type);
string tableName = tableInfo == null ? type.Name : tableInfo.TableName;
var updateSql = new StringBuilder();
updateSql.AppendFormat("UPDATE {0} SET ", tableName);
int columnCount = 0;
foreach (PropertyInfo info in propertyInfos)
{
ExtendedAttribute extended = ExtendedAttribute.GetAttribute(info);
if (extended != null)
{
continue;
}
ExcludeFieldAttribute exclude = ExcludeFieldAttribute.GetAttribute(info);
if (exclude != null)
{
continue;
}
IdentityAttribute identity = IdentityAttribute.GetAttribute(info);
if (identity != null)
{
continue;
}
RefFieldAttribute refField = RefFieldAttribute.GetAttribute(info);
if (refField != null)
{
continue;
}
if (columnCount != 0)
{
updateSql.Append(",");
}
updateSql.AppendFormat("{0}=@{0}", info.Name);
columnCount++;
}
string updateString = updateSql.ToString();
return(updateString);
}
public static bool IsModelValidateUserID(object model)
{
if (model == null)
{
throw new Exception("验证model不能为空");
}
object[] tableAttributes = model.GetType().GetCustomAttributes(typeof(TableInfoAttribute), true);
if (tableAttributes.Length == 0)
{
throw new Exception("model无TableInfoAttribute特性，不能验证");
}
TableInfoAttribute att = tableAttributes[0] as TableInfoAttribute;
PropertyInfo primary = model.GetType().GetProperty(att.PrimaryName);
if (primary == null)
{
throw new Exception("model无" + att.PrimaryName + "属性,不能验证");
}
object value = primary.GetValue(model, null);
if (value == null) //如果主键值为空 说明是新增 直接通过
{
return(true);
}
string sql = "select " + att.UserIDName + " from " + att.TableName + " where " + att.PrimaryName + " =@" + att.PrimaryName;
DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@" + att.PrimaryName, value));
if (dt.Rows.Count == 0)
{
throw new CustomException("id不存在");
}
if (dt.Rows[0][0].ToString() == IocFactory <IUserInfo> .Instance.GetUserID())
{
return(true);
}
else
{
return(false);
}
}
public static bool IsIdsValidateUserID(Type modeltype, string ids)
{
if (String.IsNullOrEmpty(ids))
{
return(true);
}
object[] tableAttributes = modeltype.GetCustomAttributes(typeof(TableInfoAttribute), true);
if (tableAttributes.Length == 0)
{
throw new Exception("modeltype无TableInfoAttribute特性，不能验证");
}
TableInfoAttribute att = tableAttributes[0] as TableInfoAttribute;
string tmp = "";
foreach (string s in ids.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
{
string t = s.Trim('\'');
tmp += "'" + t + "',";
}
tmp = tmp.TrimEnd(',');
string sql = "select " + att.UserIDName + " from " + att.TableName + " where " + att.PrimaryName + " in (" + tmp + ")";
DataTable dt = DbInstance.GetDataTable(sql);
if (dt.Rows.Count == 0)
{
throw new CustomException("id不存在");
}
foreach (DataRow dr in dt.Rows)
{
//只要有一项的userID不等于参数userID 则认证失败
if (dr[0].ToString() != IocFactory <IUserInfo> .Instance.GetUserID())
{
return(false);
}
}
return(true);
}
internal static List <FieldProperty> GetFieldPropertys(Type t)
{
var fplist = new List <FieldProperty>();
TableInfoAttribute tableInfo = TableInfoAttribute.GetAttribute(t);
PropertyInfo[] ps = t.GetProperties();
#region 取出映射关系
foreach (PropertyInfo item in ps)
{
var fp = new FieldProperty();
var excludeFieldAttribute = (ExcludeFieldAttribute)Attribute.GetCustomAttribute(item, typeof(ExcludeFieldAttribute));
if (excludeFieldAttribute != null)
{
continue;
}
ExtendedAttribute extendedAttribute = ExtendedAttribute.GetAttribute(item);
if (extendedAttribute != null)
{
continue;
}
RefFieldAttribute fieldattr = RefFieldAttribute.GetAttribute(item);
if (fieldattr != null)
{
fp.TableName = fieldattr.RefTableName;
fp.FieldName = fieldattr.RefFieldName ?? item.Name;
fp.FieldAlias = item.Name;
fp.PropertyName = item.Name;
fp.MasterTableField = fieldattr.MasterTableField;
fp.RelateField = fieldattr.RefTableKey;
fplist.Add(fp);
}
else
{
fp.TableName = tableInfo == null ? t.Name : tableInfo.TableName;
fp.FieldName = item.Name;
fp.PropertyName = item.Name;
fplist.Add(fp);
}
}
#endregion
List <FieldProperty> tables = Distinct(fplist);
for (int i = 0; i < tables.Count; i++)
{
string alias = "t" + i;
foreach (FieldProperty item in fplist)
{
if (item.TableName == tables[i].TableName && item.MasterTableField == tables[i].MasterTableField)
{
item.TableAlias = alias;
//if (string.IsNullOrEmpty(item.FieldAlias))
// item.FieldAlias = alias + "_" + item.FieldName;
}
}
}
return(fplist);
}
/// <summary>
/// 生成新增的Sql语句
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
protected string GenerateInsertSql(object obj)
{
Type type = obj.GetType();
PropertyInfo[] propertyInfos = type.GetProperties();
var insertSql = new StringBuilder();
TableInfoAttribute tableInfo = TableInfoAttribute.GetAttribute(type);
string tableName;
if (tableInfo == null)
{
tableName = type.Name;
}
else
{
tableName = tableInfo.TableName;
}
insertSql.AppendFormat("INSERT INTO {0} (", tableName);
var values = new StringBuilder(" VALUES (");
int columnCount = 0;
foreach (PropertyInfo info in propertyInfos)
{
ExtendedAttribute extended = ExtendedAttribute.GetAttribute(info);
if (extended != null)
{
continue;
}
ExcludeFieldAttribute exclude = ExcludeFieldAttribute.GetAttribute(info);
if (exclude != null)
{
continue;
}
IdentityAttribute identity = IdentityAttribute.GetAttribute(info);
if (identity != null)
{
continue;
}
RefFieldAttribute refField = RefFieldAttribute.GetAttribute(info);
if (refField != null)
{
continue;
}
if (columnCount != 0)
{
insertSql.Append(",");
values.Append(",");
}
object value = info.GetValue(obj, null);
if (value == null || value == DBNull.Value)
{
value = "NULL";
}
else
{
value = string.Format("'{0}'", value.ToString().ReplaceInsertSql());
}
insertSql.AppendFormat("{0}", info.Name);
values.Append(value);
columnCount++;
}
insertSql.AppendFormat(") {0} ) ", values);
string insertSqlstr = insertSql.ToString() + ";";
//_insertSqlCaches.Add(type, insertSqlstr);//加入缓存
return(insertSqlstr);
}
/// <summary>
/// 生成新增的Sql语句
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
internal string GenerateInsertSql(Type type)
{
//判断缓存中存在已经生成的Sql语句，则直接返回
if (_insertSqlCaches.ContainsKey(type))
{
return(_insertSqlCaches[type]);
}
PropertyInfo[] propertyInfos = type.GetProperties();
var insertSql = new StringBuilder();
bool hasIdentityField = false;
TableInfoAttribute tableInfo = TableInfoAttribute.GetAttribute(type);
string tableName = tableInfo == null ? type.Name : tableInfo.TableName;
insertSql.AppendFormat("INSERT INTO {0} (", tableName);
var values = new StringBuilder(" VALUES (");
int columnCount = 0;
foreach (PropertyInfo info in propertyInfos)
{
ExtendedAttribute extended = ExtendedAttribute.GetAttribute(info);
if (extended != null)
{
continue;
}
ExcludeFieldAttribute exclude = ExcludeFieldAttribute.GetAttribute(info);
if (exclude != null)
{
continue;
}
IdentityAttribute identity = IdentityAttribute.GetAttribute(info);
if (identity != null)
{
hasIdentityField = true;
continue;
}
GuidIdentityAttribute guidIdentity = GuidIdentityAttribute.GetAttribute(info);
if (guidIdentity != null)
{
hasIdentityField = true;
}
RefFieldAttribute refField = RefFieldAttribute.GetAttribute(info);
if (refField != null)
{
continue;
}
if (columnCount != 0)
{
insertSql.Append(",");
values.Append(",");
}
insertSql.AppendFormat("{0}", info.Name);
values.AppendLine("@" + info.Name);
columnCount++;
}
insertSql.AppendFormat(") {0} ) ", values);
if (hasIdentityField)
{
insertSql.AppendFormat(_identity);
}
string insertSqlstr = insertSql.ToString();
_insertSqlCaches.Add(type, insertSqlstr); //加入缓存
return(insertSqlstr);
}
