/// <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); }
public virtual tb_shuntruleconn_config_model CreateModel(DataRow dr) { return(new tb_shuntruleconn_config_model { //自增 id = dr["id"].Toint(), //分区id(分区标识号) partitionno = dr["f_partitionno"].Toint(), //地区编码 regioncode = dr["f_regioncode"].Toint(), //服务器地址 dbserver = dr["f_dbserver"].Tostring(), //数据库名 dbname = dr["f_dbname"].Tostring(), //用户名 dbuser = dr["f_dbuser"].Tostring(), //密码 dbpass = dr["f_dbpass"].Tostring(), //最后更新时间 lastUpdateTime = LibConvert.ObjToDateTime(dr["f_last_update_time"]), //是否删除 isDel = LibConvert.ObjToBool(dr["f_isdel"]) }); }
/// <summary> /// 获得cids的分类列表 /// </summary> /// <param name="cids"></param> /// <returns></returns> public List <Category> GetListByIds(string conn, string cids) { List <Category> list = new List <Category>(); string[] cidstr = cids.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries); long[] ids = new long[cidstr.Length]; for (int i = 0; i < cidstr.Length; i++) { ids[i] = LibConvert.StrToInt64(cidstr[i]); } string sql = "SELECT Id,CategoryName,Remark,CreateTime FROM Category"; SqlParameter[] paramters = new SqlParameter[] { new SqlParameter("@cdis", cids) }; DataTable dt = SqlServerHelper.Get(conn, sql, paramters); if (dt.Rows.Count > 0) { for (int m = 0; m < dt.Rows.Count; m++) { Category cate = new Category(); cate = Category.CreateModel(dt.Rows[m]); list.Add(cate); } } return(list.Where(c => ids.Contains(c.Id)).ToList()); }
public virtual tb_dbpartition_config_model CreateModel(DataRow dr) { return(new tb_dbpartition_config_model { //自增 id = dr["id"].Toint(), //分区id(分区标识号) partitionno = dr["f_partitionno"].Toint(), //分区类型(1:用户分区,2:商户表分区) partitiontype = dr["f_partitiontype"].ToByte(), //服务器地址 dbserver = dr["f_dbserver"].Tostring(), //数据库名 dbname = dr["f_dbname"].Tostring(), //用户名 dbuser = dr["f_dbuser"].Tostring(), //密码 dbpass = dr["f_dbpass"].Tostring(), lastUpdateTime = LibConvert.ObjToDateTime(dr["f_last_update_time"]), //是否删除 isDel = LibConvert.ObjToBool(dr["f_isdel"]) }); }
public static Config CreateModel(DataRow dr) { Config config = new Config(); if (dr.Table.Columns.Contains("Id")) { config.Id = LibConvert.ObjToInt64(dr["Id"]); } if (dr.Table.Columns.Contains("CategoryId")) { config.CategoryId = LibConvert.ObjToInt64(dr["CategoryId"]); } if (dr.Table.Columns.Contains("ConfigKey")) { config.ConfigKey = LibConvert.ObjToStr(dr["ConfigKey"]); } if (dr.Table.Columns.Contains("ConfigValue")) { config.ConfigValue = LibConvert.ObjToStr(dr["ConfigValue"]); } if (dr.Table.Columns.Contains("Remark")) { config.Remark = LibConvert.ObjToStr(dr["Remark"]); } if (dr.Table.Columns.Contains("CreateTime")) { config.CreateTime = LibConvert.ObjToDateTime(dr["CreateTime"]); } if (dr.Table.Columns.Contains("UpdateTime")) { config.UpdateTime = LibConvert.ObjToDateTime(dr["UpdateTime"]); } return(config); }
/// <summary> /// 根据节点获取所有的分区集合 /// </summary> /// <param name="conn"></param> /// <param name="node"></param> /// <returns></returns> public IDictionary <int, string> GetMqByPartitionId(DbConn conn, string node) { ICollection <string> array = this.GetDataNodeTableString(conn); IDictionary <int, string> dic = new Dictionary <int, string>(); if (array == null || array.Count <= 0) { return(null); } foreach (var item in array) { IList <string> result = item.Split('_'); if (result != null && result.Count > 3) { int date = LibConvert.ObjToInt(result[3]); string nodeId = result[2].Tostring(); if (!dic.ContainsKey(date) && nodeId.Equals(node)) { dic.Add(date, result[2]); } } } return(dic); }
public static Project CreateModel(DataRow dr) { Project project = new Project(); if (dr.Table.Columns.Contains("Id")) { project.Id = LibConvert.ObjToInt64(dr["Id"]); } if (dr.Table.Columns.Contains("ProjectName")) { project.ProjectName = LibConvert.ObjToStr(dr["ProjectName"]); } if (dr.Table.Columns.Contains("CategoryIds")) { project.CategoryIds = LibConvert.ObjToStr(dr["CategoryIds"]); } if (dr.Table.Columns.Contains("Remark")) { project.Remark = LibConvert.ObjToStr(dr["Remark"]); } if (dr.Table.Columns.Contains("CreateTime")) { project.CreateTime = LibConvert.ObjToDateTime(dr["CreateTime"]); } return(project); }
public static Command CreateModel(DataRow dr) { Command cmd = new Command(); if (dr.Table.Columns.Contains("Id")) { cmd.Id = LibConvert.ObjToInt64(dr["Id"]); } if (dr.Table.Columns.Contains("TaskId")) { cmd.TaskId = LibConvert.ObjToInt64(dr["TaskId"]); } if (dr.Table.Columns.Contains("NodeId")) { cmd.NodeId = LibConvert.ObjToInt64(dr["NodeId"]); } if (dr.Table.Columns.Contains("CommandType")) { cmd.CommandType = (CommandType)LibConvert.ObjToInt(dr["CommandType"]); } if (dr.Table.Columns.Contains("CreateTime")) { cmd.CreateTime = LibConvert.ObjToDateTime(dr["CreateTime"]); } if (dr.Table.Columns.Contains("State")) { cmd.State = LibConvert.ObjToInt(dr["State"]); } return(cmd); }
/// <summary> /// 取得已消费的数量 /// </summary> /// <param name="conn"></param> /// <param name="lastMqId"></param> /// <returns></returns> public long GetMsgCount(DbConn conn, long lastMqId) { return(SqlHelper.Visit((ps) => { MQIDInfo info = PartitionRuleHelper.GetMQIDInfo(lastMqId); string sql = "SELECT SUM(mqcount) FROM [tb_partition_messagequeue_report] WITH(NOLOCK) WHERE [day]<@day AND partitionid=@partitionid"; ps.Add("@day", info.Day); ps.Add("@partitionid", PartitionRuleHelper.GetPartitionID(new PartitionIDInfo() { DataNodePartition = info.DataNodePartition, TablePartition = info.TablePartition })); string tableName = PartitionRuleHelper.GetTableName(info.TablePartition, info.Day); object obj = conn.ExecuteScalar(sql, ps.ToParameters()); long msgCount = 0; if (obj != DBNull.Value && obj != null) { msgCount = LibConvert.ObjToInt64(obj); } long lastCount = 0; using (DbConn nodeConn = DbConfig.CreateConn(DataConfig.DataNodeParConn(PartitionRuleHelper.PartitionNameRule(info.DataNodePartition)))) { nodeConn.Open(); var dal = new tb_messagequeue_dal(); dal.TableName = tableName; lastCount = dal.GetLastDayMsgCount(nodeConn, lastMqId); } return msgCount + lastCount; })); }
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); }
/// <summary> /// Lấy dữ liệu trả về là mảng Object /// </summary> /// <param name="obj">Biến truyền vào</param> /// <param name="TypeDataOutPut">Kiểu dữ liệu trả về</param> /// <param name="procedure">Tên thủ tục</param> public static object[] GetOracleDataObject(object obj, string TypeDataOutPut, string procedure) { char[] type = TypeDataOutPut.ToCharArray(); object[] objectReturn = new object[type.Length]; SessionHelper se = SessionHelper.GetSessionUser(); OracleConnection conn = GetOracleConnection(); OracleCommand command = new OracleCommand { Connection = conn }; try { string pramater = ""; pramater = "," + LibConvert.ObjectToChar(obj); for (int i = 0; i < type.Length; i++) { string pare = type[i].ToString().Trim(); OracleDbType typeDb = TypeDbOra(type[i]); AttachParamater(ref command, "return" + i.ToString(), typeDb); pramater = pramater + ",:return" + i.ToString(); } command.CommandText = "Begin " + se.dbo + "." + procedure + "(" + se.tso + pramater + "); end;"; OracleDataAdapter oraAdapter = new OracleDataAdapter(command); DataSet set = new DataSet(); oraAdapter.Fill(set); int countCusor = 0; for (int j = 0; j < type.Length; j++) { switch (type[j]) { case 'R': { objectReturn[j] = set.Tables[countCusor]; countCusor++; break; } case 'N': objectReturn[j] = command.Parameters["return" + j.ToString().Trim()].Value.ToString(); break; default: objectReturn[j] = command.Parameters["return" + j.ToString().Trim()].Value.ToString(); break; } } } finally { command.Parameters.Clear(); conn.Close(); } return(objectReturn); }
/// <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 virtual tb_dbsubscribe_config_model CreateModel(DataRow dr) { var o = new tb_dbsubscribe_config_model(); // if (dr.Table.Columns.Contains("f_id")) { o.id = dr["f_id"].Toint(); } // if (dr.Table.Columns.Contains("f_partitionno")) { o.partitionno = dr["f_partitionno"].Tostring(); } // if (dr.Table.Columns.Contains("f_partitiontype")) { o.partitiontype = dr["f_partitiontype"].Toint(); } // if (dr.Table.Columns.Contains("f_dbserver")) { o.dbserver = dr["f_dbserver"].Tostring(); } // if (dr.Table.Columns.Contains("f_dbname")) { o.dbname = dr["f_dbname"].Tostring(); } // if (dr.Table.Columns.Contains("f_dbuser")) { o.dbuser = dr["f_dbuser"].Tostring(); } // if (dr.Table.Columns.Contains("f_dbpass")) { o.dbpass = dr["f_dbpass"].Tostring(); } // if (dr.Table.Columns.Contains("f_dbtype")) { o.dbtype = dr["f_dbtype"].Toint(); } //最后更新时间 if (dr.Table.Columns.Contains("f_last_update_time")) { o.lastUpdateTime = LibConvert.ObjToDateTime(dr["f_last_update_time"]); } //是否删除 if (dr.Table.Columns.Contains("f_isdel")) { o.isDel = LibConvert.ObjToBool(dr["f_isdel"]); } return(o); }
/// <summary> /// hash表批量获取数据 /// </summary> /// <param name="key"></param> /// <param name="fields"></param> public byte[][] HMGet(string key, List <string> fields) { byte[][] byteKeys = new byte[fields.Count][]; for (int i = 0; i < fields.Count; i++) { byteKeys[i] = LibConvert.StrToBytes(fields[i]); } return(redisCli.HMGet(key, byteKeys)); }
public string GetServerMonitorJson(DbConn PubConn, int serverid) { return(SqlHelper.Visit(ps => { ps.Add("@serverid", serverid); StringBuilder stringSql = new StringBuilder(); stringSql.Append(@"select monitorinfojson from tb_cluster_monitorinfo s where s.serverid=@serverid"); string json = LibConvert.ObjToStr(PubConn.ExecuteScalar(stringSql.Tostring(), ps.ToParameters())); return json; })); }
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 long GetLastMqIdByPartitionId(DbConn conn, int partitionId) { return(SqlHelper.Visit((ps) => { string sql = string.Format("SELECT lastmqid FROM tb_consumer_partition WITH(NOLOCK) WHERE partitionId={0}", partitionId.Tostring()); object obj = conn.ExecuteScalar(sql, null); if (obj != DBNull.Value && obj != null) { return LibConvert.ObjToInt64(obj); } return 0; })); }
/// <summary> /// 获取消息的数量 /// </summary> /// <param name="conn"></param> /// <param name="tableName"></param> /// <param name="state"></param> /// <returns></returns> public int GetMsgCount(DbConn conn, string tableName, int state) { return(SqlHelper.Visit((ps) => { string sql = string.Format("SELECT COUNT(1) FROM {0} WITH(NOLOCK) WHERE state=@state", tableName); ps.Add("@state", state); object obj = conn.ExecuteScalar(sql, ps.ToParameters()); if (obj != DBNull.Value && obj != null) { return LibConvert.ObjToInt(obj); } return 0; })); }
public int GetMaxPartitionIndex(DbConn conn, int mqpathid) { return(SqlHelper.Visit((ps) => { string sql = "select max(partitionindex) from tb_mqpath_partition p1 WITH(NOLOCK) where p1.mqpathid=@mqpathid"; ps.Add("mqpathid", mqpathid); object obj = conn.ExecuteScalar(sql, ps.ToParameters()); if (obj != DBNull.Value && obj != null) { return LibConvert.ObjToInt(obj); } return 0; })); }
public override int GetIdentity() { DataSet ds = new DataSet(); SqlToDataSet(ds, "select @@identity", null); if (ds.Tables[0].Rows.Count == 0) { return(0); } else { return(LibConvert.ObjToInt(ds.Tables[0].Rows[0][0])); } }
public int GetLogoutConsumerCount(DbConn conn, int mqpathId) { return(SqlHelper.Visit((ps) => { string sql = "SELECT COUNT(p.consumerclientid) FROM tb_consumer_partition p WITH(NOLOCK),tb_mqpath_partition m WITH(NOLOCK) where p.partitionid=m.partitionid and m.mqpathid=@mqpathid and p.lastconsumertempid not in (select distinct(lastconsumertempid) from tb_consumer WITH(NOLOCK))"; ps.Add("@mqpathid", mqpathId); object obj = conn.ExecuteScalar(sql, ps.ToParameters()); if (obj != DBNull.Value && obj != null) { return LibConvert.ObjToInt(obj); } return 0; })); }
public int GetNonProductCount(DbConn conn, int mqPathId, int sec) { return(SqlHelper.Visit((ps) => { string sql = string.Format("SELECT COUNT(1) FROM tb_producter WITH(NOLOCK) WHERE mqpathid=@mqpathid AND datediff(s,lastheartbeat,getdate())>={0}", sec); ps.Add("@mqpathid", mqPathId); object obj = conn.ExecuteScalar(sql, ps.ToParameters()); if (obj != DBNull.Value && obj != null) { return LibConvert.ObjToInt(obj); } return 0; })); }
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 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 static string DataNodeParConn(string node) { using (DbConn conn = DbConfig.CreateConn(DataConfig.MqManage)) { conn.Open(); tb_datanode_model model = nodeDal.GetModelByPartitionId(conn, LibConvert.ObjToInt(node)); if (model != null) { //string configNodeConn = ConfigConn("DataNodeConnectString"); return(string.Format("server={0};Initial Catalog=dyd_bs_MQ_datanode_{1};User ID={2};Password={3};", model.serverip, XXF.BaseService.MessageQuque.BusinessMQ.SystemRuntime.PartitionRuleHelper.PartitionNameRule(LibConvert.ObjToInt(node)) , model.username, model.password)); } return(""); } }
public int GetPartitionCountByState(DbConn conn, int state, int mqPathId) { return(SqlHelper.Visit((ps) => { string sql = "select COUNT(1) from tb_mqpath_partition WITH(NOLOCK),tb_partition WITH(NOLOCK) where tb_partition.partitionid=tb_mqpath_partition.partitionid and tb_mqpath_partition.state=@state and mqpathid=@mqpathId"; ps.Add("mqpathId", mqPathId); ps.Add("state", state); object obj = conn.ExecuteScalar(sql, ps.ToParameters()); if (obj != DBNull.Value && obj != null) { return LibConvert.ObjToInt(obj); } return 0; })); }
public int SetState(DbConn conn, int partitionid, int state) { return(SqlHelper.Visit((ps) => { string sql = "update tb_mqpath_partition set state=@state WHERE partitionid=@partitionid"; ps.Add("partitionid", partitionid); ps.Add("state", state); object obj = conn.ExecuteScalar(sql, ps.ToParameters()); if (obj != DBNull.Value && obj != null) { return LibConvert.ObjToInt(obj); } return 0; })); }
/// <summary> /// 取得未消费的数量 /// </summary> /// <param name="lastMqId"></param> /// <returns></returns> public long GetNonMsgCount(DbConn conn, long lastMqId) { return(SqlHelper.Visit((ps) => { MQIDInfo info = PartitionRuleHelper.GetMQIDInfo(lastMqId); var currentday = conn.GetServerDate().Date; string sql = "SELECT SUM(mqcount) FROM [tb_partition_messagequeue_report] WITH(NOLOCK) WHERE [day]>@day AND partitionid=@partitionid AND [day]<>@currentday"; ps.Add("@day", info.Day); ps.Add("@currentday", currentday); ps.Add("@partitionid", PartitionRuleHelper.GetPartitionID(new PartitionIDInfo() { DataNodePartition = info.DataNodePartition, TablePartition = info.TablePartition })); object obj = conn.ExecuteScalar(sql, ps.ToParameters()); long msgCount = 0; if (obj != DBNull.Value && obj != null) { msgCount = LibConvert.ObjToInt64(obj); } long firstCount = 0; long lastCount = 0; using (DbConn nodeConn = DbConfig.CreateConn(DataConfig.DataNodeParConn(PartitionRuleHelper.PartitionNameRule(info.DataNodePartition)))) { nodeConn.Open(); string firsttableName = PartitionRuleHelper.GetTableName(info.TablePartition, info.Day); var msgDal = new tb_messagequeue_dal(); msgDal.TableName = firsttableName; firstCount = msgDal.GetLastDayNonMsgCount(nodeConn, lastMqId); if (info.Day != currentday)//不是今天 { string lasttableName = PartitionRuleHelper.GetTableName(info.TablePartition, currentday); var dal = new tb_messagequeue_dal(); dal.TableName = lasttableName; long maxmqid = dal.GetMaxID(nodeConn); if (lastMqId == 0) { lastCount = 0; } else { lastCount = dal.GetLastDayMsgCount(nodeConn, maxmqid); } } } //最后一天剩余 return msgCount + firstCount + lastCount; })); }
/// <summary> ///gọi thủ tục với tham số truyền vào là mảng Object /// </summary> /// <param name="obj">Mảng tham số Object truyền vào</param> /// <param name="procedure">Tên thủ tục</param> /// <returns>Trả lại là kiểu dataTable</returns> public static DataTable GetOracleData(object[] obj, string procedure) { DataTable table = new DataTable(); SessionHelper _se = new SessionHelper(); if (_se == null) { throw new Exception("loi: Mất kết nối. Đăng nhập lại:loi"); } OracleConnection cnn = GetOracleConnection(); try { OracleCommand command = new OracleCommand { Connection = cnn }; AttachParamater(ref command, "cs1", OracleDbType.RefCursor); string str = ""; for (int i = 0; i < obj.Length; i++) { str = str + "," + LibConvert.ObjectToChar(obj[i]); } str = str + ",:cs1"; if (!string.IsNullOrEmpty(_se.tso)) { str = _se.tso + str; } command.CommandText = "Begin " + _se.dbo + "." + procedure + "(" + str + "); end;"; try { new OracleDataAdapter(command).Fill(table); } finally { command.Parameters.Clear(); } } finally { cnn.Close(); } return(table); }
public int GetMaxCommandID(DbConn PubConn) { return(SqlHelper.Visit(ps => { StringBuilder stringSql = new StringBuilder(); stringSql.Append(@"select max(id) from tb_command s where s.commandstate=2"); DataSet ds = new DataSet(); PubConn.SqlToDataSet(ds, stringSql.ToString(), ps.ToParameters()); if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { if (!LibConvert.IsDbNull(ds.Tables[0].Rows[0][0])) { return LibConvert.ObjToInt(ds.Tables[0].Rows[0][0]); } } return 0; })); }