SQL Server and ADO.NET

ADO.NET provides a rich set of components for interacting with data sources, and its integration with Microsoft SQL Server is particularly robust. This document explores the key aspects of using ADO.NET with SQL Server, highlighting features and best practices.

Provider for SQL Server

The primary component for connecting to SQL Server from ADO.NET is the System.Data.SqlClient namespace. This namespace contains classes specifically designed to work with SQL Server, offering optimal performance and access to SQL Server-specific features.

Key Classes in System.Data.SqlClient:

  • SqlConnection: Represents a unique session to a SQL Server database.
  • SqlCommand: Represents a Transact-SQL statement or stored procedure to execute against a SQL Server database.
  • SqlDataReader: Provides a way to read a forward-only stream of rows from a SQL Server database.
  • SqlDataAdapter: Represents a set of commands and a connection to a database that are used to fill a DataSet and maintain the data in the database.
  • SqlCommandBuilder: Automatically generates SQL statements for single-table manipulation operations when using a SqlDataAdapter.

Connecting to SQL Server

Establishing a connection is the first step. The SqlConnection object requires a connection string that specifies the server details, authentication method, and database name.

Example Connection String:

Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;Password=myPassword;

Alternatively, Windows Authentication can be used:

Server=myServerName;Database=myDataBase;Integrated Security=True;

Establishing the Connection:

using System.Data.SqlClient;

public class DatabaseConnector
{
    public void ConnectToSqlServer()
    {
        string connectionString = @"Server=.\SQLEXPRESS;Database=AdventureWorks;Integrated Security=True;";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("Connection to SQL Server opened successfully!");
                // Perform database operations here
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Error connecting to SQL Server: {0}", ex.Message);
            }
        }
    }
}

Executing Commands

Once connected, you can execute SQL commands using the SqlCommand object. This includes SELECT, INSERT, UPDATE, DELETE statements, and stored procedures.

Executing a Query and Reading Data:

using System.Data.SqlClient;

public void ReadDataFromSqlServer()
{
    string connectionString = @"Server=.\SQLEXPRESS;Database=AdventureWorks;Integrated Security=True;";
    string query = "SELECT TOP 5 CustomerID, FirstName, LastName FROM Sales.Customer;";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand(query, connection))
        {
            try
            {
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"ID: {reader["CustomerID"]}, Name: {reader["FirstName"]} {reader["LastName"]}");
                    }
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Error executing query: {0}", ex.Message);
            }
        }
    }
}

Working with DataSets and DataAdapters

For disconnected data scenarios, DataSet and DataAdapter are invaluable. A DataAdapter bridges the gap between a DataSet and the data source, populating the DataSet and synchronizing changes back to the database.

Populating a DataSet:

using System.Data.SqlClient;
using System.Data;

public void PopulateDataSet()
{
    string connectionString = @"Server=.\SQLEXPRESS;Database=AdventureWorks;Integrated Security=True;";
    string query = "SELECT ProductID, Name, ListPrice FROM Production.Product;";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlDataAdapter adapter = new SqlDataAdapter(query, connection))
        {
            using (DataSet dataSet = new DataSet())
            {
                try
                {
                    adapter.Fill(dataSet, "Products");
                    Console.WriteLine($"Successfully loaded {0} rows into the Products table.", dataSet.Tables["Products"].Rows.Count);

                    // Accessing data from DataSet
                    foreach (DataRow row in dataSet.Tables["Products"].Rows)
                    {
                        Console.WriteLine($"Product: {row["Name"]}");
                    }
                }
                catch (SqlException ex)
                {
                    Console.WriteLine("Error populating DataSet: {0}", ex.Message);
                }
            }
        }
    }
}

Stored Procedures

Executing stored procedures is a common and efficient way to interact with SQL Server. SqlCommand can be configured to execute a stored procedure by setting its CommandType property to CommandType.StoredProcedure.

Executing a Stored Procedure:

using System.Data.SqlClient;
using System.Data;

public void ExecuteStoredProcedure()
{
    string connectionString = @"Server=.\SQLEXPRESS;Database=AdventureWorks;Integrated Security=True;";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand("usp_GetCustomerOrders", connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            // Add parameters if the stored procedure requires them
            command.Parameters.AddWithValue("@CustomerID", 100);

            try
            {
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"Order ID: {reader["SalesOrderID"]}");
                    }
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Error executing stored procedure: {0}", ex.Message);
            }
        }
    }
}

Transactions

ADO.NET supports transactions, allowing you to group a series of operations into a single, atomic unit. If any operation within the transaction fails, the entire transaction can be rolled back, ensuring data integrity.

Implementing Transactions:

using System.Data.SqlClient;
using System.Transactions; // For TransactionScope

public void PerformTransactionalOperation()
{
    string connectionString = @"Server=.\SQLEXPRESS;Database=AdventureWorks;Integrated Security=True;";

    using (TransactionScope scope = new TransactionScope())
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            // First operation
            using (SqlCommand command1 = new SqlCommand("UPDATE Production.Product SET ListPrice = ListPrice * 1.10 WHERE ProductID = 1;", connection))
            {
                command1.ExecuteNonQuery();
            }

            // Second operation (could fail)
            using (SqlCommand command2 = new SqlCommand("UPDATE Production.Product SET ListPrice = ListPrice * 0.90 WHERE ProductID = 999;", connection)) // Assume ProductID 999 does not exist
            {
                try
                {
                    command2.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    Console.WriteLine("Error in second operation: {0}", ex.Message);
                    // No need to explicitly rollback, TransactionScope handles it on exception
                    throw; // Re-throw to ensure scope rollback
                }
            }

            // If all operations succeed, commit the transaction
            scope.Complete();
            Console.WriteLine("Transaction committed successfully.");
        }
    }
}

Related Topics