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?

Answer 1

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