public DataTable FindTable <T>(T entity, Pagination pagination)
{
int total = pagination.records;
if (pagination.sord == null)
{
pagination.sord = "asc";
}
var 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;
return(data);
}
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);
}
public IEnumerable <T> ExecuteByProcTable <T>(string procName, DbParameter[] dbParameter)
{
if (dbTransaction == null)
{
using (var dbConnection = Connection)
{
DynamicParameters dp = DatabaseCommon.GetDynamicParameter(dbParameter);
return(dbConnection.Query <T>(procName, dp, commandType: CommandType.StoredProcedure));
}
}
else
{
dbTransaction.Connection.Execute(procName, dbParameter, dbTransaction);
return(null);
}
}
public int ExecuteByProc(string procName, params DbParameter[] dbParameter)
{
if (dbTransaction == null)
{
using (var connection = Connection)
{
DynamicParameters dp = DatabaseCommon.GetDynamicParameter(dbParameter);
return(connection.Execute(procName, dp, null, null, CommandType.StoredProcedure));
}
}
else
{
dbTransaction.Connection.Execute(procName, dbParameter, dbTransaction);
return(0);
}
}
public DataTable ExecuteByProcReportTable(string procName, DbParameter[] dbParameter)
{
if (dbTransaction == null)
{
using (var dbConnection = Connection)
{
DynamicParameters dp = DatabaseCommon.GetDynamicParameter(dbParameter);
var IDataReader = new DbHelper(dbConnection).ExecuteReader(CommandType.StoredProcedure, procName, dbParameter);
return(ConvertExtension.IDataReaderToDataTable(IDataReader));
}
}
else
{
dbTransaction.Connection.Execute(procName, dbParameter, dbTransaction);
return(null);
}
}
public int ExecuteByProcReturn(string procName, DbParameter[] dbParameter)
{
if (dbTransaction == null)
{
using (var connection = Connection)
{
DynamicParameters dp = DatabaseCommon.GetDynamicParameter(dbParameter);
dp.Add("@RES", string.Empty, DbType.String, ParameterDirection.ReturnValue);
connection.Execute(procName, dp, null, null, CommandType.StoredProcedure);
return(dp.Get <int>("@RES"));
}
}
else
{
dbTransaction.Connection.Execute(procName, dbParameter, dbTransaction);
return(0);
}
}
public int Delete <T>(Expression <Func <T, bool> > condition) where T : class, new()
{
bool isTrans = true;
if (dbTransaction == null)
{
BeginTrans();
isTrans = false;
}
string conditionsql = DatabaseCommon.DealExp <T>(condition.Body, OperateType.Delete).ToString();
DbCommand cmd = dbTransaction.Connection.CreateCommand();
cmd.Transaction = dbTransaction;
cmd.CommandText = conditionsql;
cmd.ExecuteNonQuery();
if (!isTrans)
{
return(Commit());
}
return(0);
}
public DataTable FindTable <T, FindT>(FindT FindEntity)
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);
}
return(FindTable(sql));
}
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 strSql = DatabaseCommon.DealExp<T>(condition, OperateType.Select).ToString();
//int num = (pageIndex - 1) * pageSize;
//int num1 = (pageIndex) * pageSize;
//orderField = "order by " + orderField;
//StringBuilder sb = new StringBuilder();
//sb.Append("Select * From (Select ROW_NUMBER() Over (" + orderField + ")");
//sb.Append(" As rowNum, * From (" + strSql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "");
//total = Convert.ToInt32(new DbHelper(dbConnection).ExecuteScalar(CommandType.Text, "Select Count(1) From (" + strSql + ") As t"));
//var dataQuery = dbConnection.Query<T>(sb.ToString());
//return dataQuery.ToList();
string strSql = DatabaseCommon.DealExp <T>(condition, OperateType.Select).
Append($"order by {orderField}").ToString();
//var dataQuery = dbConnection.Query<T>(strSql, buffered: false).AsQueryable();
var dataQuery = dbConnection.Query <T>(strSql);
total = dataQuery.Count();
var data = dataQuery.Skip(pageSize * (pageIndex - 1)).Take(pageSize).AsQueryable();
return(data);
}
}
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);
}
/// <summary>
/// 执行表达式
/// </summary>
/// <param name="exp">表达式</param>
/// <param name="operateType">操作类型</param>
/// <param name="columnName">列名</param>
/// <param name="newValue">值</param>
/// <returns>int</returns>
public static StringBuilder DealExp <T>(Expression exp, OperateType operateType, List <string> columnName = null, List <string> newValue = null)
{
StringBuilder sb = new StringBuilder();
string TableName = EntityAttribute.GetEntityTable <T>();
switch (operateType)
{
case OperateType.Select:
sb.Append("Select ");
sb.Append(GetConditionString(columnName));
sb.Append(" From ");
sb.Append(TableName);
sb.Append(" Where ");
break;
case OperateType.Delete:
sb.Append("Delete ");
sb.Append(TableName);
sb.Append(" Where ");
break;
case OperateType.Update:
sb.Append("Update ");
sb.Append(TableName);
sb.Append(" Set(");
if (columnName != null && newValue != null && columnName.Count == newValue.Count && columnName.Count > 0)
{
for (int i = 0; i < columnName.Count; i++)
{
sb.Append(columnName[i]);
sb.Append("=");
sb.Append(newValue[i]);
}
}
sb.Append(") Where ");
break;
case OperateType.Insert:
sb.Append("Insert Into ");
sb.Append(TableName);
sb.Append(GetConditionString(columnName).Length > 0 ? string.Format("({0})", GetConditionString(columnName)) : string.Empty);
sb.Append(" Values( ");
sb.Append(GetConditionString(newValue));
sb.Append(") Where ");
break;
}
if (ExpToSqlHelper.DealExpress(exp).Substring(0, 4).ToUpper().Equals("TRUE"))
{
sb.Append(" 1=1 ");
sb.Append(ExpToSqlHelper.DealExpress(exp).Remove(0, 4));
}
else
{
sb.Append(ExpToSqlHelper.DealExpress(exp));
}
if (operateType == OperateType.Select)
{
var translateInfo = DatabaseCommon.GetTranslateValue <T>();
if (translateInfo.Count(x => x.Length > 0) > 0)
{
StringBuilder stringBuilder = new StringBuilder();
return(stringBuilder.Append(PottingSql <T>(translateInfo, sb.ToString(), TableName)));
}
}
return(sb);
}
/// <summary>
/// 实体插入dapper作实现
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entity"></param>
/// <returns></returns>
public int Insert <T>(T entity, string NotUpdateColName = "") where T : class
{
return(ExecuteBySql(DatabaseCommon.InsertSql <T>(entity, NotUpdateColName).ToString(), DatabaseCommon.GetDynamicParameter <T>(entity)));
}
public DataTable FindView <T>(T entity, string appendsql, string orderField, bool isAsc, int pageSize, int pageIndex, out int total)
{
return(FindTable(DatabaseCommon.QueryWhereSQL <T>(entity).ToString() + appendsql, orderField, isAsc, pageSize, pageIndex, out total));
}
public DataTable FindTable <T>(T entity)
{
return(db.FindTable(DatabaseCommon.QueryWhereSQL <T>(entity).ToString()));
}
public DataTable FindTable <T>(T entity, string orderField, bool isAsc, int pageSize, int pageIndex, out int total) where T : class, new()
{
return(FindTable(DatabaseCommon.QueryWhereSQL <T>(entity).ToString(), orderField, isAsc, pageSize, pageIndex, out total));
}
public DataTable FindView <T>(T entity, string appendsql)
{
return(FindTable(DatabaseCommon.QueryWhereSQL <T>(entity).ToString() + appendsql));
}
public DataTable FindTable <T>(T entity) where T : class, new()
{
return(FindTable(DatabaseCommon.QueryWhereSQL <T>(entity).ToString()));
}
public int Delete <T>() where T : class
{
return(ExecuteBySql(DatabaseCommon.DeleteSql(EntityAttribute.GetEntityTable <T>()).ToString()));
}
public DataTable FindTable <T>(string strSql, T FindEntity) where T : class, new()
{
return(FindTable(strSql + DatabaseCommon.QueryConditionSQL <T>(FindEntity).ToString()));
}
public int Update <T>(T entity) where T : class
{
return(ExecuteBySql(DatabaseCommon.UpdateSql <T>(entity).ToString(), DatabaseCommon.GetParameter <T>(entity)));
}
public int UpdateColNull <T>(T entity) where T : class
{
return(ExecuteBySql(DatabaseCommon.UpdateNullSql <T>(entity).ToString()));
}
