I've written a function in C# to retrieve results from a TSQL query.
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;
}
I am finding that when I run some SQL queries, no results are returned and yet, when I copy the SQL and run it in Microsoft SQL Server management sudio, I get some results.
Perhaps the query could be taking too long, but I'm not getting any exceptions thrown.
When I step through the code with the debugger I hit this line
myReader = myCommand.ExecuteReader();
executes quickly (it should take a couple of seconds to retrieve the data).
The while loop never executes the code inside, it just skips over as no data is returned.
EDIT
The SQL looks like this
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
)
)
)
I've tried running this in microsoft sql server management studio and it works fine.
EDIT
As Will Suggested I have tried changing the code to
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 is false
I'm still getting the same behaviour though. It works for other queries, but not the one that I posted. However this query does work when run in SQL server.
