Building a connection string programmatically in Ado.net using Visual C #. NET

Ado|visual| Programming | String How to: Build a connection string programmatically in Ado.net using Visual C #. NET
Article id:310083
Last modified: June 18, 2002
Version: 1.0

The publication number for this article was CHS310083
For the Microsoft Visual Basic. NET version of this article, see 309485.

This article references the following Microsoft. NET Framework Class Library namespaces: System.Data.OleDb

The content of this task
• overview
• Requirements
• Create an ADO connection
• Create OLE DB connections
• Other information

• Reference

Content of this page
Profile
Reference

Profile
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 of the page
Requirements
The following list lists the recommended hardware, software, network structure, and required Service pack: Microsoft Windows Professional, Windows SQL Server, Windows Advance D 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 basic knowledge and grammar
ActiveX Data Object (ADO) Basics and syntax
Back to the top of the page
Create an ADO connection
To implement interoperability with the Component Object Model (COM), you must declare the ADO Connection object as adodb._connection and cast the generic object returned by the data link to adodb._connection. 1. Start visual Studio. NET, and then create a new Visual C # Windows application project. Form1 is added to the project by default.
2. From the Project menu, click Add Reference.
3. On the COM tab, select the following reference: Microsoft ActiveX Data Objects 2.7
Microsoft OLE DB Service Component 1.0 Type Library


4. If you are prompted to generate the wrapper, click Yes.
5. Add a button control on the Form1.
6. Add the following code to the Button1_Click event:
MSDASC. DataLinks Mydlg = new MSDASC. DataLinks ();
Adodb._connection ADOcon;

Cast the generic object, PromptNew returns to a 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");
}

7. Press the F5 key to compile and run the project, and then click Button1.
8. Type the appropriate information in the Data Link Properties dialog box, and make sure that the Allow Password Save check box is selected.
9. Click Test Connection.
10. Click OK. If the connection test for the data link succeeds, a connection is made to the database and a message box is displayed.
Back to the top of the page
To create an OLE DB connection
When you create an OLE DB connection using. NET's OLE DB managed provider, you cannot create a connection to an ODBC data source by using a data link. Because ODBC has its own hosting provider in. NET, you will receive an error if you use the Microsoft OLE DB Provider for ODBC drivers option in the Data Link Properties dialog box. In addition, because the data link creates an object that is not compatible with the OleDbConnection object (the object is cast to adodb._connection), you must load ADO into the application. Therefore, you must create a adodb._connection and assign its ConnectionString property to the ConnectionString property of the OleDbConnection object to make it work correctly. 1. Start visual Studio. NET, and then create a Visual C # Windows application project. Form1 is added to the project by default.
2. From the Project menu, click Add Reference.
3. On the COM tab, select the following reference: Microsoft ActiveX Data Objects 2.7
Microsoft OLE DB Service Component 1.0 Type Library


4. Add a Button control to the Form1.
5. Add the following code to the top of the code window:
Using System.Data.OleDb;

6. Add the following code to the Button1_Click event:
MSDASC. DataLinks Mydlg = new MSDASC. DataLinks ();
OleDbConnection olecon = new OleDbConnection ();
Adodb._connection ADOcon;

Cast the generic object, PromptNew returns to a 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");
}

7. Press the F5 key to compile and run the project, and then click Button1.
8. Type the appropriate information in the Data Link Properties dialog box, and make sure that the Allow Password Save check box is selected.
9. Click Test Connection.
10. Click OK. If the connection test for the data link succeeds, a connection is made to the database and a message box is displayed.
Back to the top of the page
Other information
Creating an ODBC connection using this method also requires some work because the connection string created by the data link is specific to OLE DB and is incompatible with the ODBC managed provider. To do this, you must analyze information about the ADODB connection string, such as the user ID, password, and data source. After you obtain this information, you can use this information to create an ODBC-specific connection string. Remember that data links use only ODBC data source names (DSNs), so you cannot create a DSN-free connection through a data link.

Back to the top of the page
Reference
For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
286189 Howto:invoke The OLE DB data link Properties Dialog Box in Visual Basic code (HOWTO: Invoking an OLE DB chain in Visual Basic code Connect Properties dialog box)
283245 Howto:persist Data Links Programmatically (HOWTO: Save a link in a database programmatically)
193128 howto:create an ODBC and OLE DB Connection Prompt control in ADO (HOWTO: Create ODBC and OLE DB connection prompt controls in ADO)