SQL Databases in .NET

Note: This document provides a high-level overview of working with SQL databases within the .NET ecosystem. For detailed API references and advanced scenarios, please refer to the specific documentation for ADO.NET and Entity Framework Core.

Introduction
Working with ADO.NET
Using Entity Framework Core
Best Practices
Conclusion

Introduction

Microsoft's .NET platform offers robust and flexible tools for interacting with SQL databases. Whether you're building web applications, desktop software, or services, managing data effectively is crucial. .NET provides two primary approaches for SQL database interaction: ADO.NET for direct, low-level control and Entity Framework Core (EF Core) for a higher-level, object-relational mapping (ORM) experience.

This section will guide you through the fundamental concepts and common practices for utilizing SQL databases with .NET.

Working with ADO.NET

ADO.NET is a set of classes that expose data access services to .NET programmers. It provides a way to connect to data sources, execute commands, and retrieve results. It's highly efficient and gives developers fine-grained control over SQL statements and data manipulation.

Key Components of ADO.NET:

Connections: Establishes a link to the data source (e.g., SqlConnection for SQL Server).
Commands: Represents an SQL statement or stored procedure to be executed against the data source (e.g., SqlCommand).
DataReaders: Provides a forward-only, read-only stream of data from the data source (e.g., SqlDataReader). Ideal for performance-critical scenarios where you only need to read data.
DataAdapters: Used to fill DataSets or DataTables and to resolve changes made to these objects back to the data source.
DataSets/DataTables: In-memory representation of data, allowing for disconnected data manipulation.

Example: Executing a Simple Query

C#
using System;
using System.Data.SqlClient;

public class AdonetExample
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
        string query = "SELECT TOP 10 CustomerId, CompanyName FROM Customers;";

        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($"{reader["CustomerId"]} - {reader["CompanyName"]}");
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Error: {ex.Message}");
                }
            }
        }
    }
}
                

Using Entity Framework Core

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

Key Concepts in EF Core:

DbContext: The primary class that represents a session with the database and allows you to query and save data.
DbSet: Represents a collection of all entities in the store and can be used to query the database.
Entities: Plain Old CLR Objects (POCOs) that represent tables in your database.
Migrations: A feature that allows you to incrementally update your database schema to match your entity model.
LINQ to Entities: Allows you to write queries against your entities using LINQ syntax, which EF Core translates into SQL.

Example: Querying with EF Core

C#
using System.Linq;
using Microsoft.EntityFrameworkCore;

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

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

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;");
    }
}

public class EfCoreExample
{
    public static void Main(string[] args)
    {
        using (var context = new ApplicationDbContext())
        {
            var expensiveProducts = context.Products
                                           .Where(p => p.Price > 50.00m)
                                           .OrderBy(p => p.Name)
                                           .ToList();

            foreach (var product in expensiveProducts)
            {
                Console.WriteLine($"{product.Name} - ${product.Price}");
            }
        }
    }
}
                

Best Practices

Use parameterized queries: Always use parameterized queries (provided by ADO.NET or handled automatically by EF Core) to prevent SQL injection vulnerabilities.
Close connections promptly: Ensure database connections are properly closed and disposed of using using statements.
Handle exceptions: Implement robust error handling for database operations.
Optimize queries: Write efficient SQL queries and leverage indexing in your database.
Choose the right tool: Use ADO.NET for maximum control and performance when needed, and EF Core for rapid development and object-oriented data access.
Keep EF Core up-to-date: Stay current with EF Core releases for performance improvements and new features.

Conclusion

Effectively interacting with SQL databases is a cornerstone of many .NET applications. By understanding and leveraging ADO.NET for direct control and Entity Framework Core for object-oriented data access, developers can build robust, performant, and secure data-driven solutions.

Explore the detailed documentation for ADO.NET and EF Core to further enhance your data access capabilities.

MSDN Documentation