Cast or convert when retrieving data from a database?

Question

When accessing an object in a DataTable retrieved from a database, are there any reasons not to cast the object into your desired type, or are there reasons to use convert? I know the rule is cast when we know what data type we're working with, and convert when attempting to change the data type to something it isn't. Presuming we know what data type is stored in a column, cast seems appropriate, but are there any DB type issues that mean we can't rely on this?

Answer 1

I would always cast, for the reasons you state. The gotchas I'm aware of that you need to handle are:

1. You obviously need to be able to handle DBNulls (e.g. by testing with Convert.IsDBNull)

2. In the case of ExecuteScalar I believe you need to check for null as well as DBNull.

3. SQL Servers @@IDENTITY and SCOPE_IDENTITY functions return numeric (decimal) even for columns that are declared as INT. In this case you can cast twice "(int)(decimal)value" or handle it in the T-SQL code, e.g.: .

   INSERT INTO MyTable ... SELECT AutoIdColumn FROM MyTable WHERE AutoIdColumn = SCOPE_IDENTITY()

or

INSERT INTO MyTable ...
SELECT CAST(SCOPE_IDENTITY() AS INT)

Answer 2

Both CAST and CONVERT are used to explicitly to convert an expression of one data type to another. However, with CONVERT you can specify the format style as well.

Syntax for CAST:

CAST ( expression AS data_type [ (length ) ])

Syntax for CONVERT:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Answer 3

When retreiving from RDBMS you should let the database driver handle marshalling between native and requested type.

CAST is sanctioned by SQL standards and works on the highest number of RDBMS platforms.

CONVERT is avaliable on fewer platforms.

If you have multi-platform conciderations CONVERT should only be used for special cases such as custom formatting that cannot be accomplished with CAST.