Edit

Share via

Establishing connection

  • Article

Applies to: .NET Framework .NET .NET Standard

Download ADO.NET

To connect to Microsoft SQL Server, use the SqlConnection object of the Microsoft SqlClient Data Provider for SQL Server. For securely storing and retrieving connection strings, see Protecting Connection Information.

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 and Using Statement for more information.

You can also use 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 Garbage Collection.

Note

Login and logout events will not be raised on the server when a connection is fetched from or returned to the connection pool, because the connection is not actually closed when it is returned to the connection pool. For more information, see SQL Server Connection Pooling (ADO.NET).

Connecting to SQL Server

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.

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

using Microsoft.Data.SqlClient;

class Program1
{
    static void Main()
    {
        string s = GetConnectionString();

        OpenSqlConnection(s);
        Console.ReadLine();
    }

    private static void OpenSqlConnection(string connectionString)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            Console.WriteLine("ServerVersion: {0}", connection.ServerVersion);
            Console.WriteLine("State: {0}", connection.State);
        }
    }

    static private string GetConnectionString()
    {
        // To avoid storing the connection string in your code, 
        // you can retrieve it from a configuration file, using the 
        // System.Configuration.ConfigurationSettings.AppSettings property 
        return "Data Source=(local);Initial Catalog=AdventureWorks;"
            + "Integrated Security=SSPI;";
    }
}

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 identity 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.

See also