Skip to content

Instantly share code, notes, and snippets.

@saurabhpati
Created April 21, 2019 09:02
Show Gist options
  • Save saurabhpati/23ed20815545baebee01c601f6591e53 to your computer and use it in GitHub Desktop.
Save saurabhpati/23ed20815545baebee01c601f6591e53 to your computer and use it in GitHub Desktop.
Stored procedure - EF Core
/// <summary>
/// Using this class for base will default the primary key of the entity to be int.
/// </summary>
public class EntityBase : EntityBase<int>
{
}
/// <summary>
/// The base entity.
/// </summary>
/// <typeparam name="TKey">The primary key parameter type for the entity.</typeparam>
public class EntityBase<TKey> : IEntityBase where TKey: IEquatable<TKey>
{
/// <summary>
/// Gets or sets the Id of the entity
/// </summary>
public int Id { get; set; }
}
public interface IEntityBase
{
}
public interface ISprocRepository
{
DbCommand GetStoredProcedure(string name, params (string, object)[] nameValueParams);
DbCommand GetStoredProcedure(string name);
}
public interface ISprocRepository<TSprocEntity> : ISprocRepository
{
IList<TSprocEntity> ExecuteStoredProcedure(DbCommand command);
Task<IList<TSprocEntity>> ExecuteStoredProcedureAsync(DbCommand command);
}
/// <summary>
/// Note Entity.
/// </summary>
public class Note : EntityBase
{
/// <summary>
/// Gets or sets the title of the note.
/// </summary>
public string Title { get; set; }
/// <summary>
/// Gets or sets the detailed description of the note.
/// </summary>
public string Description { get; set; }
public int TaskItemId { get; set; }
/// <summary>
/// Gets or sets the task this note is part of.
/// </summary>
public TaskItem TaskItem { get; set; }
}
public class ProgressReportEntity
{
public int TeamId { get; set; }
public int UserId { get; set; }
public int TotalTasks { get; set; }
public int Todo { get; set; }
public int InProgress { get; set; }
public int Done { get; set; }
}
[Route("api/[controller]")]
[ApiController]
public class ReportsController : ControllerBase
{
private readonly ISprocRepository _repository;
public ReportsController(ISprocRepository repository)
{
_repository = repository;
}
[HttpGet("Progress")]
public Task<IList<ProgressReportEntity>> GetProgressReport([FromQuery] int? teamId, [FromQuery] int? userId)
{
(teamId, userId) = (teamId != 0 ? teamId : null, userId != 0 ? userId : null);
return _repository
.GetStoredProcedure("[dbo].[Sp_ProgressReport]")
.WithSqlParams(
(nameof(teamId), teamId),
(nameof(userId), userId))
.ExecuteStoredProcedureAsync<ProgressReportEntity>();
}
}
CREATE PROCEDURE [dbo].[Sp_ProgressReport]
@teamId INT NULL,
@userId INT NULL
AS
SELECT ut.TeamId, ut.UserId, COUNT(t.Id) TotalTasks,
SUM(CASE WHEN t.StatusId = 1 THEN 1 ELSE 0 END) Todo,
SUM(CASE WHEN t.StatusId = 2 THEN 1 ELSE 0 END) InProgress,
SUM(CASE WHEN t.StatusId = 3 THEN 1 ELSE 0 END) Done
FROM
[UserTeam] ut
JOIN [TaskItem] t
ON ut.UserId = t.UserId
JOIN [Status] s
ON t.StatusId = s.Id
WHERE ut.TeamId = @teamId OR @teamId IS NULL
AND
ut.UserId = @userId OR @userId IS NULL
GROUP BY
ut.TeamId, ut.UserId, s.Id
GO
public class SprocRepository : ISprocRepository
{
private readonly TaskPlannerDbContext _dbContext;
public SprocRepository(TaskPlannerDbContext dbContext)
{
_dbContext = dbContext;
}
public DbCommand GetStoredProcedure(string name, params (string, object)[] nameValueParams)
{
return _dbContext
.LoadStoredProcedure(name)
.WithSqlParams(nameValueParams);
}
public DbCommand GetStoredProcedure(string name)
{
return _dbContext.LoadStoredProcedure(name);
}
}
public class SprocRepository<TEntity> : SprocRepository, ISprocRepository<TEntity> where TEntity : class
{
public SprocRepository(TaskPlannerDbContext dbContext) : base(dbContext)
{
}
public IList<TEntity> ExecuteStoredProcedure(DbCommand command)
{
return command.ExecuteStoredProcedure<TEntity>();
}
public Task<IList<TEntity>> ExecuteStoredProcedureAsync(DbCommand command)
{
return command.ExecuteStoredProcedureAsync<TEntity>();
}
}
public static class SprocRepositoryExtensions
{
public static DbCommand LoadStoredProcedure(this DbContext context, string storedProcName)
{
var cmd = context.Database.GetDbConnection().CreateCommand();
cmd.CommandText = storedProcName;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
return cmd;
}
public static DbCommand WithSqlParams(this DbCommand cmd, params (string, object)[] nameValueParamPairs)
{
foreach (var pair in nameValueParamPairs)
{
var param = cmd.CreateParameter();
param.ParameterName = pair.Item1;
param.Value = pair.Item2 ?? DBNull.Value;
cmd.Parameters.Add(param);
}
return cmd;
}
public static IList<T> ExecuteStoredProcedure<T>(this DbCommand command) where T : class
{
using (command)
{
if (command.Connection.State == System.Data.ConnectionState.Closed)
command.Connection.Open();
try
{
using (var reader = command.ExecuteReader())
{
return reader.MapToList<T>();
}
}
finally
{
command.Connection.Close();
}
}
}
public static async Task<IList<T>> ExecuteStoredProcedureAsync<T>(this DbCommand command) where T : class
{
using (command)
{
if (command.Connection.State == System.Data.ConnectionState.Closed)
await command.Connection.OpenAsync();
try
{
using (var reader = command.ExecuteReader())
{
return reader.MapToList<T>();
}
}
finally
{
command.Connection.Close();
}
}
}
private static IList<T> MapToList<T>(this DbDataReader dr)
{
var objList = new List<T>();
var props = typeof(T).GetRuntimeProperties();
var colMapping = dr.GetColumnSchema()
.Where(x => props.Any(y => y.Name.ToLower() == x.ColumnName.ToLower()))
.ToDictionary(key => key.ColumnName.ToLower());
if (dr.HasRows)
{
while (dr.Read())
{
T obj = Activator.CreateInstance<T>();
foreach (var prop in props)
{
var val = dr.GetValue(colMapping[prop.Name.ToLower()].ColumnOrdinal.Value);
prop.SetValue(obj, val == DBNull.Value ? null : val);
}
objList.Add(obj);
}
}
return objList;
}
}
/// <summary>
/// The status entity --> Master table.
/// </summary>
public class Status : EntityBase
{
public Status()
{
TaskItems = new HashSet<TaskItem>();
}
/// <summary>
/// Gets or sets the status name.
/// </summary>
public string Name { get; set; }
/// <summary>
/// Gets or sets the tasks currently in this status.
/// </summary>
public ICollection<TaskItem> TaskItems { get; set; }
}
/// <summary>
/// Task entity.
/// </summary>
public class TaskItem : EntityBase
{
public TaskItem()
{
Notes = new HashSet<Note>();
}
/// <summary>
/// Gets or sets the name of the task.
/// </summary>
public string Name { get; set; }
/// <summary>
/// Gets or sets the description of the task.
/// </summary>
public string Description { get; set; }
/// <summary>
/// Gets or sets the status id of the task.
/// </summary>
public int StatusId { get; set; }
/// <summary>
/// Gets or sets the status of the task.
/// </summary>
public Status Status { get; set; }
public int UserId { get; set; }
/// <summary>
/// Gets or sets the person this task is assigned to.
/// </summary>
public User User { get; set; }
/// <summary>
/// Gets or sets the Notes made for the task.
/// </summary>
public ICollection<Note> Notes { get; set; }
}
/// <summary>
/// The team entity --> master entity.
/// </summary>
public class Team : EntityBase
{
public Team()
{
UserTeams = new HashSet<UserTeam>();
}
/// <summary>
/// Gets or sets the name of the team.
/// </summary>
public string Name { get; set; }
public ICollection<UserTeam> UserTeams { get; set; }
}
/// <summary>
/// This entity is not for authentication, this is an entity. --> Master table.
/// </summary>
public class User : EntityBase
{
public User()
{
UserTeams = new HashSet<UserTeam>();
TaskItems = new HashSet<TaskItem>();
}
public string FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
public string Username { get; set; }
/// <summary>
/// Gets or sets the tasks assigned to this user.
/// </summary>
public ICollection<TaskItem> TaskItems { get; set; }
public ICollection<UserTeam> UserTeams { get; set; }
}
public class UserTeam : IEntityBase
{
public int UserId { get; set; }
public User User { get; set; }
public int TeamId { get; set; }
public Team Team { get; set; }
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment