public DataTable getUserList(int pageIndex, int pageSize, int role, string fullName) { innerParmas parmas = new innerParmas(pageIndex, pageSize, role, fullName); string sql = "SELECT u.id,u.fullName,u.userName,u.isActive,u.role,r.name FROM user u left join role r on u.role = r.id "; if (parmas.role != -1) { sql += " where u.role = " + parmas.role; } if (parmas.fullName != "") { if (parmas.role != -1) { sql += " and "; } else { sql += " where "; } sql += " u.fullName like '%" + parmas.fullName + "%'"; } sql += " order by u.id desc "; if (parmas.pageIndex < 1) { parmas.pageIndex = 1; } sql += " LIMIT " + (parmas.pageIndex - 1) * parmas.pageSize + "," + parmas.pageSize; //sql += ";SELECT FOUND_ROWS();"; DataTable dt = h.ExecuteQuery(sql, CommandType.Text); //DataSet ds = h.ExecutePagingQuery(sql, CommandType.Text); return(dt); }
/// <summary> /// 绑定第Index页的数据 /// </summary> /// <param name="Index"></param> private void BindDataWithPage(int Index) { pagingCom1.PageIndex = Index; pagingCom1.PageSize = 10; int roleId = -1; if (this.comboBox1.SelectedValue != null) { roleId = Convert.ToInt32(this.comboBox1.SelectedValue.ToString()); } innerParmas parmas = new innerParmas(pagingCom1.PageIndex, pagingCom1.PageSize, roleId, textBox1.Text);//textBox1.Text dtData = uctrl.getUserList(parmas.pageIndex, parmas.pageSize, parmas.role, parmas.fullName); //获取并设置总记录数 pagingCom1.RecordCount = uctrl.getUserListCount(roleId, textBox1.Text);//Convert.ToInt32(dsData.Tables[1].Rows[0][0]); //dtData = dsData.Tables[0]; //dt = new DataTable(); //DataColumn dcNO = new DataColumn("编号"); //DataColumn dcUName = new DataColumn("用户名"); //DataColumn dcFName = new DataColumn("姓名"); //DataColumn dcRole = new DataColumn("职位"); //DataColumn dcStatus = new DataColumn("状态"); //dt.Columns.Add(dcNO); //dt.Columns.Add(dcUName); //dt.Columns.Add(dcFName); //dt.Columns.Add(dcRole); //dt.Columns.Add(dcStatus); dtData.Columns.Add("status"); for (int i = 0; i < dtData.Rows.Count; i++) { //DataRow dr = dt.NewRow(); //dr[0] = dtData.Rows[i]["id"].ToString(); //dr[1] = dtData.Rows[i]["userName"].ToString(); //dr[2] = dtData.Rows[i]["fullName"].ToString(); //dr[3] = dtData.Rows[i]["name"].ToString(); if (dtData.Rows[i]["isActive"].ToString() == "1") { dtData.Rows[i]["status"] = "激活"; } else { dtData.Rows[i]["status"] = "注销"; } //dr[4] = dtData.Rows[i]["isActive"].ToString(); //dt.Rows.Add(dr); } dataGridView1.DataSource = dtData; pagingCom1.reSet(); }