public IEnumerable <T> FindList <T>(Func <T, object> keySelector) where T : class, new()
{
using (var dbConnection = Connection)
{
return(dbConnection.Query <T>(string.Format("select * from {0} ", EntityAttribute.GetEntityTable <T>())).OrderBy(keySelector).ToList());
}
}
/// <summary>
/// 泛型方法，反射生成UpdateSql语句
/// </summary>
/// <param name="entity">实体类</param>
/// <returns>int</returns>
public static StringBuilder UpdateSql <T>(T entity)
{
string pkName = GetKeyField <T>().ToString();
Type type = entity.GetType();
PropertyInfo[] props = type.GetProperties();
StringBuilder sb = new StringBuilder();
sb.Append("Update ");
sb.Append(EntityAttribute.GetEntityTable <T>());
sb.Append(" Set ");
bool isFirstValue = true;
foreach (PropertyInfo prop in props)
{
if (prop.GetValue(entity, null) != null && pkName != prop.Name)
{
if (isFirstValue)
{
isFirstValue = false;
sb.Append(prop.Name);
sb.Append("=");
sb.Append(DbParameters.CreateDbParmCharacter() + prop.Name);
}
else
{
sb.Append("," + prop.Name);
sb.Append("=");
sb.Append(DbParameters.CreateDbParmCharacter() + prop.Name);
}
}
}
sb.Append(" Where ").Append(pkName).Append("=").Append(DbParameters.CreateDbParmCharacter() + pkName);
return(sb);
}
public DataTable FindTable <T, FindT>(FindT FindEntity, string orderField, bool isAsc, int pageSize, int pageIndex, out int total, string appendSql = "")
where FindT : class, new()
{
string sql = DatabaseCommon.QueryWhereSQL <FindT>(FindEntity).ToString();
Type type = FindEntity.GetType();
string viewName = "";
var viewAttribute = type.GetCustomAttributes(true).OfType <ViewAttribute>();
var descriptionAttributes = viewAttribute as ViewAttribute[] ?? viewAttribute.ToArray();
if (descriptionAttributes.Any())
{
viewName = descriptionAttributes.ToList()[0].viewName;
}
string tableName = string.IsNullOrWhiteSpace(viewName) ? EntityAttribute.GetEntityTable <T>() : viewName;
var translateInfo = DatabaseCommon.GetTranslateValue <T>();
if (translateInfo.Count(x => x.Length > 0) > 0)
{
sql = DatabaseCommon.PottingSql <T>(translateInfo, sql, tableName, string.IsNullOrWhiteSpace(viewName) ? false : true);
}
if (!string.IsNullOrEmpty(appendSql))
{
sql += appendSql;
}
return(FindTable(sql, orderField, isAsc, pageSize, pageIndex, out total));
}
public IEnumerable <T> FindList <T>() where T : class, new()
{
using (var dbConnection = Connection)
{
return(dbConnection.Query <T>(string.Format("SELECT * FROM {0} ", EntityAttribute.GetEntityTable <T>())).ToList());
}
}
public int Delete <T>(object keyValue) where T : class
{
DynamicParameters dynamicParameters = new global::Dapper.DynamicParameters();
dynamicParameters.Add("@primarykey", keyValue);
return(ExecuteBySql("Delete " + EntityAttribute.GetEntityTable <T>() + " where " + EntityAttribute.GetEntityKey <T>() + "=@primarykey", dynamicParameters));
}
/// <summary>
/// 删除实例
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="keyValue">主键值</param>
/// <returns></returns>
public int Delete <T>(object keyValue) where T : class
{
string sql = $"select * from {EntityAttribute.GetEntityTable<T>()} where {EntityAttribute.GetEntityKey<T>()}=@primarykey";
T entity = dbTransaction.Connection.Query <T>(sql, new { primarykey = keyValue }).FirstOrDefault();
return(Delete <T>(entity));
}
public IEnumerable <T> FindList <T>() where T : class, new()
{
using (var dbConnection = Connection)
{
//执行超时时间，刘西 修改于 2017.12.8
return(dbConnection.Query <T>(string.Format("SELECT * FROM {0} ", EntityAttribute.GetEntityTable <T>()), null, null, true, CommandTimeout).ToList());
}
}
public T FindEntity <T>(object keyValue) where T : class
{
using (var dbConnection = Connection)
{
string querySql = $"select * from {EntityAttribute.GetEntityTable<T>()} where key=@key";
var data = dbConnection.Query <T>(querySql, new { key = keyValue });
return(data.FirstOrDefault());
}
}
public T FindEntity <T>(object keyValue) where T : class
{
using (var dbConnection = Connection)
{
var sql = string.Format("select * from {0} where {1}=@primarykey", EntityAttribute.GetEntityTable <T>(), EntityAttribute.GetEntityPrimaryKey <T>());
var param = new { primarykey = keyValue };
var data = dbConnection.Query <T>(sql, param);
return(data.FirstOrDefault());
}
}
public T FindEntity <T>(object keyValue) where T : class
{
using (var dbConnection = Connection)
{
string name = EntityAttribute.GetEntityTable <T>();
string key2 = EntityAttribute.GetEntityKey <T>();
var data = dbConnection.Query <T>(string.Format("select * from {0} where {1}=@key", EntityAttribute.GetEntityTable <T>(), EntityAttribute.GetEntityKey <T>()), new { key = keyValue.ToString() });
return(data.FirstOrDefault());
}
}
public DataTable FindTable <T>(T entity)
{
if (entity == null)
{
return(db.FindTable(DatabaseCommon.SelectSql(EntityAttribute.GetEntityTable <T>())));
}
else
{
return(db.FindTable(DatabaseCommon.QueryWhereSQL <T>(entity).ToString()));
}
}
/// <summary>
/// 未实现
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public IQueryable <T> IQueryable <T>() where T : class, new()
{
string tablename = EntityAttribute.GetEntityTable <T>();
string sql = string.Format("select * from {0} where 1=1", tablename);
using (var dbConnection = Connection)
{
var data = dbConnection.Query <T>(sql);
return(data.AsQueryable());
}
}
/// <summary>
/// 泛型方法，反射生成UpdateSql语句
/// </summary>
/// <param name="entity">实体类</param>
/// <returns>int</returns>
public static StringBuilder UpdateSql <T>(T entity)
{
string pkName = null;
Type type = entity.GetType();
PropertyInfo[] props = type.GetProperties();
StringBuilder sb = new StringBuilder();
sb.Append("Update ");
sb.Append(EntityAttribute.GetEntityTable <T>());
sb.Append(" Set ");
bool isFirstValue = true;
foreach (PropertyInfo prop in props)
{
object value = prop.GetValue(entity);
if (value == null || value == DBNull.Value)
{
continue;
}
var attributesPrimaryKey = prop.GetCustomAttributes(typeof(PrimaryKeyAttribute), true) as PrimaryKeyAttribute[];
if (attributesPrimaryKey != null && attributesPrimaryKey.Length > 0)
{
pkName = prop.Name;
continue;
}
var attributesUpdate = prop.GetCustomAttributes(typeof(UpdateIgnoreAttribute), true) as UpdateIgnoreAttribute[];
if (attributesUpdate != null && attributesUpdate.Length > 0)
{
continue;
}
if (isFirstValue)
{
isFirstValue = false;
sb.Append(prop.Name);
sb.Append("=");
sb.Append(DbParameters.CreateDbParmCharacter() + prop.Name);
}
else
{
sb.Append("," + prop.Name);
sb.Append("=");
sb.Append(DbParameters.CreateDbParmCharacter() + prop.Name);
}
}
if (string.IsNullOrEmpty(pkName))
{
throw new ArgumentException("必须定义表的主键，不允许修改全表数据。");
}
sb.Append(" Where ").Append(pkName).Append("=").Append(DbParameters.CreateDbParmCharacter() + pkName);
return(sb);
}
public IQueryable <T> IQueryable <T>(string sql) where T : class, new()
{
string tablename = EntityAttribute.GetEntityTable <T>();
using (var dbConnection = Connection)
{
var translateInfo = DatabaseCommon.GetTranslateValue <T>();
if (translateInfo.Count(x => x.Length > 0) > 0)
{
sql = DatabaseCommon.PottingSql <T>(translateInfo, sql, tablename);
}
var data = dbConnection.Query <T>(sql);
return(data.AsQueryable());
}
}
/// <summary>
/// 实体更新可用状态
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entity"></param>
/// <returns></returns>
public void UpdateState <T>(int state, string key_value)
{
string tableName = EntityAttribute.GetEntityTable <T>();
string columName = DatabaseCommon.GetEnableColumn <T>();
string primaryKey = DatabaseCommon.GetKeyField <T>().ToString();
SqlParameter[] paramsMenber =
{
new SqlParameter("@TABLE", tableName),
new SqlParameter("@COLUMN", columName),
new SqlParameter("@STATE", state),
new SqlParameter("@PRIMARYKEY", primaryKey),
new SqlParameter("@KEY_VALUE", key_value)
};
ExecuteByProcReturn("P_UPDATESTATE", paramsMenber);
}
/// <summary>
/// 拼接删除SQL语句
/// </summary>
/// <param name="entity">实体类</param>
/// <returns></returns>
public static StringBuilder DeleteSql <T>(T entity)
{
Type type = entity.GetType();
PropertyInfo[] props = type.GetProperties();
StringBuilder sb = new StringBuilder("Delete From " + EntityAttribute.GetEntityTable <T>() + " Where 1=1");
foreach (PropertyInfo prop in props)
{
if (prop.GetValue(entity, null) != null)
{
sb.Append(" AND " + prop.Name + " = " + DbParameters.CreateDbParmCharacter() + "" + prop.Name + "");
}
}
return(sb);
}
public int Delete <T>(object[] keyValue) where T : class
{
DynamicParameters dynamicParameters = new global::Dapper.DynamicParameters();
string whereString = string.Empty;
string keyString = EntityAttribute.GetEntityKey <T>();
for (int i = 0; i < keyValue.Length; i++)
{
string ParametersName = string.Format("@primarykey{0}", i);
dynamicParameters.Add(ParametersName, keyValue[i]);
whereString += string.Format("{0} {1} = {2}", i == 0 ? string.Empty : " OR ", keyString, ParametersName);
}
string deleteString = "Delete " + EntityAttribute.GetEntityTable <T>() + " where ";
ExecuteBySql(string.Format("{0}{1}", deleteString, whereString), dynamicParameters);
return(dbTransaction == null?Commit() : 0);
}
public IEnumerable <T> FindList <T>(string orderField, bool isAsc, int pageSize, int pageIndex, out int total) where T : class, new()
{
using (var dbConnection = Connection)
{
StringBuilder sb = new StringBuilder();
int num = (pageIndex - 1) * pageSize;
int num1 = pageIndex * pageSize;
sb.Append("Select * From (Select ROW_NUMBER() Over ( order by " + orderField + ")");
sb.Append(" As rowNum, * From " + EntityAttribute.GetEntityTable <T>() + ") As N Where rowNum > " + num + " And rowNum <= " + num1 + "");
var dataQuery = dbConnection.Query <T>(sb.ToString());
total = Convert.ToInt32(new DbHelper(dbConnection).ExecuteScalar(CommandType.Text, "Select Count(1) From " + EntityAttribute.GetEntityTable <T>()));
return(dataQuery.ToList());
}
}
public T FindEntity <T>(object keyValue) where T : class
{
using (var dbConnection = Connection)
{
Type type = typeof(T);
string viewName = "";
var viewAttribute = type.GetCustomAttributes(true).OfType <ViewAttribute>();
var descriptionAttributes = viewAttribute as ViewAttribute[] ?? viewAttribute.ToArray();
if (descriptionAttributes.Any())
{
viewName = descriptionAttributes.ToList()[0].viewName;
}
string tableName = string.IsNullOrWhiteSpace(viewName) ? EntityAttribute.GetEntityTable <T>() : viewName;
var data = dbConnection.Query <T>("select * from " + tableName + " where " + EntityAttribute.GetEntityKey <T>() + "=@key", new { key = keyValue.ToString() });
return(data.FirstOrDefault());
}
}
/// <summary>
/// 泛型方法，反射生成UpdateSql语句
/// </summary>
/// <param name="entity">实体类</param>
/// <returns>int</returns>
public static StringBuilder UpdateNullSql <T>(T entity)
{
string pkName = GetKeyField <T>().ToString();
string[] EditColName = GetEditColArrName <T>();
Type type = entity.GetType();
PropertyInfo[] props = type.GetProperties();
StringBuilder sb = new StringBuilder();
sb.Append("Update ");
sb.Append(EntityAttribute.GetEntityTable <T>());
sb.Append(" Set ");
bool isFirstValue = true;
foreach (PropertyInfo prop in props)
{
if ((prop.GetValue(entity, null) != null && pkName != prop.Name) || EditColName.Contains(prop.Name))
{
string ColValue = Convert.ToString(type.GetProperty(prop.Name).GetValue(entity, null));
if (isFirstValue)
{
isFirstValue = false;
}
else
{
sb.Append(",");
}
if (string.IsNullOrWhiteSpace(ColValue) && EditColName.Contains(prop.Name))
{
sb.Append(prop.Name);
sb.Append("=null");
}
else
{
sb.Append(prop.Name);
sb.Append("=");
sb.Append("'" + ColValue + "'");
}
}
}
sb.Append(" Where ").Append(pkName).Append("=").Append("'" + Convert.ToString(type.GetProperty(pkName).GetValue(entity, null)) + "'");
return(sb);
}
public static StringBuilder QueryWhereSQL <T>(T entity)
{
StringBuilder sb = new StringBuilder();
Type type = entity.GetType();
string viewName = "";
var viewAttribute = type.GetCustomAttributes(true).OfType <ViewAttribute>();
var descriptionAttributes = viewAttribute as ViewAttribute[] ?? viewAttribute.ToArray();
if (descriptionAttributes.Any())
{
viewName = descriptionAttributes.ToList()[0].viewName;
}
string tableName = string.IsNullOrWhiteSpace(viewName) ? EntityAttribute.GetEntityTable <T>() : viewName;
sb.Append(string.Format("select {0}.* from ", tableName));
sb.Append(tableName);
sb.Append(" where 1=1 ");
return(GetQueryWhereString <T>(entity, tableName, type, sb));
}
public int Delete <T>(Expression <Func <T, bool> > condition) where T : class, new()
{
bool isTrans = true;
if (dbTransaction == null)
{
BeginTrans();
isTrans = false;
}
var table = EntityAttribute.GetEntityTable <T>();
IEnumerable <T> entities = dbTransaction.Connection.Query <T>(new SQLinq <T>(table).Where(condition));
Delete <T>(entities);
if (!isTrans)
{
return(Commit());
}
return(0);
}
/// <summary>
/// 泛型方法，反射生成InsertSql语句
/// </summary>
/// <param name="entity">实体类</param>
/// <returns>int</returns>
public static StringBuilder InsertSql <T>(T entity)
{
Type type = entity.GetType();
StringBuilder sb = new StringBuilder();
sb.Append(" Insert Into ");
sb.Append(EntityAttribute.GetEntityTable <T>());
sb.Append("(");
StringBuilder sp = new StringBuilder();
StringBuilder sb_prame = new StringBuilder();
PropertyInfo[] props = type.GetProperties();
foreach (PropertyInfo prop in props)
{
object value = prop.GetValue(entity);
if (value == null || value == DBNull.Value)
{
continue;
}
var attributesIdentity = prop.GetCustomAttributes(typeof(IdentityAttribute), true) as IdentityAttribute[];
if (attributesIdentity != null && attributesIdentity.Length > 0)
{
continue;
}
var attributesInsert = prop.GetCustomAttributes(typeof(InsertIgnoreAttribute), true) as InsertIgnoreAttribute[];
if (attributesInsert != null && attributesInsert.Length > 0)
{
continue;
}
sb_prame.Append("," + (prop.Name));
sp.Append("," + DbParameters.CreateDbParmCharacter() + "" + (prop.Name));
}
sb.Append(sb_prame.ToString().Substring(1, sb_prame.ToString().Length - 1) + ") Values (");
sb.Append(sp.ToString().Substring(1, sp.ToString().Length - 1) + ")");
return(sb);
}
public int Delete <T>(object propertyValue, string propertyName) where T : class
{
bool isTrans = true;
if (dbTransaction == null)
{
BeginTrans();
isTrans = false;
}
using (var dbConnection = Connection)
{
IEnumerable <T> entitys = dbConnection.Query <T>("select * from " + EntityAttribute.GetEntityTable <T>() + " where " + propertyName + "=@propertyValue", new { propertyValue = propertyValue.ToString() });
foreach (var entity in entitys)
{
Delete <T>(entity);
}
if (!isTrans)
{
return(Commit());
}
}
return(0);
}
/// <summary>
/// 根据属性键值删除匹配实例
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="propertyValue"></param>
/// <param name="propertyName"></param>
/// <returns></returns>
public int Delete <T>(object propertyValue, string propertyName) where T : class
{
bool isTrans = true;
if (dbTransaction == null)
{
BeginTrans();
isTrans = false;
}
string querySql = $"select * from {EntityAttribute.GetEntityTable<T>()} where {propertyName}=@propertyValue";
IEnumerable <T> entitys = dbTransaction.Connection.Query <T>(querySql, new { propertyValue = propertyValue });
foreach (var entity in entitys)
{
Delete <T>(entity);
}
if (!isTrans)
{
return(Commit());
}
return(0);
}
public IEnumerable <T> FindList <T>(Expression <Func <T, bool> > condition, string orderField, bool isAsc, int pageSize, int pageIndex, out int total) where T : class, new()
{
using (var dbConnection = Connection)
{
string[] _order = orderField.Split(',');
var dataLinq = $"select * from { EntityAttribute.GetEntityTable<T>()} where {ExpressionHelper.GetSqlByExpression(condition.Body)} ORDER BY";
int fieldCount = 0;
foreach (string item in _order)
{
string _orderPart = item;
_orderPart = Regex.Replace(_orderPart, @"\s+", " ");
string[] _orderArry = _orderPart.Split(' ');
string _orderField = _orderArry[0];
bool sort = isAsc;
if (_orderArry.Length == 2)
{
isAsc = _orderArry[1].ToUpper() == "ASC" ? true : false;
}
var parameter = Expression.Parameter(typeof(T), EntityAttribute.GetEntityTable <T>());
var property = typeof(T).GetProperty(_orderField);
var propertyAccess = Expression.MakeMemberAccess(parameter, property);
if (fieldCount == _order.Length - 1)
{
dataLinq += ($" {propertyAccess} {(isAsc ? "ASC" : "DESC")}");
}
else
{
dataLinq += ($" {propertyAccess} {(isAsc ? "ASC" : "DESC")},");
}
fieldCount++;
}
var dataQuery = dbConnection.Query <T>(dataLinq);
total = dataQuery.Count();
var data = dataQuery.Skip <T>(pageSize * (pageIndex - 1)).Take <T>(pageSize).AsQueryable();
return(data.ToList());
}
}
/// <summary>
/// 泛型方法，反射生成InsertSql语句
/// </summary>
/// <param name="entity">实体类</param>
/// <returns>int</returns>
public static StringBuilder InsertSql <T>(T entity)
{
Type type = entity.GetType();
StringBuilder sb = new StringBuilder();
sb.Append(" Insert Into ");
sb.Append(EntityAttribute.GetEntityTable <T>());
sb.Append("(");
StringBuilder sp = new StringBuilder();
StringBuilder sb_prame = new StringBuilder();
PropertyInfo[] props = type.GetProperties();
foreach (PropertyInfo prop in props)
{
if (prop.GetValue(entity, null) != null)
{
sb_prame.Append("," + (prop.Name));
sp.Append("," + DbParameters.CreateDbParmCharacter() + "" + (prop.Name));
}
}
sb.Append(sb_prame.ToString().Substring(1, sb_prame.ToString().Length - 1) + ") Values (");
sb.Append(sp.ToString().Substring(1, sp.ToString().Length - 1) + ")");
return(sb);
}
public DataTable FindTable <T>(T entity, Pagination pagination)
{
DataTable data;
if (entity != null && pagination != null)
{
int total = pagination.records;
if (pagination.sord == null)
{
pagination.sord = "asc";
}
data = db.FindTable(DatabaseCommon.QueryWhereSQL <T>(entity).ToString(), pagination.sidx, pagination.sord.ToLower() == "asc" ? true : false, pagination.rows, pagination.page, out total);
pagination.records = total;
}
else if (entity != null && pagination == null)
{
data = db.FindTable(DatabaseCommon.QueryWhereSQL <T>(entity).ToString());
}
else
{
data = db.FindTable(DatabaseCommon.SelectSql(EntityAttribute.GetEntityTable <T>()));
}
return(data);
}
public int Delete <T>(object keyValue) where T : class
{
T entity = dbTransaction.Connection.Query <T>(string.Format("select * from {0} where {1}=?primarykey", EntityAttribute.GetEntityTable <T>(), EntityAttribute.GetEntityKey <T>()), new { primarykey = keyValue }).FirstOrDefault();
return(Delete <T>(entity));
}
public int Delete <T>() where T : class
{
return(ExecuteBySql(DatabaseCommon.DeleteSql(EntityAttribute.GetEntityTable <T>()).ToString()));
}