C# SqlDataReader Get Value By Column Name (Case Insensitive)

Question

Using the methods of the SqlDataReader, I can get the value of a column by passing in its name.

while (dr.Read())
{  
    size = dr["size"].ToString();
    name = dr["name"].ToString();
}

In my case, the SQL script is created dynamically and column names can be typed in lower or upper cases. When I try to get value like

size = dr["size"].ToString();

it returns size, but

size = dr["Size"].ToString();

throws an exception. How to get column value by column name case insensitive, is it possible without changing SQL script?

What are you using `Microsoft.Data.SqlClient` or `System.Data.SqlClient`? — Guru Stron, May 09 '23 at 13:09
Which DBMS do you use? / Look at [1](https://learnsql.com/blog/sql-case-sensitive/), [2](https://stackoverflow.com/q/153944/5045688) — Alexander Petrov, May 09 '23 at 13:11

score 0 · Answer 1 · edited May 09 '23 at 13:06

By default, the column names are case-sensitive when accessing them using the SqlDataReader indexer (dr["columnName"]). However, you can make the column name comparison case-insensitive by using a StringComparer with the IgnoreCase option. Here's an example of how you can modify your code to achieve a case-insensitive column name lookup:

while (dr.Read())
{  
    size = GetValueIgnoreCase(dr, "size");
    name = GetValueIgnoreCase(dr, "name");
}

// Helper method to retrieve column value case-insensitively
private static string GetValueIgnoreCase(SqlDataReader reader, string columnName)
{
    for (int i = 0; i < reader.FieldCount; i++)
    {
        if (string.Equals(reader.GetName(i), columnName, StringComparison.OrdinalIgnoreCase))
        {
            return reader.GetValue(i).ToString();
        }
    }

    return null; // Column not found
}

score 0 · Answer 2 · answered May 09 '23 at 13:13

You can get the column names into a dictionary first, then look up by column ordinal.

Note that you should ideally cast the values to their real types, rather than using ToString.

var columns = dr.GetColumnSchema().ToDictionary(
  d => d.ColumnName,
  d => d.ColumnOrdinal,
  StringComparer.OrdinalIgnoreCase);
while (dr.Read())
{  
    size = (int)dr[columns["size"]];
    name = (string)dr[columns["name"]];
}

score 0 · Accepted Answer · answered May 09 '23 at 13:13

You can try creating mapping from case-insensitive column name to column ordinal. Something like (not tested):

var mapper = reader.GetColumnSchema()
    .ToDictionary(c => c.ColumnName, c => c.ColumnOrdinal.Value, StringComparer.OrdinalIgnoreCase);
    
while (dr.Read())
{   
    size = dr[mapper["size"]].ToString();
    name = dr[mapper["name"]].ToString();
}

C# SqlDataReader Get Value By Column Name (Case Insensitive)

3 Answers3