Reputation: 712
I'm working on an application that uses C# to do a search like so:
using (SqlCommand cmd = new SqlCommand("[dbo].[usp_Search]", commercialConn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 5000;
if (!String.IsNullOrEmpty(lastName))
{
cmd.Parameters.AddWithValue("@xmlLastName", lastName);
}
else
{
cmd.Parameters.AddWithValue("@xmlLastName", DBNull.Value);
}
...
}
etc... for about 20 parameters. When I hit this search, this statement shows up in the profiler:
exec [dbo].[usp_Search] @xmlPolicyId=NULL,@xmlLastName=NULL,@xmlFirstName=NULL,@xmlCompanyName=NULL,@xmlAddress=N'%TCHOUPITOULAS%',@xmlAddress2=NULL,@xmlCity=NULL,@xmlState=NULL,@xmlZipCode=NULL,@xmlCountry=NULL,@xmlQuotesOnly=0,@xmlHistory=0,@xmlEffectiveDate='1753-01-01 00:00:00',@xmlAgencyName=NULL,@xmlAgentId=NULL,@xmlssn=NULL,@xmlfein=NULL,@xmlPolicyStatus=NULL,@xmlPolicyType=NULL,@xmlUserLevel=N'5'
This statement is exactly what I expect, and when I fire it off in SSMS on the same database I get about 300 rows as expected.
My SqlDataReader is implemented like this:
commercialConn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read()) //dr._hasRows = false
{
// never enters this scope
}
}
So it looks like it fires the statement on the correct database as I can see it in the profiler, I know the connection is correct, the command parameters are correct and the command I see in the profiler returns rows, but for some reason the SqlDataReader either doesn't have them or cannot access them. I'm quite lost as I've stepped through every step of this process and it seems correct up until the reader just doesn't have any data.
Upvotes: 1
Views: 66