When connecting to SQL Server use the Microsoft OLE DB Provider for SQL Server.

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.

There are two ways to connect to SQL Server:

• Visually, with design-time tools.
• Programmatically.

Connecting to SQL Server in Server Explorer

Establishing a connection to SQL Server in Server Explorer allows you to easily create SqlConnection, SqlDataAdapter, and SqlCommand objects by dragging data objects from Server Explorer onto forms or designers.

To create a connection in Server Explorer

1. In Server Explorer, right-click Data Connections and click Add Connection.

   The Data Link Properties dialog box opens.

2. The default provider is Microsoft OLE DB Provider for SQL Server.
3. Select a server name from the drop-down list, or type the location of the server where the database you want to access is located.

   Note   Selecting the database on the server is a separate action. Refresh the list by clicking Refresh.

4. Based on the requirements of your database or application, select either Windows NT Integrated security or use a specific user name and password to log on to the SQL Server. For more information, see Data Link Properties Dialog Box.
5. Select the database you want to connect to from the drop-down list.
6. Click OK.

Connecting to SQL Server from within your application

Creating connections to data sources from within your application can be accomplished visually using Visual Studio .NET designers, or programmatically by creating data connection objects in code.

Connecting to SQL Server Visually

Visual Studio supports creating data objects by dragging items from Server Explorer or the Data tab of the Toolbox onto your form or designer.

To create a connection from Server Explorer

1. Create a Data Connection in Server Explorer to your SQL Server. For more information, see the above section titled "Connecting to SQL Server in Server Explorer."
2. Drag the connection onto your form or designer.

   A SqlConnection object appears in the component tray.

3. Advance to the section "Next Steps" later in this topic.

To create a connection from the Data tab of the Toolbox

1. Drag a SqlConnection onto your form or designer.

   An unconfigured SqlConnection object appears in the component tray.

2. In the Properties window, click the ConnectionString property.
3. Select an existing connection in the drop-down list, or click New Connection to open the Data Link Properties Dialog Box and configure a new connection.
4. Advance to the section "Next Steps" later in this topic.

Connecting to SQL Server Programmatically

You can create a SqlConnection object directly in code.

To programmatically create a connection between your application and a SQL Server database

• The following code creates a SqlConnection object, sets the SqlConnection.ConnectionString property, and opens the connection.
  ' Visual Basic
  Public Sub ConnectToSql()
      Dim conn As New SqlClient.SqlConnection
      ' TODO: Modify the connection string and include any
      ' additional required properties for your database.
      conn.ConnectionString = & _
      "integrated security=SSPI;data source=SQL Server Name;" & _
      "persist security info=False;initial catalog=northwind"
      Try
          conn.Open()
          ' Insert code to process data.
      Catch ex As Exception
          MessageBox.Show("Failed to connect to data source")
      Finally
          conn.Close()
      End Try
  End Sub
  
  // C#
  public void ConnectToSql ()
  {
      System.Data.SqlClient.SqlConnection conn = 
          new System.Data.SqlClient.SqlConnection ();
      // TODO: Modify the connection string and include any
      // additional required properties for your database.
      conn.ConnectionString = 
       "integrated security=SSPI;data source=SQL Server Name;" +
       "persist security info=False;initial catalog=northwind";
      try
      {
          conn.Open();
          // Insert code to process data.
      }
          catch (Exception ex)
      {
          MessageBox.Show("Failed to connect to data source");
      }
      finally
      {
          conn.Close();
      }
  }

Next Steps

Now that you have established a connection between your application and the SQL Server database, you can create objects to fetch and update data. The following table provides links to some relevant topics.

See Also

Connecting to Data Sources with ADO.NET | Adding New Data Connections in Server Explorer | Databases in Server Explorer | SQL Server Databases | Visual Database Tools | Data Link Properties Dialog Box