Connecting to SQL Server Using ADO.NET

The .NET Framework Data Provider for SQL Server provides connectivity to Microsoft SQL Server version 7.0 or later using the SqlConnection object.

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 SqlConnection.ConnectionString Property.

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

Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;" & _
                                                   "Initial Catalog=northwind")
nwindConn.Open()
[C#]
SqlConnection nwindConn = new SqlConnection("Data Source=localhost; Integrated Security=SSPI;" +
                                            "Initial Catalog=northwind");
nwindConn.Open();

Closing the Connection

It is recommended that you always close the Connection when you are finished using it, in order for the connection to be returned to the pool. This can be done using either the Close or Dispose methods of the Connection object. 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.

Note   Do not call Close or Dispose on a Connection, a DataReader, or any other managed object in the Finalize method of your class. In a finalizer, only release unmanaged resources that your class owns directly. If your class does not own any unmanaged resources, do not include a Finalize method in your class definition. For more information, see Programming for Garbage Collection.

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 userid 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, most commonly, 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 will use when connecting to SQL Server using Integrated Security, see Accessing SQL Server Using Windows Integrated Security.

See Also

Using .NET Framework Data Providers to Access Data | SqlConnection Class | System.Data.SqlClient Namespace