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.
NextResulton that reader, and if so, does it returntrue? It can often be easy to overlook that something is causing multiple result sets to be returned and the first result set might be empty..Add()methods that include the specific (Sql)DbType, length, precision and/or scale parameters, especially when dates, times or datetimes are involved.