SqlConnection Class (System.Data) | .NET Framework

Namespace:

Assembly:

System.Data.dll

MarshalByRefObject → Component → SqlConnection

Inheritance

Object → Component → SqlConnection

Remarks

The SqlConnection class represents a unique session to a SQL Server data source. This class is used to configure and execute SQL statements and commands against the SQL Server database.

This class is inherited from DbConnection.

The SqlConnection object can be created using the default constructor or by passing a connection string as a parameter.

SqlConnection connection = new SqlConnection("Data Source=server;Initial Catalog=database;User ID=user;Password=password;");

Constructors

SqlConnection()

Initializes a new instance of the SqlConnection class.

SqlConnection(string connectionString)

Initializes a new instance of the SqlConnection class with the specified connection string.

The connection string specifies the properties required to connect to the data source, such as the server name, database name, and authentication credentials.

Properties

ConnectionString

Gets or sets the connection string used to open the SQL Server data source.

public string ConnectionString { get; set; }

ConnectionTimeout

Gets the time-out period (in seconds) before the attempt to establish a connection is terminated when trying to execute a Connect command.

public int ConnectionTimeout { get; }

Database

Gets the name of the database to which the client is connected.

public string Database { get; }

ServerVersion

Gets a string that represents the version of the connected SQL Server.

public string ServerVersion { get; }

State

Gets the current state of the connection.

public ConnectionState State { get; }

Methods

Open()

Opens a connection to the SQL Server database with the settings specified by the ConnectionString property.

public void Open();

If the connection is already open, this method does nothing. It is recommended to check the State property before calling Open.

Close()

Closes the connection to the data source.

public void Close();

This method releases the connection resources. It is important to close connections when they are no longer needed to prevent resource leaks.

CreateCommand()

Creates and returns a SqlCommand object associated with the SqlConnection.

public SqlCommand CreateCommand();

The returned command can be configured with SQL text and parameters to be executed against the data source.

Example

// Using System.Data;
// Using Microsoft.Data.SqlClient; // For newer versions, but we'll stick to System.Data for this example

public class DatabaseHelper
{
    private string connectionString;

    public DatabaseHelper(string connString)
    {
        connectionString = connString;
    }

    public void ExecuteQuery(string query)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("Connection opened successfully.");

                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.ExecuteNonQuery();
                    Console.WriteLine("Query executed successfully.");
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine($"Error: {e.Message}");
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                    Console.WriteLine("Connection closed.");
                }
            }
        }
    }

    public SqlDataReader GetDataReader(string query)
    {
        SqlConnection connection = null;
        try
        {
            connection = new SqlConnection(connectionString);
            connection.Open();
            SqlCommand command = new SqlCommand(query, connection);
            SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); // Important: CloseConnection ensures the connection is closed when the reader is disposed
            return reader;
        }
        catch (SqlException e)
        {
            Console.WriteLine($"Error: {e.Message}");
            if (connection != null && connection.State == ConnectionState.Open)
            {
                connection.Close();
            }
            return null;
        }
    }
}
                