private bool SqlBulkCopy <T>(IEnumerable <T> entities) where T : class
{
if (entities == null)
{
return(false);
}
;
Type type = typeof(T);
string typeName = type.Name;
typeName = GetTableNameByClassType(typeName);
string pkName = SqlSugarTool.GetPrimaryKeyByTableName(this, typeName);
var identityNames = SqlSugarTool.GetIdentitiesKeyByTableName(this, typeName);
var isIdentity = identityNames != null && identityNames.Count > 0;
var columnNames = SqlSugarTool.GetColumnsByTableName(this, typeName);
if (isIdentity)
{
columnNames = columnNames.Where(c => !identityNames.Any(it => it.Value == c)).ToList();//去掉自添列
}
StringBuilder sbSql = new StringBuilder("INSERT INTO ");
sbSql.AppendLine(typeName);
sbSql.AppendFormat("({0})", string.Join(",", columnNames.Select(it => "[" + it + "]")));
//属性缓存
string cachePropertiesKey = "db." + type.FullName + ".GetProperties";
var cachePropertiesManager = CacheManager <PropertyInfo[]> .GetInstance();
PropertyInfo[] props = null;
if (cachePropertiesManager.ContainsKey(cachePropertiesKey))
{
props = cachePropertiesManager[cachePropertiesKey];
}
else
{
props = type.GetProperties();
cachePropertiesManager.Add(cachePropertiesKey, props, cachePropertiesManager.Day);
}
foreach (var entity in entities)
{
sbSql.AppendLine("SELECT ");
foreach (var name in columnNames)
{
var isLastName = name == columnNames.Last();
var prop = props.Single(it => it.Name == name);
var objValue = prop.GetValue(entity, null);
bool isNullable = false;
var underType = SqlSugarTool.GetUnderType(prop, ref isNullable);
if (objValue == null)
{
objValue = "NULL";
}
else if (underType == SqlSugarTool.DateType)
{
objValue = "'" + objValue.ToString() + "'";
}
else if (underType == SqlSugarTool.BoolType)
{
objValue = Convert.ToBoolean(objValue) ? 1 : 0;
}
else if (underType == SqlSugarTool.StringType)
{
//string参数需要处理注入 (因为SqlParameter参数上限为2100所以无法使用参数化)
objValue = "'" + objValue.ToString().ToSqlFilter() + "'";
}
else
{
objValue = "'" + objValue.ToString() + "'";
}
sbSql.Append(objValue + (isLastName ? "" : ","));
}
var isLastEntity = entities.Last() == entity;
if (!isLastEntity)
{
sbSql.AppendLine(" UNION ALL ");
}
}
var reval = base.ExecuteCommand(sbSql.ToString());
sbSql = null;
return(reval > 0);
}
/// <summary>
/// 根据表达式条件将实体对象更新到数据库
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="rowObj">rowObj为匿名对象时只更新指定列( 例如:new{ name='abc'}只更新name )，为T类型将更新整个实体(排除主键、自增列和禁止更新列)</param>
/// <param name="expression">表达式条件</param>
/// <returns>更新成功返回true</returns>
public bool Update <T>(object rowObj, Expression <Func <T, bool> > expression) where T : class
{
if (rowObj == null)
{
throw new ArgumentNullException("SqlSugarClient.Update.rowObj");
}
if (expression == null)
{
throw new ArgumentNullException("SqlSugarClient.Update.expression");
}
Type type = typeof(T);
string typeName = type.Name;
typeName = GetTableNameByClassType(typeName);
var rows = SqlSugarTool.GetParameters(rowObj);
var isDynamic = rowObj.GetType() != type;
var isClass = !isDynamic;
//sql语句缓存
string cacheSqlKey = "db.update." + type.FullName + rows.Length;
var cacheSqlManager = CacheManager <StringBuilder> .GetInstance();
string pkName = SqlSugarTool.GetPrimaryKeyByTableName(this, typeName);
var identityNames = SqlSugarTool.GetIdentitiesKeyByTableName(this, typeName);
ResolveExpress re = new ResolveExpress();
re.ResolveExpression(re, expression);
StringBuilder sbSql = new StringBuilder();
if (cacheSqlManager.ContainsKey(cacheSqlKey) && isClass)
{
sbSql = cacheSqlManager[cacheSqlKey];
}
else
{
sbSql = new StringBuilder(string.Format(" UPDATE [{0}] SET ", typeName));
foreach (var r in rows)
{
var name = r.ParameterName.TrimStart('@');
var isPk = pkName != null && pkName.ToLower() == name.ToLower();
var isIdentity = identityNames.Any(it => it.Value.ToLower() == name.ToLower());
var isDisableUpdateColumns = DisableUpdateColumns != null && DisableUpdateColumns.Any(it => it.ToLower() == name.ToLower());
if (this.IsIgnoreErrorColumns)
{
if (!SqlSugarTool.GetColumnsByTableName(this, typeName).Any(it => it.ToLower() == name.ToLower()))
{
continue;
}
}
if (isPk || isIdentity || isDisableUpdateColumns)
{
if (isClass)
{
continue;
}
}
sbSql.Append(string.Format(" [{0}] =@{0} ,", name));
}
sbSql.Remove(sbSql.Length - 1, 1);
sbSql.Append(" WHERE 1=1 ");
sbSql.Append(re.SqlWhere);
cacheSqlManager.Add(cacheSqlKey, sbSql, cacheSqlManager.Day);
}
List <SqlParameter> parsList = new List <SqlParameter>();
parsList.AddRange(re.Paras);
var pars = rows;
if (pars != null)
{
foreach (var par in pars)
{
if (par.SqlDbType == SqlDbType.Udt)
{
par.UdtTypeName = "HIERARCHYID";
}
SqlSugarTool.SetParSize(par);
parsList.Add(par);
}
}
try
{
var updateRowCount = ExecuteCommand(sbSql.ToString(), parsList.ToArray());
return(updateRowCount > 0);
}
catch (Exception ex)
{
throw new SqlSugarException(ex.Message, sbSql.ToString(), new { rowObj = rowObj, expression = expression + "" });
}
}
/// <summary>
/// 插入
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entity">插入对象</param>
/// <param name="isIdentity">过期参数无需填写</param>
/// <returns>默认返回Identity，如果没有Identity执行成功将返回true</returns>
public object Insert <T>(T entity, bool isIdentity = true) where T : class
{
Type type = entity.GetType();
string typeName = type.Name;
typeName = GetTableNameByClassType(typeName);
StringBuilder sbInsertSql = new StringBuilder();
List <SqlParameter> pars = new List <SqlParameter>();
var identities = SqlSugarTool.GetIdentitiesKeyByTableName(this, typeName);
isIdentity = identities != null && identities.Count > 0;
//sql语句缓存
string cacheSqlKey = "db.Insert." + type.FullName;
if (this.DisableInsertColumns.IsValuable())
{
cacheSqlKey = cacheSqlKey + string.Join("", this.DisableInsertColumns);
}
var cacheSqlManager = CacheManager <StringBuilder> .GetInstance();
//属性缓存
string cachePropertiesKey = "db." + type.FullName + ".GetProperties";
var cachePropertiesManager = CacheManager <PropertyInfo[]> .GetInstance();
PropertyInfo[] props = null;
if (cachePropertiesManager.ContainsKey(cachePropertiesKey))
{
props = cachePropertiesManager[cachePropertiesKey];
}
else
{
props = type.GetProperties();
cachePropertiesManager.Add(cachePropertiesKey, props, cachePropertiesManager.Day);
}
var isContainCacheSqlKey = cacheSqlManager.ContainsKey(cacheSqlKey);
if (isContainCacheSqlKey)
{
sbInsertSql = cacheSqlManager[cacheSqlKey];
}
else
{
//2.获得实体的属性集合
//实例化一个StringBuilder做字符串的拼接
sbInsertSql.Append("insert into [" + typeName + "] (");
//3.遍历实体的属性集合
foreach (PropertyInfo prop in props)
{
if (this.IsIgnoreErrorColumns)
{
if (!SqlSugarTool.GetColumnsByTableName(this, typeName).Any(it => it.ToLower() == prop.Name.ToLower()))
{
continue;
}
}
if (this.DisableInsertColumns.IsValuable())
{
if (this.DisableInsertColumns.Any(it => it.ToLower() == prop.Name.ToLower()))
{
continue;
}
}
//EntityState,@EntityKey
if (!isIdentity || identities.Any(it => it.Value.ToLower() != prop.Name.ToLower()))
{
//4.将属性的名字加入到字符串中
sbInsertSql.Append("[" + prop.Name + "],");
}
}
//**去掉最后一个逗号
sbInsertSql.Remove(sbInsertSql.Length - 1, 1);
sbInsertSql.Append(" ) values(");
}
//5.再次遍历，形成参数列表"(@xx,@xx@xx)"的形式
foreach (PropertyInfo prop in props)
{
//EntityState,@EntityKey
if (!isIdentity || identities.Any(it => it.Value.ToLower() != prop.Name.ToLower()))
{
if (this.IsIgnoreErrorColumns)
{
if (!SqlSugarTool.GetColumnsByTableName(this, typeName).Any(it => it.ToLower() == prop.Name.ToLower()))
{
continue;
}
}
if (this.DisableInsertColumns.IsValuable())
{
if (this.DisableInsertColumns.Any(it => it.ToLower() == prop.Name.ToLower()))
{
continue;
}
}
if (!cacheSqlManager.ContainsKey(cacheSqlKey))
{
sbInsertSql.Append("@" + prop.Name + ",");
}
object val = prop.GetValue(entity, null);
if (val == null)
{
val = DBNull.Value;
}
if (_serialNumber.IsValuable())
{
Func <PubModel.SerialNumber, bool> serEexp = it => it.TableName.ToLower() == typeName.ToLower() && it.FieldName.ToLower() == prop.Name.ToLower();
var isAnyNum = _serialNumber.Any(serEexp);
if (isAnyNum && (val == DBNull.Value || val.IsNullOrEmpty()))
{
if (_serialNumber.First(serEexp).GetNumFunc != null)
{
val = _serialNumber.First(serEexp).GetNumFunc();
}
if (_serialNumber.First(serEexp).GetNumFuncWithDb != null)
{
val = _serialNumber.First(serEexp).GetNumFuncWithDb(this);
}
}
}
if (prop.PropertyType.IsEnum)
{
val = (int)(val);
}
var par = new SqlParameter("@" + prop.Name, val);
SqlSugarTool.SetParSize(par);
if (par.SqlDbType == SqlDbType.Udt)
{
par.UdtTypeName = "HIERARCHYID";
}
pars.Add(par);
}
}
if (!isContainCacheSqlKey)
{
//**去掉最后一个逗号
sbInsertSql.Remove(sbInsertSql.Length - 1, 1);
if (isIdentity == false)
{
sbInsertSql.Append(");select 'true';");
}
else
{
sbInsertSql.Append(");select SCOPE_IDENTITY();");
}
cacheSqlManager.Add(cacheSqlKey, sbInsertSql, cacheSqlManager.Day);
}
var sql = sbInsertSql.ToString();
try
{
var lastInsertRowId = GetScalar(sql, pars.ToArray());
return(lastInsertRowId);
}
catch (Exception ex)
{
throw new SqlSugarException(ex.Message, sql, entity);
}
}
/// <summary>
/// 清除所有缓存
/// </summary>
public void RemoveAllCache <T>()
{
CacheManager <T> .GetInstance().RemoveAll(c => true);
}
/// <summary>
/// 清除所有缓存
/// </summary>
public void RemoveAllCache()
{
CacheManager<int>.GetInstance().RemoveAll(c => c.Contains("SqlSugar."));
}
/// <summary>
/// 插入
/// 使用说明:sqlSugar.Insert(entity);
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entity">插入对象</param>
/// <param name="isIdentity">主键是否为自增长,true可以不填,false必填</param>
/// <returns></returns>
public object Insert<T>(T entity, bool isIdentity = true) where T : class
{
Type type = entity.GetType();
string typeName = type.Name;
typeName = GetTableNameByClassType(typeName);
StringBuilder sbInsertSql = new StringBuilder();
List<SqlParameter> pars = new List<SqlParameter>();
var primaryKeyName = SqlSugarTool.GetPrimaryKeyByTableName(this, typeName);
//sql语句缓存
string cacheSqlKey = "db.Insert." + typeName;
var cacheSqlManager = CacheManager<StringBuilder>.GetInstance();
//属性缓存
string cachePropertiesKey = "db." + typeName + ".GetProperties";
var cachePropertiesManager = CacheManager<PropertyInfo[]>.GetInstance();
PropertyInfo[] props = null;
if (cachePropertiesManager.ContainsKey(cachePropertiesKey))
{
props = cachePropertiesManager[cachePropertiesKey];
}
else
{
props = type.GetProperties();
cachePropertiesManager.Add(cachePropertiesKey, props, cachePropertiesManager.Day);
}
var isContainCacheSqlKey = cacheSqlManager.ContainsKey(cacheSqlKey);
if (isContainCacheSqlKey)
{
sbInsertSql = cacheSqlManager[cacheSqlKey];
}
else
{
//2.获得实体的属性集合
//实例化一个StringBuilder做字符串的拼接
sbInsertSql.Append("insert into " + typeName + " (");
//3.遍历实体的属性集合
foreach (PropertyInfo prop in props)
{
//EntityState,@EntityKey
if (isIdentity == false || (isIdentity && prop.Name != primaryKeyName))
{
//4.将属性的名字加入到字符串中
sbInsertSql.Append("["+prop.Name + "],");
}
}
//**去掉最后一个逗号
sbInsertSql.Remove(sbInsertSql.Length - 1, 1);
sbInsertSql.Append(" ) values(");
}
//5.再次遍历，形成参数列表"(@xx,@xx@xx)"的形式
foreach (PropertyInfo prop in props)
{
//EntityState,@EntityKey
if (isIdentity == false || (isIdentity && prop.Name != primaryKeyName))
{
if (!cacheSqlManager.ContainsKey(cacheSqlKey))
sbInsertSql.Append("@" + prop.Name + ",");
object val = prop.GetValue(entity, null);
if (val == null)
val = DBNull.Value;
var par = new SqlParameter("@" + prop.Name, val);
if (par.SqlDbType == SqlDbType.Udt)
{
par.UdtTypeName = "HIERARCHYID";
}
pars.Add(par);
}
}
if (!isContainCacheSqlKey)
{
//**去掉最后一个逗号
sbInsertSql.Remove(sbInsertSql.Length - 1, 1);
if (isIdentity == false)
{
sbInsertSql.Append(");select 'true';");
}
else
{
sbInsertSql.Append(");select @@identity;");
}
cacheSqlManager.Add(cacheSqlKey, sbInsertSql, cacheSqlManager.Day);
}
var sql = sbInsertSql.ToString();
try
{
var lastInsertRowId = GetScalar(sql, pars.ToArray());
return lastInsertRowId;
}
catch (Exception ex)
{
throw new Exception("sql:" + sql + "\n" + ex.Message);
}
}
/// <summary>
/// Reader转成T的集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="type"></param>
/// <param name="dr"></param>
/// <param name="fields"></param>
/// <param name="isClose"></param>
/// <returns></returns>
internal static List <T> DataReaderToList <T>(Type type, IDataReader dr, string fields, bool isClose = true)
{
if (type.Name.Contains("KeyValuePair"))
{
List <T> strReval = new List <T>();
FillValueTypeToDictionary(type, dr, strReval);
return(strReval);
}
//值类型
else if (type.IsValueType() || type == SqlSugarTool.StringType)
{
List <T> strReval = new List <T>();
FillValueTypeToDr <T>(type, dr, strReval);
return(strReval);
}
//数组类型
else if (type.IsArray)
{
List <T> strReval = new List <T>();
FillValueTypeToArray(type, dr, strReval);
return(strReval);
}
var cacheManager = CacheManager <IDataReaderEntityBuilder <T> > .GetInstance();
string key = "DataReaderToList." + fields + type.FullName;
IDataReaderEntityBuilder <T> eblist = null;
if (cacheManager.ContainsKey(key))
{
eblist = cacheManager[key];
}
else
{
eblist = IDataReaderEntityBuilder <T> .CreateBuilder(type, dr);
cacheManager.Add(key, eblist, cacheManager.Day);
}
List <T> list = new List <T>();
try
{
if (dr == null)
{
return(list);
}
while (dr.Read())
{
list.Add(eblist.Build(dr));
}
if (isClose)
{
dr.Close(); dr.Dispose(); dr = null;
}
}
catch (Exception ex)
{
if (isClose)
{
dr.Close(); dr.Dispose(); dr = null;
}
Check.Exception(true, "错误信息：实体映射出错。\r\n错误详情：{0}", ex.Message);
}
return(list);
}