This code defines a generic repository class for database
operations. It provides methods for CRUD operations, lazy loading, and
executing SQL queries. Additionally, it includes exception handling for
database validation errors.
.png)
using System;
using System.Data.Entity;
using
System.Data.Entity.Validation;
using System.Data.SqlClient;
using System.Linq.Expressions;
using System.Threading.Tasks;
namespace WebApplication.Repository
{
/// <summary>
/// Generic repository class for
database operations.
/// </summary>
/// <typeparam name="T">The type of entity.</typeparam>
public class Repository<T> where T : class
{
private ProjectContext context;
private IDbSet<T> entities;
/// <summary>
/// Initializes a new instance
of the <see cref="Repository{T}"/> class.
/// </summary>
public Repository()
{
context = new ProjectContext();
}
/// <summary>
/// Gets a queryable
representation of the entity without tracking changes.
/// </summary>
public virtual IQueryable<T>
AsNoTracking => Entities.AsNoTracking();
/// <summary>
/// Gets a queryable
representation of the entity.
/// </summary>
public virtual IQueryable<T> Table
=> Entities;
/// <summary>
/// Gets the entities set.
/// </summary>
protected virtual IDbSet<T> Entities
{
get
{
if (entities == null)
{
entities
= context.Set<T>();
}
return entities;
}
}
/// <summary>
/// Retrieves an entity by its
ID.
/// </summary>
/// <param name="id">The ID of the entity to
retrieve.</param>
/// <returns>The entity
with the specified ID.</returns>
public virtual T GetById(object id) => Entities.Find(id);
/// <summary>
/// Inserts a new entity into
the database.
/// </summary>
/// <param name="entity">The entity to insert.</param>
public virtual void Insert(T entity)
{
try
{
if (entity == null)
{
throw new
ArgumentNullException(nameof(entity));
}
Entities.Add(entity);
}
catch (DbEntityValidationException
dbex)
{
string msg = string.Empty;
foreach (var validationErrors in dbex.EntityValidationErrors)
{
foreach (var validationError in
validationErrors.ValidationErrors)
{
// Log or handle validation errors
}
}
var fail = new Exception(msg, dbex);
throw fail;
}
}
// Other methods omitted for brevity...
/// <summary>
/// Asynchronously executes a
SQL query.
/// </summary>
/// <typeparam name="T1">The type of the result.</typeparam>
/// <param name="v">The command text.</param>
/// <param name="sqlParameter">The SQL parameter.</param>
/// <returns>A task
representing the asynchronous operation.</returns>
internal Task QuerySQLAsync<T1>(string v, SqlParameter
sqlParameter)
{
throw new NotImplementedException();
}
}
}
using
System.Data.Entity.Validation;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq.Expressions;
namespace WebApplication.Repository
{
public class Repository<T> where T : class
{
private ProjectContext context;
private IDbSet<T> entities;
public Repository()
{
context = new ProjectContext();
}
// Property to retrieve entities without tracking changes
public virtual IQueryable<T>
AsNoTracking
{
get { return Entities.AsNoTracking(); }
}
// Property to access the DbSet of entities
public virtual IQueryable<T> Table
{
get { return Entities; }
}
// Private property to lazily initialize DbSet
protected virtual IDbSet<T> Entities
{
get
{
if (entities == null)
{
entities
= context.Set<T>();
}
return entities;
}
}
// Retrieves an entity by its primary key
public virtual T GetById(object id)
{
return Entities.Find(id);
}
// Inserts a new entity into the database
public virtual void Insert(T entity)
{
try
{
if (entity == null)
{
throw new
ArgumentNullException(nameof(entity));
}
Entities.Add(entity);
}
catch (DbEntityValidationException
dbex)
{
// Handles entity validation errors
string msg = string.Empty;
foreach (var validationErrors in dbex.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
// Log or handle validation errors
}
}
var fail = new Exception(msg, dbex);
throw fail;
}
}
// Updates an existing entity in the database
public virtual void Update(T entity, bool changeState = true)
{
try
{
if (entity == null)
{
throw new
ArgumentNullException(nameof(entity));
}
if (changeState)
{
context.Entry(entity).State = EntityState.Modified;
}
}
catch (DbEntityValidationException dbex)
{
// Handles entity validation errors
string msg = string.Empty;
foreach (var validationErrors in dbex.EntityValidationErrors)
{
foreach (var validationError in
validationErrors.ValidationErrors)
{
// Log or handle validation errors
}
}
var fail = new Exception(msg, dbex);
throw fail;
}
}
// Saves changes made to entities in the context to the database
public virtual void SaveChanges()
{
context.SaveChanges();
}
// Deletes an entity from the database
public virtual void Delete(T entity)
{
try
{
if (entity == null)
{
throw new
ArgumentNullException(nameof(entity));
}
context.Entry(entity).State = EntityState.Deleted;
Entities.Remove(entity);
}
catch (DbEntityValidationException
dbex)
{
// Handles entity validation errors
string msg = string.Empty;
foreach (var validationErrors in dbex.EntityValidationErrors)
{
foreach (var validationError in
validationErrors.ValidationErrors)
{
//
Log or handle validation errors
}
}
var fail = new Exception(msg, dbex);
throw fail;
}
}
// Retrieves entities with lazy loading of related entities
public virtual IQueryable<T> GetAllLazyLoad(Expression<Func<T, bool>> filter, params Expression<Func<T, object>>[] children)
{
IQueryable<T> query = Entities;
if (filter != null)
{
query =
query.Where(filter);
}
query =
children.Aggregate(query, (current, include) => current.Include(include));
return query;
}
// Executes a SQL query and returns a list of entities
public IList<TEntity> QuerySQL<TEntity>(string commandText, params object[] parameters) where TEntity : class
{
context.Database.CommandTimeout = 300;
return
context.QuerySQL<TEntity>(commandText, parameters);
}
// Executes a SQL query and returns a sequence of typed results
public IEnumerable<TElement>
ExecuteSQL<TElement>(string commandText, params object[] parameters)
{
context.Database.CommandTimeout = 300;
return
context.ExecuteSQL<TElement>(commandText, parameters);
}
// Asynchronously executes a SQL query
internal Task QuerySQLAsync<T1>(string v, SqlParameter
sqlParameter)
{
throw new NotImplementedException();
}
}
}
using System.Data;
using WebApplication.Models;
using
WebApplication.Repository.Interface;
namespace
WebApplication.Repository.Service
{
public class GetUserService :
IUserService
{
public Response<GetUser>
CreateUser(CreateUserModel model)
{
throw new NotImplementedException();
}
public Response<GetUser>
GetUser(GetUser model)
{
Response<GetUser> response = new Response<GetUser>();
try
{
Repository<GetUser> Repository = new Repository<GetUser>();
var result =
Repository.QuerySQL<GetUser>("Sp_TableName_Get",
ProjectUtility.GetSQLParam("UserName", SqlDbType.NVarChar, (object)model.UserName ?? DBNull.Value));
response.Data = result.ToList();
if (response.Data != null)
{
response.Success = true;
}
else
{
response.Success
= false;
response.Message.Add("No
data found or invalid result.");
}
}
catch (Exception ex)
{
response.Success = false;
response.Message.Add(ex.Message);
}
return response;
}
}
}