C# SQLDataReader accessing by column name

Question

I have a Visual Basic application where I have a connection to a MS SQL database. I have code which defines a SqlDataReader, opens the connection, and executes the ExecuteReader() command. I use the following code to retrieve the data from the reader

While myDataReader.Read()
    Session("menu_PEO") = myDataReader("menu_PEO")
    Session("menu_Transfer") = myDataReader("menu_Transfer")
    Session("menu_Loan") = myDataReader("menu_loan")
End While

menu_PEO, menu_Transfer, and menu_loan are 3 of the column headings in the data that the SQL returns.

I am now tasked with converting the code to c#. I have the following code in c# which works:

while (dataReader.Read())
{
    dbMenuPEO = dataReader.GetString(1);
    dbMenuTransfer = dataReader.GetString(2);
    dbMenuLoan = dataReader.GetString(3);
}

Since my SQL is a SELECT *, I can not guarantee the order of the data being returned so I dont want to rely on specifying the instance of the GetString.

Is there a way in c# to specify the column name that I would like to retrieve similar to the way it works in Visual Basic?

Thanks!

Does this answer your question? [DataReader.GetString() via columnname](https://stackoverflow.com/questions/3228972/datareader-getstring-via-columnname) — Matt U, Dec 24 '21 at 18:01

score 2 · Answer 1 · answered Dec 24 '21 at 18:05

There are two ways to do this. The easy way is to use the named indexer:

dbMenuPEO = (string)dataReader["menu_PEO"];

Which is essentially the same as:

dbMenuPEO = (string)dataReader.GetValue(dataReader.GetOrdinal("menu_PEO"));

You can move the ordinal lookup outside of the loop, which is a bit more efficient. You can also use the the strongly-typed accessor which avoids the cast. This can be important when reading value types (int, dateTime, etc) as it avoid needing to box the value, which would happen with GetValue.

var menuPeoIdx = dataReader.GetOrdinal("menu_PEO");
while(dataReader.Read())
{
   dbMenuPEO = dataReader.GetString(menuPeoIdx);
}

Generally, you should never use select * in a production system. Always explicitly list the columns you want. The reason is that it allows the underlying tables to change without having to fix the consuming code, or over-selecting columns that you don't need. Select * is a great tool for "exploring" a data set, but should only be used as a dev tool.

Caius Jard · Accepted Answer · 2021-12-24T19:10:10.563

I have code which defines a SqlDataReader, opens the connection, and executes the ExecuteReader()

And isn't it the most incredibly tedious code to have to write? Many people have thought this over the time and many things have been invented to relieve you of the tedium of it. MarkPflug's answer directly addresses your question, but just in case you aren't aware that there are significant productivity boosts available I'd like to introduce you to one of these technologies

Is there a way in c# to specify the column name that I would like to retrieve similar to the way it works in Visual Basic?

Here's a way to do it, in that when you do this you don't have to type it. It avoids typing the same thing again that you've already typed (twice - once for the variable name, once in the SQL)

Use the nuget package manager built into visual studio, to install Dapper

Then lets say you have a class that holds your data:

//C#
record DbMenu(string DbMenuPEO, string DbMenuTransfer, string DbMenuLoan);

'or VB, if you like that sort of thing
Class DbMenu
  Public Property DbMenuPEO as String
  Public Property DbMenuTransfer As String
  Public Property DbMenuLoan As String
End Class

You can get Dapper to make your query, add any parameters, open your connection, download your data, fill up a list full of your classes, close the connection and return it.. all in one line of code:

//C#
using var conn = ... //code here that gets a connection; doesn't need to be open
var myListOfDbMenus = conn.Query<DbMenu>("SELECT * FROM ... ");

'VB
Using conn = ...
  Dim myListOfDbMenus = conn.Query(Of DbMenu)("SELECT * FROM ... ");
End Using

The short short version is: your c# class properties should be named the same as your columns. If they aren't, it's easiest to use AS xyz in the SQL to equalize the names. If you want to write a parameterized query, you provide @parameterNames that are the same as the property names of an anonymous object you pass at the same time as your query:

var q = conn.Query<Type>("SELECT ... WHERE col = @val1", new {val1 = "hello" } );

If you like writing SQL and having that low level control/don't want to use an ORM like EF, then Dapper lets you carry on doing the SQL directly as you're doing, but takes away all the repetitive surrounding boilerplate

I have no problem using Dapper but my original concern was the potential randomness in the order of the values returned when using a SELECT *. And, I am early enough in my development where I can change the c# class properties to be the same as the table column headings. If Dapper does in fact load each of the columns into the appropriate class fields, then Dapper would certainly be easier than manually typing in each instance. — Jonathan Small, Dec 24 '21 at 19:15
It does indeed; you can have a class like `record X(string A, string B, string C)` and a dapper call like `Query<..>("SELECT C, B, A FROM X WHERE col1 = @val1 AND col2 = @val2", new { col2 = "x", col1 = "y" })` and it all works; the order is totally irrelevant. There's a boatload more it does, update/insert/delete, querying for single objects, even splitting up joined result sets into tree of objects.. etc.. Recommend a good tutorial site for some more in-depth advice, but it makes a lot of sense to me — Caius Jard, Dec 24 '21 at 19:18
*change the c# class properties to be the same as the table column headings* - it's easy to use `AS` in the SQL to match them up, so you can keep C# conventions if the DB doesn't follow them (like if your db_col_is_like_this and your C# IsLikeThis) — Caius Jard, Dec 24 '21 at 19:24
Like magic! 1 line of code reads in 20 columns from the database and loads the data into my object! Thanks for the advice! — Jonathan Small, Dec 24 '21 at 19:51

score 0 · Answer 3 · answered Dec 24 '21 at 18:56

0

The SQLDataReader class exposes a set of instance methods that allow to retrieve the value of a field of a specific type, such as GetInt32() and GetString(), which can be used in combination with the GetOrdinal() method

Example

dataReader.GetString(dataReader.GetOrdinal("YOUR FIELD NAME HERE"))

answered Dec 24 '21 at 18:56

Fabio Scagliola

338
2
11

MarkPflug's answer already covers this. Please don't post duplicate advice – Caius Jard Dec 24 '21 at 19:02
I would never do that. It would be a waste of everyone’s time. As a matter of fact, when I started typing my answer, there was only yours – which suggests a good approach indeed, but does not really answer the question. By the time I submitted mine, Mark’s answer was there too. So, we actually wrote them as the same time – Fabio Scagliola Dec 25 '21 at 01:51
In those cases, you make a call as to whether the other answer is more complete/informative than yours and either delete your or extend it to out-compete. If there'sreally nothing else to add (rare) that can make your answer the obviously better learning experience, and the two answers are in essence the same, then you point to the "posted X seconds ago" and see who got there first, and if you weren't the fastest gun in the west you hit delete.. All in, the aim is to keep SO clean of duplicate noise – Caius Jard Dec 25 '21 at 07:57

C# SQLDataReader accessing by column name

3 Answers3