/// <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;
}));
}
