Geek Answers Handbook

The correct way to get a string field in SQL

I have seen various ways to extract 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!

+4
source share
5 answers

You can 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); }

What if you look at the definition for SQlDataReader ["field"], it looks like this:

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

In fact, this is the same, only a safe type. I like to create my own IDataReader that wraps SqlDataReader. CSLA uses the simillar mechanism, which they call SafeDataReader, because it provides overloads for all data types that implement this template.

If you know that the field will not be empty, you can leave isDbNull unchecked. Due to the verbosity, I would recommend putting this in some type of shell or helper class and making functions out of them.

+6
source
  • May throw an exception if the type is not a string
  • Definitely wrong, because it can throw an exception if the value is null.
  • Assign null if it is null, or if it is of a type other than a string

So, to be safe - I would choose 3.

+4
source

Keep in mind that this question interacts with your data definitions. If the "field" is defined as "Not Null", you do not need to worry about null data and select # 1 for readability. Similarly, if the field is NULL, but the IsNull function is used when executing the request:

 Select IsNull(Field1, '') as Field1 From DBTable Where...

then, again, you should select # 1, because you still don't need to worry about null. Of course, this assumes that you WANT a null value that will be masked by an empty string. If you want to test null because this is an error condition, then you will have logic like this:

 if (nwReader.IsDBNull(nwReader.GetOrdinal("Field1"))) *throw exception or otherwise handle null condition string aStr = (string)nwReader["field"];

This last case, however, is not a good practice. If null is an invalid value - an error condition - then you should exclude it from your DDL.

In the end, however, I always choose option # 1, because I think it leads to better readability, and it makes me make the explicit call explicit.

+2
source

If I expect a string, I will do # 1. If for some reason the field is not a string or changes the type, you will at least learn about it through the exception mechanism. (Just don't wrap it inside try / empty catch.)

+1
source

I like??? operator for checking zeros (as discussed in other posts)

String field = rdr ["field"] As String ?? string.Empty

That should work. If not, then his lateness is good: P, if you really do not want the result to be zero. If so, I like 3.

0
source

More articles:


All Articles