-1

Problem

I have a button in the UI that reveals all remaining records in a table.

To handle this, in my controller, I have a simple SQL SELECT * statement with a LIMIT and OFFSET. My ExecuteReader is currently returning my data from the SQL command, which I am adding to a List. The list contains instances of my custom Run class.

In SSMS, the SQL query executes without exception no matter how large of a LIMIT I request. If my limit is > the number of rows in the table, it just returns all rows.

In webAPI, though, when my limit is > 200, it returns an exception. Otherwise, when less than 200, it returns a List of Runs without exception. I'm trying to debug the exception that occurs when I try to return all the data, but when it passes to the catch block, the exception is null. Which is weird.

So, I think there is a step I'm missing. Maybe I shouldn't be transforming the data into the Run class while the Reader is streaming. If I verified that the SQL command is accurate, then this seems to be the step that is causing the bug. Maybe transforming the data is making the Reader sorta time out? I don't understand ExecuteReader well enough to be able to figure out how I can pass all the data to List and then transform the data in that list into Runs after closing the connection. And don't even know if that would solve problem anyway.

All misgivings about potential SQL injections and lack of dbContext, etc. aside, how can I return all my records from the database utilizing ExecuteReader()?

Thanks.

Edit to add:

My exception value in the catch block is {"Data is Null. This method or property cannot be called on Null values."}.

In the debugger output, I my exception Exception thrown: 'System.Data.SqlTypes.SqlNullValueException' in Microsoft.Data.SqlClient.dll.

Edit to comment on the solution.

Ann L. figured this out. I had null values coming from the database. I learned from her and PSGuy that I can check for null values by using DbNull. Thank you!

Note - an easy place to get tripped up is that your class has to allow for nulls or else VS won't allow you to check for nulls in the method in the controller.

Solution

BoGoodSki
  • 123
  • 1
  • 4
  • 12
  • 1
    I doubt that this is a problem caused by the executereader. If there is an exception then there is an Exception object. Perhaps you should clean your solution using the debug menu from Visual Studio or even clearing the hidden .vs folder the bin folder and the obj folder. Eventually you can use an ORM like Dapper to directly transform your records in the objects and the list container – Steve Jan 14 '23 at 23:56
  • 1
    Are you doing anything with async/await or tasks? – Ann L. Jan 14 '23 at 23:59
  • 1
    Do you get any text (for example, in the debugging window) when the exception happens? – Ann L. Jan 15 '23 at 00:00
  • 1
    And, just out of curiosity: are all these values you're accessing _always_ non-nulls? Because your code isn't making any provisions for null-checking in the data. Perhaps a record somewhere after record 200 has a `null` in it: while that wouldn't bother SSMS at all, that _would_ make your code crash. – Ann L. Jan 15 '23 at 00:03
  • No async being used. I edited the question include exception data. – BoGoodSki Jan 15 '23 at 00:04
  • 1
    Ah, okay. What I suspect is happening (based on that error!) is that you have a Null in your data! And you are attempting to access and convert it as if it were _not_ a Null! So, you are going to need to use a slightly more complicated syntax to access the values in the record (which I am going to look up because I don't remember it off the top of my head.) – Ann L. Jan 15 '23 at 00:05
  • 1
    It sounds like you need to be checking the value of a column for `DBNull` before trying to convert it. First go figure out which fields are nullable (declared as `NULL` versus `NOT NULL` in SQL Server). When "parsing" those fields, make sure to handle `DBNull` values. That aside, using ADO.NET directly is fine for learning, but in production code, you should use either Dapper or EFCore. They take care of all this nonsense for you. – PSGuy Jan 15 '23 at 00:06
  • @AnnL. Oh wow, that's probably it. Since it's a SQLNullValueException. So I need to make provision for records that have null in their fields. Hmm. – BoGoodSki Jan 15 '23 at 00:06
  • Thanks @PSGuy. I suspect this is the solution. Will confirm. I'm purposely not using ORM because I learned using EF and have never tried to build from "scratch". This is a personal project - for learning! – BoGoodSki Jan 15 '23 at 00:08
  • 2
    @BoGoodSki that makes sense. As I mentioned, that's good for learning. I'd recommend learning to make this asynchronous (which is fairly easy TBH). Be aware: you need to call `Open()` or `OpenAsync()` on your `SqlConnection` before executing commands (easy trap to miss). Once you're satisfied that you've learned to do the low-level programming directly against ADO.NET, I'd check out Dapper: it's still _close_ to low-level, but solves all the problems with using the lower-level API (and works against all providers). – PSGuy Jan 15 '23 at 00:21
  • Do you might know the answer to this one as well? https://stackoverflow.com/questions/75245865/copybatchrequestproperties-and-deleterequestcontainer-replacement-in-aspnetcore – CodeMonkey Jan 29 '23 at 09:21

1 Answers1

2

Here's one approach to the syntax you'll need to use (although there are lots of other approaches: see here for a bunch of alternatives!)

shoeAge = reader.IsDBNull(13) ? null : reader.GetInt64(13)

This assumes shoeAge is a nullable Int64. If it isn't, you'll get another error since you won't be able to assign null to it.

Ann L.
  • 13,760
  • 5
  • 35
  • 66