protected Archive DataRowToArchive(DataRow row) { Archive model = new Archive(); if (row["id"].ToString() != "") { model.id = int.Parse(row["id"].ToString()); } model.idx = row["idx"].ToString(); model.manager = row["manager"].ToString(); model.title = row["title"].ToString(); model.pages = row["pages"].ToString(); model.number = row["number"].ToString(); model.remark = row["remark"].ToString(); model.operateman = row["operateman"].ToString(); model.operatetime = DateTime.Parse(row["operatetime"].ToString()); if (row.Table.Columns.Contains("position")) { model.position = row["position"].ToString(); } if (row.Table.Columns.Contains("floorrfid")) { model.floorrfid = row["floorrfid"].ToString(); } if (row.Table.Columns.Contains("boxrfid")) { model.boxrfid = row["boxrfid"].ToString(); } return model; }
public Page<Archive> QueryByPage(Archive query, int pageidx, int pagesize = 20) { Page<Archive> page = new Page<Archive>(); 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_archive 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_archive 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<Archive> list = new List<Archive>(); for (int i = 0; i < count; i++) { DataRow row = ds.Tables[0].Rows[i]; Archive model = DataRowToArchive(row); list.Add( model ); } page.Data = list; return page; }
public List<Archive> Query(Archive query) { string where = GetWhere(query); string orderby = "order by operatetime desc"; string sql = string.Format(" select * from t_archive where {0} {1}", where, orderby); DataSet ds = MySqlHelper.Query(sql); if (ds == null || ds.Tables.Count < 1 || ds.Tables[0].Rows.Count < 1) return null; int count = ds.Tables[0].Rows.Count; List<Archive> list = new List<Archive>(); for (int i = 0; i < count; i++) { DataRow row = ds.Tables[0].Rows[i]; Archive model = DataRowToArchive(row); list.Add(model); } return list; }
protected string GetWhere(Archive query) { query.manager = FilterSpecial(query.manager); query.title = FilterSpecial(query.title); query.number = FilterSpecial(query.number); query.floorrfid = FilterSpecial(query.floorrfid); query.boxrfid = FilterSpecial(query.boxrfid); string where = ""; if (string.IsNullOrEmpty(query.manager) == false) { if (string.IsNullOrEmpty(where) == false ) where += " or "; where += " manager like '%" + query.manager + "%'"; } if (string.IsNullOrEmpty(query.title) == false) { if (string.IsNullOrEmpty(where) == false) where += " or "; where += string.Format(" title like '%{0}%'", query.title); } if (string.IsNullOrEmpty(query.number) == false) { if (string.IsNullOrEmpty(where) == false) where += " or "; where += string.Format(" number like '%{0}%' ", query.number); } if (string.IsNullOrEmpty(query.floorrfid) == false) { if (string.IsNullOrEmpty(where) == false) where += " or "; where += string.Format(" floorrfid = '{0}' ", query.floorrfid); } if (string.IsNullOrEmpty(query.boxrfid) == false) { if (string.IsNullOrEmpty(where) == false) where += " or "; where += string.Format(" boxrfid = '{0}' ", query.boxrfid); } if (query.shownoposition) { if (string.IsNullOrEmpty(where) == false) { where = " boxid = 0 and ( " + where + " )"; } else { where = " boxid=0 "; } } if (string.IsNullOrEmpty(where)) { where = " 1=1 "; } return where; }
public bool EditArchive(Archive model) { StringBuilder strSql = new StringBuilder(); strSql.Append("update t_archive set "); strSql.Append("idx=@idx,"); strSql.Append("manager=@manager,"); strSql.Append("title=@title,"); strSql.Append("pages=@pages,"); strSql.Append("number=@number,"); strSql.Append("remark=@remark,"); strSql.Append("operateman=@operateman,"); strSql.Append("operatetime=@operatetime"); strSql.Append(" where id=@id"); MySqlParameter[] parameters = { new MySqlParameter("@idx", MySqlDbType.VarChar,50), new MySqlParameter("@manager", MySqlDbType.VarChar,255), new MySqlParameter("@title", MySqlDbType.VarChar,255), new MySqlParameter("@pages", MySqlDbType.VarChar,255), new MySqlParameter("@number", MySqlDbType.VarChar,255), new MySqlParameter("@remark", MySqlDbType.VarChar,255), new MySqlParameter("@operateman", MySqlDbType.VarChar,255), new MySqlParameter("@operatetime", MySqlDbType.Timestamp), new MySqlParameter("@id",MySqlDbType.Int32) }; parameters[0].Value = model.idx; parameters[1].Value = model.manager; parameters[2].Value = model.title; parameters[3].Value = model.pages; parameters[4].Value = model.number; parameters[5].Value = model.remark; parameters[6].Value = model.operateman; parameters[7].Value = model.operatetime; parameters[8].Value = model.id; int rows = MySqlHelper.ExecuteSql(strSql.ToString(), parameters); if (rows > 0) { return true; } else { return false; } }
public bool AddArchive(Archive model) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into t_archive("); strSql.Append("idx, manager,title,pages,number,remark,operateman)"); strSql.Append(" values ("); strSql.Append("@idx,@manager,@title,@pages,@number,@remark,@operateman)"); MySqlParameter[] parameters = { new MySqlParameter("@idx", MySqlDbType.VarChar,45), new MySqlParameter("@manager", MySqlDbType.VarChar,255), new MySqlParameter("@title", MySqlDbType.VarChar,255), new MySqlParameter("@pages", MySqlDbType.VarChar,255), new MySqlParameter("@number", MySqlDbType.VarChar,100), new MySqlParameter("@remark", MySqlDbType.VarChar,255), new MySqlParameter("@operateman", MySqlDbType.VarChar,255) }; parameters[0].Value = model.idx; parameters[1].Value = model.manager; parameters[2].Value = model.title; parameters[3].Value = model.pages; parameters[4].Value = model.number; parameters[5].Value = model.remark; parameters[6].Value = model.operateman; int result = MySqlHelper.ExecuteSql(strSql.ToString(), parameters); return result > 0 ? true : false; } catch (Exception ex) { return false; } }