如何使用 ADO.NET 获取表中列的 SqlDbType?

问题描述

我试图在运行时确定 sql server 表列的 SqlDbType 是什么.

I'm trying to determine at runtime what the SqlDbType of a sql server table column is.

是否有一个类可以在 System.Data.SqlClient 中执行此操作，还是我应该自己进行映射?我可以从

is there a class that can do that in System.Data.SqlClient or should I do the mapping myself? I can get a string representation back from

SELECT DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_CATALOG = '{0}' AND TABLE_SCHEMA = '{1}' 
   AND TABLE_NAME = '{2}' AND COLUMN_NAME = '{3}'

我不能使用 SMO，因为我无法控制执行机器，所以我不能保证它会被安装.(抱歉没有说清楚 rp).

I can't use SMO as I have no control over the executing machine so I can't guarantee it will be installed. (Sorry for not making that clear rp).

作为对 Joel 的回答，我正在尝试创建一个可以调用的函数，当传递一个 SqlConnection、一个表名和一个列名时，它将返回一个 SqlDBType.

In answer to Joel, I'm trying to make a function that I can call that will return me a SqlDBType when passed a SqlConnection, a table name, and a column name.

推荐答案

在 SQL Server 中，您可以使用 FMTONLY 选项.它允许您在不获取任何数据的情况下运行查询，只返回列.

In SQL Server you can use the FMTONLY option. It allows you to run a query without getting any data, just returning the columns.

SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "SET FMTONLY ON; select column from table; SET FMTONLY OFF";
SqlDataReader reader = cmd.ExecuteReader();
SqlDbType type = (SqlDbType)(int)reader.GetSchemaTable().Rows[0]["ProviderType"];

这篇关于如何使用 ADO.NET 获取表中列的 SqlDbType?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！