在从数据库中检索数据时转换或转换?

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?

在访问从数据库检索的DataTable中的对象时，是否有理由不将对象转换为所需的类型，或者有理由使用convert?我知道规则是在我们知道要处理的数据类型时转换的，在尝试将数据类型转换为非数据类型时转换的。假设我们知道存储在列中的数据类型，那么cast似乎是合适的，但是是否存在任何DB类型问题，这意味着我们不能依赖它?

3 个解决方案

#1

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

因为你说的原因，我总是投。我知道你需要处理的问题是:

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

显然，您需要能够处理DBNulls(例如通过使用Convert.IsDBNull进行测试)
In the case of ExecuteScalar I believe you need to check for null as well as DBNull.

对于ExecuteScalar，我认为您需要检查null和DBNull。
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.: .

SQL server @@IDENTITY和SCOPE_IDENTITY函数返回数字(十进制)，即使是被声明为INT的列。在这种情况下，您可以使用两次“(INT)(decimal)值”或在T-SQL代码中处理它，例如:。

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

插入MyTable……从MyTable中选择AutoIdColumn，其中AutoIdColumn = SCOPE_IDENTITY()

或

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

#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.

CAST和CONVERT都用于显式地将一个数据类型的表达式转换为另一个数据类型。但是，使用CONVERT，您还可以指定格式样式。

Syntax for CAST:

语法演员:

CAST ( expression AS data_type [ (length ) ])

Syntax for CONVERT:

语法转换:

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

#3

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

当从RDBMS进行重构时，应该让数据库驱动程序处理本机类型和请求类型之间的封送。

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

CAST被SQL标准认可，并且可以在最多的RDBMS平台上运行。

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.

如果您有多平台的看门人，转换应该只用于特殊的情况，比如使用强制转换无法完成的自定义格式。

#1