| 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 back to the 'Resources for Visual Studio .NET extensibility' section for more articles like this
|
