Database Connectivity in C#

Database connectivity in C# allows you to interact with databases to perform operations such as querying, inserting, updating, and deleting data. Understanding how to connect to and manipulate databases is crucial for building data-driven applications. This guide will explain the key concepts and provide examples to help you master database connectivity in C#.

1. ADO.NET

ADO.NET (ActiveX Data Objects .NET) is a set of classes that provide data access services to .NET developers. It is the foundation for database connectivity in C#. ADO.NET provides a consistent model for accessing and manipulating data from different data sources.

Example: Connecting to a SQL Server Database

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            Console.WriteLine("Connected to the database.");
        }
    }
}

2. Connection Strings

A connection string is a string that contains the information required to connect to a database. It typically includes the server address, database name, user credentials, and other parameters. Connection strings are used by ADO.NET to establish a connection to the database.

Example: Connection String for SQL Server

string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";

3. Data Providers

Data providers are components that implement the interfaces defined by ADO.NET. They provide the actual connection to the database and the methods for executing commands. Common data providers include SQL Server Data Provider, OLE DB Data Provider, and ODBC Data Provider.

Example: Using SQL Server Data Provider

using System.Data.SqlClient;

SqlConnection connection = new SqlConnection(connectionString);

4. Commands and Parameters

Commands are used to execute SQL statements or stored procedures against a database. Parameters are used to pass values to SQL statements or stored procedures, which helps prevent SQL injection attacks.

Example: Executing a SQL Command with Parameters

using (SqlCommand command = new SqlCommand("SELECT * FROM Customers WHERE Country = @Country", connection))
{
    command.Parameters.AddWithValue("@Country", "USA");
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine(reader["CustomerName"]);
        }
    }
}

5. Data Readers

Data readers are used to retrieve data from a database in a forward-only, read-only manner. They are efficient for reading large amounts of data because they do not require the entire result set to be loaded into memory.

Example: Using SqlDataReader

using (SqlDataReader reader = command.ExecuteReader())
{
    while (reader.Read())
    {
        Console.WriteLine(reader["CustomerName"]);
    }
}

6. Data Adapters

Data adapters are used to fill a DataSet or DataTable with data from a database and to update the database with changes made to the DataSet or DataTable. They act as a bridge between the database and the in-memory data representation.

Example: Using SqlDataAdapter

using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Customers", connection))
{
    DataTable table = new DataTable();
    adapter.Fill(table);
    foreach (DataRow row in table.Rows)
    {
        Console.WriteLine(row["CustomerName"]);
    }
}

7. Transactions

Transactions are used to group a set of database operations into a single unit of work. If any operation fails, the entire transaction can be rolled back, ensuring data integrity. Transactions are essential for maintaining consistency in multi-step operations.

Example: Using Transactions

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    SqlTransaction transaction = connection.BeginTransaction();
    try
    {
        SqlCommand command = new SqlCommand("INSERT INTO Customers (CustomerName) VALUES (@CustomerName)", connection, transaction);
        command.Parameters.AddWithValue("@CustomerName", "John Doe");
        command.ExecuteNonQuery();
        transaction.Commit();
        Console.WriteLine("Transaction committed.");
    }
    catch (Exception)
    {
        transaction.Rollback();
        Console.WriteLine("Transaction rolled back.");
    }
}