/// <summary>
/// 根据主键判断一个实体是否存在
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entityId"></param>
/// <param name="primaryKey"></param>
/// <returns></returns>
public override bool Exists <T>(int entityId, string primaryKey = "")
{
var ps = typeof(T).GetProperties();
if (string.IsNullOrEmpty(primaryKey))
{
foreach (var propertyInfo in ps)
{
IdentityAttribute identity = IdentityAttribute.GetAttribute(propertyInfo);
if (identity != null)
{
primaryKey = propertyInfo.Name;
break;
}
}
}
if (string.IsNullOrEmpty(primaryKey))
{
throw new Exception("没有指定主键");
}
var sqlwhere = primaryKey + "=" + entityId;
int count = GetCount <T>(sqlwhere);
return(count > 0);
}
/// <summary>
/// 以表为单位生成SQL脚本
/// </summary>
/// <param name="tableName"></param>
protected string InsetValueIntoMemory <T>(string tableName, T[] objList, bool inserHead = true)
{
string DbName = hashLoadBalance.FindCloseServerDBsByTableName(tableName).DbName;
var Pros = typeof(T).GetProperties();
var ProsNamelist = Pros.Where(x => IdentityAttribute.GetAttribute(x) == null).Select(x => x.Name);;//排除私有键属性名
string Sqlpramslist = "(" + string.Join(",", ProsNamelist) + ")";
StringBuilder stringBuilder = new StringBuilder();
if (inserHead)
{
//stringBuilder.Append("INSERT INTO [" + DbName + "].[dbo].[" + tableName + "]" + Sqlpramslist + " VALUES ");
stringBuilder.Append("INSERT INTO [" + tableName + "]" + " VALUES ");
}
foreach (var drow in objList)
{
stringBuilder.Append("(");
foreach (var dcol in Pros)
{
if (IdentityAttribute.GetAttribute(dcol) != null)
{
continue; //排除私有键属性值
}
var value = dcol.GetValue(drow).ToString();
stringBuilder.Append("'" + value + "',");
}
stringBuilder.Remove(stringBuilder.Length - 1, 1);
stringBuilder.Append("),");
}
stringBuilder.Remove(stringBuilder.Length - 1, 1);
return(stringBuilder.ToString());
}
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);
}
/// <summary>
/// 获取列表数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="strWhere"></param>
/// <param name="param"></param>
/// <returns></returns>
public override List <T> GetList <T>(string strWhere = "1=1", dynamic param = null)
{
using (IDbConnection connection = OpenConnection(SqlExecuteType.Write))
{
string sql = GenerateSelectSql <T>(strWhere);
var type = typeof(T);
if (CheckListEnableCache(type))
{
//缓存使用策略
//先找到要查询的数据的所有主键
var ps = type.GetProperties();
var pk = ps.FirstOrDefault(p => IdentityAttribute.GetAttribute(p) != null);
if (pk != null)
{
var idsql = GenerateSelectPkSql <T>(strWhere);
var idList = connection.Query <int>((idsql), param as object).ToArray();
return(GetListByEntityIds <T>(idList));
}
//用找到的主键从缓存中查询
//没有从缓存中找到的实体，再去数据库查询
//把查询到的数据，放到缓存中
}
var execSql = sql;
OnExecutingCommand(execSql);
var result = connection.Query <T>(execSql, param as object).ToList();
OnExecutedCommand(execSql);
if (result.Count == 1)
{
result.ForEach(p => p.InitDbValue());
}
return(result);
}
}
private PropertyInfo GetPrimaryKey(Type type)
{
PropertyInfo[] ps = type.GetProperties();
PropertyInfo pk = ps.FirstOrDefault(p => IdentityAttribute.GetAttribute(p) != null);
return(pk);
}
/// <summary>
/// 生成查询主键的SQL语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="where"></param>
/// <returns></returns>
protected virtual string GenerateSelectPkSql <T>(string where = "")
{
if (!string.IsNullOrEmpty(where))
{
if (!where.TrimStart().StartsWith("WHERE", StringComparison.CurrentCultureIgnoreCase))
{
where = " WHERE " + where;
}
}
var selectSql = new StringBuilder("SELECT ");
List <FieldProperty> fpmap = FieldProperty.GetFieldPropertys <T>();
List <FieldProperty> tables = FieldProperty.GetTables <T>();
FieldProperty masterTable = fpmap.FirstOrDefault(p => string.IsNullOrEmpty(p.MasterTableField));
var ps = typeof(T).GetProperties();
var pk = ps.FirstOrDefault(p => IdentityAttribute.GetAttribute(p) != null);
if (pk == null)
{
return(string.Empty);
}
selectSql.AppendFormat("{0}.{1}", masterTable.TableAlias, pk.Name);
//selectSql.Append(pk.Name);
selectSql.AppendLine();
selectSql.AppendLine(" FROM ");
selectSql.Append(" " + "".PadLeft(tables.Count - 1, '('));
selectSql.AppendFormat(" {0} {1} ", masterTable.TableName, masterTable.TableAlias);
var strSql = LeftJoinWhere(@where, tables, masterTable, selectSql);
return(strSql);
}
/// <summary>
/// 更新一个实体
/// </summary>
/// <param name="entity"></param>
/// <param name="primaryKeyField"></param>
/// <returns></returns>
public override int UpdateEntity(BaseEntity entity, string primaryKeyField = "")
{
Type t = entity.GetType();
PropertyInfo[] ps = t.GetProperties();
PropertyInfo pk;
if (string.IsNullOrEmpty(primaryKeyField))
{
pk = ps.FirstOrDefault(p => IdentityAttribute.GetAttribute(p) != null);
}
else
{
pk = ps.FirstOrDefault(p => p.Name == primaryKeyField);
}
if (pk == null)
{
throw new Exception(string.Format("实体{0}没有设置主键", t.FullName));
}
string where = " WHERE " + pk.Name + "=@" + pk.Name;
string updateSql = "";
//if (entity.Dbvalue.Count == 0)
updateSql = GenerateUpdateSql(entity);
//else
// updateSql = GenerateUpdateSql(t, entity);
if (string.IsNullOrWhiteSpace(updateSql))
{
return(0);
}
updateSql += where;
try
{
using (IDbConnection connection = OpenConnection(SqlExecuteType.Write))
{
lock (syncObj)
{
var execSql = ToMySql(updateSql);
OnExecutingCommand(execSql);
int result = connection.Execute(execSql, entity, null, 60);
OnExecutedCommand(execSql);
if (CheckEnableCache(entity.GetType()))
{
CacheHelper.CacheService.Set(
EntityUpdateTrackHelper.GetEntityKey(entity),
entity,
CachingExpirationType.SingleObject);
}
return(result);
}
}
}
catch (Exception ex)
{
throw new Exception("发生错误，SQL语句为：" + updateSql, ex);
}
}
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(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);
}
/// <summary>
/// 添加一个实体
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public string AddEntity(BaseEntity entity)
{
//判断是否启用了缓存
Type type = entity.GetType();
PropertyInfo[] propertyInfos = type.GetProperties();
var ps = new DynamicParameters {
};
PropertyInfo pk = null;
foreach (PropertyInfo propertyInfo in propertyInfos)
{
if (IdentityAttribute.GetAttribute(propertyInfo) != null)
{
pk = propertyInfo;
}
if (GuidIdentityAttribute.GetAttribute(propertyInfo) != null)
{
pk = propertyInfo;
}
if (ExcludeFieldAttribute.GetAttribute(propertyInfo) != null)
{
continue;
}
ps.Add(propertyInfo.Name, propertyInfo.GetValue(entity, null));
}
string insertSql = GenerateInsertSql(type);
try
{
using (IDbConnection connection = OpenConnection())
{
string id = connection.Query <string>(insertSql, ps).FirstOrDefault();
if (pk != null)
{
pk.SetValue(entity, id, null);
}
return(id);
}
}
catch (Exception ex)
{
throw new Exception("发生错误，SQL语句为：" + insertSql + "\r\n实体为：" + entity, ex);
}
}
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>
/// 更新一个实体
/// </summary>
/// <param name="entity"></param>
/// <param name="primaryKeyField"></param>
/// <returns></returns>
public int UpdateEntity(BaseEntity entity, string primaryKeyField = "")
{
Type t = entity.GetType();
PropertyInfo[] ps = t.GetProperties();
PropertyInfo pk;
if (string.IsNullOrEmpty(primaryKeyField))
{
pk = ps.FirstOrDefault(p => IdentityAttribute.GetAttribute(p) != null);
if (pk == null)
{
pk = ps.FirstOrDefault(p => GuidIdentityAttribute.GetAttribute(p) != null);
}
}
else
{
pk = ps.FirstOrDefault(p => p.Name == primaryKeyField);
}
if (pk == null)
{
throw new Exception(string.Format("实体{0}没有设置主键", t.FullName));
}
string where = " WHERE " + pk.Name + "=@" + pk.Name;
string updateSql = "";
//if (entity.Dbvalue.Count == 0)
updateSql = GenerateUpdateSql(t);
//else
// updateSql = GenerateUpdateSql(t, entity);
if (string.IsNullOrWhiteSpace(updateSql))
{
return(0);
}
updateSql += where;
try
{
using (IDbConnection connection = OpenConnection())
{
int result = connection.Execute(ToSqlServer(updateSql), entity, null, 60);
return(result);
}
}
catch (Exception ex)
{
throw new Exception("发生错误，SQL语句为：" + updateSql, ex);
}
}
/// <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);
}
/// <summary>
/// 生成查询主键的SQL语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="where"></param>
/// <returns></returns>
internal string GenerateSelectPkSql <T>(string where = "")
{
if (!string.IsNullOrEmpty(where))
{
if (!where.TrimStart().StartsWith("WHERE", StringComparison.CurrentCultureIgnoreCase))
{
where = " WHERE " + where;
}
}
var selectSql = new StringBuilder("SELECT ");
List <FieldProperty> fpmap = FieldProperty.GetFieldPropertys <T>();
List <FieldProperty> tables = FieldProperty.GetTables <T>();
FieldProperty masterTable = fpmap.FirstOrDefault(p => string.IsNullOrEmpty(p.MasterTableField));
var ps = typeof(T).GetProperties();
var pk = ps.FirstOrDefault(p => IdentityAttribute.GetAttribute(p) != null);
if (pk == null)
{
return(string.Empty);
}
selectSql.AppendFormat("{0}.{1}", masterTable.TableAlias, pk.Name);
//selectSql.Append(pk.Name);
selectSql.AppendLine();
selectSql.AppendLine(" FROM ");
selectSql.Append(" " + "".PadLeft(tables.Count - 1, '('));
selectSql.AppendFormat(" {0} {1} ", masterTable.TableName, masterTable.TableAlias);
foreach (FieldProperty item in tables)
{
if (!string.IsNullOrEmpty(where))
{
where = where.Replace(" " + item.TableName + ".", " " + item.TableAlias + ".")
.Replace("(" + item.TableName + ".", "(" + item.TableAlias + ".")
.Replace("=" + item.TableName + ".", "=" + item.TableAlias + ".");
}
if (item.TableAlias == masterTable.TableAlias)
{
continue;
}
selectSql.AppendFormat(" LEFT JOIN {0} {1} ", item.TableName, item.TableAlias);
selectSql.AppendFormat(" ON {0}.{1}={2}.{3}) ", masterTable.TableAlias, item.MasterTableField,
item.TableAlias, item.RelateField);
selectSql.AppendLine();
}
string strSql = selectSql + where;
return(strSql);
}
/// <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);
}
static public string CreateTableSql <T>(string tableName)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(string.Format("CREATE TABLE [dbo].[{0}](", tableName));
bool haskey = false;
foreach (var dcol in typeof(T).GetProperties())
{
var filedAttr = FiledInfoAttribute.GetAttribute(dcol);
string typename = filedAttr != null ? filedAttr.PropertyTypeName : "";
int len = filedAttr != null ? filedAttr.Len : -1;
string cannull = filedAttr != null ? filedAttr.CanNull ? "NULL" : "NOT NULL" : "NOT NULL";
if (string.IsNullOrWhiteSpace(typename))
{
typename = SqlHelp.ConvertType2Sql(dcol.PropertyType.Name);
}
stringBuilder.Append(string.Format("[{0}] [{1}]", dcol.Name, typename));
if (len != -1)
{
stringBuilder.Append(string.Format("({0})", len));
}
if (IdentityAttribute.GetAttribute(dcol) != null)
{
stringBuilder.Append("IDENTITY(1,1)");
}
stringBuilder.Append(string.Format(" {0}", cannull));
stringBuilder.Append(",");
if (KeyAttribute.IsDefined(dcol, typeof(KeyAttribute)))
{
stringBuilder.Append(string.Format(@"CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED ([{1}] ASC),", tableName, dcol.Name));
haskey = true;
}
}
if (!haskey)
{
throw new InvalidOperationException("you need appoint PRIMARY use KeyAttribute " + typeof(T).Name + " above");
}
stringBuilder.Append(") ON [PRIMARY]");
return(stringBuilder.ToString());
}
/// <summary>
/// 拼接sql批量插入语句，最大尺寸限制在1M ,1*1024*1024/102B=10240大概1w条数据
/// </summary>
/// <param name="dbName"></param>
/// <param name="tableName"></param>
/// <param name="prams"></param>
/// <returns></returns>
static public string insertMuanySql <T>(string dbName, string tableName, T[] objList, string CreateFromTempletteTable = "")
{
var Pros = typeof(T).GetProperties();
var ProsNamelist = Pros.Where(x => IdentityAttribute.GetAttribute(x) == null).Select(x => x.Name);//排除自增属性
string Sqlpramslist = "(" + string.Join(",", ProsNamelist) + ")";
StringBuilder stringBuilder = new StringBuilder("INSERT INTO [" + dbName + "].[dbo].[" + tableName + "]" + Sqlpramslist + " VALUES ");
foreach (var drow in objList)
{
stringBuilder.Append("(");
foreach (var dcol in Pros)
{
if (IdentityAttribute.GetAttribute(dcol) != null)
{
continue; //排除自增属性
}
var value = dcol.GetValue(drow).ToString();
stringBuilder.Append("'" + value + "',");
}
stringBuilder.Remove(stringBuilder.Length - 1, 1);
stringBuilder.Append("),");
}
stringBuilder.Remove(stringBuilder.Length - 1, 1);
if (CreateFromTempletteTable != "")
{
StringBuilder createBudiler = new StringBuilder(string.Format(@"
if not exists (select * from dbo.sysobjects where id = object_id(N'{0}.dbo.{1}') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
select * into {0}.dbo.[{1}] from {2}
end
", dbName, tableName, CreateFromTempletteTable));//从模板创建表
stringBuilder.Insert(0, createBudiler);
}
return(stringBuilder.ToString());
}
/// <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);
}
/// <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>
/// 添加一个实体
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public override int AddEntity(BaseEntity entity)
{
//判断是否启用了缓存
Type type = entity.GetType();
PropertyInfo[] propertyInfos = type.GetProperties();
var ps = new DynamicParameters {
};
PropertyInfo pk = null;
bool hasIdentityField = false;
foreach (PropertyInfo propertyInfo in propertyInfos)
{
if (IdentityAttribute.GetAttribute(propertyInfo) != null)
{
pk = propertyInfo;
hasIdentityField = true;
}
if (ExcludeFieldAttribute.GetAttribute(propertyInfo) != null)
{
continue;
}
ps.Add(propertyInfo.Name, propertyInfo.GetValue(entity, null));
}
string insertSql = GenerateInsertSql(type);
try
{
using (IDbConnection connection = OpenConnection(SqlExecuteType.Write))
{
OnExecutingCommand(insertSql);
decimal id = connection.Query <decimal>(insertSql, ps).FirstOrDefault();
OnExecutedCommand(insertSql);
if (hasIdentityField)
{
pk.SetValue(entity, Convert.ToInt32(id), null);
if (entity.IsEnableCache)
{
RetechWing.Infrastructure.Caching.CacheHelper.CacheService.Add(
EntityUpdateTrackHelper.GetEntityKey(entity), entity,
CachingExpirationType.SingleObject
);
}
}
return(Convert.ToInt32(id));
}
}
catch (Exception ex)
{
throw new Exception("发生错误，SQL语句为：" + insertSql + "\r\n实体为：" + entity, ex);
}
}