generic repository using entity framework Raw SQL

Enhancing Database Interactions in C# Applications with Entity Framework Core and Stored Procedures

Introduction:

In the realm of modern software development, efficient management of database operations is crucial for ensuring application performance and scalability. In C# applications, Entity Framework Core (EF Core) stands out as a powerful ORM (Object-Relational Mapping) tool that simplifies database interactions. When combined with stored procedures, EF Core offers a robust solution for executing complex database logic while maintaining the benefits of object-oriented programming. In this article, we'll explore how to harness the capabilities of EF Core and stored procedures to streamline database operations in C# applications.

Understanding Entity Framework Core:

Entity Framework Core is a lightweight, extensible ORM framework provided by Microsoft for .NET applications. It allows developers to work with databases using a high-level, object-oriented approach, abstracting away the complexities of SQL queries and database connections. EF Core supports various database providers, including SQL Server, SQLite, MySQL, and PostgreSQL, making it a versatile choice for a wide range of projects.

Leveraging Stored Procedures:

Stored procedures are precompiled sets of SQL statements that are stored in the database and can be executed by applications to perform specific tasks. They offer several benefits, including improved performance, enhanced security, and encapsulation of complex business logic. By integrating stored procedures into EF Core-based applications, developers can leverage the power of SQL while maintaining the advantages of ORM-based development.

Implementing a Generic Repository:

A generic repository acts as an abstraction layer between the application code and the database, providing a unified interface for performing CRUD operations on different types of entities. By implementing a generic repository pattern with EF Core and stored procedures, developers can ensure consistency, maintainability, and reusability across their data access layer.

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

public class GenericRepository<T> : IGenericRepository<T> where T : class
{
    private readonly DbContext _context;

    public GenericRepository(DbContext context)
    {
        _context = context ?? throw new ArgumentNullException(nameof(context));
    }

public async Task<(List<T> data, int totalRecords)> GetAllWithTotalCountAsync(string procedureName, string searchText, int take, int skip)
{
    var parameters = new[]
    {
        new SqlParameter("@SearchText", searchText),
        new SqlParameter("@Take", take),
        new SqlParameter("@Skip", skip)
    };

    // Create output parameter for total records
    var totalRecordsParam = new SqlParameter("@TotalRecords", SqlDbType.Int)
    {
        Direction = ParameterDirection.Output
    };

    // Execute stored procedure with parameters and output parameter
    var result = await _context.Set<T>()
                               .FromSqlRaw($"EXEC {procedureName} @SearchText, @Take, @Skip, @TotalRecords OUTPUT", parameters.Concat(new[] { totalRecordsParam }).ToArray())
                               .ToListAsync();

    // Retrieve total records count from output parameter
    var totalRecords = Convert.ToInt32(totalRecordsParam.Value);

    return (result, totalRecords);
}

    public async Task<List<T>, int totalRecords> GetAllWithSearchAsync(string procedureName, string searchText, int take, int skip)
    {
        var parameters = new[]
        {
        new SqlParameter("@SearchText", searchText),
        new SqlParameter("@Take", take),
        new SqlParameter("@Skip", skip)
    };

        return await _context.Set<T>()
                            .FromSqlRaw($"EXEC {procedureName} @SearchText, @Take, @Skip", parameters)
                            .ToListAsync();
    }

    public async Task<List<T>> GetAllAsync(string procedureName, string searchText, int take, int skip)
    {
        return await _context.Set<T>().FromSqlRaw($"EXEC {procedureName}").ToListAsync();
    }

    public async Task<T> GetByIdAsync(int id, string procedureName)
    {
        return await _context.Set<T>().FromSqlRaw($"EXEC {procedureName} @Id", id).FirstOrDefaultAsync();
    }

    public async Task<int> InsertAsync(T entity, string procedureName)
    {
        await _context.Database.ExecuteSqlRawAsync($"EXEC {procedureName}", entity);
        return await _context.SaveChangesAsync();
    }

    public async Task<int> UpdateAsync(T entity, string procedureName)
    {
        await _context.Database.ExecuteSqlRawAsync($"EXEC {procedureName}", entity);
        return await _context.SaveChangesAsync();
    }

    public async Task<int> DeleteAsync(int id, string procedureName)
    {
        return await _context.Database.ExecuteSqlRawAsync($"EXEC {procedureName} @Id", id);
    }


    public interface IGenericRepository<T> where T : class
    {
        Task<List<T>> GetAllAsync(string procedureName);
        Task<T> GetByIdAsync(int id, string procedureName);
        Task<int> InsertAsync(T entity, string procedureName);
        Task<int> UpdateAsync(T entity, string procedureName);
        Task<int> DeleteAsync(int id, string procedureName);
    }
}

Example Scenario:

Consider a scenario where you're developing a C# application to manage student records. You have a Student entity representing student data stored in a SQL Server database. To interact with the database efficiently, you decide to use EF Core with stored procedures. You create a generic repository that encapsulates common database operations and leverages stored procedures for data manipulation.

using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Threading.Tasks;

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public string Grade { get; set; }
}

public class StudentRepository : GenericRepository<Student>
{
    public StudentRepository(DbContext context) : base(context)
    {
    }
}

// Example usage:
public class Program
{
    static async Task Main(string[] args)
    {
       // Replace "YourConnectionString" with your actual connection string
        var options = new DbContextOptionsBuilder<YourDbContext>()
            .UseSqlServer("YourConnectionString")
            .Options;

        using var context = new YourDbContext(options);
        var repository = new StudentRepository(context);

        // Example usage of repository methods

        // Fetch all students
        var students = await repository.GetAllAsync("GetAllStudentsProcedure");
        foreach (var student in students)
        {
            Console.WriteLine($"Student ID: {student.Id}, Name: {student.Name}, Age: {student.Age}, Grade: {student.Grade}");
        }

        // Get student by ID
        var studentById = await repository.GetByIdAsync(1, "GetStudentByIdProcedure");
        if (studentById != null)
        {
            Console.WriteLine($"Found student by ID: {studentById.Id}, Name: {studentById.Name}, Age: {studentById.Age}, Grade: {studentById.Grade}");
        }
        else
        {
            Console.WriteLine("Student not found.");
        }

        // Insert a new student
        var newStudent = new Student { Name = "Alice", Age = 21, Grade = "A" };
        await repository.InsertAsync(newStudent, "InsertStudentProcedure");
        Console.WriteLine("New student inserted successfully.");

        // Update an existing student
        var studentToUpdate = await repository.GetByIdAsync(2, "GetStudentByIdProcedure"); // Assuming student with ID 2 exists
        if (studentToUpdate != null)
        {
            studentToUpdate.Name = "Updated Name";
            await repository.UpdateAsync(studentToUpdate, "UpdateStudentProcedure");
            Console.WriteLine("Student updated successfully.");
        }
        else
        {
            Console.WriteLine("Student not found for updating.");
        }

        // Delete a student by ID
        var studentIdToDelete = 3; // Assuming student with ID 3 exists
        await repository.DeleteAsync(studentIdToDelete, "DeleteStudentProcedure");
        Console.WriteLine("Student deleted successfully.");
    }
}

Conclusion:

In conclusion, combining Entity Framework Core with stored procedures offers a robust solution for managing database operations in C# applications. By leveraging the high-level abstractions provided by EF Core and the performance optimizations of stored procedures, developers can create efficient, scalable, and maintainable applications. Whether you're building a small-scale application or a large enterprise system, EF Core and stored procedures provide the tools you need to achieve optimal database performance while adhering to best practices in software development.