MSDN Documentation

Working with Data in .NET

This section provides a comprehensive guide to accessing and manipulating data within the .NET ecosystem. Whether you're working with relational databases, NoSQL stores, or cloud-based services, .NET offers robust and efficient tools.

ADO.NET: The Foundation of Data Access

ADO.NET is a set of classes that expose data access services to .NET programmers. It provides a consistent interface for accessing data sources such as SQL Server and XML, as well as data sources exposed by an application.

Connections

Establishing a connection to your data source is the first step. This involves using connection strings to specify server details, authentication credentials, and database names.

using System.Data.SqlClient;

// ...

string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // ... perform operations
}

Commands

Commands are used to execute SQL statements (like SELECT, INSERT, UPDATE, DELETE) or stored procedures against your database.

using System.Data.SqlClient;

// ...

using (SqlCommand command = new SqlCommand("SELECT COUNT(*) FROM MyTable", connection))
{
    int count = (int)command.ExecuteScalar();
    Console.WriteLine($"Number of records: {count}");
}

DataReaders

SqlDataReader (or its equivalent for other providers) is an efficient way to retrieve data from a database in a forward-only, read-only manner. It's ideal for large result sets where you don't need to load all data into memory.

using System.Data.SqlClient;

// ...

using (SqlCommand command = new SqlCommand("SELECT Id, Name FROM MyTable", connection))
using (SqlDataReader reader = command.ExecuteReader())
{
    while (reader.Read())
    {
        int id = reader.GetInt32(0);
        string name = reader.GetString(1);
        Console.WriteLine($"ID: {id}, Name: {name}");
    }
}

DataSets

DataSet is an in-memory representation of data. It can hold multiple tables, relationships, and constraints, allowing for disconnected data manipulation.

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

// ...

DataTable dataTable = new DataTable();
using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM MyTable", connection))
{
    adapter.Fill(dataTable);
}

foreach (DataRow row in dataTable.Rows)
{
    Console.WriteLine($"Row: {row["Name"]}");
}

Entity Framework Core (EF Core): The Modern ORM

Entity Framework Core is a powerful, open-source, cross-platform object-relational mapper (ORM) for .NET. It enables developers to work with databases using .NET objects instead of raw SQL statements.

DbContext

The DbContext class is the primary class that interfaces with your database. It represents a session with the database and allows you to query and save data.

using Microsoft.EntityFrameworkCore;

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=BloggingEFCore;Trusted_Connection=True;");
    }
}

Migrations

Migrations are EF Core's way of evolving your database schema to match your C# model. You can generate and apply migrations to create or update your database.

// In Package Manager Console:
// Add-Migration InitialCreate
// Update-Database

Querying Data

EF Core allows you to query data using LINQ (Language Integrated Query).

using (var db = new BloggingContext())
{
    var blogs = db.Blogs.Where(b => b.Rating > 3).OrderBy(b => b.Url).ToList();
    foreach (var blog in blogs)
    {
        Console.WriteLine($"Blog: {blog.Url}");
    }
}

Saving Data

Adding, updating, and deleting entities is straightforward with DbContext.

using (var db = new BloggingContext())
{
    // Add
    db.Add(new Blog { Url = "http://blogs.msdn.com/adonet" });
    db.SaveChanges();

    // Update
    var blogToUpdate = db.Blogs.FirstOrDefault(b => b.Url == "http://blogs.msdn.com/adonet");
    if (blogToUpdate != null)
    {
        blogToUpdate.Url = "http://blogs.msdn.com/entityframework";
        db.SaveChanges();
    }

    // Delete
    var blogToDelete = db.Blogs.FirstOrDefault(b => b.Url == "http://blogs.msdn.com/entityframework");
    if (blogToDelete != null)
    {
        db.Remove(blogToDelete);
        db.SaveChanges();
    }
}

Other Data Access Technologies

Beyond ADO.NET and EF Core, .NET supports various other data access patterns and libraries, including:

• Dapper: A popular micro-ORM that offers excellent performance.
• LINQ to SQL: An older ORM that is part of .NET Framework but less recommended for new projects compared to EF Core.
• Data Access Components for specific cloud providers (e.g., Azure Cosmos DB SDK, AWS SDK for .NET).

Best Practices for Data Access

To ensure your applications are performant, secure, and maintainable, consider these best practices:

• Use Parameterized Queries: Always use parameterized queries to prevent SQL injection vulnerabilities.
• Dispose of Resources: Ensure that database connections, commands, and readers are properly disposed of (using using statements is highly recommended).
• Optimize Queries: Write efficient SQL queries and leverage indexing in your database.
• Choose the Right Technology: Select the data access technology that best suits your project's needs (e.g., ADO.NET for raw performance, EF Core for rapid development and ORM features).
• Handle Concurrency: Implement strategies for managing concurrent data modifications.
• Error Handling: Implement robust error handling for database operations.