Logo
HOWTO: Using the Choose Data Source dialog of Visual Studio 2005 from your own code

Author: Carlos J. Quintero (Microsoft MVP) Applies to: Microsoft Visual Studio 2005
Date: February 2007   Microsoft Visual Studio 2008
Updated: March 2013 Microsoft Visual Studio 2010
      Microsoft Visual Studio 2012

Introduction

This article provides information about how to use the "Choose Data Source" dialog of Visual Studio 2005 from your own code (typically an add-in).

More Information

There are three standard database technologies (APIs) that can be used to connect to databases:

  • ODBC: it uses standard DLLs written in C implementing the ODBC API. The components are named ODBC drivers, and when installed they are registered in the ODBC Driver Manager (Control Panel, Administrative Tools, Data Sources (ODBC) item, Drivers tab).
  • OLEDB: it uses COM (ActiveX) DLLs written in C++ implementing the OLEDB API. The components are named OLEDB providers. There is a special OLEDB provider which allows you to use existing ODBC drivers, but whenever available, it is preferable to use a native OLEDB provider.
  • ADO.NET: it uses managed .NET DLLs written in any .NET language implementing the ADO.NET API. The components are named ADO.NET Providers. There are two special ADO.NET providers: one of them allows you to use existing ODBC drivers, and the other one allows you to use existing OLEDB providers, but whenever available, it is preferable to use a native ADO.NET provider. There are ADO.NET 1.x providers and ADO.NET 2.0 providers.

So, using ADO.NET to connect to a given database such as Oracle or SQL Server you can use three alternatives:

  • The native ADO.NET provider for the database (the preferred method). For SQL Server there is an ADO.NET provider provided by Microsoft, while for Oracle there are two native providers, one provided by Microsoft in the .NET Framework and other provided by Oracle named ODP.NET.
  • The ADO.NET provider for ODBC plus an ODBC driver for the database.
  • The ADO.NET provider for OLEDB plus an OLEDB provider for the database.

When you want to show the user a dialog to select a data source and get a connect string with previous APIs (ODBC, OLEDB) you could use:

Unfortunately, ADO.NET providers in .NET Framework 1.x don't allow you to use a connection dialog, or to build a connection string programatically. For that reason, the "Add New Connection..." context menu of the Data Connections node of the Server Explorer toolwindow of Visual Studio .NET 2002/2003 still uses the old New Data Link dialog with OLEDB providers, instead of using ADO.NET data providers.

Microsoft fixed this situation in the .NET Framework 2.0 and Visual Studio 2005 and therefore the new "Add New Connection..." context menu of the Data Connections node of the Server Explorer toolwindow of this IDE provides a new Choose Data Source dialog that for the first time allows you to use native .NET data providers. This dialog and other functionalities of the Server Explorer require the use of DDEX providers for the ADO.NET 2.0 data providers, which are created with the Data Designer Extensibility (DDEX) SDK of the Visual Studio 2005 SDK. DDEX providers provide design-time capabilities for the .NET Data providers. To learn more about DDEX and how to make an ADO.NET 2.0 data provider appear in the Choose Data Source dialog, read the article Introduction to Visual Studio Data Designer Extensibility (DDEX).

Since it would be useful to show the Choose Data Source dialog outside Visual Studio (from other Microsoft or 3rd party products), Microsoft provides two ways to show it:

Showing the Choose Data Source dialog from outside of Visual Studio

For this purpose Microsoft provides the following assemblies:

  • Microsoft.Data.ConnectionUI.dll
  • Microsoft.Data.ConnectionUI.Dialog.dll

