public Page<BoxLabel> QueryBoxByPage(BoxLabel query, int pageidx, int pagesize = 20) { Page<BoxLabel> page = new Page<BoxLabel>(); page.PageIdx = pageidx; page.PageSize = pagesize; string where = GetWhere(query); string limit = string.Format(" limit {0} , {1}", pageidx < 0 ? 0 : pageidx * pagesize, pagesize); string orderby = "";//"order by operatetime desc , id desc"; string sql = string.Format("select count(1) from v_box where {0} ", where); int totalrecord = 0; object obj = MySqlHelper.GetSingle(sql); if (obj == null) totalrecord = 0; int.TryParse(obj.ToString(), out totalrecord); int totalPages = 0; totalPages = totalrecord / pagesize; totalPages += totalrecord % pagesize == 0 ? 0 : 1; page.TotalPages = totalPages; page.TotalRecords = totalrecord; sql = string.Format(" select * from v_box where {0} {1} {2}", where, orderby, limit); DataSet ds = MySqlHelper.Query(sql); if (ds == null || ds.Tables.Count < 1 || ds.Tables[0].Rows.Count < 1) return page; int count = ds.Tables[0].Rows.Count; List<BoxLabel> list = new List<BoxLabel>(); for (int i = 0; i < count; i++) { DataRow row = ds.Tables[0].Rows[i]; BoxLabel model = DataRowToBoxLabel(row); list.Add(model); } page.Data = list; return page; }
protected BoxLabel DataRowToBoxLabel(DataRow row) { BoxLabel model = new BoxLabel(); if (row["id"].ToString() != "") { model.id = int.Parse(row["id"].ToString()); } model.name = row["name"].ToString(); model.rfid = row["rfid"].ToString(); model.number = row["number"].ToString(); if (row.Table.Columns.Contains("floorrfid")) { model.floorrfid = row["floorrfid"].ToString(); } if (row.Table.Columns.Contains("floorname")) { model.floorname = row["floorname"].ToString(); } return model; }
public bool EditBoxLabel(BoxLabel model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update t_boxlabel set "); strSql.Append(" name=@name,"); strSql.Append(" rfid=@rfid,"); strSql.Append(" number=@number"); strSql.Append(" where id=@id"); MySqlParameter[] parameters = { new MySqlParameter("@name", MySqlDbType.VarChar,255), new MySqlParameter("@rfid", MySqlDbType.VarChar,255), new MySqlParameter("@number", MySqlDbType.VarChar,100), new MySqlParameter("@id",MySqlDbType.Int32) }; parameters[0].Value = model.name; parameters[1].Value = model.rfid; parameters[2].Value = model.number; parameters[3].Value = model.id; int rows = MySqlHelper.ExecuteSql(strSql.ToString(), parameters); if (rows > 0) { return true; } else { return false; } }
protected string GetWhere(BoxLabel query) { query.name = FilterSpecial(query.name); query.rfid = FilterSpecial(query.rfid); query.floorrfid = FilterSpecial(query.floorrfid); string where = ""; if (string.IsNullOrEmpty(query.name) == false) { if (string.IsNullOrEmpty(where) == false) where += " or "; where += " name like '%" + query.name + "%'"; } if (string.IsNullOrEmpty(query.rfid) == false) { if (string.IsNullOrEmpty(where) == false) where += " or "; where += string.Format(" rfid like '%{0}%'", query.rfid); } if (string.IsNullOrEmpty(query.floorrfid) == false) { if (string.IsNullOrEmpty(where) == false) where += " or "; where += string.Format(" floorrfid = '{0}'", query.floorrfid); } if (string.IsNullOrEmpty(where) ) where = " 1=1 "; return where; }
public bool AddBoxLabel(BoxLabel model) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into t_boxlabel("); strSql.Append(" name,rfid, number)"); strSql.Append(" values ("); strSql.Append("@name,@rfid,@number)"); MySqlParameter[] parameters = { new MySqlParameter("@name", MySqlDbType.VarChar,255), new MySqlParameter("@rfid", MySqlDbType.VarChar,255), new MySqlParameter("@number", MySqlDbType.VarChar,100) }; parameters[0].Value = model.name; parameters[1].Value = model.rfid; parameters[2].Value = model.number; int result = MySqlHelper.ExecuteSql(strSql.ToString(), parameters); return result > 0 ? true : false; } catch (Exception ex) { return false; } }