SqlDataReader HasRows=True but no data

Question

I am connecting to an SQL Server 2012 database to query for a single value based on an ID. (It may be worth mentioning that this database is on a server on another continent from my development machine, and so latencies are quite high. Somewhere around 100ms).

The query appears to execute successfully. The HasRows property of the SqlDataReader object is set to true, so I try to use the value to assign a variable. When I run the program normally, I encounter an exception with message 'Given key was not present in the dictionary'. If I stop the execution and inspect the SqlDataReader object, and enumerate the results. Firstly I am told 'enumeration yielded no results' and then when I continue execution I get a different exception with the message 'invalid attempt to read when no data is present'

Here is the code in question:

SqlConnection sql_conn = new SqlConnection(ConnectionString);
SqlCommand sql_cmd = new SqlCommand(String.Format("select ItemType from ItemTable where ItemID='{0}'", item_id), sql_conn);

Console.WriteLine(sql_cmd.CommandText);

sql_conn.Open();

SqlDataReader rdr = sql_cmd.ExecuteReader();

rdr.Read();

if (rdr.HasRows) //True
{
    item_type= TypesMap[rdr["ItemType"].ToString()]; //Either 'given key not found in dictionary' or 'invalid attempt to read when no data is present'
}

I have executed the SQL statement in SQL Server Management Studio and it is successful. I have tried hardcoding an ItemID into the statement in the C# code, and the same errors exist.

What more can I do to debug this? Everything appears to be okay, until I try to access the results of the query.

Answer 1

You have to debug: it seems that the TypesMap doesn't have the key read from the database:

// Wrap IDisposable into using
using (SqlConnection sql_conn = new SqlConnection(ConnectionString)) {
  // Make SQL readable
  // Make SQL parametrized (and not formatted) when it's possible
  String sql = 
    @"select ItemType 
        from ItemTable 
       where ItemID = @prm_ItemId"; 

  // Wrap IDisposable into using
  using (SqlCommand sql_cmd = new SqlCommand(sql, sql_conn)) {
    // I don't know ItemID's type that's why I've put AddWithValue 
    sql_cmd.Parameters.AddWithValue("@prm_ItemId", item_id);

    // Wrap IDisposable into using
    using (SqlDataReader rdr = sql_cmd.ExecuteReader()) {
      // rdr.HasRows is redundant - rdr.Read() returns true if record has been read
      if (rdr.Read()) {
        String key = Convert.ToString(rdr.GetValue(0));
        // Put break point here: what is the "key" value?
        item_type = TypesMap[key];
      }
    }
  } 
}

Edit: as Luke has mentioned in the comment, the cause of the error was that key comparison is expected to be case insensitive, so the amendment is to explain .Net how to compare keys:

var TypesMap = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);

...
TypesMap.Add("aBc", "xyz"); 
String test = TypesMap["Abc"]; // return "xyz"; notice "aBc" and "Abc"

Answer 2

As Dmitry pointed out the 'given key not found...' is not a DB thing but a dictionary thing. Below I've added a simple check to ensure the key is in the dictionary - if it is then we can assigned to item_type.

Also, if HasRows() isn't doing what you expect, try the following. It's the standard way I read from DB:

using (SqlDataReader results = sql_cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
    while (results.Read())
    {
        string Key = rdr["ItemType"].ToString();
        if (TypesMap.ContainsKey(Key))
            item_type = TypesMap[Key];       
    }
}