How can I determine the data type of a SQL Server table row using VB.Net

Question

I am working on some code that allows the client to connect to a variety of external data sources, including OLEDB, ODBC and SQL Server. In my VB.NET code, if I set up a connection using system.data.oledb or system.data.odbc, I can use the system.data.oledb.oledbType or system.data.odbc.odbcType methods to test for Integer or Double data types.

For example, in the code snippet below, dr is a data row from a data table populated using the OLEDB connection, GetSchema method.

--- Start Example-------
If dr("Data_Type") = System.Data.OleDb.OleDbType.Integer Then
  ...
End
--- End Example-------

There is a similar solution for system.data.odbc.

However, I can't seem to find a way to get the data type from a data row table populated from the GetSchema method of a SQLClient connection.

Any ideas?

Thanks in advance.

Dave

Answer 1

Here is a snippet that loads a DataGridView. Instead of using a form you'll just load a local DGV or use DBSchema which is a DataTable.

    Private Sub DataDictionaryToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DataDictionaryToolStripMenuItem.Click
    If g.DBSchema Is Nothing Then
        Using con As New SqlConnection(g.OISConnectString)
            con.Open()
            g.DBSchema = con.GetSchema("Columns") ' full DB Schema
        End Using
    End If
    Dim frm As New frmSearch2
    frm.inDataView = New DataView(g.DBSchema) ' dv
    frm.inSQLName = "Database Schema"
    frm.Show()
End Sub

If you have an existing DataTable (e.g. g.DBSchema) you can use this:

    For Each col As DataColumn In g.DBSchema.Columns
        Debug.Print(col.DataType.ToString)
    Next

But this will return the ADO DataType (I think) and not the SQL data type.