none
Need exact Type from database table RRS feed

  • Question

  •  


    Hello,

    I need to get the correct Datatype for the columns from  a database-table

    I've written some code and it just works fine.
    But it doesn't give me exactly the right datatype

    for Example my Table

    Table Tickets
    {
     ticketname  varchar(50)
     description varchar(255)
    }


    My code now gives me the type 'char' for 'ticketname'

    DataTable oSchema;

    oSchema = oControl.Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new Object[] { null, null, "Tickets", null });


    ...
    OleDbType 
    oType = (OleDbType)oSchema.Rows[oTmpColumn.Ordinal]["DATA_TYPE"];  
     
     
     
     

    For now I am using a Database at MS Sql Server.

    Is there a way to get the exact type?

    thanks in advance

    Sonia

    Wednesday, February 11, 2009 11:40 AM

Answers

  • I don't know where OleDbMetaDataColumnNames.NativeDataType would be used. The documentation seems to be a bit skimpy.

    GetSchema was enhanced for ADO.NET 2.0 so you can return native data type names (DATA_TYPE or TYPE_NAME) when using a native .NET provider to connect. It looks like the ODBC library supports this as well but OLEDB, for some odd reason, does not support these columns when retrieving schema. Below is some code I was tinkering around with to see what metadata I could return:

            Dim ConnectionString As String = "Data Source=xxx\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=Northwind" 
            'Dim ConnectionString As String = "Driver={SQL Server Native Client 10.0};Server=xxx\sqlexpress;Database=Northwind;Trusted_Connection=yes;"  
            'Dim ConnectionString As String = "Driver={Microsoft Access Driver (*.mdb)};" & _  
            '                                 "Dbq=C:\Test Files\db1 XP.mdb;" & _  
            '                                 "Uid=Admin;" & _  
            '                                 "Pwd="  
            'Dim ConnectionString As String = "DRIVER={Microsoft ODBC for Oracle};" & _  
            '                                    "SERVER=xxx;" & _  
            '                                    "UID=xxx;PWD=xxx;"  
            Dim Connection As New SqlConnection(ConnectionString)  
            'Dim Connection As New System.Data.Odbc.OdbcConnection(ConnectionString)  
            Connection.Open()  
            Dim Restrictions() As String = {"Northwind", Nothing"Orders"Nothing}  
            Dim CollectionName As String = "Columns" 
            'Display all available restrictions parameters  
            'Dim dt As DataTable = Connection.GetSchema("Restrictions")  
            Dim dt As DataTable = Connection.GetSchema(CollectionName, Restrictions)  
            frmMain.DataGrid1.DataSource = dt  
            Connection.Close() 

    You can find more info on GetSchema at the below link:

    Retrieving Database Schema Information (ADO.NET)

    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by apfelsine Wednesday, March 4, 2009 4:35 PM
    Friday, February 13, 2009 4:25 PM

All replies

  • You may have to create your own data type mapping in order the obtain the actual native data type name. The following should help:

    http://www.w3schools.com/ADO/ado_datatypes.asp
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, February 11, 2009 4:29 PM
  • Hi,

    I'm sorry - but I would prefer to get the information without making another call to the database.

    But all I get out of the DataTable object is the C# Datatype although I KNOW somewhere below there is a property wich already contains the name of the native Datatype. Cause I've seen it in the watchlist.
    But I don't know how to get it.

    I'm talking about

    OleDbMetaDataColumnNames.NativeDataType

    But the descriptions at the library are a little confusing so I did not manage to get it to work.
    Maybe this property is also not what its name makes me think. I managed to get all types available on sql server but not for a specific column. And its hard to believe that there is no way to get that information then.


    Is there any other idea ?






    Thursday, February 12, 2009 9:01 PM
  • I don't know where OleDbMetaDataColumnNames.NativeDataType would be used. The documentation seems to be a bit skimpy.

    GetSchema was enhanced for ADO.NET 2.0 so you can return native data type names (DATA_TYPE or TYPE_NAME) when using a native .NET provider to connect. It looks like the ODBC library supports this as well but OLEDB, for some odd reason, does not support these columns when retrieving schema. Below is some code I was tinkering around with to see what metadata I could return:

            Dim ConnectionString As String = "Data Source=xxx\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=Northwind" 
            'Dim ConnectionString As String = "Driver={SQL Server Native Client 10.0};Server=xxx\sqlexpress;Database=Northwind;Trusted_Connection=yes;"  
            'Dim ConnectionString As String = "Driver={Microsoft Access Driver (*.mdb)};" & _  
            '                                 "Dbq=C:\Test Files\db1 XP.mdb;" & _  
            '                                 "Uid=Admin;" & _  
            '                                 "Pwd="  
            'Dim ConnectionString As String = "DRIVER={Microsoft ODBC for Oracle};" & _  
            '                                    "SERVER=xxx;" & _  
            '                                    "UID=xxx;PWD=xxx;"  
            Dim Connection As New SqlConnection(ConnectionString)  
            'Dim Connection As New System.Data.Odbc.OdbcConnection(ConnectionString)  
            Connection.Open()  
            Dim Restrictions() As String = {"Northwind", Nothing"Orders"Nothing}  
            Dim CollectionName As String = "Columns" 
            'Display all available restrictions parameters  
            'Dim dt As DataTable = Connection.GetSchema("Restrictions")  
            Dim dt As DataTable = Connection.GetSchema(CollectionName, Restrictions)  
            frmMain.DataGrid1.DataSource = dt  
            Connection.Close() 

    You can find more info on GetSchema at the below link:

    Retrieving Database Schema Information (ADO.NET)

    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by apfelsine Wednesday, March 4, 2009 4:35 PM
    Friday, February 13, 2009 4:25 PM
  • Yes, thanks. This helped!

    Finally I used SqlConnection to get it done.
    But I had to customize the restrictions by putting an owner

    like that

    Dim Restrictions() As String = {"Northwind", "dbo""Orders"}

    For those who try this later,  I'd like to mention that you must keep in mind that the restrictions are filters.

    In my case without having given the owner,
    the DataTable object had no rows.


    Wednesday, March 4, 2009 4:35 PM