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())); }