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
– Guru Stron, Commented May 9, 2023 at 13:09

Guru Stron · Accepted Answer · 2023-05-09 13:13:57Z

0

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

answered May 9, 2023 at 13:13

Guru Stron

151k11 gold badges186 silver badges233 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JHBonarius · Accepted Answer · 2023-05-09 13:06:43Z

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
}

Charlieface · Accepted Answer · 2023-05-09 13:13:40Z

0

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

answered May 9, 2023 at 13:13

Charlieface

78.8k8 gold badges35 silver badges77 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related