SqlDataReader Get Value By Column Name (Not Ordinal Number)

Question

Using the methods of the SqlDataReader, I can get the value of a column by passing in it's ordinal, such as the value of the first column if I pass in read.GetValue(0), or the second column if I pass in read.GetValue(1).

In looking at the methods, I don't see an option to get the value of a column by passing in the name of a column, such as ColumnID. In my mythical example, I would want to pass in read.GetValueofColumn("ColumnID") and read the value in the column (note that the method GetValueofColumn doesn't exist so far as I can tell from the method list).

Am I missing the method to do this, or a way to do this?

I've tried that and unfortunately `GetValue` only accepts an `int`. — user9927, Feb 04 '15 at 16:03
why don't you just get the value you are looking for by the `dataReader["ColumnName"]` it's not that trivial.. here is a good site to keep in your arsenal [C# Get DataReader values by Column Name](http://www.java2s.com/Code/CSharp/Database-ADO.net/ReferencedatainSqlDataReaderbycolumnname.htm) — MethodMan, Feb 04 '15 at 16:05

score 82 · Accepted Answer · answered Feb 04 '15 at 16:02

82

You can get the ordinal of the column by using the GetOrdinal method, so your call could be:

read.GetValue(read.GetOrdinal("ColumnID"));

answered Feb 04 '15 at 16:02

MatthewG

8,583
2
25
27

1

Works beautifully, thanks; will accept when I can't (*can accept an answer in 8 minutes*?) – user9927 Feb 04 '15 at 16:07
6

For performance reasons, Microsoft now recommends you call your GetOrdinal outside your read loop, and only call GetValue(int) inside the loop – Guy Schalnat Jul 05 '17 at 18:24
2

great answer, however, using `read.GetFieldValue(read.GetOrdinal("ColumnID"));` will save you the casting – Hesham Yassin Mar 13 '19 at 12:10
Late to the game on this one. Just want to add that you should be careful if your select list columns use an AS clause to name columns with the same name. In this case the outer GetValue() call could end up getting you the value of a potentially different column. – vitaminjeff Mar 27 '20 at 23:02

score 37 · Answer 2 · edited Dec 02 '15 at 10:31

37

Datareader has numeric (position based) method, and a textual (field name based) one. So, with field name, you can get the value like

object value = reader["some field name"];

(assuming that reader is a datareader)

edited Dec 02 '15 at 10:31

Arghya C

9,805
2
47
66

answered Feb 04 '15 at 16:08

Emanuele Greco

12,551
7
51
70

2

True, but the other helper functions to the reader only take an int, so if you want to use them, you have to call GetOrdinal (preferably outside the read loop) to get the index, and then you can call any of the functions (including reader[index]) inside the read loop . – Guy Schalnat Jul 05 '17 at 18:26
4

The question is 'an option to get the value of a column by passing in the name of a column', and I suppose my answer is the most simple. – Emanuele Greco Jul 06 '17 at 12:26

score 15 · Answer 3 · answered Feb 12 '17 at 00:37

Late answer, but... This has always worked for me, and I think it is closer to what OP is trying to achieve:

using (SqlCommand cmd = new SqlCommand(cmdString, cn))
using (SqlDataReader rs = cmd.ExecuteReader()) {

    if (rs.HasRows) {

        while (rs.Read()) {

            Meeting_DiscussionItems_MX di = new Meeting_DiscussionItems_MX();

            di._Discussion_Item_MX_ID   = (int) rs["Discussion_Item_MX_ID"];
            di._Meeting_ID              = (int) rs["Meeting_ID"];
            di._Discussion_Item_Name    = (string) rs["Discussion_Item_Name"];
            di._Display_Order           = (string) rs["Display_Order"];
            di._Status                  = (string) rs["Status"];
            di._Discussion_Items        = (string) rs["Discussion_Items"];
            di._ETOPS_Items             = (string) rs["ETOPS_Items"];
            di._Followup                = (string) rs["Followup"];
            di._Pinned                  = (string) rs["Pinned"];
            di._Active                  = (string) rs["Active"];

            _Meeting_DiscussionItems_MX.Add(di);
        }

    }
}

Not for me, so far. It does require checking for nulls, if nulls are possible in your columns, with DBNull.Value. — KWallace, Oct 22 '21 at 23:53

score 7 · Answer 4 · answered Mar 09 '19 at 20:52

For convenience you can add some helpers like this:

public static string GetString(this SqlDataReader reader, string name) {
    return GetFieldValue<String>(reader, name, (string)null);
}

public static T GetFieldValue<T>(this SqlDataReader reader, string fieldName, T defaultvalue = default(T)) {
    try {
        var value = reader[fieldName];
        if (value == DBNull.Value || value == null)
            return defaultvalue;
        return (T)value;
    } catch (Exception e) {
        //SimpleLog.Error("Error reading databasefield " + fieldName + "| ", e);
    }
    return defaultvalue;
}

As you can see it is the reader that allows to use fieldnames but it returns an object that needs to be casted to the right datatype. The extension takes care of both and adds a defaultvalue for when the field is null.

score 4 · Answer 5 · edited Apr 25 '18 at 13:47

4

you can use:

MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
    Console.WriteLine(rdr["id"] + " -- " + rdr["time"]);
}
rdr.Close();

where id and time are column names

edited Apr 25 '18 at 13:47

J Pollack

2,788
3
29
43

answered Apr 25 '18 at 12:45

Grzegorz J

403
3
6

score 2 · Answer 6 · answered Oct 16 '22 at 17:34

2

you must use like below , replacing "(string)" by the type you must use for:

(string) reader["name"];

answered Oct 16 '22 at 17:34

Devmyselz

346
3
13

SqlDataReader Get Value By Column Name (Not Ordinal Number)

6 Answers6

Linked

Related