Repository Pattern | Database layer

Repository Pattern 

The Repository Pattern is a design pattern that abstracts the data access layer from the rest of the application. It provides a way to encapsulate the logic for interacting with data storage, making the code more maintainable and testable. 

Here's an example of how you can implement the CRUD operations using the Repository Pattern:

1. First, create a class for your data model (e.g., `Person.cs`):

public class Person

{

    public int ID { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

}

2. Next, create an interface for your repository (e.g., `IPersonRepository.cs`):

public interface IPersonRepository

{

    Person GetById(int id);

    IEnumerable<Person> GetAll();

    void Insert(Person person);

    void Update(Person person);

    void Delete(int id);

}

3. Implement the repository interface (e.g., `PersonRepository.cs`):

using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

public class PersonRepository : IPersonRepository

{

    private readonly string _connectionString;

    public PersonRepository(string connectionString)

    {

        _connectionString = connectionString;

    }

    public Person GetById(int id)

    {

        using (SqlConnection connection = new SqlConnection(_connectionString))

        {

            connection.Open();

            string selectQuery = "SELECT * FROM People WHERE ID = @ID";

            SqlCommand cmd = new SqlCommand(selectQuery, connection);

            cmd.Parameters.AddWithValue("@ID", id);

            SqlDataReader reader = cmd.ExecuteReader();

            if (reader.Read())

            {

                return new Person

                {

                    ID = (int)reader["ID"],

                    FirstName = reader["FirstName"].ToString(),

                    LastName = reader["LastName"].ToString()

                };

            }

            return null;

        }

    }

    public IEnumerable<Person> GetAll()

    {

        using (SqlConnection connection = new SqlConnection(_connectionString))

        {

            connection.Open();

            string selectQuery = "SELECT * FROM People";

            SqlCommand cmd = new SqlCommand(selectQuery, connection);

            List<Person> people = new List<Person>();

            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())

            {

                people.Add(new Person

                {

                    ID = (int)reader["ID"],

                    FirstName = reader["FirstName"].ToString(),

                    LastName = reader["LastName"].ToString()

                });

            }

            return people;

        }

    }

    public void Insert(Person person)

    {

        using (SqlConnection connection = new SqlConnection(_connectionString))

        {

            connection.Open();

            string insertQuery = "INSERT INTO People (FirstName, LastName) VALUES (@FirstName, @LastName)";

            SqlCommand cmd = new SqlCommand(insertQuery, connection);

            cmd.Parameters.AddWithValue("@FirstName", person.FirstName);

            cmd.Parameters.AddWithValue("@LastName", person.LastName);

            cmd.ExecuteNonQuery();

        }

    }

    public void Update(Person person)

    {

        using (SqlConnection connection = new SqlConnection(_connectionString))

        {

            connection.Open();

            string updateQuery = "UPDATE People SET FirstName = @FirstName, LastName = @LastName WHERE ID = @ID";

            SqlCommand cmd = new SqlCommand(updateQuery, connection);

            cmd.Parameters.AddWithValue("@ID", person.ID);

            cmd.Parameters.AddWithValue("@FirstName", person.FirstName);

            cmd.Parameters.AddWithValue("@LastName", person.LastName);

            cmd.ExecuteNonQuery();

        }

    }

    public void Delete(int id)

    {

        using (SqlConnection connection = new SqlConnection(_connectionString))

        {

            connection.Open();

            string deleteQuery = "DELETE FROM People WHERE ID = @ID";

            SqlCommand cmd = new SqlCommand(deleteQuery, connection);

            cmd.Parameters.AddWithValue("@ID", id);

            cmd.ExecuteNonQuery();

        }

    }

}

4. Finally, you can use the repository in your application:

public class Program

{

    private const string ConnectionString = "Your_Connection_String_here"; // Replace with your SQL Server connection string

    public static void Main(string[] args)

    {

        IPersonRepository personRepository = new PersonRepository(ConnectionString);

        // CREATE operation

        personRepository.Insert(new Person { FirstName = "John", LastName = "Doe" });

        // READ operation

        var people = personRepository.GetAll();

        foreach (var person in people)

        {

            Console.WriteLine($"ID: {person.ID}, FirstName: {person.FirstName}, LastName: {person.LastName}");

        }

        // UPDATE operation

        var personToUpdate = personRepository.GetById(1);

        if (personToUpdate != null)

        {

            personToUpdate.FirstName = "UpdatedFirstName";

            personToUpdate.LastName = "UpdatedLastName";

            personRepository.Update(personToUpdate);

        }

        // DELETE operation

        personRepository.Delete(1);

    }

}

Replace `"Your_Connection_String_here"` with your actual SQL Server connection string. This code demonstrates how to implement CRUD operations using the Repository Pattern for a `Person` entity. It abstracts the data access logic, making it more modular and testable.