Datareader.Getstring() via Columnname

DataReader.GetString() via columnname

You're looking for the GetOrdinal method:

this._MyField1 = reader.GetString(dr.GetOrdinal("field1"));
this._Myfield2 = reader.GetInt16(dr.GetOrdinal("field2"));

I generally cache the ordinals in an anonymous type for performance and readability:

// ...
using (IDataReader dr = cmd.ExecuteReader())
{
    var ordinals = new {
                           Foo = dr.GetOrdinal("Foo"),
                           Bar = dr.GetOrdinal("Bar")
                       };

    while (dr.Read())
    {
        DoSomething(dr.GetString(ordinals.Foo), dr.GetInt16(ordinals.Bar));
    }
}
// ...

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"));

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

How to get data by SqlDataReader.GetValue by column name

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

SqlDataReader.GetString and sqlnullvalueexception

If you don't want to repeat this a lot, just create a helper function, like this:

public static class DataReaderExtensions
{
    public static string GetStringOrNull(this IDataReader reader, int ordinal)
    {
        return reader.IsDBNull(ordinal) ? null : reader.GetString(ordinal);
    }

    public static string GetStringOrNull(this IDataReader reader, string columnName)
    {
        return reader.GetStringOrNull(reader.GetOrdinal(columnName));
    }
}

Which you can call like this:

value = reader.GetStringOrNull(n);

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.

When to use the DataReader Get_ vs GetSql_ Methods in C#?

The reader["ColumnName"] approach returns an object and generally strongly typed alternatives would be preferred.

The straight Get commands, e.g. reader.GetString() will error if the value of a particular column is NULL and therefore require separate checking for DBNull. See the "Remarks" section of the API docs at: https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader.getstring?view=dotnet-plat-ext-6.0#remarks.

The equivalent GetSql commands, e.g. reader.GetSqlString() return "Sql" types which can cope with NULL values, and in the case of a string when cast to the c# System.String simply result in a null value if the data was NULL.

I can't find any particular Microsoft documentation outside of the terse API docs, but here is a reference source code of the SqlDataReader class (albeit .NET 4.7) to help dig into the implementation specifics: https://github.com/microsoft/referencesource/blob/master/System.Data/System/Data/SqlClient/SqlDataReader.cs

Error: The best overloaded method match for and Argument 1: cannot convert from 'string' to 'int'

You cant use GetSrting() with a string parameter.
GetString() gets the column index as a parameter.
In your example SECTION_NAME has 0 column index.
So your code must be sqlDR.GetString(0);

if your query was select SOMETHING_ELSE,SECTION_NAME from SECTION"
your code must be sqlDR.GetString(1); to get the value of SECTION_NAME

Datareader.Getstring() via Columnname