MSDN Documentation

.NET Data Access Concepts

SqlClient Namespace

The System.Data.SqlClient namespace provides classes that allow you to connect to a Microsoft SQL Server database and retrieve data. It offers a highly performant way to access data from SQL Server.

Key Classes in SqlClient

The SqlClient namespace includes several fundamental classes for interacting with SQL Server:

  • SqlConnection: Represents an open connection to a SQL Server data source. It is used to establish a connection to the database. 
    // Example of creating a SqlConnection
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
}
  • SqlCommand: Represents a Transact-SQL statement or stored procedure to execute against a SQL Server data source. 
    // Example of creating a SqlCommand
using (SqlCommand command = new SqlCommand("SELECT COUNT(*) FROM Customers", connection))
{
    int count = (int)command.ExecuteScalar();
    Console.WriteLine($"Number of customers: {count}");
}
  • SqlDataReader: Provides a way of reading a forward-only stream of rows from a SQL Server data source. It is a highly efficient way to retrieve data when you only need to iterate through the results once. 
    // Example of using SqlDataReader
using (SqlCommand command = new SqlCommand("SELECT CustomerID, CompanyName FROM Customers", connection))
using (SqlDataReader reader = command.ExecuteReader())
{
    while (reader.Read())
    {
        Console.WriteLine($"{reader["CustomerID"]}: {reader["CompanyName"]}");
    }
}
  • SqlDataAdapter: Represents a set of commands and a connection that are used to fill a Dataset and manipulate data in the data source. It acts as a bridge between a Dataset and a SQL Server data source.
  • SqlParameter: Represents a parameter to a SqlCommand and its mapping to and from the data source. Using parameters helps prevent SQL injection attacks and improves performance. 
    // Example of using SqlParameter
using (SqlCommand command = new SqlCommand("SELECT * FROM Products WHERE CategoryID = @CategoryID", connection))
{
    SqlParameter categoryIdParam = new SqlParameter("@CategoryID", 5);
    command.Parameters.Add(categoryIdParam);
    // Execute command
}

Benefits of SqlClient

  • Performance: Optimized for SQL Server, offering faster data retrieval and manipulation.
  • Security: Supports parameterized queries to help prevent SQL injection.
  • Rich Functionality: Provides access to SQL Server-specific features like table-valued parameters and MARS (Multiple Active Result Sets).
  • Integration: Seamlessly integrates with other .NET Framework components.

Best Practices

  • Always use using statements for disposable objects like SqlConnection, SqlCommand, and SqlDataReader to ensure resources are properly released.
  • Use parameterized queries to protect against SQL injection vulnerabilities.
  • Close connections as soon as they are no longer needed, or rely on the using statement to manage connection lifetime.
  • Handle exceptions gracefully to manage potential database errors.

Further Reading