Boosting Database Operations with a Generic Repository Pattern in C#
Introduction:
In the world of software development, efficient interaction with databases is crucial for performance and scalability. Whether you're working with a small-scale application or a large enterprise system, managing database operations effectively can significantly impact overall efficiency. In this article, we'll explore how to optimize database operations using a generic repository pattern in C#, leveraging the power of ADO.NET.
Understanding the Generic Repository Pattern:
The generic repository pattern is a design pattern commonly used in software development to abstract data access operations from the rest of the application. It provides a unified interface for performing CRUD (Create, Read, Update, Delete) operations on various types of entities within a database.
Implementation Overview:
Let's dive into a practical implementation of the generic repository pattern in C#. Our example revolves around a GenericRepository<T> class, where T represents the entity type with which we'll be working. This class encapsulates common database operations such as insertion, retrieval, updating, and deletion.
public class GenericRepository<T> where T : class, new() { private readonly string _connectionString; public GenericRepository(string connectionString) { _connectionString = connectionString; } public async Task<(List<T> list, int count)> GetAllAndCountAsync(string procedureName, string searchText, int take, int skip) { var list = new List<T>(); int count = 0; using (var connection = new SqlConnection(_connectionString)) { using var command = new SqlCommand(procedureName, connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@SkipCount", skip); command.Parameters.AddWithValue("@PageSize", take); if (searchText != "null") { command.Parameters.AddWithValue("@searchWord", searchText); } await connection.OpenAsync(); using var reader = await command.ExecuteReaderAsync(); if (await reader.ReadAsync()) count = reader.GetInt32(reader.GetOrdinal("TotalRecords")); if (await reader.NextResultAsync()) { while (await reader.ReadAsync()) { list.Add(TestRepository<T>.PopulateEntity(reader)); } } } return (list, count); } public async Task<(List<T> list, int count)> GetAllAndLoopCountAsync(string procedureName, string searchText, int take, int skip) { var list = new List<T>(); int count = 0; using (var connection = new SqlConnection(_connectionString)) { using var command = new SqlCommand(procedureName, connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@SkipCount", skip); command.Parameters.AddWithValue("@PageSize", take); if (searchText != "null") { command.Parameters.AddWithValue("@searchWord", searchText); } await connection.OpenAsync(); using var reader = await command.ExecuteReaderAsync(); while (await reader.ReadAsync()) { list.Add(TestRepository<T>.PopulateEntity(reader)); count++; } if (reader.NextResult() && await reader.ReadAsync()) { count = reader.GetInt32(0); } } return (list, count); } public async Task<List<T>> GetAllAsync(string procedureName) { var list = new List<T>(); using (var connection = new SqlConnection(_connectionString)) { using var command = new SqlCommand(procedureName, connection); command.CommandType = CommandType.StoredProcedure; await connection.OpenAsync(); using var reader = await command.ExecuteReaderAsync(); while (await reader.ReadAsync()) { list.Add(TestRepository<T>.PopulateEntity(reader)); } } return list; } public async Task<T?> GetByIdAsync(int id, string procedureName) { using var connection = new SqlConnection(_connectionString); using var command = new SqlCommand(procedureName, connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@Id", id); await connection.OpenAsync(); using var reader = await command.ExecuteReaderAsync(); if (await reader.ReadAsync()) { return TestRepository<T>.PopulateEntity(reader); } else { return null; } } public async Task<int> InsertAsync(T entity, string procedureName) { using var connection = new SqlConnection(_connectionString); using var command = new SqlCommand(procedureName, connection); command.CommandType = CommandType.StoredProcedure; await connection.OpenAsync(); TestRepository<T>.PopulateParameters(command, entity); return await command.ExecuteNonQueryAsync(); } public async Task<int> UpdateAsync(T entity, string procedureName) { using var connection = new SqlConnection(_connectionString); using var command = new SqlCommand(procedureName, connection); command.CommandType = CommandType.StoredProcedure; await connection.OpenAsync(); TestRepository<T>.PopulateParameters(command, entity); return await command.ExecuteNonQueryAsync(); } public async Task<int> DeleteAsync(long id, string procedureName) { using var connection = new SqlConnection(_connectionString); using var command = new SqlCommand(procedureName, connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@Id", id); await connection.OpenAsync(); return await command.ExecuteNonQueryAsync(); } public async Task<int> BulkInsertAsync(IEnumerable<T> entities, string procedureName) { using var connection = new SqlConnection(_connectionString); await connection.OpenAsync(); using var transaction = connection.BeginTransaction(); int affectedRows = 0; try { foreach (var entity in entities) { using var command = new SqlCommand(procedureName, connection, transaction); command.CommandType = CommandType.StoredProcedure; TestRepository<T>.PopulateParameters(command, entity); affectedRows += await command.ExecuteNonQueryAsync(); } transaction.Commit(); } catch { transaction.Rollback(); throw; } return affectedRows; } private static void PopulateParameters(SqlCommand command, T entity) { foreach (var property in typeof(T).GetProperties()) { command.Parameters.AddWithValue("@" + property.Name, property.GetValue(entity) ?? DBNull.Value); } } private static T PopulateEntity(SqlDataReader reader) { var entity = new T(); var properties = typeof(T).GetProperties(); foreach (var property in properties) { var value = reader[property.Name]; if (value != DBNull.Value) { property.SetValue(entity, value); } } return entity; } } public class Student { public int Id { get; set; } public string Name { get; set; } public int Age { get; set; } public string Grade { get; set; } } class Program { static async Task Main(string[] args) { // Replace "YourConnectionString" with your actual connection string var repository = new GenericRepository<Student>("YourConnectionString"); // Insert example var newStudent = new Student { Name = "John", Age = 20, Grade = "A" }; await repository.InsertAsync(newStudent, "InsertStudentProcedure"); // Get by ID example var studentById = await repository.GetByIdAsync(1, "GetStudentByIdProcedure"); // Update example if (studentById != null) { studentById.Name = "UpdatedName"; await repository.UpdateAsync(studentById, "UpdateStudentProcedure"); } // Delete example await repository.DeleteAsync(1, "DeleteStudentProcedure"); // Get all example var allStudents = await repository.GetAllAsync("GetAllStudentsProcedure"); // Bulk insert example var studentsToInsert = new List<Student> { new() { Name = "Alice", Age = 22, Grade = "B" }, new() { Name = "Bob", Age = 21, Grade = "C" } }; await repository.BulkInsertAsync(studentsToInsert, "BulkInsertStudentsProcedure"); } }
Key Features and Benefits:
- Flexibility: The generic repository pattern allows developers to work with various entity types without writing repetitive data access code.
- Modularity: Separation of concerns is achieved by isolating database operations within the repository, promoting cleaner and more maintainable code.
- Performance: Asynchronous database operations enhance application responsiveness and scalability, ensuring smooth user experiences.
- Scalability: The repository pattern scales well with the size and complexity of the application, facilitating future modifications and enhancements.
Conclusion:
In conclusion, the generic repository pattern in C# serves as a powerful tool for streamlining database operations and enhancing application performance. By encapsulating data access logic in a reusable and modular manner, developers can build robust and efficient systems that meet the demands of modern software development.
Implementing this pattern in your projects can lead to cleaner codebases, improved maintainability, and ultimately, a more satisfying user experience. So why not leverage the generic repository pattern in your next C# project and unlock its full potential for database interaction?
0 Comments