Data Source: Managing Connections (ODBC)

Article
09/15/2006

This article applies to the MFC ODBC classes. For information about the MFC DAO classes, see the article Data Access Objects (DAO).

This article explains:

How to configure a data source.
How a multiuser environment affects a data source and its recordsets.
Why you generalize a connection string to a data source.
How to connect to a data source.
How to disconnect from a data source.
How to reuse a CDatabase object.

Connecting to a data source means establishing communications with a DBMS in order to access the data. When you connect to a data source from an application through an ODBC driver, the driver makes the connection for you, either locally or across a network.

You can connect to any data source for which you have an ODBC driver. Users of your application must also have the same ODBC driver for their data source. For more information about redistributing ODBC drivers, see Redistributing ODBC Components to Your Customers in the article ODBC.

Configuring a Data Source

ODBC Administrator is used to configure your data sources. You can also use ODBC Administrator after installation to add or remove data sources. When you create applications, you can either direct your users to the ODBC Administrator to let them add data sources, or you can build this functionality into your application by making direct ODBC installation calls. For more information, see the Setup DLL Function Reference chapter in the ODBC Programmer’s Reference, the article ODBC Administrator, and the online ODBC API Reference help system.

Working in a Multiuser Environment

If multiple users are connected to a data source, they can change data while you are manipulating it in your recordsets. Similarly, your changes may affect other users’ recordsets. For more information about how your updates affect other users, and how their updates affect you, see the articles Recordset: How Recordsets Update Records (ODBC) and Transaction (ODBC).

Generalizing the Connection String

ClassWizard uses a default connection string to establish a connection to a data source. You use this connection to view tables and columns while you are developing your application. However, this default connection string may not be appropriate for your users’ connections to the data source through your application. For example, their data source and the path to its location may be different from the one used in developing your application. In that case, you should re-implement the member function in a more generic fashion and discard ClassWizard’s implementation. For example, use one of the following approaches:

Register and manage the connect strings by using ODBC Administrator.
Remove the data-source name completely. The framework supplies “ODBC” as the data source and ODBC will display a dialog box asking for the data-source name, and any other required connection information.

Supply the data-source name only. ODBC will ask for the user ID and password, if required. For example, before generalizing, the connection string looks like this:

CString CApp1Set::GetDefaultConnect()
{
return "ODBC;DSN=afx;UID=sa;PWD=Fred;";
}

To generalize it, rewrite GetDefaultConnect so that it returns one of the following values:

// Most general case. User must select data source and
// supply user and password
    return "ODBC;";
// User and password required
    return "ODBC;DSN=mydb;";
// Password required
    return "ODBC;DSN=mydb;UID=sa;";
// On most systems, will connect to server w/o any queries to user
    return "ODBC;DSN=mydb;UID=sa;PWD=Fred;";

Connecting to a Specific Data Source

To connect to a specific data source, your data source must already have been configured with ODBC Administrator.

To connect to a specific data source

Construct a CDatabase object.
Call its OpenEx or Open member function.

For more information about how to specify the data source if it is something other than the one you specified with ClassWizard, see or in the Class Library Reference.

Disconnecting from a Data Source

You must close any open recordsets before calling the Close member function of CDatabase. In recordsets associated with the CDatabase object you want to close, any pending AddNew or Edit statements are canceled, and all pending transactions are rolled back.

To disconnect from a data source

Call the CDatabase object’s member function.
Destroy the object unless you want to reuse it.

Reusing a CDatabase Object

You can reuse a CDatabase object after disconnecting from it, whether you use it to reconnect to the same data source or to connect to a different data source.

To reuse a CDatabase object

Close the object’s original connection.
Instead of destroying the object, call its OpenEx or Open member function again.