/// <summary> /// 删除 /// </summary> /// <param name="strWhere"></param > /// <param name="DFTable"></param> public bool Delete(string strWhere) { StringBuilder strSql = new StringBuilder(); strSql.AppendFormat("delete from {0}", TableName); strSql.Append(GetWhere(strWhere)); return(DBHelperMySql.ExcuteNonQuery(strSql.ToString()) > 0); }
/// <summary> /// 更新方法模版 /// </summary> /// <param name="setValue">更新字段和值</param> /// <param name="strWhere"></param> /// <param name="arrayList"></param> public bool Update(string setValue, string strWhere) { StringBuilder strSql = new StringBuilder(); strSql.AppendFormat("update {0} set {1}", TableName, setValue); strSql.Append(GetWhere(strWhere)); return(DBHelperMySql.ExcuteNonQuery(strSql.ToString()) > 0); }
/// <summary> /// 查询经营类目详细信息列表 /// </summary> /// <returns></returns> public IList <CategoryDetail> QueryDetails() { StringBuilder builder = new StringBuilder(); builder.Append("SELECT t1.*,t2.categoryName AS parentName FROM categorys t1 "); builder.Append("LEFT JOIN categorys t2 ON t1.parentId = t2.cid; "); var dataSet = DBHelperMySql.Query(builder.ToString()); return(dataSet.ToList <CategoryDetail>()); }
/// <summary> /// 查询分页总数 /// </summary> /// <param name="type"></param> /// <param name="beginTime"></param> /// <param name="endTime"></param> /// <param name="where"></param> /// <returns></returns> public int SelectLimitCount(int type, string beginTime, string endTime, string where) { StringBuilder builder = new StringBuilder(); builder.Append("SELECT COUNT(t1.payId) FROM pays t1 "); builder.AppendFormat($"WHERE {where}"); var result = DBHelperMySql.GetSingle(builder.ToString()); return(Convert.ToInt32(result)); }
public int Count() { StringBuilder strSql = new StringBuilder(); MySqlParameter[] parameters; strSql.AppendFormat("select count(*) from {0}", TableName); object result = DBHelperMySql.GetSingle(strSql.ToString()); return(Convert.ToInt32(result)); }
/// <summary> /// 查询钱包信息 /// </summary> /// <param name="uid"></param> /// <returns></returns> public Wallet SelectById(int uid) { var dataSet = DBHelperMySql.Query("SELECT * FROM wallets WHERE userId = " + uid); var list = dataSet.ToList <Wallet>(); if (list == null || list.Count == 0) { return(null); } return(list.First()); }
/// <summary> /// 查询分页总数 /// </summary> /// <param name="state"></param> /// <param name="beginTime"></param> /// <param name="endTime"></param> /// <param name="where"></param> /// <returns></returns> public int SelectLimitCount(int state, string beginTime, string endTime, string where) { StringBuilder builder = new StringBuilder(); builder.Append("SELECT COUNT(t1.pid) FROM products t1 "); builder.Append("LEFT JOIN users t2 ON t1.sid = t2.uid "); builder.AppendFormat($"WHERE {where}"); var result = DBHelperMySql.GetSingle(builder.ToString()); return(Convert.ToInt32(result)); }
/// <summary> /// 查询 /// </summary> /// <typeparam name="R"></typeparam> /// <returns></returns> public IList <R> Query <R>(QueryParam model, string tableName) { MySqlParameter[] parameters = { new MySqlParameter("_fields", model.Fields), new MySqlParameter("_tableName", tableName), new MySqlParameter("_where", model.StrWhere), new MySqlParameter("_orderby", model.OrderBy), }; return(DBHelperMySql.RunProceReturnDataSet("Query", parameters) .Tables[0].ToList <R>()); }
/// <summary> /// 查询店铺商品信息 /// </summary> /// <param name="uid"></param> /// <returns></returns> public IList <ShopProductDetail> GetShopProducts(int uid) { StringBuilder builder = new StringBuilder(); builder.Append("SELECT t1.*,t3.cid, t3.categoryName AS parentName, t2.* FROM products t1 "); builder.Append("LEFT JOIN categorys t2 ON t1.categoryId = t2.cid "); builder.Append("LEFT JOIN categorys t3 ON t2.parentId = t3.cid "); builder.Append($"WHERE t1.sid = {uid} AND t1.status = 1 "); var dataSet = DBHelperMySql.Query(builder.ToString()); return(dataSet.ToList <ShopProductDetail>()); }
/// <summary> /// 插入返回主键 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="model"></param> /// <param name="DFTable"></param> public bool InsertReturn <T>(T model, ref long pk) { StringBuilder strSql = new StringBuilder(); MySqlParameter[] parameters; strSql.AppendFormat("insert into {0}", TableName); strSql.Append(GetInsertFields()); strSql.Append(" values "); strSql.Append(GetInsertValues <T>(model, out parameters)); int result = DBHelperMySql.ExcuteInsertReturnId(strSql.ToString(), out pk, parameters); return(result > 0); }
/// <summary> /// 查询分页 /// </summary> /// <param name="page"></param> /// <param name="limit"></param> /// <param name="state"></param> /// <param name="beginTime"></param> /// <param name="endTime"></param> /// <param name="where"></param> /// <returns></returns> public IList <Accounts> SelectLimit(int page, string limit, int state, string beginTime, string endTime, string where) { StringBuilder builder = new StringBuilder(); builder.Append("SELECT t1.* FROM accounts t1 "); builder.AppendFormat($"WHERE {where} GROUP BY t1.accountsId ORDER BY t1.addTime DESC LIMIT {page},{limit}"); var dataSet = DBHelperMySql.Query(builder.ToString()); if (dataSet == null || dataSet.Tables.Count == 0) { return(null); } return(dataSet.Tables[0].ToList <Accounts>()); }
/// <summary> /// 查询分页 /// </summary> /// <param name="page"></param> /// <param name="limit"></param> /// <param name="type"></param> /// <param name="beginTime"></param> /// <param name="endTime"></param> /// <param name="where"></param> /// <returns></returns> public IList <PaysDetail> SelectLimit(int page, string limit, int tradeType, int type, string beginTime, string endTime, string where) { StringBuilder builder = new StringBuilder(); builder.Append("SELECT t1.*,t2.username,t3.* FROM pays t1 LEFT JOIN users t2 ON t1.fromUid = t2.uid "); builder.Append("LEFT JOIN pays t3 ON t1.toUid = t2.uid "); builder.AppendFormat($"WHERE {where} GROUP BY t1.payId ORDER BY t1.addTime DESC LIMIT {page},{limit}"); var dataSet = DBHelperMySql.Query(builder.ToString()); if (dataSet == null || dataSet.Tables.Count == 0) { return(null); } return(dataSet.Tables[0].ToList <PaysDetail>()); }
/// <summary> /// 查询经营类目详细信息 /// </summary> /// <param name="value"></param> /// <returns></returns> public CategoryDetail QueryDetail(int value) { StringBuilder builder = new StringBuilder(); builder.Append("SELECT t1.*,t2.categoryName AS parentName FROM categorys t1 "); builder.Append($"LEFT JOIN categorys t2 ON t1.parentId = t2.cid WHERE t1.cid = {value} "); var dataSet = DBHelperMySql.Query(builder.ToString()); var list = dataSet.ToList <CategoryDetail>(); if (list == null || list.Count == 0) { return(null); } return(list.First()); }
/// <summary> /// 查询商品详情 韦德 2018年9月25日15:39:40 /// </summary> /// <param name="value"></param> /// <returns></returns> public ProductDetail QueryDetail(int value) { StringBuilder builder = new StringBuilder(); builder.Append("SELECT t1.*,t2.roleId,t2.level,t3.* FROM products t1 "); builder.Append("LEFT JOIN users t2 ON t1.sid = t2.uid "); builder.Append("LEFT JOIN permissions t3 ON t2.roleId = t3.permissionId "); builder.Append($"WHERE pid = {value}; "); var dateSet = DBHelperMySql.Query(builder.ToString()); if (dateSet == null || dateSet.Tables.Count <= 0) { return(null); } return(dateSet.Tables[0].ToList <ProductDetail>().First()); }
/// <summary> /// 查询用户财务详情 /// </summary> /// <param name="uid"></param> /// <returns></returns> public UserFinanceDetail SelectFinanceDetail(int uid) { StringBuilder builder = new StringBuilder(); builder.Append("SELECT t1.*,t2.* FROM users t1 "); builder.Append("LEFT JOIN wallets t2 ON t1.uid = t2.userId "); builder.Append("WHERE uid = " + uid); var dataSet = DBHelperMySql.Query(builder.ToString()); var userFinanceDetails = dataSet.ToList <UserFinanceDetail>(); if (userFinanceDetails == null || userFinanceDetails.Count == 0) { return(null); } return(userFinanceDetails.First()); }
/// <summary> /// 查询多个用户的财务详情 /// </summary> /// <param name="uids"></param> /// <returns></returns> public IList <UserFinanceDetail> SelectFinanceDetails(int[] uids) { StringBuilder builder = new StringBuilder(); builder.Append("SELECT t1.*,t2.* FROM users t1 "); builder.Append("LEFT JOIN wallets t2 ON t1.uid = t2.userId "); builder.Append($"WHERE uid IN ({uids.AsJoin()}) "); var dataSet = DBHelperMySql.Query(builder.ToString()); var userFinanceDetails = dataSet.ToList <UserFinanceDetail>(); if (userFinanceDetails == null || userFinanceDetails.Count == 0) { return(null); } return(userFinanceDetails); }
/// <summary> /// 查询分页 /// </summary> /// <param name="page"></param> /// <param name="limit"></param> /// <param name="state"></param> /// <param name="beginTime"></param> /// <param name="endTime"></param> /// <param name="where"></param> /// <returns></returns> public List <ProductDetail> SelectLimit(int page, string limit, int state, string beginTime, string endTime, string where) { StringBuilder builder = new StringBuilder(); builder.Append("SELECT t1.*, t2.*, t4.*, (SELECT permissionId FROM permissions WHERE permissionId IN(t3.permissionList)), (SELECT roleName FROM permissions WHERE permissionId IN(t3.permissionList)) FROM products t1 "); builder.Append("LEFT JOIN users t2 ON t1.sid = t2.uid "); builder.Append("LEFT JOIN permission_relations t3 ON t1.sid = t3.uid "); builder.Append("LEFT JOIN categorys t4 ON t1.categoryId = t4.cid "); builder.AppendFormat($"WHERE {where} GROUP BY t1.pid ORDER BY t1.addTime DESC LIMIT {page},{limit}"); var dataSet = DBHelperMySql.Query(builder.ToString()); if (dataSet == null || dataSet.Tables.Count == 0) { return(null); } return(dataSet.Tables[0].ToList <ProductDetail>()); }
/// <summary> /// 查询收入支出金额 /// </summary> /// <param name="uid"></param> /// <returns></returns> public IDictionary <string, string> SelectAmount(int uid) { StringBuilder builder = new StringBuilder(); builder.Append($"SELECT (SELECT SUM(amount) FROM pays WHERE fromUid != {uid}) AS incomeAmount, "); builder.AppendFormat($"(SELECT SUM(amount) FROM pays WHERE fromUid = {uid}) AS expendAmount FROM pays LIMIT 1"); var dataSet = DBHelperMySql.Query(builder.ToString()); if (dataSet == null || dataSet.Tables.Count == 0 || dataSet.Tables[0].Rows.Count == 0) { return(null); } var convertDictionary = dataSet.Tables[0].Rows.Cast <DataRow>().ToDictionary(x => x[0].ToString(), x => x[1].ToString()); IDictionary <string, string> dictionary = new Dictionary <string, string>(); dictionary.Add("incomeAmount", Convert.ToString(convertDictionary.First().Key)); dictionary.Add("expendAmount", Convert.ToString(convertDictionary.First().Value)); return(dictionary); }
/// <summary> /// 根据条件查询分页订单列表--条件不含子表信息--只查询一张主表 /// </summary> /// <param name="condition">查询条件</param> /// <returns>返回需要的订单列表(不含订单子表信息)</returns> public virtual PageModel <R> PageQuery <R>(PageParam model) where R : class, new() { StringBuilder strSql = new StringBuilder(); MySqlParameter[] parameters = new MySqlParameter[7]; parameters[0] = new MySqlParameter("_fields", model.Fields); parameters[1] = new MySqlParameter("_tableName", model.TableName ?? TableName); parameters[2] = new MySqlParameter("_where", model.StrWhere); parameters[3] = new MySqlParameter("_orderby", model.OrderBy); parameters[4] = new MySqlParameter("_pageindex", model.PageIndex); parameters[5] = new MySqlParameter("_pagesize", model.PageSize); parameters[6] = new MySqlParameter("_totalcount", MySqlDbType.Int32); parameters[6].Direction = ParameterDirection.Output; DataSet ds = DBHelperMySql.RunProceReturnDataSet("PageQuery", parameters); PageModel <R> respQuery = new PageModel <R>() { List = ds.Tables[0].ToList <R>(), TotalCount = int.Parse(parameters[6].Value.ToString()), PageIndex = model.PageIndex, PageSize = model.PageSize }; return(respQuery); }
/// <summary> /// IDictionary /// </summary> /// <param name="DFTable">sql 哈希集合</param> public void CommitTransaction(IDictionary <string, DbParameter[]> DFTable, IsolationLevel isolationLevel) { DBHelperMySql.ExecuteSqlTran(DFTable, isolationLevel); }
/// <summary> /// Hashtable提交事务 /// </summary> /// <param name="DFTable">sql 哈希集合</param> public void CommitTransaction(Hashtable DFTable) { DBHelperMySql.ExecuteSqlTran(DFTable); }
/// <summary> /// 查询返回List /// </summary> /// <typeparam name="R">占位符</typeparam> /// <param name="strSql">Sql</param> /// <param name="param">参数</param> /// <returns></returns> public IList <R> Query <R>(string strSql) { return(DBHelperMySql.Query(strSql).Tables[0].ToList <R>()); }
/// <summary> /// 查询返回List /// </summary> /// <typeparam name="R">占位符</typeparam> /// <param name="strSql">Sql</param> /// <param name="param">参数</param> /// <returns></returns> protected IList <R> Query <R>(string strSql, MySqlParameter[] param) { return(DBHelperMySql.Query(strSql, param).Tables[0].ToList <R>()); }
/// <summary> /// 根据uid查询权限列表 韦德 2018年9月16日14:35:31 /// </summary> /// <param name="uid"></param> /// <returns></returns> public IList <Permission> SelectList(int uid) { string sql = $"SELECT * FROM permissions WHERE permissionId IN( SELECT permissionList FROM `permission_relations` WHERE uid = {uid} )"; return(DBHelperMySql.Query(sql).Tables[0].ToList <Permission>()); }
/// <summary> /// Hashtable提交事务--支持乐观锁 /// </summary> /// <param name="DFTable"></param> /// <returns>返回false代表存在Sql受影响行数为0,更新失败!事务回滚</returns> public bool CommitTransactionLock(Hashtable DFTable) { return(DBHelperMySql.ExecuteSqlTranLock(DFTable)); }
/// <summary> /// IDictionary /// </summary> /// <param name="DFTable">sql 哈希集合</param> public void CommitTransaction(IDictionary <string, DbParameter[]> DFTable) { DBHelperMySql.ExecuteSqlTran(DFTable); }
/// <summary> /// IDictionary /// </summary> /// <param name="DFTable">sql 哈希集合</param> public bool CommitTransactionLock(IDictionary <StringBuilder, DbParameter[]> DFTable) { return(DBHelperMySql.ExecuteSqlTranLock(DFTable)); }