public static List<MetricNodes> GetRootNodes_OneView2(string userName, string userDomain)
{
DataTable dt;
string strSql = "SELECT COUNT(1) FROM TopLevelMetricsForUser WHERE UserName = '" + userName + "' AND Type ='U'";
int cnt = DbConnector.GenerateScalar_Int(strSql);
if (cnt > 0)
{
//user selection
strSql = string.Format(@"SELECT DISTINCT
MN.NodeId,MN.Operation,MN.MetricId,NULL As ParentId,M.parameterId,M.MetricName,M.StatusId,M.HostName,M.value,M.unit,CASE WHEN M.HighLevel IS NULL THEN 0 ELSE M.HighLevel END As HighLevel,
(select count(1) from MetricNodes where NodePath.GetAncestor(1)=MN.NodePath) As HighLevelChildren,MN.ParentNodeId,
CASE WHEN T.MetricId IS NULL THEN 'UnChecked' ELSE 'Checked' END As Checked ,NodePath.GetLevel() as level,
(select count(1) from MetricNodeRelations where SourceNodeId=MN.NodeId) as hasRelation
FROM MetricNodes MN,Metrics M,TopLevelMetricsForUser T
WHERE M.MetricId = MN.MetricId AND M.MetricId = T.MetricId
AND T.UserName = '{0}' AND T.Type ='U'
AND MN.MetricId NOT IN ( SELECT MetricId From FilteredMetrics F, UserDomains U WHERE U.UserDomain In ('{1}') AND F.UserDomainId = U.UserDomainId )",
userName, userDomain.Replace(",", "','"));
dt = DbConnector.GenerateDataTable(strSql);
}
else
{
strSql = "SELECT COUNT(*) FROM TopLevelMetricsForUser WHERE UserName = '" + userName + "' AND Type ='A'";
cnt = DbConnector.GenerateScalar_Int(strSql);
if (cnt > 0)
{
//admin setting for a user
strSql = string.Format(@"SELECT DISTINCT
MN.NodeId,MN.Operation,MN.MetricId,NULL As ParentId,M.parameterId,M.MetricName,M.StatusId,M.HostName,M.value,M.unit,CASE WHEN M.HighLevel IS NULL THEN 0 ELSE M.HighLevel END As HighLevel,
(select count(1) from MetricNodes where NodePath.GetAncestor(1)=MN.NodePath) As HighLevelChildren,MN.ParentNodeId,
CASE WHEN T.MetricId IS NULL THEN 'UnChecked' ELSE 'Checked' END As Checked,NodePath.GetLevel() as level,
(select count(1) from MetricNodeRelations where SourceNodeId=MN.NodeId) as hasRelation
FROM MetricNodes MN,Metrics M,TopLevelMetricsForUser T
WHERE M.MetricId = MN.MetricId AND M.MetricId = T.MetricId
AND T.UserName = '{0}' AND T.Type ='A'
AND MN.MetricId NOT IN ( SELECT MetricId From FilteredMetrics F, UserDomains U WHERE U.UserDomain In ('{1}') AND F.UserDomainId = U.UserDomainId )",
userName, userDomain.Replace(",", "','"));
dt = DbConnector.GenerateDataTable(strSql);
}
else
{
strSql = string.Format(@"SELECT DISTINCT
MN.NodeId,MN.Operation,MN.MetricId, NULL AS ParentId,M.parameterId ,M.MetricName,M.StatusId,M.HostName,M.value,M.unit,CASE WHEN M.HighLevel IS NULL THEN 0 ELSE M.HighLevel END As HighLevel,
(select count(1) from MetricNodes where NodePath.GetAncestor(1)=MN.NodePath) As HighLevelChildren,MN.ParentNodeId,
CASE WHEN T.MetricId IS NULL THEN 'UnChecked' ELSE 'Checked' END As Checked ,NodePath.GetLevel() as level,
(select count(1) from MetricNodeRelations where SourceNodeId=MN.NodeId) as hasRelation
FROM MetricNodes MN,Metrics M
LEFT JOIN TopLevelMetricsForUserDomain T on M.MetricId = T.MetricId
JOIN UserDomains U ON T.UserDomainId = U.UserDomainId
AND U.UserDomain IN ('{0}')
WHERE M.metricid=MN.MetricId
AND M.MetricId NOT IN (SELECT MetricId From FilteredMetrics Where UserDomainId = U.UserDomainId)", userDomain.Replace(",", "','"));
dt = DbConnector.GenerateDataTable(strSql);
}
}
var resultList = new List<MetricNodes>();
foreach (DataRow row in dt.Rows)
{
resultList.Add(new MetricNodes
{
MetricId = Convert.ToInt32(row["MetricId"]),
HasChildren = Convert.ToInt32(row["HighLevelChildren"]),
HighLevelChildren = Convert.ToInt32(row["HighLevelChildren"]),
HighLevel = row["HighLevel"] == DBNull.Value ? null : (bool?)Convert.ToBoolean(row["HighLevel"]),
MetricName = Convert.ToString(row["MetricName"]),
HostName = Convert.ToString(row["HostName"]),
UserDomain = userDomain,
StatusId = row["StatusId"] == DBNull.Value ? null : (int?)row["StatusId"],
NodeId = row["NodeId"] == DBNull.Value ? 0 : Convert.ToInt32(row["NodeId"]),
ParentNodeId = row["ParentNodeId"] == DBNull.Value ? 0 : Convert.ToInt32(row["ParentNodeId"]),
Value = row["value"] == DBNull.Value ? (double?)null : Convert.ToDouble(row["value"]),
Unit = row["unit"] == DBNull.Value ? "" : Convert.ToString(row["unit"]),
Level = row["level"] == DBNull.Value ? 0 : Convert.ToInt32(row["level"]),
HasRelation = row["hasRelation"] == DBNull.Value ? 0 : Convert.ToInt32(row["hasRelation"]),
Operation = row["Operation"] != DBNull.Value && Convert.ToBoolean(row["Operation"]),
ParameterId = row["parameterId"] == DBNull.Value ? null : Convert.ToString(row["parameterId"])
});
}
if (dt.Rows.Count == 1)
{
var childlist = GetChildNodes_OneView2(Convert.ToInt32(dt.Rows[0]["NodeId"]), userDomain);
resultList = resultList.Union(childlist).ToList();
}
return resultList;
}
dbconnect方法
public static DataTable GenerateDataTable(string query)
{
if (connStr == "")
loadConnStrings();
//for timebomb tp
SqlConnection conn = new SqlConnection(connStr);
DataTable dt = GenerateDataTable(query, conn);
try
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
catch (SqlException sqle)
{
throw (sqle);
}
return dt;
}
This method generates DataTable using an existing connection
/// <summary>
/// This method generates DataTable using an existing connection
/// </summary>
/// <param name="query">a sql query statement</param>
/// <returns>DataTale</returns>
public static DataTable GenerateDataTable(string query, SqlConnection conn)
{
DataTable dt;
try
{
if (conn != null && conn.State == ConnectionState.Closed || conn.State == ConnectionState.Broken)
conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(query, conn);
adapter.SelectCommand.CommandTimeout = 200;
dt = new DataTable();
adapter.Fill(dt);
adapter.Dispose();
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
catch (SqlException sqle)
{
throw (sqle);
}
return dt;
}