Programmatically determining the sqlDbType of a field

egoldin — Wed, 20 Dec 2006 21:34:00 GMT

I found this remarkable info in a newsgroup post from David Sceppa from Microsoft:

You can build a SqlCommand to query the table and retrieve this schema information from the SqlDataReader through its GetSchemaTable method. This method returns a DataTable of schema information. Each row in the DataTable corresponds to a column in the resultset for the original query. The DataTable of schema information includes columns for column name, data type, etc. There's a column called ProviderType that contains an integer, which you can cast to SqlDbType.

Here's some code that should help:

Visual Basic .NET:
Dim strConn, strSQL As String
strConn = "Data Source=(local)\NetSDK;Initial Catalog=Northwind;" & _
"Trusted_Connection=Yes;"
Dim cn As New SqlConnection(strConn)
cn.Open()
strSQL = "SELECT OrderDate FROM Orders"
Dim cmd As New SqlCommand(strSQL, cn)
Dim rdr As SqlDataReader
rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly)
Dim tbl As DataTable = rdr.GetSchemaTable()
rdr.Close()
cn.Close()
Dim intOrderDateType As Integer
intOrderDateType = CType(tbl.Rows(0)("ProviderType"), Integer)
Dim typOrderDateType As SqlDbType
typOrderDateType = CType(intOrderDateType, SqlDbType)
Console.WriteLine("ProviderType = " & intOrderDateType)
Console.WriteLine("SqlDbType = " & typOrderDateType.ToString())

Visual C# .NET:
string strConn, strSQL;
strConn = @"Data Source=(local)\NetSDK;Initial Catalog=Northwind;" +
"Trusted_Connection=Yes;";
SqlConnection cn = new SqlConnection(strConn);
cn.Open();
strSQL = "SELECT OrderDate FROM Orders";
SqlCommand cmd = new SqlCommand(strSQL, cn);
SqlDataReader rdr;
rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
DataTable tbl = rdr.GetSchemaTable();
rdr.Close();
cn.Close();
int intOrderDateType;
intOrderDateType = (int) tbl.Rows[0]["ProviderType"];
SqlDbType typOrderDateType;
typOrderDateType = (SqlDbType) intOrderDateType;
Console.WriteLine("ProviderType = " + intOrderDateType);
Console.WriteLine("SqlDbType = " + typOrderDateType.ToString());

Usable Asp.Net : ado.net

Programmatically determining the sqlDbType of a field