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.

awimley
  • 692
  • 1
  • 9
  • 29
  • 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. – Damien_The_Unbeliever Aug 05 '22 at 13:39
  • @Damien_The_Unbeliever I added var tr = dr.NextResult(); and when I hit NextResult() it returns false. – awimley Aug 05 '22 at 13:57
  • Is the first statement in your procedure "set nocount on;"? If not, add it and test again. – SMor Aug 05 '22 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. – awimley Aug 05 '22 at 14:36
  • Aside... [AddWithValue is Evil](https://www.dbdelta.com/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. – AlwaysLearning Aug 05 '22 at 14:48
  • Also [AddWithValue adds no value](https://sqlblog.org/addwithvalue) – Aaron Bertrand Aug 05 '22 at 14:49
  • @AaronBertrand What should I use instead? I was using cmd.Parameters.Add() but vs warned me it was obsolete. – awimley Aug 05 '22 at 14:51
  • @awimley VS has been saying that [for over a decade](https://stackoverflow.com/q/13580993/61305). Ignore it (and the accepted answer there), just be sure you use all of the arguments (data type, length and precision where appropriate, etc). `AddWithValue()` is just a slightly lazier way to do the same thing but the posts I linked to should illustrate why it's the wrong choice. – Aaron Bertrand Aug 05 '22 at 14:55
  • @AaronBertrand I updated the code to add parameters like this: – awimley Aug 05 '22 at 15:36
  • We don't need to see how you fixed that aspect of the code, especially not in a comment and especially if unrelated to the question above. – Aaron Bertrand Aug 05 '22 at 15:38

0 Answers0