SUMMARY
This step-by-step article demonstrates how to use the
Data Link Properties dialog box to programmatically create a connection string at design time.
back to the top
Requirements
The following list outlines the recommended hardware, software, network
infrastructure, and service packs that you need:
- Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Windows NT 4.0 Server
- Microsoft Visual Studio .NET
This article assumes that you are familiar with the following topics:
- Visual Studio .NET
- ADO.NET fundamentals and syntax
- ActiveX Data Objects (ADO) fundamentals and syntax
back to the top
Create an ADO Connection
For Component Object Model (COM) interoperability, you must declare the ADO
Connection object as
ADODB._Connection and cast the generic object that the data link returns to an
ADODB._Connection.
- Start Visual Studio .NET, and then create a new Visual C# Windows Application project. Form1 is added to the project by default.
- From the Project menu, click Add Reference.
- On the COM tab, select the following references:
- Microsoft ActiveX Data Objects 2.7
- Microsoft OLEDB Service Component 1.0 Type Library
- If you are prompted to have a wrapper generated for you, click Yes.
- Add a Button control to Form1.
- Add the following code to the Button1_Click event:
MSDASC.DataLinks mydlg = new MSDASC.DataLinks();
ADODB._Connection ADOcon;
//Cast the generic object that PromptNew returns to an ADODB._Connection.
ADOcon = (ADODB._Connection) mydlg.PromptNew();
ADOcon.Open("","","",0);
if (ADOcon.State == 1) {
MessageBox.Show("Connection Opened");
ADOcon.Close();
}
else {
MessageBox.Show("Connection Failed");
}
- Press the F5 key to compile and run the project, and then click Button1.
- Type the appropriate information in the Data Link Properties dialog box, and make sure that you select the Allow Saving Password check box.
- Click Test Connection.
- Click OK. If the connection test succeeded in the data link, a connection to the database is established, and a message box is displayed.
back to the top
Create an OLE DB Connection
When you create an OLE DB connection with the OLE DB managed provider in .NET, you cannot use the data link to create connections to ODBC data sources. Because ODBC has its own managed provider in .NET, you receive an error if you use the
Microsoft OLEDB provider for ODBC drivers option in the
Data Link Properties dialog box. In addition, you must load ADO into the application because the data link creates an object, which you cast to an
ADODB._Connection, that is not compatible with the
OleDbConnection object. Thus, you must create an
ADODB._Connection and assign its
ConnectionString property to the
ConnectionString property of the
OleDbConnection object for this to work properly.
- Start Visual Studio .NET, and then create a Visual C# Windows Application project. Form1 is added to the project by default.
- From the Project menu, click Add Reference.
- On the COM tab, select the following references:
- Microsoft ActiveX Data Objects 2.7
- Microsoft OLEDB Service Component 1.0 Type Library
- Add a Button control to Form1.
- Add the following code to the top of the Code window:
using System.Data.OleDb;
- Add the following code in the Button1_Click event:
MSDASC.DataLinks mydlg = new MSDASC.DataLinks();
OleDbConnection OleCon = new OleDbConnection();
ADODB._Connection ADOcon;
//Cast the generic object that PromptNew returns to an ADODB._Connection.
ADOcon = (ADODB._Connection) mydlg.PromptNew();
OleCon.ConnectionString = ADOcon.ConnectionString;
OleCon.Open();
if (OleCon.State.ToString() == "Open") {
MessageBox.Show("Connection Opened");
OleCon.Close();
}
else {
MessageBox.Show("Connection Failed");
}
- Press F5 to compile and run the project, and then click Button1.
- Type the appropriate information in the Data Link Properties dialog box, and make sure that you select the Allow Saving Password check box.
- Click Test Connection.
- Click OK. If the connection test succeeded in the data link, a connection to the database is established, and a message box is displayed.
back to the top
Additional Information
It requires additional effort to use this method to create an ODBC connection because the data link creates a connection string that is specific to OLE DB and is not compatible with the ODBC managed provider. For this to work, you must parse the ADODB connection string for the relevant information such as the user ID, password, and data source. After you obtain this information, you can use it to create a connection string that is specific to ODBC. Keep in mind that the data link only uses ODBC data source names (DSNs); thus, you cannot create a DSN-less connection through the data link.
back to the top
REFERENCES
For additional information, click the article numbers below
to view the articles in the Microsoft Knowledge Base:
286189 HOWTO: Invoke the OLE DB Data Link Properties Dialog Box in Visual Basic Code
283245 HOWTO: Persist Data Links Programmatically
193128 HOWTO: Create an ODBC and OLEDB Connection Prompt Control in ADO
back to the top