CODEWITHSUNDEEP
c#sqldatareaderdatareader
alinz
alinz

Reputation: 81

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

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?

Upvotes: 1

Views: 633

Answers (3)

Guru Stron
Guru Stron

Reputation: 143098

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

Upvotes: 0

Charlieface
Charlieface

Reputation: 72194

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

Upvotes: 0

Murad
Murad

Reputation: 62

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
}

Upvotes: 0

Related Questions