Data Access with ADO.NET and SQL Server

This document explores how to effectively access and manipulate data stored in Microsoft SQL Server using the ADO.NET framework. ADO.NET provides a rich set of classes for connecting to data sources, executing commands, and retrieving data.

Introduction to ADO.NET

ADO.NET is a set of .NET Framework classes that exposes data access services to the .NET programmer. It is an integral part of the .NET Framework that allows developers to build applications that access data from a variety of data sources, both relational and non-relational.

Core Components for SQL Server Data Access

The primary classes used for interacting with SQL Server in ADO.NET include:

• SqlConnection: Establishes a 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 forward-only, read-only stream of data from the data source. It's highly efficient for retrieving large amounts of data.
• SqlDataAdapter: Acts as a bridge between a DataSet and a data source for retrieving and saving data.
• DataSet: An in-memory representation of data, consisting of tables, rows, and columns.

Connecting to SQL Server

Establishing a connection is the first step. You'll need a connection string that specifies the server name, database name, and authentication details.

using System;
using System.Data.SqlClient;

public class ConnectionExample
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerName;Database=myDatabase;User ID=myUsername;Password=myPassword;";
        
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("Connection opened successfully.");
                // Perform database operations here
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error connecting to database: {ex.Message}");
            }
        }
    }
}
                

Executing Commands and Retrieving Data

Once connected, you can execute SQL commands. The SqlDataReader is an excellent choice for reading data sequentially.

Using SqlDataReader

This method is efficient for reading a large number of rows without loading them all into memory at once.

using System;
using System.Data.SqlClient;

public class ReadDataExample
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerName;Database=myDatabase;User ID=myUsername;Password=myPassword;";
        string query = "SELECT CustomerID, CompanyName FROM Customers";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                try
                {
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine($"CustomerID: {reader["CustomerID"]}, CompanyName: {reader["CompanyName"]}");
                        }
                    }
                    else
                    {
                        Console.WriteLine("No rows found.");
                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Error retrieving data: {ex.Message}");
                }
            }
        }
    }
}
                

Using SqlDataAdapter and DataSet

For scenarios where you need to work with data offline, manipulate it, and then update the database, SqlDataAdapter and DataSet are more suitable.

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

public class DataSetExample
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerName;Database=myDatabase;User ID=myUsername;Password=myPassword;";
        string query = "SELECT ProductID, ProductName, UnitPrice FROM Products";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
            DataSet dataSet = new DataSet();

            try
            {
                adapter.Fill(dataSet, "Products");

                DataTable productsTable = dataSet.Tables["Products"];
                foreach (DataRow row in productsTable.Rows)
                {
                    Console.WriteLine($"ProductID: {row["ProductID"]}, ProductName: {row["ProductName"]}, UnitPrice: {row["UnitPrice"]}");
                }
                
                // Example: Adding a new row (requires INSERT command setup in adapter for Update)
                // DataRow newRow = productsTable.NewRow();
                // newRow["ProductName"] = "New Gadget";
                // newRow["UnitPrice"] = 19.99;
                // productsTable.Rows.Add(newRow);
                // adapter.Update(dataSet, "Products"); // This would require InsertCommand setup
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error working with DataSet: {ex.Message}");
            }
        }
    }
}
                

Parameterized Queries and Security

Always use parameterized queries to prevent SQL injection vulnerabilities. Never concatenate user input directly into SQL strings.

string query = "SELECT CustomerID, CompanyName FROM Customers WHERE Country = @Country";
using (SqlCommand command = new SqlCommand(query, connection))
{
    command.Parameters.AddWithValue("@Country", "USA");
    SqlDataReader reader = command.ExecuteReader();
    // ... read data ...
}
                

Stored Procedures

Executing stored procedures is another common and often more performant way to interact with your database. You can pass parameters to them as well.

string query = "GetCustomersByCountry";
using (SqlCommand command = new SqlCommand(query, connection))
{
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@Country", "Germany");
    
    connection.Open();
    SqlDataReader reader = command.ExecuteReader();
    // ... read data ...
}
                

Conclusion

ADO.NET provides a robust and flexible way to interact with SQL Server. By understanding the core components and best practices like using using statements and parameterized queries, developers can build secure and efficient data-driven applications.