public List<tb_partition_model> GetClientCreateTime(DbConn conn) { try { List<tb_partition_model> list = new List<tb_partition_model>(); string sql = @"select partitionid,clientname,tb_consumer_partition.createtime from tb_consumer_partition,[tb_consumer] WITH(NOLOCK) where [tb_consumer].tempid=tb_consumer_partition.lastconsumertempid"; DataTable dt = conn.SqlToDataTable(sql, null); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { tb_partition_model model = new tb_partition_model(); model.partitionid = Convert.ToInt32(dr["partitionid"]); model.createtime = Convert.ToDateTime(dr["createtime"]); list.Add(model); } } return list; } catch (Exception ex) { throw ex; } }
/// <summary> /// nodeList /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="count"></param> /// <returns></returns> public IList<tb_datanode_model> GetPageList(DbConn conn, int pageIndex, int pageSize, ref int count) { int tempCount = 0; IList<tb_datanode_model> list = new List<tb_datanode_model>(); var result = SqlHelper.Visit((ps) => { string sql = "SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS rownum,* FROM tb_datanode WITH(NOLOCK)"; string countSql = "SELECT COUNT(1) FROM tb_datanode WITH(NOLOCK) "; object obj = conn.ExecuteScalar(countSql, null); if (obj != DBNull.Value && obj != null) { tempCount = LibConvert.ObjToInt(obj); } string sqlPage = string.Concat("SELECT * FROM (", sql.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) { tb_datanode_model model = CreateModel(dr); list.Add(model); } } return list; }); count = tempCount; return result; }
/// <summary> /// debug日志分页列表 /// </summary> /// <param name="startTime"></param> /// <param name="endTime"></param> /// <param name="pageSize"></param> /// <param name="pageIndex"></param> /// <param name="count"></param> /// <returns></returns> public IList<tb_debuglog_model> GetPageList(DbConn conn, DateTime? startTime, DateTime? endTime, string mqpathid, string mqpath, string methodname, string info, int pageSize, int pageIndex, ref int count) { int tempCount = 0; var result = SqlHelper.Visit((ps) => { IList<tb_debuglog_model> list = new List<tb_debuglog_model>(); StringBuilder where = new StringBuilder(); List<ProcedureParameter> parameters = new List<ProcedureParameter>(); where.Append(" WHERE 1=1 "); if (startTime != null && endTime != null) { parameters.Add(new ProcedureParameter("startTime", startTime.Value.ToString("yyyy-MM-dd"))); parameters.Add(new ProcedureParameter("endTime", endTime.Value.ToString("yyyy-MM-dd"))); where.Append(" AND createtime>=@startTime AND createtime<=@endTime "); } if (!string.IsNullOrWhiteSpace(mqpathid)) { parameters.Add(new ProcedureParameter("mqpathid", mqpathid)); where.Append(" AND mqpathid=@mqpathid "); } if (!string.IsNullOrWhiteSpace(mqpath)) { parameters.Add(new ProcedureParameter("mqpath", mqpath)); where.Append(" AND mqpath=@mqpath "); } if (!string.IsNullOrWhiteSpace(methodname)) { parameters.Add(new ProcedureParameter("methodname", methodname)); where.Append(" AND methodname like '%'+@methodname+'%' "); } if (!string.IsNullOrWhiteSpace(info)) { parameters.Add(new ProcedureParameter("info", info)); where.Append(" AND info like '%'+@info+'%' "); } StringBuilder sql = new StringBuilder(); sql.Append("SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS rownum,* FROM tb_debuglog WITH(NOLOCK)"); string countSql = string.Concat("SELECT COUNT(1) FROM tb_debuglog WITH(NOLOCK) ", where.ToString()); object obj = conn.ExecuteScalar(countSql, parameters); 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, parameters); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { tb_debuglog_model model = CreateModel(dr); list.Add(model); } } return list; }); count = tempCount; return result; }
public IList<tb_producterview_model> GetPageList(DbConn conn, string mqpathid, string name, string ip, int pageIndex, int pageSize, ref int count) { int tempCount = 0; IList<tb_producterview_model> list = new List<tb_producterview_model>(); var result = SqlHelper.Visit((ps) => { StringBuilder where = new StringBuilder("");// WHERE 1=1 if (!string.IsNullOrEmpty(name)) { where.AppendFormat(" AND p.productername LIKE '%{0}%'", name); } if (!string.IsNullOrEmpty(ip)) { where.AppendFormat(" AND p.ip='{0}'", ip); } if (!string.IsNullOrWhiteSpace(mqpathid)) { int temp = 0; if (int.TryParse(mqpathid, out temp)) { where.AppendFormat(" and (p.mqpathid='{0}')", mqpathid); } else { where.AppendFormat(" and (m.mqpath like '%'+'{0}'+'%')", mqpathid); } } string sql = "SELECT ROW_NUMBER() OVER(ORDER BY p.Id DESC) AS rownum,p.*,m.mqpath FROM tb_producter p WITH(NOLOCK),tb_mqpath m WITH(NOLOCK) where p.mqpathid=m.id "; string countSql = "SELECT COUNT(1) FROM tb_producter p WITH(NOLOCK),tb_mqpath m WITH(NOLOCK) where p.mqpathid=m.id " + where; 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) { tb_producterview_model model = new tb_producterview_model(); model.ProducterModel = CreateModel(dr); model.mqpath = Convert.ToString(dr["mqpath"]); list.Add(model); } } return list; }); count = tempCount; return result; }
public tb_consumer_partition_model GetByPartitionId(DbConn conn, int partitionId) { return SqlHelper.Visit((ps) => { IList<int> list = new List<int>(); string sql = "SELECT top 1 * FROM tb_consumer_partition WITH(NOLOCK) WHERE partitionId=@id"; ps.Add("@id", partitionId); DataTable dt = conn.SqlToDataTable(sql, ps.ToParameters()); tb_consumer_partition_model model = null; if (dt != null && dt.Rows.Count > 0) { model = CreateModel(dt.Rows[0]); } return model; }); }
/// <summary> ///获取所有队列 /// </summary> /// <param name="conn"></param> /// <returns></returns> public IList<tb_mqpath_model> GetAllMaPath(DbConn conn) { return SqlHelper.Visit((ps) => { IList<tb_mqpath_model> list = new List<tb_mqpath_model>(); string sql = "SELECT * FROM tb_mqpath WITH(NOLOCK)"; DataTable dt = conn.SqlToDataTable(sql, null); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { tb_mqpath_model model = CreateModel(dr); list.Add(model); } } return list; }); }
public virtual List<tb_consumer_model> GetAllList(DbConn PubConn) { return SqlHelper.Visit((ps) => { List<tb_consumer_model> rs = new List<tb_consumer_model>(); string sql = "SELECT * FROM tb_consumer WITH(NOLOCK) "; DataTable dt = PubConn.SqlToDataTable(sql, ps.ToParameters()); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { tb_consumer_model model = CreateModel(dr); rs.Add(model); } } return rs; }); }
/// <summary> /// ��ȡ���нڵ� /// </summary> /// <param name="conn"></param> /// <returns></returns> public IList<string> GetNodeList(DbConn conn) { return SqlHelper.Visit((ps) => { string sql = "SELECT datanodepartition FROM tb_datanode WITH(NOLOCK) ORDER BY datanodepartition"; IList<string> list = new List<string>(); DataTable dt = conn.SqlToDataTable(sql, null); string temp = string.Empty; if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { int node = LibConvert.ObjToInt(dr["datanodepartition"]); list.Add(XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.PartitionRuleHelper.PartitionNameRule(node)); } } return list; }); }
public IList<MqPathPartitionModel> GetPageList(DbConn conn, string mqPathid, string partitionId, int pageIndex, int pageSize, ref int count) { int tempCount = 0; IList<MqPathPartitionModel> list = new List<MqPathPartitionModel>(); var result = SqlHelper.Visit((ps) => { StringBuilder where = new StringBuilder(""); if (!string.IsNullOrWhiteSpace(mqPathid)) { if (!mqPathid.isint()) where.AppendFormat(" AND m.mqpath like '%'+'{0}'+'%'", mqPathid); else where.AppendFormat(" AND m.id ='{0}'", mqPathid); } if (!string.IsNullOrWhiteSpace(partitionId)) { where.AppendFormat(" AND p.partitionId={0}", partitionId); } string sql = "SELECT ROW_NUMBER() OVER(ORDER BY p.mqpathid DESC,p.[partitionindex] desc) AS rownum,p.*,m.mqpath,(select max(partitionindex) from tb_mqpath_partition p1 where p.mqpathid=p1.mqpathid) as maxpartitionindex FROM tb_mqpath_partition p WITH(NOLOCK),tb_mqpath m with(nolock) where p.mqpathid=m.id"; string countSql = "SELECT COUNT(1) FROM tb_mqpath_partition p WITH(NOLOCK),tb_mqpath m with(nolock) where p.mqpathid=m.id" + 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) { MqPathPartitionModel m = new MqPathPartitionModel(); m.mqpath_partition_model = CreateModel(dr); m.mqpath = Convert.ToString(dr["mqpath"]); m.maxpartitionindex = Convert.ToInt32(dr["maxpartitionindex"]); list.Add(m); } } return list; }); count = tempCount; return result; }
public List<ConsumerPartitionModel> ListByPartitionIds(DbConn conn, List<int> partitionids) { return SqlHelper.Visit((ps) => { var pps = ps.ToParameters(); List<ConsumerPartitionModel> list = new List<ConsumerPartitionModel>(); if (partitionids.Count > 0) { string sql = string.Format("SELECT p.*,c.client FROM tb_consumer_partition p WITH(NOLOCK),tb_consumer_client c WITH(NOLOCK) WHERE p.consumerclientid=c.id and p.partitionid in ({0})", SqlHelper.CmdIn<int>(pps, partitionids)); DataTable dt = conn.SqlToDataTable(sql, pps); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { ConsumerPartitionModel m = new ConsumerPartitionModel(); m.consumerpartitionmodel = CreateModel(dr); m.msgCount = reportDal.GetMsgCount(conn, m.consumerpartitionmodel.lastmqid); m.nonMsgCount = reportDal.GetNonMsgCount(conn,m.consumerpartitionmodel.lastmqid); m.client = Convert.ToString(dr["client"]); list.Add(m); } } } return list; }); }
public IList<int> GetPartitionByPartitionId(DbConn conn, int partitionId) { return SqlHelper.Visit((ps) => { IList<int> list = new List<int>(); string sql = "SELECT * FROM tb_consumer_partition WITH(NOLOCK) WHERE partitionId=@id"; ps.Add("@id", partitionId); DataTable dt = conn.SqlToDataTable(sql, ps.ToParameters()); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { tb_consumer_partition_model model = CreateModel(dr); list.Add(model.consumerclientid); } } return list; }); }
public IList<tb_messagequeue_model> GetPageList(DbConn conn, int pageIndex, int pageSize, string id, ref int count) { int tempCount = 0; IList<tb_messagequeue_model> list = new List<tb_messagequeue_model>(); var result = SqlHelper.Visit((ps) => { StringBuilder where = new StringBuilder(" WHERE 1=1"); if (!string.IsNullOrWhiteSpace(id)) { where.AppendFormat(" AND id={0}", id); } string sql = string.Format("SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS rownum,* FROM {0} WITH(NOLOCK)", TableName) + where; string countSql = string.Format("SELECT COUNT(1) FROM {0} WITH(NOLOCK)", TableName) + where; object obj = conn.ExecuteScalar(countSql, null); if (obj != DBNull.Value && obj != null) { tempCount = LibConvert.ObjToInt(obj); } string sqlPage = string.Concat("SELECT * FROM (", sql.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) { tb_messagequeue_model model = CreateModel(dr); list.Add(model); } } return list; }); count = tempCount; return result; }
public tb_messagequeue_model GetModel(DbConn conn, long id, string tableName) { return SqlHelper.Visit((ps) => { tb_messagequeue_model model = null; string sql = string.Format("SELECT TOP 1 * FROM {0} WITH(NOLOCK) WHERE id=@id", tableName); ps.Add("@id", id); DataTable dt = conn.SqlToDataTable(sql, ps.ToParameters()); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { model = CreateModel(dr); } } return model; }); }
/// <summary> /// ��ȡָ��node�ڵ��µ����б��� /// </summary> /// <param name="conn"></param> /// <param name="node"></param> /// <returns></returns> public ICollection<string> GetDataNodeTableString(DbConn conn) { return SqlHelper.Visit((ps) => { string sql = "SELECT Name FROM SysObjects WITH(NOLOCK) Where XType='U' order BY Name DESC"; IList<string> list = new List<string>(); DataTable dt = conn.SqlToDataTable(sql, null); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { list.Add(dr["Name"].Tostring()); } } return list; }); }
public List<TableInfo> GetDataNodeTable(DbConn conn, DateTime fromcreatedate) { return SqlHelper.Visit((ps) => { string sql = "SELECT Name FROM SysObjects WITH(NOLOCK) Where XType='U' and name like 'tb_messagequeue_%' and crdate>=@crdate order by name desc"; ps.Add("crdate", fromcreatedate); List<TableInfo> list = new List<TableInfo>(); DataTable dt = conn.SqlToDataTable(sql, ps.ToParameters()); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { list.Add(XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.PartitionRuleHelper.GetTableInfo(Convert.ToString(dr["Name"]))); } } return list; }); }
public tb_consumer_model GetModel(DbConn conn, int consumerclientid) { return SqlHelper.Visit((ps) => { string sql = "SELECT * FROM tb_consumer WITH(NOLOCK) WHERE consumerclientid=@id"; ps.Add("@id", consumerclientid); DataTable dt = conn.SqlToDataTable(sql, ps.ToParameters()); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { tb_consumer_model model = CreateModel(dr); return model; } } return null; }); }
public IList<ConsumerModel> GetPageList(DbConn conn, string name, int pageIndex, int pageSize, ref int count) { int tempCount = 0; IList<ConsumerModel> list = new List<ConsumerModel>(); ConsumerModel cm = new ConsumerModel(); var result = SqlHelper.Visit((ps) => { StringBuilder where = new StringBuilder(" WHERE 1=1 "); if (!string.IsNullOrEmpty(name)) { where.AppendFormat(" AND clientname LIKE '%{0}%'", name); } string sql = "SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS rownum,* FROM tb_consumer WITH(NOLOCK)"; string countSql = "SELECT COUNT(1) FROM tb_consumer WITH(NOLOCK) " + 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) { ConsumerModel model = cm.CreateModel(dr); IList<tb_consumer_partition_model> consumerList = partitionDal.GetPartitionByConsumerId(conn, model.consumerclientid); if (consumerList != null && consumerList.Count > 0) { IList<ConsumerPartition> partitionList = new List<ConsumerPartition>(); foreach (var item in consumerList) { ConsumerPartition m = new ConsumerPartition(); m.PartitionId = item.partitionid; PartitionIDInfo partitionInfo = PartitionRuleHelper.GetPartitionIDInfo(item.partitionid); string node = string.Empty; if (partitionInfo.DataNodePartition < 10) { node = "0" + partitionInfo.DataNodePartition.Tostring(); } else { node = partitionInfo.DataNodePartition.Tostring(); } using (DbConn nodeConn = DbConfig.CreateConn(DataConfig.DataNodeParConn(node))) { nodeConn.Open(); tb_partition_model partitionModel = new tb_partition_dal().Get(conn, item.partitionid); if (partitionModel != null) { m.IsOnline = partitionModel.isused; } string table = msgDal.GetMaxMqTable(nodeConn, node); m.Msg = msgDal.GetMsgCount(nodeConn, table, 0); m.NonMsg = msgDal.GetMsgCount(nodeConn, table, 1); partitionList.Add(m); } } model.PartitionList = partitionList; } list.Add(model); } } return list; }); count = tempCount; return result; }
public IList<MqPathModel> GetPageList(DbConn conn, string mqpathid, string mqpath, int pageIndex, int pageSize, ref int count) { int tempCount = 0; IList<MqPathModel> list = new List<MqPathModel>(); MqPathModel createM = new MqPathModel(); var result = SqlHelper.Visit((ps) => { StringBuilder where = new StringBuilder(" WHERE 1=1"); if (!string.IsNullOrEmpty(mqpath)) { where.AppendFormat(" AND mqpath LIKE '%{0}%'", mqpath); } if (!string.IsNullOrEmpty(mqpathid)) { where.AppendFormat(" AND id = '{0}'", mqpathid); } string sql = "SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS rownum,* FROM tb_mqpath WITH(NOLOCK)"; string countSql = "SELECT COUNT(1) FROM tb_mqpath WITH(NOLOCK)" + where; 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(), ") as t 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) { MqPathModel model = createM.CreateModel(dr); model.ProductCount = proDal.GetProductCount(conn, model.id, XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.SystemParamConfig.Producter_HeatBeat_Every_Time); model.NonProductCount = proDal.GetNonProductCount(conn, model.id, XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.SystemParamConfig.Producter_HeatBeat_Every_Time); model.Connsumer = new tb_consumer_partition_dal().GetActiveConsumerCount(conn,model.id); model.NonConnsumer = new tb_consumer_partition_dal().GetLogoutConsumerCount(conn, model.id); model.Partition = parDal.GetPartitionCountByState(conn, (int)XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.EnumMqPathPartitionState.Running, model.id); model.NonPartition = parDal.GetPartitionCountByState(conn, (int)XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.EnumMqPathPartitionState.WaitConsumeCompleted, model.id); list.Add(model); } } return list; }); count = tempCount; return result; }
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; }