Extending DatabaseSpy to open UDL files.

I was asked to find utility to test OLEDB conection paramenters.
I desided to use EXCEL with the following procedure:

 
2. Open MS Excel (I’ve tested it with Excel 2003) , hope that previous versions are similar.
3. On the Data menu, point to Import External Data, and then click Import Data.
4. In the “Files of  Type” select “Microsoft Data Links( *.udl)”
5. Open UDL file created in step 1
6. Select table that you are interesting in.
7. The results should be shown in the spreadsheet.

However even the connection was tested successfully in Data Link Properties, Excel reported “no tables found“ when user tried to open the UDL file.
Then I was asked  to find another way to test OLEDB Connection.
Microsoft has KB article How to use Data Link files with the OleDbConnection object in Visual Basic .NET  shows only basic connection test.

Then I found An Approach to Viewing the Structure of a Database Using VB 2005 (AKA DatabaseSpy).

I’ve downloaded the code , but UI for different supported databases(Oracle,SQL Server and Access) doesn’t include generic OLEDB provider.

Fortunately, It was easy enough to add new connection tab UDL (in frmConnect.vb) with Browse  button and ability to load ,test and assign connection from UDL file.

 

 TODO: It would be better to call DATA Link APIs  directly as described in “Incorporating the Data Link Properties Editor into a VB.NET Application” article.

 

    Private Sub btnUDLBrouse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUDLBrouse.Click

        Dim openFile As System.Windows.Forms.OpenFileDialog = Me.OpenFileDialogUDL

        openFile.Title = “Universal DataLink”

        ‘openFile.DefaultExt = “mdb”

        openFile.Filter = “Universal DataLink (*.udl)|*.udl”

        openFile.ShowDialog()

        txtUDL.Text = openFile.FileName

    End Sub

 

    Private Sub btnUDLTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUDLTest.Click

        ‘Dim response As String

 

        Dim cn As New OleDbConnection()

        mConnectionString = “File Name=” & txtUDL.Text

        cn.ConnectionString = mConnectionString

        TestConnectionString(cn)

    End Sub

    Private Sub TestConnectionString(ByVal cn As OleDbConnection)

        Try

            cn.Open()

            If cn.State = ConnectionState.Open Then

                MessageBox.Show(“Connection opened successfully”)

            Else

                MessageBox.Show(“Connection could not be established”)

            End If

                        MessageBox.Show(“Connection attempt successful, the database connection information provided has been successfully used to connect to the database.”, “Connection Successful”, MessageBoxButtons.OK, MessageBoxIcon.Information)

        Catch ex As Exception

 

            MessageBox.Show(“Connection attempt failed.”, “Unable to Connect”, MessageBoxButtons.OK, MessageBoxIcon.Error)

        Finally

            cn.Close()

            cn = Nothing

 

        End Try

    End Sub

 

    Private Sub btnUDLCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUDLCancel.Click

        Me.Dispose()

    End Sub

 

    Private Sub btnUDLOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUDLOk.Click

        btnUDLTest_Click(sender, e)

        ‘set app setting global variables

        mCurrentDataModel = “UDL”

        mCurrentDatabaseType = “UDL”

        AppVars.gCurrentDataModel = mCurrentDataModel

        AppVars.gCurrentDatabaseType = mCurrentDatabaseType

        AppVars.gConnectionString = mConnectionString

        ‘create hashtable to hold settings

        Dim ht As New Hashtable

        ht.Add(“CurrentDataModel”, gCurrentDataModel)

        ht.Add(“CurrentDatabaseType”, gCurrentDatabaseType)

        ht.Add(“ConnectionString”, gConnectionString)

                ht.Add(“ProviderString”, gProviderString)

        ‘ht.Add(“ServerName”, gServerName)

        ‘ht.Add(“UserID”, gUserID)

        ‘ht.Add(“Password”, gPassword)

 

        ‘serialize data

        SerializeAppVars(ht)

 

        Me.Dispose()

 

    End Sub