Establishing the Connection (ADO.NET)

To connect to Microsoft SQL Server 7.0 or later, use the SqlConnection object of the .NET Framework Data Provider for SQL Server. To connect to an OLE DB data source, or to Microsoft SQL Server 6.x or earlier, use the OleDbConnection object of the .NET Framework Data Provider for OLE DB. To connect to an ODBC data source, use the OdbcConnection object of the .NET Framework Data Provider for ODBC. To connect to an Oracle data source, use the OracleConnection object of the .NET Framework Data Provider for Oracle. For securely storing and retrieving connection strings, see Protecting Connection Information (ADO.NET).

Closing Connections

We recommend that you always close the connection when you are finished using it, so that the connection can be returned to the pool. The Using block in Visual Basic or C# automatically disposes of the connection when the code exits the block, even in the case of an unhandled exception. See using Statement (C# Reference) and Using Statement (Visual Basic) for more information.

You can also use the Close or Dispose methods of the connection object for the provider that you are using. Connections that are not explicitly closed might not be added or returned to the pool. For example, a connection that has gone out of scope but that has not been explicitly closed will only be returned to the connection pool if the maximum pool size has been reached and the connection is still valid. For more information, see OLE DB, ODBC, and Oracle Connection Pooling (ADO.NET).

Connecting to SQL Server

The .NET Framework Data Provider for SQL Server supports a connection string format that is similar to the OLE DB (ADO) connection string format. For valid string format names and values, see the ConnectionString property of the SqlConnection object. You can also use the SqlConnectionStringBuilder class to create syntactically valid connection strings at run time. For more information, see Connection String Builders (ADO.NET).

The following code example demonstrates how to create and open a connection to a SQL Server 7.0 or later database.

' Assumes connectionString is a valid connection string.
Using connection As New SqlConnection(connectionString)
    connection.Open()
    ' Do work here.
End Using

// Assumes connectionString is a valid connection string.
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // Do work here.
}

Integrated Security and ASP.NET

SQL Server integrated security (also known as trusted connections) helps to provide protection when connecting to SQL Server as it does not expose a user ID and password in the connection string and is the recommended method for authenticating a connection. Integrated security uses the current security identity, or token, of the executing process. For desktop applications, this is typically the identity of the currently logged-on user.

The security identity for ASP.NET applications can be set to one of several different options. To better understand the security identity that an ASP.NET application uses when connecting to SQL Server, see ASP.NET Impersonation, ASP.NET Authentication, and How to: Access SQL Server Using Windows Integrated Security.

Connecting to an OLE DB Data Source

The .NET Framework Data Provider for OLE DB provides connectivity to data sources exposed using OLE DB and to Microsoft SQL Server 6.x or earlier (through SQLOLEDB, the OLE DB Provider for SQL Server), using the OleDbConnection object.

For the .NET Framework Data Provider for OLE DB, the connection string format is identical to the connection string format used in ADO, with the following exceptions:

• The Provider keyword is required.

• The URL, Remote Provider, and Remote Server keywords are not supported.

For more information about OLE DB connection strings, see the ConnectionString topic. You can also use the OleDbConnectionStringBuilder to create connection strings at run time.

The following code example demonstrates how to create and open a connection to an OLE DB data source.

' Assumes connectionString is a valid connection string.
Using connection As New OleDbConnection(connectionString)
    connection.Open()
    ' Do work here.
End Using

// Assumes connectionString is a valid connection string.
using (OleDbConnection connection = 
  new OleDbConnection(connectionString))
{
    connection.Open();
    // Do work here.
}

Do Not Use Universal Data Link Files

It is possible to supply connection information for an OleDbConnection in a Universal Data Link (UDL) file; however you should avoid doing so. UDL files are not encrypted, and expose connection string information in clear text. Because a UDL file is an external file-based resource to your application, it cannot be secured using the .NET Framework.

Connecting to an ODBC Data Source

The .NET Framework Data Provider for ODBC provides connectivity to data sources exposed using ODBC using the OdbcConnection object.

For the .NET Framework Data Provider for ODBC, the connection string format is designed to match the ODBC connection string format as closely as possible. You may also supply an ODBC data source name (DSN). For more detail on the OdbcConnection , see the OdbcConnection Class.

The following code example demonstrates how to create and open a connection to an ODBC data source.

' Assumes connectionString is a valid connection string.
Using connection As New OdbcConnection(connectionString)
    connection.Open()
    ' Do work here.
End Using

// Assumes connectionString is a valid connection string.
using (OdbcConnection connection = 
  new OdbcConnection(connectionString))
{
    connection.Open();
    // Do work here.
}

Connecting to an Oracle Data Source

The .NET Framework Data Provider for Oracle provides connectivity to Oracle data sources using the OracleConnection object.

For the .NET Framework Data Provider for Oracle, the connection string format is designed to match the OLE DB Provider for Oracle (MSDAORA) connection string format as closely as possible. For more detail on the OracleConnection, see the OracleConnection Class.

The following code example demonstrates how to create and open a connection to an Oracle data source.

' Assumes connectionString is a valid connection string.
Using connection As New OracleConnection(connectionString)
    connection.Open()
    ' Do work here.
End Using

// Assumes connectionString is a valid connection string.
using (OracleConnection connection = 
  new OracleConnection(connectionString))
{
    connection.Open();
    // Do work here.
}
OracleConnection nwindConn = new OracleConnection("Data Source=MyOracleServer;Integrated Security=yes;");
nwindConn.Open();

See Also

Concepts

OLE DB, ODBC, and Oracle Connection Pooling (ADO.NET)

Other Resources

Connecting to a Data Source (ADO.NET)

Connection Strings (ADO.NET)

ADO.NET Managed Providers and DataSet Developer Center