How to Connect to SQL Database on C#

  2. HowTo
  3. C# Howtos
  4. How to Connect to SQL Database on C#
Fil Zjazel Romaeus Villegas Feb 02, 2024
Csharp Csharp SQL
How to Connect to SQL Database on C#

This tutorial will demonstrate how to connect to an SQL database on C# using the SqlConnection object.

Use the SqlConnection Object to Connect to a SQL Database on C#

A SqlConnection class is an object that represents a connection to an SQL Server database as specified by the passed connection string. It is included in the namespace System.Data.SqlClient.

SqlConnection connection = new SqlConnection(connectionString);

A connection string contains the information about a data source, how to connect to it, and the connection configuration details. You can include many different parameters to the connection string, but we’ll discuss some of the most commonly used ones.

  1. Server / Data Source: The server’s name holding the database.
  2. Database / Initial Catalog: This is the database’s name.
  3. Trusted Connection / Integrated Security: Specifies if the application can use any available security packages on a system. If this is set to true, the User ID and Password parameters are not required.
  4. User ID: The username for the connection.
  5. Password: The password to be used for the connection.

Once you pass the connection string to the SqlConnection object, you can manage the connection using its methods.

  1. Open(): Opens the connection.
  2. Close(): Closes the connection.
  3. Dispose(): Releases the resources used by the connection.
  4. ChangeDatabase(): Changes the current database for an open SqlConnection.

Example:

using System;
using System.Data.SqlClient;

namespace SQLConnection_Sample {
  class Program {
    static void Main(string[] args) {
      // The server's name that holds the database
      string DataSource = "MSI\\SQLEXPRESS";

      // The name of the database
      string InitialCatalog = "SampleDB";

      // Sets if the connection should use integrated security.
      // If this value is set to "SSPI", the user's Windows Authentication will be used
      string IntegratedSecurity = "SSPI";

      // Should the database require a specific log in
      string UserID = "";
      string Password = "";

      string connectionString = "Data Source =" + DataSource +
                                "; Initial Catalog =" + InitialCatalog +
                                "; Integrated Security=" + IntegratedSecurity
          //+ "; User ID=" + UserID
          //+ "; Password=" + Password
          ;

      try {
        SqlConnection connection = new SqlConnection(connectionString);
        connection.Open();
        Console.WriteLine("The database has been opened!");
        Console.WriteLine("Connection State: " + connection.State.ToString());

        connection.Close();
        Console.WriteLine("The database has been closed!");

        connection.Dispose();
        Console.WriteLine("The database connection has been disposed!");
        Console.WriteLine("Connection State: " + connection.State.ToString());
      } catch (Exception ex) {
        Console.WriteLine("There's an error connecting to the database!\n" + ex.Message);
      }

      Console.ReadLine();
    }
  }
}

In the example above, we first created the connection string by inputting the server, database name, and integrated security parameters. After passing it to the SqlConnection object, we demonstrated the different states by opening, closing, and finally disposing of the connection.

All of this is printed in the console.

Output:

The database has been opened!
Connection State: Open
The database has been closed!
The database connection has been disposed!
Connection State: Closed

Related Article - Csharp SQL