none
Import Data from ODBC data source RRS feed

  • Question

  • Hi All

    I need help from U in VB.Net or C#.net.

    I want to import data from ODBC database, similarly MS Access 2007 provides the way like….

    Import ODBC database

    Then, select data source GUI will appear

    U can select the data source from that dialog

    It will ask username and password depend upon the database system

    Finally it retrieves the tables list from that database.

    Thank....

    Thursday, December 17, 2009 12:32 PM

Answers

  • Import ODBC database.-->import to where? any where use OdbcConnection.

    Finally it retrieves the tables list from that database.-->use OdbcConnection.GetSchema Method.

    Summary:
    use OdbcConnection.GetSchema Method and import the database yourself with returned information.


    With best regards, Yasser Zamani
    Thursday, December 17, 2009 12:41 PM
  • Hi,
    if you just want to retrieve the tables in Visual Studio you can use Data Connection in Server Explorer (there is also possibility to use odbc data sources). Please refer to:
    http://msdn.microsoft.com/en-us/library/3d1wkhas(VS.80).aspx

    Regards

    Ryszard Gawron [MSFT]

    „This posting is provided "AS IS" with no warranties, and confers no rights.”

    Thursday, December 17, 2009 2:04 PM
  • This will require some knowledge of the ODBC API. For example, in order to get the list of the currently defined DSNs you need the following:

    Private Declare Function SQLDataSources Lib "ODBC32.DLL" (ByVal henv As Integer, ByVal fDirection As Short, ByVal szDSN As String, ByVal cbDSNMax As Short, ByRef pcbDSN As Short, ByVal szDescription As String, ByVal cbDescriptionMax As Short, ByRef pcbDescription As Short) As Short
    Private Declare Function SQLAllocEnv Lib "ODBC32.DLL" (ByRef env As Integer) As Short
    Const SQL_SUCCESS As Integer = 0
    Const SQL_FETCH_NEXT As Integer = 1
    
            Dim ReturnValue As Short
            Dim DSNName As String
            Dim DriverName As String
            Dim DSNNameLen As Short
            Dim DriverNameLen As Short
            Dim SQLEnv As Integer 'handle to the environment
    
    
            If SQLAllocEnv(SQLEnv) <> -1 Then
                Do Until ReturnValue <> SQL_SUCCESS
                    DSNName = Space(1024)
                    DriverName = Space(1024)
                    ReturnValue = SQLDataSources(SQLEnv, SQL_FETCH_NEXT, DSNName, 1024, DSNNameLen, DriverName, 1024, DriverNameLen)
                    DSNName = Left(DSNName, DSNNameLen)
                    DriverName = Left(DriverName, DriverNameLen)
    
                    If DSNName <> Space(DSNNameLen) Then
                        System.Diagnostics.Debug.WriteLine(DSNName)
                        System.Diagnostics.Debug.WriteLine(DriverName)
                    End If
                Loop
            End If

    To connect to the database you can use the System.Data.Odbc namespace and then use the method suggested by Yasser.Zamani to fetch the tables. You will need to prompt the user for the user ID and password if required.

            Dim ODBCConnection As New System.Data.Odbc.OdbcConnection
    
            ODBCConnection.ConnectionString = "DSN=DSNName;UID=MyUID;PWD=MyPwd;"
    
            ODBCConnection.Open()
            '
            '
            '
            ODBCConnection.Close()

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, December 17, 2009 2:45 PM

All replies

  • Import ODBC database.-->import to where? any where use OdbcConnection.

    Finally it retrieves the tables list from that database.-->use OdbcConnection.GetSchema Method.

    Summary:
    use OdbcConnection.GetSchema Method and import the database yourself with returned information.


    With best regards, Yasser Zamani
    Thursday, December 17, 2009 12:41 PM
  • Hi,
    if you just want to retrieve the tables in Visual Studio you can use Data Connection in Server Explorer (there is also possibility to use odbc data sources). Please refer to:
    http://msdn.microsoft.com/en-us/library/3d1wkhas(VS.80).aspx

    Regards

    Ryszard Gawron [MSFT]

    „This posting is provided "AS IS" with no warranties, and confers no rights.”

    Thursday, December 17, 2009 2:04 PM
  • This will require some knowledge of the ODBC API. For example, in order to get the list of the currently defined DSNs you need the following:

    Private Declare Function SQLDataSources Lib "ODBC32.DLL" (ByVal henv As Integer, ByVal fDirection As Short, ByVal szDSN As String, ByVal cbDSNMax As Short, ByRef pcbDSN As Short, ByVal szDescription As String, ByVal cbDescriptionMax As Short, ByRef pcbDescription As Short) As Short
    Private Declare Function SQLAllocEnv Lib "ODBC32.DLL" (ByRef env As Integer) As Short
    Const SQL_SUCCESS As Integer = 0
    Const SQL_FETCH_NEXT As Integer = 1
    
            Dim ReturnValue As Short
            Dim DSNName As String
            Dim DriverName As String
            Dim DSNNameLen As Short
            Dim DriverNameLen As Short
            Dim SQLEnv As Integer 'handle to the environment
    
    
            If SQLAllocEnv(SQLEnv) <> -1 Then
                Do Until ReturnValue <> SQL_SUCCESS
                    DSNName = Space(1024)
                    DriverName = Space(1024)
                    ReturnValue = SQLDataSources(SQLEnv, SQL_FETCH_NEXT, DSNName, 1024, DSNNameLen, DriverName, 1024, DriverNameLen)
                    DSNName = Left(DSNName, DSNNameLen)
                    DriverName = Left(DriverName, DriverNameLen)
    
                    If DSNName <> Space(DSNNameLen) Then
                        System.Diagnostics.Debug.WriteLine(DSNName)
                        System.Diagnostics.Debug.WriteLine(DriverName)
                    End If
                Loop
            End If

    To connect to the database you can use the System.Data.Odbc namespace and then use the method suggested by Yasser.Zamani to fetch the tables. You will need to prompt the user for the user ID and password if required.

            Dim ODBCConnection As New System.Data.Odbc.OdbcConnection
    
            ODBCConnection.ConnectionString = "DSN=DSNName;UID=MyUID;PWD=MyPwd;"
    
            ODBCConnection.Open()
            '
            '
            '
            ODBCConnection.Close()

    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, December 17, 2009 2:45 PM