/// <summary>
/// 根据SQL获取T
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="pars"></param>
/// <returns></returns>
public T GetSingle <T>(string sql, params SqlParameter[] pars)
{
var reval = SqlSugarTool.DataReaderToList <T>(typeof(T), GetReader(sql, pars), null).Single();
return(reval);
}
/// <summary>
/// 获取DataTable
/// </summary>
/// <param name="sql"></param>
/// <param name="pars">匿名参数(例如:new{id=1,name="张三"})</param>
/// <returns></returns>
public DataTable GetDataTable(string sql, object pars)
{
return(GetDataTable(sql, SqlSugarTool.GetParameters(pars)));
}
public object GetScalar(string sql, object pars)
{
return(GetScalar(sql, SqlSugarTool.GetParameters(pars)));
}
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>
/// 将实体对象转换成SqlParameter[]
/// </summary>
/// <param name="obj"></param>
/// <param name="pis"></param>
/// <returns></returns>
public static SqlParameter[] GetParameters(object obj, PropertyInfo [] pis = null)
{
List <SqlParameter> listParams = new List <SqlParameter>();
if (obj != null)
{
var type = obj.GetType();
var isDic = type.IsIn(SqlSugarTool.DicArraySO, SqlSugarTool.DicArraySS);
if (isDic)
{
if (type == SqlSugarTool.DicArraySO)
{
var newObj = (Dictionary <string, object>)obj;
var pars = newObj.Select(it => new SqlParameter("@" + it.Key, it.Value));
foreach (var par in pars)
{
SetParSize(par);
}
listParams.AddRange(pars);
}
else
{
var newObj = (Dictionary <string, string>)obj;
var pars = newObj.Select(it => new SqlParameter("@" + it.Key, it.Value));
foreach (var par in pars)
{
SetParSize(par);
}
listParams.AddRange(pars);;
}
}
else
{
PropertyInfo[] propertiesObj = null;
if (pis != null)
{
propertiesObj = pis;
}
else
{
propertiesObj = type.GetProperties();
}
string replaceGuid = Guid.NewGuid().ToString();
foreach (PropertyInfo r in propertiesObj)
{
var value = r.GetValue(obj, null);
if (r.PropertyType.IsEnum)
{
value = value.ObjToInt();
}
if (value == null || value.Equals(DateTime.MinValue))
{
value = DBNull.Value;
}
if (r.Name.ToLower().Contains("hierarchyid"))
{
var par = new SqlParameter("@" + r.Name, SqlDbType.Udt);
par.UdtTypeName = "HIERARCHYID";
par.Value = value;
listParams.Add(par);
}
else
{
var par = new SqlParameter("@" + r.Name, value);
SetParSize(par);
if (value == DBNull.Value)
{//防止文件类型报错
SqlSugarTool.SetSqlDbType(r, par);
}
listParams.Add(par);
}
}
}
}
return(listParams.ToArray());
}
/// <summary>
///获取没有符号的参数名称
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
internal static string GetSqlParameterNameNoParSymbol(this string name)
{
return(SqlSugarTool.GetSqlParameterNameNoParSymbol(name));
}
/// <summary>
/// 根据SQL获取T的集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="pars"></param>
/// <returns></returns>
public virtual List <T> GetList <T>(string sql, params SqlParameter[] pars)
{
var reval = SqlSugarTool.DataReaderToList <T>(typeof(T), GetReader(sql, pars), null);
return(reval);
}
/// <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>
/// 插入
/// 使用说明: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 identities = SqlSugarTool.GetIdentitiesKeyByTableName(this, typeName);
isIdentity = identities != null && identities.Count > 0;
//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.GetTypeInfo().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 || 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 (!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()))
{
val = _serialNumber.First(serEexp).GetNumFunc();
}
}
if (prop.PropertyType.GetTypeInfo().IsEnum)
{
val = (int)(val);
}
var par = new SqlParameter("@" + prop.Name, val);
if (par.SqlDbType == SqlDbType.Udt)
{
par.TypeName = "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>
/// 更新
/// 注意：rowObj为T类型将更新该实体的非主键所有列，如果rowObj类型为匿名类将更新指定列
/// 使用说明:sqlSugar.Update《T》(rowObj,whereObj);
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="rowObj">new T(){name="张三",sex="男"}或者new {name="张三",sex="男"}</param>
/// <param name="whereIn">new int[]{1,2,3}</param>
/// <returns></returns>
public bool Update <T, FiledType>(object rowObj, params FiledType[] whereIn) where T : class
{
if (rowObj == null)
{
throw new ArgumentNullException("SqlSugarClient.Update.rowObj");
}
Type type = typeof(T);
string typeName = type.Name;
typeName = GetTableNameByClassType(typeName);
StringBuilder sbSql = new StringBuilder(string.Format(" UPDATE {0} SET ", typeName));
Dictionary <string, object> rows = SqlSugarTool.GetObjectToDictionary(rowObj);
string pkName = SqlSugarTool.GetPrimaryKeyByTableName(this, typeName);
var identityNames = SqlSugarTool.GetIdentitiesKeyByTableName(this, typeName);
foreach (var r in rows)
{
var isPk = pkName != null && pkName.ToLower() == r.Key.ToLower();
var isIdentity = identityNames.Any(it => it.Value.ToLower() == r.Key.ToLower());
var isDisableUpdateColumns = DisableUpdateColumns != null && DisableUpdateColumns.Any(it => it.ToLower() == r.Key.ToLower());
if (isPk || isIdentity || isDisableUpdateColumns)
{
if (rowObj.GetType() == type)
{
continue;
}
}
sbSql.Append(string.Format(" [{0}] =@{0} ,", r.Key));
}
sbSql.Remove(sbSql.Length - 1, 1);
if (whereIn.Count() == 0)
{
var value = type.GetTypeInfo().GetProperties().Cast <PropertyInfo>().Single(it => it.Name == pkName).GetValue(rowObj, null);
sbSql.AppendFormat("WHERE {1} IN ('{2}')", typeName, pkName, value);
}
else
{
sbSql.AppendFormat("WHERE {1} IN ({2})", typeName, pkName, whereIn.ToJoinSqlInVal());
}
List <SqlParameter> parsList = new List <SqlParameter>();
var pars = rows.Select(c => new SqlParameter("@" + c.Key, c.Value));
if (pars != null)
{
foreach (var par in pars)
{
var isDisableUpdateColumns = DisableUpdateColumns != null && DisableUpdateColumns.Any(it => it.ToLower() == par.ParameterName.TrimStart('@').ToLower());
if (par.SqlDbType == SqlDbType.Udt || par.ParameterName.ToLower().Contains("hierarchyid"))
{
par.TypeName = "HIERARCHYID";
}
if (!isDisableUpdateColumns)
{
parsList.Add(par);
}
}
}
try
{
var updateRowCount = ExecuteCommand(sbSql.ToString(), parsList.ToArray());
return(updateRowCount > 0);
}
catch (Exception ex)
{
throw new Exception("sql:" + sbSql.ToString() + "\n" + ex.Message);
}
}
/// <summary>
/// 根据SQL语句将结果集映射到List《T》
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="whereObj"></param>
/// <returns></returns>
public List <T> SqlQuery <T>(string sql, object whereObj = null)
{
var pars = SqlSugarTool.GetParameters(whereObj).ToList();
return(SqlQuery <T>(sql, pars));
}
private bool SqlBulkReplace <T>(IEnumerable <T> entities) where T : class
{
if (entities == null)
{
return(false);
}
;
StringBuilder sbSql = new StringBuilder("");
Type type = typeof(T);
string typeName = type.Name;
typeName = GetTableNameByClassType(typeName);
string pkName = SqlSugarTool.GetPrimaryKeyByTableName(this, typeName);
Check.Exception(pkName.IsNullOrEmpty(), "没有找到主键。");
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();//去掉自添列
}
//属性缓存
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)
{
string pkValue = string.Empty;
sbSql.Append(" UPDATE ");
sbSql.Append(typeName);
sbSql.Append(" SET ");
pkValue = props.Single(it => it.Name.ToLower() == pkName.ToLower()).GetValue(entity, null).ToString();
foreach (var name in columnNames)
{
var isPk = pkName != null && pkName.ToLower() == name.ToLower();
var isDisableUpdateColumns = DisableUpdateColumns != null && DisableUpdateColumns.Any(it => it.ToLower() == name.ToLower());
var isLastName = name == columnNames.Last();
var prop = props.Single(it => it.Name == name);
var objValue = prop.GetValue(entity, null);
if (this.IsIgnoreErrorColumns)
{
if (!SqlSugarTool.GetColumnsByTableName(this, typeName).Any(it => it.ToLower() == name.ToLower()))
{
continue;
}
}
if (isPk || isDisableUpdateColumns)
{
continue;
}
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.AppendFormat(" [{0}]={1}{2} ", name, objValue, ",");
}
sbSql.Remove(sbSql.ToString().LastIndexOf(","), 1);
sbSql.AppendFormat("WHERE [{0}]='{1}' ", pkName, pkValue.ToSuperSqlFilter());
}
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.TypeName = "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>
/// 获取DataSet
/// </summary>
/// <param name="sql"></param>
/// <param name="pars"></param>
/// <returns></returns>
public DataSet GetDataSetAll(string sql, object pars)
{
return(GetDataSetAll(sql, SqlSugarTool.GetParameters(pars)));
}
/// <summary>
/// 获取第一行第一列
/// </summary>
/// <param name="sql"></param>
/// <param name="pars">匿名参数(例如:new{id=1,name="张三"})</param>
/// <returns></returns>
public string GetString(string sql, object pars)
{
return(GetString(sql, SqlSugarTool.GetParameters(pars)));
}
/// <summary>
/// 获取表结构信息
/// </summary>
/// <param name="db"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public List <PubModel.DataTableMap> GetTableColumns(SqlSugarClient db, string tableName)
{
string sql = SqlSugarTool.GetTtableColumnsInfo(tableName);
return(db.SqlQuery <PubModel.DataTableMap>(sql));
}
/// <summary>
/// 获取第一行第一列
/// </summary>
/// <param name="sql"></param>
/// <param name="pars">匿名参数(例如:new{id=1,name="张三"})</param>
/// <returns></returns>
public int GetInt(string sql, object pars)
{
return(GetInt(sql, SqlSugarTool.GetParameters(pars)));
}
/// <summary>
/// 批量假删除
/// 使用说明::
/// FalseDelete《T》("is_del",new int[]{1,2,3})或者Delete《T》("is_del",3)
/// </summary>
/// <param name="field">更新删除状态字段</param>
/// <param name="whereIn">delete ids</param>
public bool FalseDelete <T, FiledType>(string field, params FiledType[] whereIn)
{
Type type = typeof(T);
string typeName = type.Name;
typeName = GetTableNameByClassType(typeName);
//属性缓存
string cachePropertiesKey = "db." + typeName + ".GetProperties";
var cachePropertiesManager = CacheManager <PropertyInfo[]> .GetInstance();
PropertyInfo[] props = SqlSugarTool.GetGetPropertiesByCache(type, cachePropertiesKey, cachePropertiesManager);
bool isSuccess = false;
if (whereIn != null && whereIn.Length > 0)
{
string sql = string.Format("UPDATE {0} SET {3}=1 WHERE {1} IN ({2})", typeName, SqlSugarTool.GetPrimaryKeyByTableName(this, typeName), whereIn.ToJoinSqlInVal(), field);
int deleteRowCount = ExecuteCommand(sql);
isSuccess = deleteRowCount > 0;
}
return(isSuccess);
}
/// <summary>
/// 获取DataReader
/// </summary>
/// <param name="sql"></param>
/// <param name="pars">匿名参数(例如:new{id=1,name="张三"})</param>
/// <returns></returns>
public SqlDataReader GetReader(string sql, object pars)
{
return(GetReader(sql, SqlSugarTool.GetParameters(pars)));
}
/// <summary>
/// 获取转释后的表名和列名
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public static string GetTranslationSqlName(this string tableName)
{
return(SqlSugarTool.GetTranslationSqlName(tableName));
}
/// <summary>
/// 根据SQL获取T的集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="pars">匿名参数(例如:new{id=1,name="张三"})</param>
/// <returns></returns>
public List <T> GetList <T>(string sql, object pars)
{
return(GetList <T>(sql, SqlSugarTool.GetParameters(pars)));
}
/// <summary>
/// 动态获取IDataRecord里面的函数
/// </summary>
/// <param name="generator"></param>
/// <param name="type"></param>
/// <param name="isNullable"></param>
/// <param name="pro"></param>
/// <param name="dbTypeName"></param>
/// <param name="fieldName"></param>
private static void GeneratorCallMethod(ILGenerator generator, Type type, bool isNullable, PropertyInfo pro, string dbTypeName, string fieldName)
{
List <string> guidThrow = new List <string>()
{
"int32", "datetime", "decimal", "double", "byte", "string"
}; //数据库为GUID有错的实体类形
List <string> intThrow = new List <string>()
{
"datetime", "byte"
}; //数据库为int有错的实体类形
List <string> stringThrow = new List <string>()
{
"int32", "datetime", "decimal", "double", "byte", "guid"
}; //数据库为vachar有错的实体类形
List <string> decimalThrow = new List <string>()
{
"datetime", "byte", "guid"
};
List <string> doubleThrow = new List <string>()
{
"datetime", "byte", "guid"
};
List <string> dateThrow = new List <string>()
{
"int32", "decimal", "double", "byte", "guid"
};
List <string> shortThrow = new List <string>()
{
"datetime", "guid"
};
List <string> byteThrow = new List <string>()
{
"datetime", "guid"
};
MethodInfo method = null;
var typeName = SqlSugarTool.ChangeDBTypeToCSharpType(dbTypeName);
var objTypeName = type.Name.ToLower();
var isEnum = type.IsEnum;
if (isEnum)
{
typeName = "ENUMNAME";
}
else if (dbTypeName.Contains("hierarchyid") || typeName == "byte[]" || objTypeName == "object")
{
generator.Emit(OpCodes.Call, getValueMethod);
generator.Emit(OpCodes.Unbox_Any, pro.PropertyType);//找不到类型才执行拆箱（类型转换）
return;
}
if (isNullable)
{
switch (typeName)
{
case "int":
CheckType(intThrow, objTypeName, typeName, fieldName);
var isNotInt = objTypeName != "int32";
if (isNotInt)
{
method = getOtherNull.MakeGenericMethod(type);
}
else
{
method = getConvertInt32;
} break;
case "long":
CheckType(intThrow, objTypeName, typeName, fieldName);
var isNotLong = objTypeName != "int64";
if (isNotLong)
{
method = getOtherNull.MakeGenericMethod(type);
}
else
{
method = getConvetInt64;
} break;
case "bool":
if (objTypeName != "bool" && objTypeName != "boolean")
{
method = getOtherNull.MakeGenericMethod(type);
}
else
{
method = getConvertBoolean;
} break;
case "string":
CheckType(stringThrow, objTypeName, typeName, fieldName);
method = getString; break;
case "dateTime":
CheckType(dateThrow, objTypeName, typeName, fieldName);
if (objTypeName != "datetime")
{
method = getOtherNull.MakeGenericMethod(type);
}
else
{
method = getConvertDateTime;
} break;
case "decimal":
CheckType(decimalThrow, objTypeName, typeName, fieldName);
var isNotDecimal = objTypeName != "decimal";
if (isNotDecimal)
{
method = getOtherNull.MakeGenericMethod(type);
}
else
{
method = getConvertDecimal;
} break;
case "double":
CheckType(doubleThrow, objTypeName, typeName, fieldName);
var isNotDouble = objTypeName != "double";
if (isNotDouble)
{
method = getOtherNull.MakeGenericMethod(type);
}
else
{
method = getConvertDouble;
} break;
case "float":
CheckType(decimalThrow, objTypeName, typeName, fieldName);
if (objTypeName != "float" && objTypeName != "single")
{
method = getOtherNull.MakeGenericMethod(type);
}
else
{
method = getConvertFloat;
} break;
case "guid":
CheckType(guidThrow, objTypeName, typeName, fieldName);
if (objTypeName != "guid")
{
method = getOtherNull.MakeGenericMethod(type);
}
else
{
method = getConvertGuid;
} break;
case "byte":
CheckType(byteThrow, objTypeName, typeName, fieldName);
if (objTypeName != "byte")
{
method = getOtherNull.MakeGenericMethod(type);
}
else
{
method = getConvertByte;
} break;
case "ENUMNAME":
method = getConvertToEnum_Nullable.MakeGenericMethod(type); break;
case "short":
CheckType(shortThrow, objTypeName, typeName, fieldName);
var isNotShort = objTypeName != "int16" && objTypeName != "short";
if (isNotShort)
{
method = getOtherNull.MakeGenericMethod(type);
}
else
{
method = getConvertInt16;
}
break;
default:
method = getOtherNull.MakeGenericMethod(type); break;
}
generator.Emit(OpCodes.Call, method);
}
else
{
switch (typeName)
{
case "int":
CheckType(intThrow, objTypeName, typeName, fieldName);
var isNotInt = objTypeName != "int32";
if (isNotInt)
{
method = getOther.MakeGenericMethod(type);
}
else
{
method = getInt32;
} break;
case "long":
CheckType(intThrow, objTypeName, typeName, fieldName);
var isNotLong = objTypeName != "int64";
if (isNotLong)
{
method = getOther.MakeGenericMethod(type);
}
else
{
method = getInt64;
} break;
case "bool":
if (objTypeName != "bool" && objTypeName != "boolean")
{
method = getOther.MakeGenericMethod(type);
}
else
{
method = getBoolean;
} break;
case "string":
CheckType(stringThrow, objTypeName, typeName, fieldName);
method = getString; break;
case "dateTime":
CheckType(dateThrow, objTypeName, typeName, fieldName);
if (objTypeName != "datetime")
{
method = getOther.MakeGenericMethod(type);
}
else
{
method = getDateTime;
} break;
case "decimal":
CheckType(decimalThrow, objTypeName, typeName, fieldName);
var isNotDecimal = objTypeName != "decimal";
if (isNotDecimal)
{
method = getOther.MakeGenericMethod(type);
}
else
{
method = getDecimal;
} break;
case "double":
CheckType(doubleThrow, objTypeName, typeName, fieldName);
var isNotDouble = objTypeName != "double";
if (isNotDouble)
{
method = getOther.MakeGenericMethod(type);
}
else
{
method = getDouble;
} break;
case "float":
CheckType(decimalThrow, objTypeName, typeName, fieldName);
if (objTypeName != "float" && objTypeName != "single")
{
method = getOther.MakeGenericMethod(type);
}
else
{
method = getFloat;
} break;
case "guid":
CheckType(guidThrow, objTypeName, typeName, fieldName);
if (objTypeName != "guid")
{
method = getOther.MakeGenericMethod(type);
}
else
{
method = getGuid;
} break;
case "byte":
CheckType(byteThrow, objTypeName, typeName, fieldName);
if (objTypeName != "byte")
{
method = getOther.MakeGenericMethod(type);
}
else
{
method = getByte;
} break;
case "ENUMNAME":
method = getValueMethod; break;
case "short":
CheckType(shortThrow, objTypeName, typeName, fieldName);
var isNotShort = objTypeName != "int16" && objTypeName != "short";
if (isNotShort)
{
method = getOther.MakeGenericMethod(type);
}
else
{
method = getInt16;
}
break;
default:
method = getOther.MakeGenericMethod(type);
break;;
}
generator.Emit(OpCodes.Call, method);
if (method == getValueMethod)
{
generator.Emit(OpCodes.Unbox_Any, pro.PropertyType);//找不到类型才执行拆箱（类型转换）
}
}
}
/// <summary>
/// 根据SQL获取T
/// </summary>
/// <typeparam name="T">可以是int、string等，也可以是类或者数组、字典</typeparam>
/// <param name="sql"></param>
/// <param name="pars">匿名参数(例如:new{id=1,name="张三"})</param>
/// <returns></returns>
public T GetSingle <T>(string sql, object pars)
{
return(GetSingle <T>(sql, SqlSugarTool.GetParameters(pars)));
}
public int ExecuteCommand(string sql, object pars)
{
return(ExecuteCommand(sql, SqlSugarTool.GetParameters(pars)));
}
/// <summary>
/// 批量删除
/// 注意：whereIn 主键集合
/// 使用说明:Delete《T》(new int[]{1,2,3}) 或者 Delete《T》(3)
/// </summary>
/// <param name="whereIn"> delete ids </param>
public bool Delete <T>(params dynamic[] whereIn)
{
Type type = typeof(T);
//属性缓存
string cachePropertiesKey = "db." + type.Name + ".GetProperties";
var cachePropertiesManager = CacheManager <PropertyInfo[]> .GetInstance();
PropertyInfo[] props = SqlSugarTool.GetGetPropertiesByCache(type, cachePropertiesKey, cachePropertiesManager);
string key = type.FullName;
bool isSuccess = false;
if (whereIn != null && whereIn.Length > 0)
{
string sql = string.Format("DELETE FROM {0} WHERE {1} IN ({2})", type.Name, SqlSugarTool.GetPrimaryKeyByTableName(this, type.Name), whereIn.ToJoinSqlInVal());
int deleteRowCount = ExecuteCommand(sql);
isSuccess = deleteRowCount > 0;
}
return(isSuccess);
}
