C# 如何使用 ADO.NET 获取表中列的 SqlDbType？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/459572/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I get the SqlDbType of a column in a table using ADO.NET?
提问by WOPR
I'm trying to determine at runtime what the SqlDbType of a sql server table column is.
我试图在运行时确定 sql server 表列的 SqlDbType 是什么。
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
是否有一个类可以在 System.Data.SqlClient 中做到这一点，还是我应该自己做映射？我可以从
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}'
EDIT: 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).
编辑：我不能使用 SMO，因为我无法控制执行机器，所以我不能保证它会被安装。（抱歉没有说清楚 rp）。
EDIT: 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.
编辑：为了回答乔尔，我正在尝试创建一个我可以调用的函数，当传递一个 SqlConnection、一个表名和一个列名时，它会返回一个 SqlDBType。
采纳答案by Adam Says - Reinstate Monica
In SQL Server you can use the FMTONLY option. It allows you to run a query without getting any data, just returning the columns.
在 SQL Server 中，您可以使用 FMTONLY 选项。它允许您在不获取任何数据的情况下运行查询，只返回列。
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"];
回答by rp.
For SQL Server, use the SMO (SQL Server Management Objects).
对于 SQL Server，请使用 SMO（SQL Server 管理对象）。
http://www.yukonxml.com/articles/smo/
http://www.yukonxml.com/articles/smo/
For example, you can use this code to traverse over all of the columns of a table.
例如，您可以使用此代码遍历表的所有列。
Server server = new Server();
Database database = new Database( "MyDB" );
Table table = new Table( database, "MyTable" );
foreach ( Column column in table.Columns )
{
WriteLine( column.Name );
}
Here are all of the column properties available to you: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.column_members.aspx
以下是所有可用的列属性：http: //msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.column_members.aspx
回答by Gorkem Pacaci
If you are eventually going to read the data, you can do this:
如果您最终要读取数据，您可以这样做：
SqlCommand comm = new SqlCommand("SELECT * FROM Products", connection);
using (SqlDataReader reader = comm.ExecuteReader())
{
while (reader.Read())
{
Type type = reader.GetSqlValue(0).GetType();
// OR Type type = reader.GetSqlValue("name").GetType();
// yields type "System.Data.SqlTypes.SqlInt32"
}
}
回答by Steve In CO
Old question, but if all you are trying to do is get from the string DATA_TYPE to the SqlDbType enum, the query presented in the original question combined with one line of code will do the trick:
老问题，但如果您要做的只是从字符串 DATA_TYPE 获取到 SqlDbType 枚举，那么原始问题中提供的查询结合一行代码就可以解决问题：
string dataType = "nvarchar"; // result of the query in the original question
var sqlType = (SqlDbType)Enum.Parse(typeof(SqlDbType), dataType, true);
回答by Nikolay Fedorov
You can use enum System.Data.CommandBehavior as paramter for method SqlCommand.ExecuteReader(System.Data.CommandBehavior.KeyInfo):
您可以使用枚举 System.Data.CommandBehavior 作为方法 SqlCommand.ExecuteReader(System.Data.CommandBehavior.KeyInfo) 的参数：
SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "select column from table";
SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.KeyInfo);
SqlDbType type = (SqlDbType)(int)reader.GetSchemaTable().Rows[0]["ProviderType"];
This example looks like the example with using sql: SET FMTONLY ON; SET FMTONLY OFF. But you haven't to use SET FMTONLY. And in this case you don't
receive data from the table. You receive only metadata.
这个例子看起来像使用 sql 的例子： SET FMTONLY ON; 设置 FMTONLY 关闭。但是您不必使用 SET FMTONLY。在这种情况下，您不会
从表中接收数据。您只收到元数据。
