Export (0) Print
Expand All

Introduction to ADO.NET Connection Design Tools

To move data between a data store and your application, you must first have a connection to the data store.

Note   Data adapters, data connections, data commands, and data readers are the components that make up a .NET Framework data provider. Microsoft and third-party providers can make available other .NET Framework data providers that can be integrated into Visual Studio. For more information, see .NET Data Providers.

In ADO.NET you create and manage connections using connection objects:

  • SqlConnection - an object that manages a connection to a SQL Server version 7.0 or later. It is optimized for use with SQL Server 7.0 or later by (among other things) bypassing the OLE DB layer.
  • OleDbConnection - an object that manages a connection to any data store accessible via OLE DB.
  • OdbcConnection - an object that manages a connection to a data source created by using a connection string or ODBC data source name (DSN).
  • OracleConnection - an object that manages a connection to Oracle databases.

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, which consists of a string with attribute/value pairs for information required to log on to a database 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 (NT authentication). A connection string can instead include a user id and password, but this is not recommended, because these attributes are then compiled into your application and therefore a potential security breach. For more information, see Access Permissions for Web Applications and Security Model.

Security Note   Storing connection-string details (such as the server name, user name, and password) can have implications for the security of your application. Using Windows Integrated Security is a more secure way to control access to a database. For more information, see Database Security.

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.) Alternatively, you can also set the ConnectionString property to the path of a Microsoft Data Link (.udl) file. For more information about data link files, see Data Link API Overview.

Note   The SQLConnection object does not support — or allow you to specify — the Provider attribute.

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 the connection down. 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 data adapters or data commands, you do not have to explicitly open and close a connection. When you call a method of these objects (for example, the data 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, 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 data 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, in ASP.NET Web applications 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 Connection Pooling for the .Net Framework Data Provider for SQL Server.

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 Performing Transactions.

Configurable Connection Properties

In many applications, connection information cannot be determined at design time. For example, in an application that will be distributed to many different customers, you might not be able to determine at design time the connection information (such as the name of a server).

Connection strings are therefore often specified as dynamic properties. Because dynamic properties are stored in a configuration file (and not compiled into the application binary files), they can be changed without having to recompile the application.

A typical strategy is to specify connection properties as dynamic properties, provide a way (such as a Windows Form or Web Form page) for users to specify the relevant details, and then update the configuration file. The dynamic-property mechanism built into the .NET Framework automatically gets the values from the configuration file when the property is read, and updates the file when the value is updated.

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 the server name, user name, and password) can have implications for the security of your application. Using Windows Integrated Security is a more secure way to control access to a database. For more information, see Database Security.

The ASP.NET Web application binary files and configuration file are secured from Web access by the inherent ASP.NET security model, which prevents access to these files using any Internet protocol (HTTP, FTP, and so on). To prevent access to the Web server computer itself via other methods (for example, from within your internal network), you use the security features of Windows.

In intranet or two-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 are used in the connection string. (Typically, permissions are established on the database server computer via 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 topics:

Design-Time Connections in Server Explorer

Server 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 you create this way. Generally, the information provided by a design-time connection is used to set properties for a new connection object that you add to your application.

For example, at design time you might use Server Explorer to create a connection to the SQL server MyServer and the database Northwind. Later, when designing a form, you can browse the Northwind database, select columns from a table, and drag them onto the form. This creates a data adapter for your form. It also creates a new form-specific connection, copying connection string information from the design-time connection to your new connection. When the application runs, your new connection is used to access the data source.

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 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 and creating design-time connections, see Adding New Data Connections in Server Explorer.

Note   The data connection you create in Server Explorer determines the objects that get created when dragging database elements onto a designer. For example, if you are connecting to a SQL Server at design time, and your data connection was originally set up by selecting the OLE DB Provider for SQL Server, then dragging a table from Server Explorer onto a designer will create a SqlConnection object. If your application requires an ODBC connection, be sure to select the OLE DB Provider for ODBC when creating your data connection in Server Explorer.

Connection Design Tools in Visual Studio

You usually do not need to directly create and manage connection objects in Visual Studio. When you use tools such as the Data Adapter Wizard, the tools typically prompt you for connection information (that is, connection-string information) and automatically create connection objects for you on the form or component you are working with.

However, if you want, you can add connection objects yourself to a form or component and set their properties. This is useful if you are not working with datasets (and therefore not with data adapters), but instead simply reading data. You might create connection objects yourself if you will be using transactions.

See Also

Creating ADO.NET Connection Objects | Connecting to a Data Source Using ADO.NET | Connection Pooling for the .NET Framework Data Provider for SQL Server

Show:
© 2015 Microsoft