SqlClient Namespace

The SqlClient namespace provides classes that allow access to Microsoft SQL Server. It is part of the ADO.NET data provider model, offering high-performance access to SQL Server data.

Introduction

When working with SQL Server databases from a .NET application, the SqlClient namespace is your primary tool. It contains classes that represent SQL Server-specific features and data types, enabling you to connect, query, and manipulate data efficiently.

Key Classes

The SqlClient namespace is comprised of several essential classes, each serving a distinct purpose in data access:

• SqlConnection: Represents an open connection to a SQL Server database.
• SqlCommand: Represents a Transact-SQL statement or stored procedure to execute against a SQL Server data source.
• SqlDataReader: Provides a way of reading a forward-only stream of rows from a SQL Server data source.
• SqlDataAdapter: Represents a set of commands for retrieving rows from a data source and populating a DataSet with those rows, and for sending changes to the data source from the DataSet.
• SqlParameter: Represents a parameter to a SqlCommand and its properties.
• SqlException: Represents the errors that occur during the execution of a SqlCommand.

Connection Management

Establishing and managing connections is fundamental. The SqlConnection class handles this.

To create a connection, you typically provide a connection string.

using System.Data.SqlClient;

// ...

string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // Perform database operations here
} // Connection is automatically closed and disposed here

Command Execution

SqlCommand is used to execute SQL statements or stored procedures. You can associate it with a SqlConnection and specify the command text.

using System.Data.SqlClient;

// ...

string queryString = "SELECT ProductID, ProductName FROM Production.Product;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(queryString, connection);
    connection.Open();
    // Execute the command
}

Executing Queries

For commands that return a result set (like SELECT statements), you typically use ExecuteReader() or ExecuteNonQuery() (for non-query statements like INSERT, UPDATE, DELETE).

Data Readers

SqlDataReader provides a high-performance, forward-only, read-only stream of data. It's ideal for scenarios where you need to iterate through results without loading the entire dataset into memory.

using System.Data.SqlClient;

// ...

string queryString = "SELECT CustomerID, CompanyName FROM Sales.Customer;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(queryString, connection);
    connection.Open();
    SqlDataReader reader = command.ExecuteReader();
    try
    {
        while (reader.Read())
        {
            Console.WriteLine($"CustomerID: {reader["CustomerID"]}, CompanyName: {reader["CompanyName"]}");
        }
    }
    finally
    {
        reader.Close(); // Always close the reader
    }
}

Parameters

Using parameters is crucial for security (preventing SQL injection) and performance. SqlParameter objects are added to the Parameters collection of a SqlCommand.

using System.Data.SqlClient;

// ...

string queryString = "SELECT ProductID, ProductName FROM Production.Product WHERE ProductID = @ProductID;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(queryString, connection);
    command.Parameters.AddWithValue("@ProductID", 123); // Example parameter
    connection.Open();
    SqlDataReader reader = command.ExecuteReader();
    // ... process reader ...
}

Parameter Data Types

It's best practice to specify the explicit SQL Server data type for parameters:

command.Parameters.Add("@ProductID", SqlDbType.Int).Value = 123;
command.Parameters.Add("@ProductName", SqlDbType.NVarChar, 50).Value = "Road Bike";

Connection Strings

A connection string contains the information required to establish a connection to a data source. For SQL Server, common parameters include:

• Server or Data Source: The name or IP address of the SQL Server instance.
• Database or Initial Catalog: The name of the database to connect to.
• User ID: The username for SQL Server authentication.
• Password: The password for SQL Server authentication.
• Integrated Security=true: Use Windows authentication.

Connection String Examples

SQL Server Authentication:

Server=tcp:your_server.database.windows.net,1433;Initial Catalog=YourDatabase;Persist Security Info=False;User ID=your_username;Password=your_password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Windows Authentication:

Server=.\SQLEXPRESS;Database=MyDatabase;Integrated Security=SSPI;

The SqlClient namespace is a powerful and integral part of ADO.NET for .NET developers interacting with SQL Server.