Both of them are installed by Visual Studio in the folder "C:\Program Files\Microsoft Visual Studio 8\Common7\IDE" (for Visual Studio 2005), but notice that none of them has the "VisualStudio" term in the name of the assembly. Apparently they are NOT redistributable (they aren't listed in the redist.txt file in the Visual Studio installation folder), so you should contact Microsoft about this before using them on a machine where Visual Studio is not installed.

To use the Choose Data Source dialog in your own code you have to add a reference to the Microsoft.Data.ConnectionUI.Dialog.dll assembly. Then:

  • To show the dialog to create a new data source connection use this code:
    Imports Microsoft.Data.ConnectionUI
    ...
    Dim objDataConnectionDialog As DataConnectionDialog
    		
    objDataConnectionDialog = New DataConnectionDialog()
    
    DataSource.AddStandardDataSources(objDataConnectionDialog)
    
    If DataConnectionDialog.Show(objDataConnectionDialog) = Windows.Forms.DialogResult.OK Then
       MessageBox.Show(objDataConnectionDialog.ConnectionString)
    End If
    
    objDataConnectionDialog.Dispose()
    
  • To show the dialog to create a connection with a specific data source and provider or to edit an existing data source connection, assuming that you know the data source (Oracle, SQL Server, etc.) and the .NET Data provider (in this sample the .NET Framework Data Provider for Oracle is assumed), use this code:
    Imports Microsoft.Data.ConnectionUI
    ...
    Dim objDataConnectionDialog As DataConnectionDialog
    
    objDataConnectionDialog = New DataConnectionDialog()
    
    DataSource.AddStandardDataSources(objDataConnectionDialog)
    
    objDataConnectionDialog.SelectedDataSource = DataSource.OracleDataSource
    
    For Each objDataProvider As DataProvider In objDataConnectionDialog.SelectedDataSource.Providers
    
       If objDataProvider.Name = "System.Data.OracleClient" Then
          objDataConnectionDialog.SelectedDataProvider = objDataProvider
          Exit For
       End If
    
    Next
    
    objDataConnectionDialog.ConnectionString = "Data Source=MyOracleServer"
    
    If DataConnectionDialog.Show(objDataConnectionDialog) = Windows.Forms.DialogResult.OK Then
    
       MessageBox.Show(objDataConnectionDialog.ConnectionString)
    
    End If
    
    objDataConnectionDialog.Dispose()
    

Unfortunately this approach will show only the data sources and .NET data providers included in the .NET Framework (SQL Server, Oracle, OLEDB and ODBC), but not 3rd party .NET data providers. The reason for this is that 3rd party providers are registered for Visual Studio (under the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\8.0\DataProviders) and therefore they are unknown to these assemblies.

Showing the Choose Data Source dialog from within Visual Studio

For this purpose Visual Studio provides the following assemblies:

  • Microsoft.VisualStudio.DataTools.dll
  • Microsoft.VisualStudio.Data.dll
  • Microsoft.VisualStudio.Data.Interop.dll
  • Etc.

To use the Choose Data Source dialog in your own code you have to add a reference to the Microsoft.VisualStudio.Data.Interop.dll assembly. You will need to get the IVsDataConnectionDialogFactory service from Visual Studio (see the article HOWTO: Get a Visual Studio service from an add-in). Then:

  • To show the dialog to create a new data source connection use this code:
    Microsoft.VisualStudio.Data.Interop
    ...
    Dim objIVsDataConnectionDialogFactory As IVsDataConnectionDialogFactory
    Dim objIVsDataConnectionDialog As IVsDataConnectionDialog
    Dim sConnectionString As String
    Dim objService As Object 
    
    objService = GetService(objDTE, GetType(IVsDataConnectionDialogFactory))
    
    objIVsDataConnectionDialogFactory = CType(objService, IVsDataConnectionDialogFactory)
    
    objIVsDataConnectionDialog = objIVsDataConnectionDialogFactory.CreateConnectionDialog
    
    objIVsDataConnectionDialog.AddAllSources()
    
    If objIVsDataConnectionDialog.ShowDialog() Then
       sConnectionString = objIVsDataConnectionDialog.DisplayConnectionString
    End If
    objIVsDataConnectionDialog.Dispose()
  • To show the dialog to create a connection with a specific data source and provider or to edit an existing data source connection, assuming that you know the data source Guid (Oracle, SQL Server, etc. that you can get from the registry key KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\8.0\DataSources) and the .NET Data provider Guid (that you can get from the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\8.0\DataProviders), use this code:
    Microsoft.VisualStudio.Data.Interop
    ...
    Dim objIVsDataConnectionDialogFactory As IVsDataConnectionDialogFactory
    Dim objIVsDataConnectionDialog As IVsDataConnectionDialog
    Dim sConnectionString As String
    Dim objService As Object 
    
    objService = GetService(objDTE, GetType(IVsDataConnectionDialogFactory))
    
    objIVsDataConnectionDialogFactory = CType(objService, IVsDataConnectionDialogFactory)
    
    objIVsDataConnectionDialog = objIVsDataConnectionDialogFactory.CreateConnectionDialog
    
    objIVsDataConnectionDialog.AddAllSources()
    
    ' Select the Oracle data source
    objIVsDataConnectionDialog.SelectedSource = New Guid("E5E86CD6-6454-4fdd-910F-DC5DFC73EB45")
    
    ' Select the .NET Framework Data Provider for Oracle
    
    objIVsDataConnectionDialog.SelectedProvider = New Guid("8F5C5018-AE09-42cf-B2CC-2CCCC7CFC2BB") ' Set the existing connection string objIVsDataConnectionDialog.DisplayConnectionString = sConnectionString If objIVsDataConnectionDialog.ShowDialog() Then sConnectionString = objIVsDataConnectionDialog.DisplayConnectionString End If objIVsDataConnectionDialog.Dispose()

This approach will show not only the data sources and .NET data providers included in the .NET Framework (SQL Server, Oracle, OLEDB and ODBC), but also 3rd party .NET data providers such as OPD.NET (for Oracle), etc.

When referencing an assembly, be sure to set its "Copy Local" property to False to avoid copying it to the output folder. If it is copied, Visual Studio could load it from there and you would get errors when casting types because they come from different locations.

Related articles



Go to the 'Visual Studio Extensibility (VSX)' web site for more articles like this (Articles section)


Top