Connecting to Data in Visual Studio Overview
Visual Studio provides tools to connect your application to data from many different sources, such as databases, Web services, and objects. If you are using data design tools in Visual Studio, you often do not need to explicitly create a connection object for your form or component. The connection object is typically created as a result of completing one of the data wizards or of dragging data objects onto your form. To connect your application to data in a database, Web service, or object, run the Data Source Configuration Wizard by selecting Add New Data Source from the Data Sources Window.
The following diagram shows the standard flow of operations when connecting to data by executing a TableAdapter query to fetch data and display it on a form in a Windows application.
.gif "Data flow in a client application")
In some situations, you might find it convenient to create a connection object without the assistance of any data design tools. For information on creating connections programmatically, see Connecting to a Data Source (ADO.NET).
Note
For information on connecting Web Applications to data, see Accessing Data with ASP.NET.
Creating Connections
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:
Action |
Description |
|---|---|
Run the Data Source Configuration Wizard. |
Connections are configured when the database path is chosen in the Data Source Configuration Wizard. For more information, see How to: Connect to Data in a Database. |
Run the TableAdapter Configuration Wizard. |
Connections are created within the TableAdapter Configuration Wizard. For more information, see How to: Create TableAdapters. |
Connections are created within the TableAdapter Query Configuration Wizard. For more information, see How to: Create TableAdapter Queries. |
|
Drag items from the Data Sources Window onto a form or the Component Designer. |
Connection objects are created when you drag items from the Data Sources window onto the Windows Forms Designer or Component Designer. For more information, see Displaying Data on Forms in Windows Applications. |
Add new data connections to Server Explorer/Database Explorer. |
Data connections in Server Explorer/Database Explorer appear in the list of available connections within the data wizards. For more information, see How to: Add New Data Connections in Server Explorer/Database Explorer. |
Connection Strings
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.
.gif "Security note") 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
Connection strings can be stored within your compiled application or in the application configuration file. For more information, see How to: Save a Connection String.
Opening and Closing Connections
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.
Pooling Connections
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).
Transactions
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).
Connection Information and Security
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:
Design-Time Connections in Server Explorer/Database Explorer
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.
See Also
Tasks
How to: Connect to Data in a Database
Walkthrough: Connecting to Data in a Database
Other Resources
Connecting to Data in Visual Studio
Preparing Your Application to Receive Data
Fetching Data into Your Application
Displaying Data on Forms in Windows Applications