如何将查询结果生成DataTable并返回赋值

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