Check for Column Name in a SQLdatareader Object

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.

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

Checking to see if a column exists in a data reader

I ended up finding a solution using the reader.GetName(int) method. I created the below method to encompass the logic.

public bool ColumnExists(IDataReader reader, string columnName)
{
    for (int i = 0; i < reader.FieldCount; i++)
    {
         if (reader.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
        {
            return true;
        }
    }

    return false;
}

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

Check a column if doesn't exists in SqlDataReader

use this method

  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;
            }
        }

try this

public static bool HasColumn(DbDataReader Reader, string ColumnName) { 
    foreach (DataRow row in Reader.GetSchemaTable().Rows) { 
        if (row["ColumnName"].ToString() == ColumnName) 
            return true; 
    } //Still here? Column not found. 
    return false; 
}

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

Efficient way to check if Columns exist in the DataReader

SqlDataReader.GetSchemaTable Method will give the DataTable of the executed query, from there you can get all the columns

Returns a DataTable that describes the column metadata.

MSDN http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getschematable.aspx

Check if a column exists while reading a database in C#

You need to check if the column MODIFICATIONS exists in the datareader, as you don't know it on forehand.

In the documentation of Microsoft I found a method called GetSchemaTable.

It will give you a datatable that describes the column metadata. So you could check that table to see if there is a column with the name MODIFICATIONS.

I would put that check in a different method so it doesn't clutter the code in your while loop that much.

OR

You could check this question on StackOverflow Check for column name in a SqlDataReader object which has a shorter solution to your problem.

I think that's even a nicer and easier solution, but I found it (with a simple google search) after I had almost completed my answer above. So that's why I give you both solutions (and also to help you a little with the MS documentation, which I pointed to in my comment)

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.

---

Related Topics