public Page<FloorLabel> QueryFloorByPage(FloorLabel query, int pageidx, int pagesize = 20) { Page<FloorLabel> page = new Page<FloorLabel>(); 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 t_floorlabel 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 t_floorlabel 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<FloorLabel> list = new List<FloorLabel>(); for (int i = 0; i < count; i++) { DataRow row = ds.Tables[0].Rows[i]; FloorLabel model = DataRowToFloorLabel(row); list.Add( model ); } page.Data = list; return page; }
protected FloorLabel DataRowToFloorLabel(DataRow row) { FloorLabel model = new FloorLabel(); 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(); return model; }
protected string GetWhere(FloorLabel query) { query.name = FilterSpecial(query.name); query.rfid = FilterSpecial(query.rfid); 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(where) ) where += " 1=1 "; return where; }
public bool EditFloorLabel(FloorLabel model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update t_floorlabel 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; } }
public bool AddFloorLabel(FloorLabel model) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into t_floorlabel("); 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; } }