0

I am using c# and SQL Server to get data from the database and populate a few labels on a page on pageload. The information comes from a SQL view which returns a whole bunch of information in a single row. eg:

SELECT 'car' AS product, 12 As daysUntilEOM, '2010-01-01' AS LastHoliday, 23.7 AS Length

There will always be only 1 row returned

The rest of my project is using LINQ and I originally started going down that path, but what is the point of creating an object that I'm only going to use once off for this, assigning all the values into the object as key-value pairs, and then reassigning them out to the label texts? Seems a lot of pointless coding.

Using a datareader also seems to have the same problem.

The simplest solution (ie least code) I can see is to use a dataAdapter to fill a dataset and pass the dataset back from the DAL to the page and then do the assigning straight to the labels. But for a single row of data this seems to be like using a truck to carry a single tomato.

Is there something like ExecuteScalar that returns a whole row instead of just the first column? Or should the ease of coding the DataAdapter just be enough and I should stop worrying about overhead?

Thanks!

SLaks
  • 868,454
  • 176
  • 1,908
  • 1,964
JumpingJezza
  • 5,498
  • 11
  • 67
  • 106

2 Answers2

5

You should call ExecuteReader and pass the CommandBehavior.SingleRow flag.

To avoid coupling, you can convert the DataReader into a dictionary, like this:

return Enumerable.Range(0, reader.FieldCount)
                 .ToDictionary(reader.GetName, reader.GetValue);
SLaks
  • 868,454
  • 176
  • 1,908
  • 1,964
  • But then how do I pass the data from the data access layer to the label texts? Create an object or an array - load it in - and then load it out into the label texts? – JumpingJezza Nov 26 '10 at 03:20
  • @Jump: Can you populate it directly from the reader? (perhaps as `IDataRecord`) – SLaks Nov 26 '10 at 03:32
  • @Slaks: Would definitely prefer to keep DAL & front end separate. – JumpingJezza Nov 26 '10 at 03:33
  • @Jump: Can you pass an `IDataRecord` between them? (instead of an array) – SLaks Nov 26 '10 at 03:35
  • @Slaks: I had a go without luck, but how do I get the datareader row into the IDataRecord? – JumpingJezza Nov 26 '10 at 03:38
  • @Jump: `DataReader` implements `IDataRecord`. Just call `Read()`, then return the `DataReader` itself. – SLaks Nov 26 '10 at 03:39
  • @Slaks: It assigns fine, but when I go to get the data out like `label1.Text = dr["car"].ToString();` it throws an error: Invalid attempt to call MetaData when reader is closed. – JumpingJezza Nov 26 '10 at 03:47
  • @Jump: You can't close the reader or its connection until after getting the data – SLaks Nov 26 '10 at 03:50
  • @Slaks: which kinda links the DAL and front end together? There is no Datarow equivalent for datareaders is there? - I guess that is the point or it wouldn't be so quick. I either have to move the datareader into my frontend code, or create a new object and populate that, or just use a dataAdapter. – JumpingJezza Nov 26 '10 at 04:00
  • @Jump: DataReader is a fast, forward-only view of the data. It doesn't hold _anything_ in memory. You might want to convert it to a dictionary. – SLaks Nov 26 '10 at 04:01
  • @Slaks: Ah now THATS what I was after!! nice example [here](http://stackoverflow.com/questions/912948/sqldatareader-how-to-convert-the-current-row-to-a-dictionary/912968#912968). Update your answer with this second half of the question and I'll mark you as correct :) – JumpingJezza Nov 26 '10 at 04:38
0

ExecuteScalar returns a scalar value, not the row. DataAdapter with a command object (possibly also feeding a parameter to a stored proc rather than executing the sql statement) will be fine.

Tim
  • 5,371
  • 3
  • 32
  • 41