Using LINQ to SQL for Data Access

Introduction to LINQ to SQL

LINQ to SQL is a component of .NET that provides a run-time infrastructure for managing relational data as objects. LINQ to SQL allows you to query databases using standard LINQ syntax, simplifying data access code and improving readability.

Prerequisites

Before you begin, ensure you have the following:

Visual Studio installed on your machine.
A basic understanding of .NET and SQL.
An existing SQL Server database to which you want to connect.

Setting Up the Database

First, set up a database and create a table to work with.

// SQL script to create a database and table
CREATE DATABASE SampleDb;
GO
USE SampleDb;
GO
CREATE TABLE Users (
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(100),
    Email NVARCHAR(100)
);

Creating the Data Context

Next, create a Data Context class in your .NET application. The Data Context represents the main point of interaction with the database, allowing you to query and update data.

// Create a Data Context class
using System.Data.Linq;

public class SampleDataContext : DataContext
{
    public Table<User> Users;

    public SampleDataContext(string connectionString) : base(connectionString) { }
}

Mapping the Database Table to a Class

Map the database table to a .NET class using attributes.

// Map the Users table to a User class
using System.Data.Linq.Mapping;

[Table(Name = "Users")]
public class User
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    public int Id { get; set; }

    [Column]
    public string Name { get; set; }

    [Column]
    public string Email { get; set; }
}

Querying the Database

Use LINQ to SQL to query the database and retrieve data.

// Querying the database
using System;
using System.Linq;

public class Program
{
    public static void Main(string[] args)
    {
        var connectionString = "YourConnectionStringHere";
        var db = new SampleDataContext(connectionString);

        // Retrieve all users
        var users = db.Users.ToList();
        foreach (var user in users)
        {
            Console.WriteLine($"{user.Id} - {user.Name} - {user.Email}");
        }
    }
}

Inserting Data

Use the Data Context to insert new data into the database.

// Inserting data
public class Program
{
    public static void Main(string[] args)
    {
        var connectionString = "YourConnectionStringHere";
        var db = new SampleDataContext(connectionString);

        // Insert a new user
        var newUser = new User { Name = "John Doe", Email = "john.doe@example.com" };
        db.Users.InsertOnSubmit(newUser);
        db.SubmitChanges();

        Console.WriteLine("User added.");
    }
}

Updating Data

Use the Data Context to update existing data in the database.

// Updating data
public class Program
{
    public static void Main(string[] args)
    {
        var connectionString = "YourConnectionStringHere";
        var db = new SampleDataContext(connectionString);

        // Retrieve a user by ID
        var user = db.Users.FirstOrDefault(u => u.Id == 1);
        if (user != null)
        {
            user.Name = "Jane Doe";
            db.SubmitChanges();

            Console.WriteLine("User updated.");
        }
    }
}

Deleting Data

Use the Data Context to delete data from the database.

// Deleting data
public class Program
{
    public static void Main(string[] args)
    {
        var connectionString = "YourConnectionStringHere";
        var db = new SampleDataContext(connectionString);

        // Retrieve a user by ID
        var user = db.Users.FirstOrDefault(u => u.Id == 1);
        if (user != null)
        {
            db.Users.DeleteOnSubmit(user);
            db.SubmitChanges();

            Console.WriteLine("User deleted.");
        }
    }
}

Conclusion

In this tutorial, we covered how to use LINQ to SQL for data access in a .NET application. We discussed how to set up a database, create a Data Context, map database tables to .NET classes, and perform CRUD operations using LINQ to SQL. LINQ to SQL provides a powerful and intuitive way to interact with your database, making data access code more readable and maintainable.