SQL Server Integration with .NET

1. Introduction to SQL Server in .NET

Microsoft SQL Server is a powerful relational database management system widely used in enterprise applications. .NET provides first-class support for interacting with SQL Server through libraries like ADO.NET and Entity Framework Core.

Key components for SQL Server integration include:

• System.Data.SqlClient namespace: The core ADO.NET provider for SQL Server.
• Entity Framework Core (EF Core): A modern, object-relational mapper (ORM) that simplifies database interactions.
• SQL Server Integration Services (SSIS): For complex data transformation and workflow tasks (beyond the scope of this basic tutorial).

2. Connecting to SQL Server with ADO.NET

ADO.NET provides a set of classes for connecting to data sources and retrieving data. The SqlConnection class is used to establish a connection to a SQL Server instance.

Establishing a Connection

A connection string is required to specify the server name, database name, and authentication details.

using System.Data.SqlClient;

string connectionString = "Server=your_server_name;Database=your_database_name;User ID=your_username;Password=your_password;";
// For Windows Authentication:
// string connectionString = "Server=your_server_name;Database=your_database_name;Integrated Security=True;";

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

Executing Queries

The SqlCommand class is used to execute SQL commands against the database.

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    string query = "SELECT ProductID, ProductName FROM Products WHERE Category = @Category;";

    using (SqlCommand command = new SqlCommand(query, connection))
    {
        // Use parameters to prevent SQL injection
        command.Parameters.AddWithValue("@Category", "Beverages");

        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                int productId = reader.GetInt32(0); // Index of ProductID
                string productName = reader.GetString(1); // Index of ProductName
                Console.WriteLine($"ID: {productId}, Name: {productName}");
            }
        }
    }
}
                

3. Using Stored Procedures

Stored procedures offer performance benefits and better security by encapsulating SQL logic on the server. You can execute them using SqlCommand.

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (SqlCommand command = new SqlCommand("GetCustomerOrders", connection))
    {
        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.Parameters.AddWithValue("@CustomerID", "ALFKI");

        using (SqlDataReader reader = command.ExecuteReader())
        {
            // Process results
        }
    }
}
                

4. Entity Framework Core for SQL Server

Entity Framework Core (EF Core) is the recommended ORM for modern .NET applications. It allows you to work with your database using C# objects.

Installation

Install the necessary NuGet packages:

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools
                

Defining DbContext and Models

Create your entity classes and a DbContext derived class.

public class Product
{
    public int ProductID { get; set; }
    public string ProductName { get; set; }
    public decimal UnitPrice { get; set; }
}

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

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // Replace with your actual connection string
        optionsBuilder.UseSqlServer("Server=your_server_name;Database=your_database_name;...");
    }
}
                

Performing CRUD Operations

using (var context = new AppDbContext())
{
    // Create
    var newProduct = new Product { ProductName = "New Gadget", UnitPrice = 199.99m };
    context.Products.Add(newProduct);
    context.SaveChanges();

    // Read
    var products = context.Products.Where(p => p.UnitPrice > 100).ToList();
    foreach (var product in products)
    {
        Console.WriteLine($"{product.ProductName}: ${product.UnitPrice}");
    }

    // Update
    var existingProduct = context.Products.Find(newProduct.ProductID);
    if (existingProduct != null)
    {
        existingProduct.UnitPrice = 219.99m;
        context.SaveChanges();
    }

    // Delete
    var productToDelete = context.Products.Find(newProduct.ProductID);
    if (productToDelete != null)
    {
        context.Products.Remove(productToDelete);
        context.SaveChanges();
    }
}
                

5. Advanced Topics

Explore these topics for more advanced SQL Server integration:

• Transactions: Ensure data integrity with SqlTransaction or EF Core's transaction management.
• Asynchronous Operations: Improve application responsiveness using async/await with ADO.NET and EF Core.
• Data Adapters: Use SqlDataAdapter to efficiently populate DataTable and DataSet objects.
• Performance Tuning: Understand SQL indexing, query optimization, and connection pooling.
• Data Types: Learn about mapping SQL Server data types to .NET types.