SqlConnection Class

Namespace: System.Data.SqlClient

Assembly: System.Data.dll

The SqlConnection class represents a unique session to a SQL Server data source. This class is inherited from DbConnection.

Overview

The SqlConnection object is used to establish a connection to a SQL Server database. It provides properties and methods to manage the connection state, execute commands, and retrieve data. Key aspects include:

Connection String: Specifies parameters for connecting to the data source, such as server name, database name, and authentication details.
Connection State: Can be Closed or Open.
Transactions: Supports transactional operations for data integrity.
Command Execution: Used in conjunction with SqlCommand to execute T-SQL statements and stored procedures.

Public Constructors

Constructor	Description
`SqlConnection()`	Initializes a new instance of the `SqlConnection` class.
`SqlConnection(string connectionString)`	Initializes a new instance of the `SqlConnection` class with a specified connection string.

Public Properties

Property	Description
`ConnectionString`	Gets or sets the connection string used to open the SQL Server data source.
`ConnectionTimeout`	Gets the time in seconds to wait for a connection to be established before terminating the attempt.
`Database`	Gets the name of the database to connect to.
`ServerVersion`	Gets the version of the connected SQL Server.
`State`	Gets the current state of the connection (`ConnectionState.Closed` or `ConnectionState.Open`).

Public Methods

Method	Description
`Open()`	Opens a database connection with the properties and values specified by the `ConnectionString`.
`Close()`	Closes the connection to the data source.
`BeginTransaction()`	Begins a database transaction.
`ChangeDatabase(string value)`	Changes the current database for the connection.
`CreateCommand()`	Creates and returns a `SqlCommand` object associated with the connection.

Example Usage

Connecting to SQL Server and Executing a Query


using System;
using System.Data.SqlClient;

public class Sample
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("Connection opened successfully.");

                // Create a command object
                string sql = "SELECT COUNT(*) FROM Customers;";
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    // Execute the query and get the count
                    int customerCount = (int)command.ExecuteScalar();
                    Console.WriteLine($"Number of customers: {customerCount}");
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine($"Error connecting to database: {e.Message}");
            }
            finally
            {
                if (connection.State == System.Data.ConnectionState.Open)
                {
                    connection.Close();
                    Console.WriteLine("Connection closed.");
                }
            }
        }
    }
}

Important Considerations:

Always use using statements for SqlConnection and SqlCommand to ensure proper disposal of resources.
Never hardcode sensitive credentials directly in your code. Use configuration files or secure credential management systems.
Handle SqlException to gracefully manage potential database errors.
For improved security and performance, consider using parameterized queries with SqlCommand to prevent SQL injection vulnerabilities.

Microsoft Docs