C#程序未从SQL查询中获取结果

我用C#编写了一个函数，用于从TSQL查询中检索结果。

public List<string> ExecuteRule(string rule)
{
    List<string> foundLCNs = new List<string>();
    try
    {
        myConnection = new SqlConnection(ConnectionString);
        SqlDataReader myReader = null;
        SqlCommand myCommand = new SqlCommand
        (
            rule, 
            myConnection 
        ); 
        myConnection.Open();
        myReader = myCommand.ExecuteReader();
        while(myReader.Read())
        {
            foundLCNs.Add(myReader["labno"].ToString());
        }
    }
    catch (Exception e)
    {
        Controller.PrintStackTrace (e);
        Console.WriteLine(e.ToString());
    }
    finally
    {
        myConnection.Close();
    }
    return foundLCNs;
}

我发现，当我运行一些SQL查询时，不会返回任何结果，但当我复制SQL并在Microsoft SQL Server管理sudio中运行它时，我会得到一些结果。

也许查询可能花费了太长时间，但我不会抛出任何异常。

当我使用调试器逐步执行代码时我打了这条线

myReader=myCommand.ExecuteReader（）；

执行速度很快（检索数据应该需要几秒钟时间）。

while循环从不执行内部的代码，它只是跳过，因为没有返回任何数据。

编辑

SQL看起来像这个

WITH FilterPatches AS
(
    SELECT 
        OIL_SAMPLE.labno 
    FROM OIL_SAMPLE 
    INNER JOIN LU_OIL_TEST_TYPE ON OIL_SAMPLE.test_type_auto = LU_OIL_TEST_TYPE.test_type_auto 
    LEFT JOIN OIL_EQ_UNIT ON OIL_SAMPLE.equnit_auto = OIL_EQ_UNIT.equnit_auto 
    LEFT JOIN Equipment ON OIL_EQ_UNIT.equipmentid_auto = Equipment.equipmentid_auto 
    LEFT JOIN CRSF ON Equipment.crsf_auto = CRSF.crsf_auto 
    LEFT JOIN CUSTOMER ON CRSF.customer_auto = CUSTOMER.customer_auto 
    LEFT JOIN OIL_SAMPLE_READING_STORE AS DEP ON OIL_SAMPLE.labno = DEP.labno 
    LEFT JOIN OIL_SAMPLE_READING AS DEP2 ON OIL_SAMPLE.labno_auto = DEP2.labno_auto 
    WHERE CUSTOMER.custid IN 
    ( 
        '555555', '555'
    ) AND 
    ( 
        (
            DEP.element_auto IN 
            ( 
                 SELECT element_auto 
                 FROM LU_OIL_ELEMENT 
                 WHERE LU_OIL_ELEMENT.elementid = 'DEP' 
            ) 
            AND 
            DEP.reading NOT IN ('vw', 'ok') 
        ) 
        OR 
        ( 
            DEP2.element_auto IN 
            ( 
                SELECT element_auto 
                FROM LU_OIL_ELEMENT 
                WHERE LU_OIL_ELEMENT.elementid = 'DEP' 
            ) 
            AND 
            DEP2.reading NOT IN ('vw', 'ok') 
            AND OIL_SAMPLE.data_released_date IS NULL
        )
    ) 
    AND OIL_SAMPLE.labno NOT IN
    ( 
        SELECT labno 
        FROM OIL_SAMPLE_READING_STORE 
        WHERE element_auto IN 
        ( 
            SELECT element_auto 
            FROM LU_OIL_ELEMENT 
            WHERE LU_OIL_ELEMENT.elementid = 'FPATCH' 
        ) 
    ) 
    AND LU_OIL_TEST_TYPE.test_type_desc NOT IN 
    (
        'Diesel',
        'DieselStd',
        'DieselSml'
    )
),
ParticleCountElements AS
(
    SELECT element_auto 
    FROM LU_OIL_ELEMENT 
    WHERE LU_OIL_ELEMENT.elementid IN
    (
        'AB',
        'CD'
    ) 
)
(
    SELECT labno
    FROM FilterPatches
    EXCEPT
    (
        SELECT DISTINCT labno
        FROM OIL_SAMPLE_READING_STORE
        WHERE element_auto IN
        (
            SELECT element_auto
            FROM ParticleCountElements
        )
        AND labno IN 
        (
            SELECT labno
            FROM FilterPatches
        )
        AND reading_raw IS NOT NULL
        UNION
        SELECT labno
        FROM OIL_SAMPLE 
        LEFT JOIN OIL_SAMPLE_READING ON OIL_SAMPLE.labno_auto = OIL_SAMPLE_READING.labno_auto 
        WHERE OIL_SAMPLE.data_released_date IS NULL
        AND OIL_SAMPLE_READING.element_auto IN
        (
            SELECT element_auto
            FROM ParticleCountElements
        )
        AND labno IN 
        (
            SELECT labno
            FROM FilterPatches
        )
    )
)

我试过在microsoftsqlservermanagementstudio中运行这个，它运行得很好。

编辑

正如Will建议的那样，我已经尝试将代码更改为

List<string> foundLCNs = new List<string>();
using (var cnx = new SqlConnection (ConnectionString)) 
{
    using (SqlCommand cmd = new SqlCommand (rule, cnx)) 
    {
        cmd.CommandType = CommandType.Text;
        cnx.Open ();
        using (var dr = cmd.ExecuteReader())
        {
            if (dr.HasRows) 
            {
                while (dr.Read ()) 
                {
                    if (!dr.IsDBNull (0)) 
                    {
                        foundLCNs.Add (dr.GetString (0));
                    }
                }
            }
        }
    }
}
return foundLCNs;

dr.HasRows是错误的

不过我还是有同样的行为。它适用于其他查询，但不适用于我发布的查询。但是，当在SQL server中运行时，此查询确实有效。

public IList<string> ExecuteRule(string rule) {
    var found = new List<string>();
    using (var cnx = new SqlConnection(connectionString)) 
        using (var cmd = new SqlCommand(rule, cnx)) {
            cmd.CommandType = CommandType.Text;
            
            // Make sure to add values to parameters whenever required by your query.
            // e.g. cmd.Parameters.AddWithValue("@paramName", value);
            cnx.Open();
            using (var dr = cmd.ExecuteReader()) 
                if (dr.HasRows)
                    while (dr.Read()) 
                        if (!dr.IsDBNull(0)) 
                            found.Add(dr.GetStringValue(0));                                        
        }   
    return found;         
}