public List<RegisterdConsumersModel> GetRegisterdConsumers(DbConn PubConn) { return SqlHelper.Visit((ps) => { StringBuilder stringSql = new StringBuilder(); stringSql.Append(@"select s.id as tb_consumer_id,tempid as tb_consumer_tempid,s.consumerclientid as tb_consumer_consumerclientid,s.partitionindexs as tb_consumer_partitionindexs ,s.clientname as tb_consumer_clientname,s.lastheartbeat as tb_consumer_lastheartbeat,s.lastupdatetime as tb_consumer_lastupdatetime,s.createtime as tb_consumer_createtime, c.id as tb_consumer_client_id, c.client as tb_consumer_client_client,c.createtime as tb_consumer_client_createtime, p.id as tb_consumer_partition_id,p.consumerclientid as tb_consumer_partition_consumerclientid ,p.partitionindex as tb_consumer_partition_partitionindex,p.partitionid as tb_consumer_partition_partitionid,p.lastconsumertempid as tb_consumer_partition_lastconsumertempid,p.lastmqid as tb_consumer_partition_lastmqid ,p.lastupdatetime as tb_consumer_partition_lastupdatetime,p.createtime as tb_consumer_partition_createtime from tb_consumer s WITH(NOLOCK),tb_consumer_client c WITH(NOLOCK),tb_consumer_partition p WITH(NOLOCK) where s.consumerclientid=c.id and s.consumerclientid=p.consumerclientid and s.tempid=p.lastconsumertempid"); DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters()); List<RegisterdConsumersModel> rs = new List<RegisterdConsumersModel>(); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) { RegisterdConsumersModel m = new RegisterdConsumersModel(dr); rs.Add(m); } } return rs; }); }
public IList<RegisterdConsumersModel> GetPageList2(DbConn conn, string partitionid, string consumerclientid, string mqpathid, int pageIndex, int pageSize, ref int count) { int tempCount = 0; IList<RegisterdConsumersModel> list = new List<RegisterdConsumersModel>(); ConsumerModel cm = new ConsumerModel(); var result = SqlHelper.Visit((ps) => { StringBuilder where = new StringBuilder(" WHERE 1=1 "); if (!string.IsNullOrEmpty(consumerclientid)) { if (!consumerclientid.isint()) where.AppendFormat(" AND (c.client LIKE '%{0}%')", consumerclientid); else where.AppendFormat(" AND (c.id = '{0}')", consumerclientid); } if (!string.IsNullOrEmpty(partitionid)) { where.AppendFormat(" AND (p.partitionid='{0}')", partitionid); } if (!string.IsNullOrEmpty(mqpathid)) { if (!mqpathid.isint()) { where.AppendFormat(" AND (m.mqpath like '%{0}%')", mqpathid); } else { where.AppendFormat(" AND (m.id='{0}')", mqpathid); } } string sql = @"SELECT ROW_NUMBER() OVER(ORDER BY p.consumerclientid DESC,p.partitionindex desc) AS rownum, s.id as tb_consumer_id,tempid as tb_consumer_tempid,s.consumerclientid as tb_consumer_consumerclientid,s.partitionindexs as tb_consumer_partitionindexs ,s.clientname as tb_consumer_clientname,s.lastheartbeat as tb_consumer_lastheartbeat,s.lastupdatetime as tb_consumer_lastupdatetime,s.createtime as tb_consumer_createtime, c.id as tb_consumer_client_id, c.client as tb_consumer_client_client,c.createtime as tb_consumer_client_createtime, p.id as tb_consumer_partition_id,p.consumerclientid as tb_consumer_partition_consumerclientid ,p.partitionindex as tb_consumer_partition_partitionindex,p.partitionid as tb_consumer_partition_partitionid,p.lastconsumertempid as tb_consumer_partition_lastconsumertempid,p.lastmqid as tb_consumer_partition_lastmqid ,p.lastupdatetime as tb_consumer_partition_lastupdatetime,p.createtime as tb_consumer_partition_createtime,m.mqpath as tb_mqpath_mqpath,m.id as tb_mqpath_id from tb_consumer_partition p WITH(NOLOCK) left join tb_consumer s WITH(NOLOCK) on s.tempid=p.lastconsumertempid left join tb_consumer_client c WITH(NOLOCK) on p.consumerclientid=c.id left join tb_mqpath_partition mp with (nolock) on mp.partitionid=p.partitionid left join tb_mqpath m with (nolock) on m.id=mp.mqpathid "; string countSql = "SELECT COUNT(p.id) from tb_consumer_partition p WITH(NOLOCK) left join tb_consumer s WITH(NOLOCK) on s.tempid=p.lastconsumertempid left join tb_consumer_client c WITH(NOLOCK) on p.consumerclientid=c.id left join tb_mqpath_partition mp with (nolock) on mp.partitionid=p.partitionid left join tb_mqpath m with (nolock) on m.id=mp.mqpathid " + where.ToString(); object obj = conn.ExecuteScalar(countSql, null); if (obj != DBNull.Value && obj != null) { tempCount = LibConvert.ObjToInt(obj); } string sqlPage = string.Concat("SELECT * FROM (", sql.ToString(), where.ToString(), ") A WHERE rownum BETWEEN ", ((pageIndex - 1) * pageSize + 1), " AND ", pageSize * pageIndex); DataTable dt = conn.SqlToDataTable(sqlPage, null); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { RegisterdConsumersModel model = new RegisterdConsumersModel(dr); model.msgCount = reportDal.GetMsgCount(conn, model.consumerpartitionmodel.lastmqid); model.nonMsgCount = reportDal.GetNonMsgCount(conn, model.consumerpartitionmodel.lastmqid); model.mqpath = dr["tb_mqpath_mqpath"].Tostring(); model.mqpathid = dr["tb_mqpath_id"].Toint(); list.Add(model); } } return list; }); count = tempCount; return result; }