When using Visual Studio, connections are configured using the Add/Modify Connection Dialog Box (General). The Add Connection dialog box appears when you are editing or creating connections within one of the data wizards or Server Explorer/Database Explorer or when you are editing connection properties in the Properties window.

Data connections are automatically configured when you perform one of the following actions:

All connection objects expose roughly the same members. However, the specific members available with a given OleDbConnection object depend on what data source it is connected to; not all data sources support all members of the OleDbConnection class.

The primary property associated with a connection object is the ConnectionString property. This property consists of a string with attribute/value pairs for information required to log on to a database server and point to a specific database. A typical ConnectionString property might look like the following:

Provider=SQLOLEDB.1;Data Source=MySQLServer;Initial Catalog=NORTHWIND;Integrated Security=SSPI

This particular connection string specifies that the connection should use Windows integrated security. A connection string can instead include a user name and password, but this is not recommended, because these attributes are then compiled into your application and therefore a potential security breach.

Security Note:
Storing connection-string details (such as a password) can affect the security of your application. Using Windows integrated security is a more secure way to control access to a database. For more information, see Protecting Connection Information (ADO.NET).

The most common attribute/value pairs used by OLE DB are also represented separately by an individual property, such as DataSource and Database. When working with a connection object, you can either set the ConnectionString property as a single string, or you can set individual connection properties. (If your data source requires connection-string values that are not represented by individual properties, then you must set the ConnectionString property.)

Saving and Retrieving Connection Strings

The two primary methods for connections are Open and Close. The Open method uses the information in the ConnectionString property to contact the data source and establish an open connection. The Close method shuts down the connection. Closing connections is essential, because most data sources support only a limited number of open connections, and open connections take up valuable system resources.

If you are working with TableAdapters, DataAdapters, or DataCommands, you do not have to explicitly open and close a connection. When you call a method of these objects (for example, an adapter's Fill or Update method), the method checks whether the connection is already open. If not, the adapter opens the connection, performs its logic, and closes the connection again.

Methods such as Fill only open and close the connection automatically if it is not already open. If the connection is open, then the methods use it but do not close it. This gives you the flexibility to open and close data commands yourself. You might do this if you have multiple adapters that share a connection. In that case, it is inefficient to have each adapter open and close the connection when you call its Fill method. Instead, you can open the connection, call the Fill method of each adapter, and then close the connection when you are done.

Applications often have different users performing the same type of database access. For example, many users might be querying the same database to get the same data. In those cases, the performance of the application can be enhanced by having the application share, or pool, connections to the data source. The overhead of having each user open and close a separate connection can otherwise have an adverse effect on application performance.

If you are using the OleDbConnection, OdbcConnection, or OracleConnection class, connection pooling is handled automatically by the provider, so you do not need to manage it yourself.

If you are using the SqlConnection class, connection pooling is managed implicitly but also provides options that allow you to manage pooling yourself. For more information, see SQL Server Connection Pooling (ADO.NET).

Connection objects support transactions with a BeginTransaction method that creates a transaction object (for example, a SqlTransaction object). The transaction object in turn supports methods that allow you to commit or roll back the transactions.

Transactions are managed in code. For more information, see Transactions and Concurrency (ADO.NET).

The .NET Framework version 2.0 includes a new transaction framework, accessible through the System.Transactions namespace. This framework exposes transactions in a way that is fully integrated in the .NET Framework, including ADO.NET. For more information, see System.Transactions Integration with SQL Server (ADO.NET).

Because opening a connection involves getting access to an important resource — a database — there are often security issues in configuring and working with a connection.

How you secure the application and its access to the data source depends on the architecture of your system. In a Web-based application, for example, users typically get anonymous access to Internet Information Services (IIS) and therefore do not provide security credentials. In that case, your application maintains its own logon information and uses it (rather than any specific user information) to open the connection and access the database.

Security Note:
Storing connection-string details (such as a password) can affect the security of your application. Using Windows integrated security is a more secure way to control access to a database. For more information, see Protecting Connection Information (ADO.NET).

In intranet or multi-tier applications, you can take advantage of the integrated security option provided by Windows, IIS, and SQL Server. In that model, a user's authentication credentials for the local network are also used to access database resources, and no explicit user name or password is used in the connection string. (Typically, permissions are established on the database server computer by means of groups, so that you do not need to establish individual permissions for every user who might access the database.) In this model, you do not need to store logon information for the connection at all, and there are no extra steps required to protect connection string information.

For more information about security, see the following pages:

• Securing ADO.NET Applications

• More Secure File and Data Access in Windows Forms

Server Explorer/Database Explorer provides a way for you to create design-time connections to data sources. This permits you to browse available data sources; display information about the tables, columns, and other elements they contain; and edit and create database elements.

Your application does not directly use the connections available in Server Explorer/Database Explorer. These connections are used by Visual Studio for working with your database at design time. For more information, see Visual Database Tools.

For example, at design time you might use Server Explorer/Database Explorer to create a connection to a database. Later, when designing a form, you can browse the database, select columns from a table, and drag them onto the Dataset Designer. This creates a TableAdapter in your dataset. It also creates a new connection object (which is part of the newly created TableAdapter).

Information about design-time connections is stored on your local computer independently of a specific project or solution. Therefore, once you have established a design-time connection while working in an application, it appears in Server Explorer/Database Explorer whenever you work in Visual Studio (as long as the server to which the connection points is available). For more information about using Server Explorer/Database Explorer and creating design-time connections, see How to: Add New Data Connections in Server Explorer/Database Explorer.

Tasks

Other Resources