1

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.

10
  • 2
    Are you able to experimentally call NextResult on that reader, and if so, does it return true? 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. Commented Aug 5, 2022 at 13:39
  • @Damien_The_Unbeliever I added var tr = dr.NextResult(); and when I hit NextResult() it returns false. Commented Aug 5, 2022 at 13:57
  • Is the first statement in your procedure "set nocount on;"? If not, add it and test again. Commented Aug 5, 2022 at 14:31
  • @Damien_The_Unbeliever I've added "set nocount on" to the procedure and the result is still the same, NextResult() still false and I still have no rows. Commented Aug 5, 2022 at 14:36
  • Aside... AddWithValue is Evil. You should prefer to use the .Add() methods that include the specific (Sql)DbType, length, precision and/or scale parameters, especially when dates, times or datetimes are involved. Commented Aug 5, 2022 at 14:48

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.