如何将查询结果生成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;
        }