How to Get Data by SQLdatareader.Getvalue by Column Name

SqlDataReader Get Value By Column Name (Not Ordinal Number)

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

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

How to get data by SqlDataReader.GetValue by column name

Log.WriteLine("Value of CompanyName column:" + thisReader["CompanyName"]); 

C# SQLDataReader accessing by column name

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

C# - SQLDataReader by Index vs. SQLDataReader.GetOrdinal(ColumnName)

reader.GetOrdinal(string) will get the column ordinal, given the name of the column

We can see GetOrdinal sourcecode from SqlDataReader it will return a index from _fieldNameLookup.GetOrdinal (_fieldNameLookup field is a FieldNameLookup class)

_fieldNames is a hashtable stores the index, match via case-sensitive

override public int GetOrdinal(string name) {
    SqlStatistics statistics = null;
    try {
        statistics = SqlStatistics.StartTimer(Statistics);
        if (null == _fieldNameLookup) {
            CheckMetaDataIsReady();
            _fieldNameLookup = new FieldNameLookup(this, _defaultLCID);
        }
        return _fieldNameLookup.GetOrdinal(name); // MDAC 71470
    }
    finally {
        SqlStatistics.StopTimer(statistics);
    }
}

we can see the source code GetOrdinal method from FieldNameLookup class.

public int GetOrdinal(string fieldName) { // V1.2.3300
    if (null == fieldName) {
        throw ADP.ArgumentNull("fieldName");
    }
    int index = IndexOf(fieldName);
    if (-1 == index) {
        throw ADP.IndexOutOfRange(fieldName);
    }
    return index;
}

public int IndexOf(string fieldName) { // V1.2.3300
    if (null == _fieldNameLookup) {
        GenerateLookup();
    }
    int index;
    object value = _fieldNameLookup[fieldName];
    if (null != value) {
        // via case sensitive search, first match with lowest ordinal matches
        index = (int) value;
    }
    else {
        // via case insensitive search, first match with lowest ordinal matches
        index = LinearIndexOf(fieldName, CompareOptions.IgnoreCase);
        if (-1 == index) {
            // do the slow search now (kana, width insensitive comparison)
            index = LinearIndexOf(fieldName, ADP.compareOptions);
        }
    }
    return index;
}

Is one quicker than the other?

If you already know columns exist index number reader.GetValue(0) will faster then reader.GetValue(reader.GetOrdinal("COLUMN1")) becuase it didn't cause resource to get the colunm index from reader.GetOrdinal method.

Is one considered better standard?

There isn't comparison standard because of reader.GetValue(0) and reader.GetValue(reader.GetOrdinal("COLUMN1")) are doing the same thing, as before answer.

reader.GetValue(reader.GetOrdinal("COLUMN1")) be better reading then reader.GetValue(0), because columns name will be better to know instead index.

Can you get the column names from a SqlDataReader?

var reader = cmd.ExecuteReader();

var columns = new List<string>();

for(int i=0;i<reader.FieldCount;i++)
{
   columns.Add(reader.GetName(i));
}

or

var columns = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList();

SqlDataReader.GetValue in C#

The number is zero-based. So 0 will get you the first column, and 1 will get you the second.

var query = "SELECT title, locked FROM some_table;"

using (var cmd = new SqlCommand(someConnection, query))
{
    ...  // open the connection, run the query, assign the results to yourReader

    while (yourReader.Read())
    {
        var title = (string)yourReader.GetValue(0);
        var locked = (int)yourReader.GetValue(1);

        // do something with title and locked
    }
}

Get the column names and rows from a DbDataReader to a List

cmd.CommandText = uspName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@inparam_Fromdate", "2019-05-05"));
cmd.Parameters.Add(new SqlParameter("@inparam_Todate", "2019-05-22"));

cmd.Connection.Open();

using (var reader = cmd.ExecuteReader())
{
    var dataList = new List<string[]>();

    var tempCol = new string[reader.FieldCount];
    for (var i = 0; i < reader.FieldCount; i++)
    {
        tempCol[i] = reader.GetName(i);

    }
    dataList.Add(tempCol);

    while (reader.Read())
    {
        var tempRow = new string[reader.FieldCount];
        for (var i = 0; i < reader.FieldCount; i++)
        {
            tempRow[i] = Convert.ToString(reader.GetValue(i));
        }

        dataList.Add(tempRow);
    }

}

Check for column name in a SqlDataReader object

public static class DataRecordExtensions
{
    public static bool HasColumn(this IDataRecord dr, string columnName)
    {
        for (int i=0; i < dr.FieldCount; i++)
        {
            if (dr.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
                return true;
        }
        return false;
    }
}

Using Exceptions for control logic like in some other answers is considered bad practice and has performance costs. It also sends false positives to the profiler of # exceptions thrown and god help anyone setting their debugger to break on exceptions thrown.

GetSchemaTable() is also another suggestion in many answers. This would not be a preffered way of checking for a field's existance as it is not implemented in all versions (it's abstract and throws NotSupportedException in some versions of dotnetcore). GetSchemaTable is also overkill performance wise as it's a pretty heavy duty function if you check out the source.

Looping through the fields can have a small performance hit if you use it a lot and you may want to consider caching the results.

---

Related Topics