.NET Database Access

This section provides comprehensive guidance on how to interact with databases from your .NET applications. We'll cover various approaches, from low-level data manipulation to high-level Object-Relational Mappers (ORMs).

Core Technologies

The .NET ecosystem offers several powerful technologies for database connectivity:

ADO.NET

ADO.NET is a set of classes that expose data access services to the .NET Framework. It provides a consistent interface to access various data sources, including relational databases, XML, and other data sources.

• Connecting to a Database
• Executing Commands
• Reading Data
• DataAdapters and Datasets

Entity Framework Core

Entity Framework (EF) Core is a modern, open-source, cross-platform version of the popular Entity Framework data access technology. It allows developers to work with databases using .NET objects, abstracting away much of the direct SQL interaction.

• Getting Started with EF Core
• Migrations
• LINQ Queries

Other Data Access Libraries

Explore other libraries and best practices:

• Dapper: A popular micro-ORM.
• SQLMetal: For generating entity classes from existing databases.
• Best Practices for Security and Performance.

Connecting to a Database (ADO.NET)

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

using System;
using System.Data.SqlClient; // For SQL Server

public class DatabaseConnector
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerAddress;Database=myDatabase;User Id=myUsername;Password=myPassword;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("Connection successful!");
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"Error connecting to database: {ex.Message}");
            }
        }
    }
}
        

Executing Commands (ADO.NET)

Once connected, you can execute SQL commands like INSERT, UPDATE, DELETE, and SELECT.

using System.Data.SqlClient;

// ... inside a method after opening connection ...

string query = "INSERT INTO Products (Name, Price) VALUES (@Name, @Price)";
using (SqlCommand command = new SqlCommand(query, connection))
{
    command.Parameters.AddWithValue("@Name", "Awesome Gadget");
    command.Parameters.AddWithValue("@Price", 199.99);

    int rowsAffected = command.ExecuteNonQuery();
    Console.WriteLine($"{rowsAffected} row(s) inserted.");
}
        

Reading Data (ADO.NET)

For SELECT statements, you'll typically use a SqlDataReader to iterate through results.

using System.Data.SqlClient;

// ... inside a method after opening connection ...

string query = "SELECT ProductID, Name, Price FROM Products WHERE Price > @MinPrice";
using (SqlCommand command = new SqlCommand(query, connection))
{
    command.Parameters.AddWithValue("@MinPrice", 100.00);

    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine($"ID: {reader["ProductID"]}, Name: {reader["Name"]}, Price: {reader["Price"]}");
        }
    }
}
        

DataAdapters and Datasets (ADO.NET)

SqlDataAdapter and DataSet are useful for retrieving large amounts of data at once and working with it offline.

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

// ... inside a method after opening connection ...

string query = "SELECT * FROM Customers";
using (SqlDataAdapter adapter = new SqlDataAdapter(query, connection))
{
    DataSet dataSet = new DataSet();
    adapter.Fill(dataSet, "Customers");

    // Access data from the DataTable
    DataTable customersTable = dataSet.Tables["Customers"];
    foreach (DataRow row in customersTable.Rows)
    {
        Console.WriteLine($"Name: {row["CompanyName"]}");
    }
}
        

Getting Started with Entity Framework Core

EF Core simplifies data access by mapping your classes to database tables.

Defining Your Model

public class Product
{
    public int ProductID { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}
        

Configuring the DbContext

using Microsoft.EntityFrameworkCore;

public class AppDbContext : DbContext
{
    public DbSet Products { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // Replace with your actual connection string
        optionsBuilder.UseSqlServer("Server=myServerAddress;Database=myDatabase;User Id=myUsername;Password=myPassword;");
    }
}
        

Performing Operations

using (var context = new AppDbContext())
{
    // Add a new product
    context.Products.Add(new Product { Name = "Super Widget", Price = 49.99M });
    context.SaveChanges();

    // Query products
    var expensiveProducts = context.Products.Where(p => p.Price > 50.00M).ToList();
    foreach (var product in expensiveProducts)
    {
        Console.WriteLine(product.Name);
    }
}
        

Migrations (EF Core)

EF Core Migrations allow you to evolve your database schema over time in sync with your application's data model.

To add a migration:

dotnet ef migrations add InitialCreate
        

To apply migrations to the database:

dotnet ef database update
        

LINQ Queries with EF Core

Language Integrated Query (LINQ) makes querying data concise and powerful.

// Find products containing "Widget" in their name
var widgets = context.Products.Where(p => p.Name.Contains("Widget")).ToList();

// Order products by price descending
var sortedProducts = context.Products.OrderByDescending(p => p.Price).ToList();

// Select specific properties
var productNamesAndPrices = context.Products.Select(p => new { p.Name, p.Price }).ToList();
        

For more advanced topics like transactions, stored procedures, and different database providers, please refer to the official ADO.NET documentation and the Entity Framework Core documentation.