Loading Entity /w No Data

pinch · January 20th, 2008, 12:22 AM

It seems that in most of the examples in the book, the queries issued through the DAL return at least one or more records. In my case, I have a method which queries the database for 'Players' based on first name and last name. Sometimes, no players are returned, and when this happens I'm getting an exception when trying to convert the database data to en entity object as outlined in the book.

Specifically, I'm getting an exception in the GetPlayerFromReader method as I try to build the entity object:

Code:

    public override PlayerDetails GetPlayerByFirstNameLastName(string firstName, string lastName)
    {
      using (SqlConnection cn = new SqlConnection(this.ConnectionString))
      {
        SqlCommand cmd = new SqlCommand("Sheets_GetPlayerByFirstNameLastName", cn);
        cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = firstName;
        cmd.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = lastName;
        cmd.CommandType = CommandType.StoredProcedure;
        cn.Open();
        return GetPlayerFromReader(ExecuteReader(cmd));
      }
    }


    protected virtual PlayerDetails GetPlayerFromReader(IDataReader reader)
    {
      PlayerDetails player = new PlayerDetails(
          (int)reader["PlayerID"],
          reader["PositionCode"].ToString(),
          reader["FirstName"].ToString(),
          reader["LastName"].ToString(),
          reader["MiddleName"].ToString(),
          reader["TeamCode"].ToString(),
          (short)reader["Number"],
          (DateTime)reader["FirstYear"]);
      return player;
    }

The exception I get says: "Invalid attempt to read when no data is present." because no records are returned.

Should I be checking the 'depth' property of the Reader object here to see if there is any data before creating the Player entity object?

Thanks

Lee Dumond · January 20th, 2008, 02:27 AM

As you've already discovered, the GetPlayerFromReader method will throw an exception is there is no data.

What you should do is to use the Read() method on the DataReader BEFORE passing it to the GetPlayerFromReader method. If there is no data, the Read() method will return false. In this case, you can just return null from your GetPlayerByFirstName method, indicating there is no such player with the given firstName/lastName combo.

public override PlayerDetails GetPlayerByFirstNameLastName(string firstName, string lastName)
{
  using (SqlConnection cn = new SqlConnection(this.ConnectionString))
  {
    SqlCommand cmd = new SqlCommand("Sheets_GetPlayerByFirstNameLastName", cn);
    cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = firstName;
    cmd.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = lastName;
    cmd.CommandType = CommandType.StoredProcedure;
    cn.Open();
    IDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
    if (reader.Read())
        return GetPlayerFromReader(reader);
    else
        return null;
  }
}