Is there a right way to retrieve a String field in SQL

Question

I have seen different ways to retrieve a string field from SQL. Is there a "right" way, and what are the differences

  SqlDataReader rdr;
  1.  String field = (String) rdr["field"];
  2.  String field = rdr["field"].ToString();
  3.  String field = rdr["field"] As String;

Thanks!

Answer 1

You could also use:

int ordinal=rdr.GetOrdinal("stringField");
if (rdr.IsDBNull(ordinal))
{
    return string.Empty; //Or null or however you want to handle it 
}
else
{   
   rdr.GetString(ordinal);
}

Which if you look at the definition for SQlDataReader["field"] looks like:

public override object this[string name]
{
    get
    {
        return this.GetValue(this.GetOrdinal(name));
    }
}

Essentially this is doing the same thing, only it's type safe. What I like to do is Create my own IDataReader which wraps SqlDataReader. CSLA uses a simillar mechanism that they call SafeDataReader since it provides overloads for all the various data types which implements this pattern.

If you know the field won't be null you could leave out the isDbNull checks. Due to the verbosity I'd recommend putting this in some type of wrapper or helper class and make functions out of them.

Answer 2

1. May cause an exception if the type is not string
2. Is definitely wrong because that could raise an exception if the value is null.
3. Will assign null if it is null or if it is of type other than string

So, to be on the safe side - I would choose 3.