User-164452226 posted
Good day tech gurus,
l have been able to save, update and delete using Entity Framework but without procedures. l find procedures to be very handy and fast and now want to use them. Could anyone please assist me on how l can employ stored procedures on my design. Here is an
example of a functional design which l would be happy if anyone employs stored procedures without diverting me from the design pattern:
1) Courses table from SQL Server 2017
CREATE TABLE [dbo].[Courses](
[CourseID] [int] IDENTITY(1,1) NOT NULL,
[CourseCode] [nvarchar](50) NOT NULL,
[CourseName] [nvarchar](50) NULL,
CONSTRAINT [PK_Courses_1] PRIMARY KEY CLUSTERED
2) Procedures to save, update and delete
Save and Update proc
create proc sp_SaveCourse (
@CourseID int,
@CourseCode nvarchar(50),
@CourseName nvarchar(50)
)
as
if (@CourseID<=0)
insert into Courses
(
CourseCode,
Coursename
)
values
(
@CourseCode,
@CourseName
)
else
update Courses set
CourseCode=@CourseCode,
CourseName=@CourseName
where CourseID=@CourseID
Delete proc
create proc sp_DeleteCourse(
@CourseID int
)
as
delete from Courses
where CourseID=@CourseID
3) My ASP.Net MVC
Course Class in CollegeSys.Domain project under entities
namespace CollegeSys.Domain.Entities
{
public class Course
{
[Key]
public int CourseID { get; set; }
public string CourseCode { get; set; }
public string CourseName { get; set; }
}
}
4) My Repository class that links with the Entities
using CollegeSys.Domain.Entities;
namespace CollegeSys.Domain.Abstract
{
public interface ICourseRepository
{
IEnumerable<Course> Courses { get; }
}
}
5) My DBContext Class that handles DB
using System.Data.Entity;
using CollegeSys.Domain.Entities;
using System.ComponentModel.DataAnnotations;
namespace CollegeSys.Domain.Concrete
{
public class EFDbContext : DbContext
{
public DbSet<Course> Courses { get; set; }
}
}
6) My Entity Framework data access methods for saving, updating and deleting (This is were l want to use stored procedures)
using CollegeSys.Domain.Abstract;
using CollegeSys.Domain.Entities;
namespace CollegeSys.Domain.Concrete
{
public class EFCourseRepository : ICourseRepository
{
private EFDbContext context = new EFDbContext();
public IEnumerable<Course> Courses
{
get { return context.Courses; }
}
public void SaveCourse(Course course)//REPLACE WITH sp_SaveCourses
{
if (course.CourseID == 0)
{
context.Courses.Add(course);
}
else
{
Course dbEntry = context.Courses.Find(course.CourseID);
if (dbEntry != null)
{
dbEntry.CourseCode = course.CourseCode;
dbEntry.CourseName = course.CourseName;
}
}
context.SaveChanges();
}
public Course DeleteCourse (int CourseID) //REPLACE WITH sp_DeleteCourses
{
Course dbEntry = context.Courses.Find(CourseID);
if (dbEntry != null)
{
context.Courses.Remove(dbEntry);
context.SaveChanges();
}
return dbEntry;
}
}
}
7) My Ninject Infrastructure - binds the repository to the entity framework and its under a second project, CollegeSys.WebUI
using Ninject;
using CollegeSys.Domain.Abstract;
using CollegeSys.Domain.Concrete;
using CollegeSys.Domain.Entities;
namespace CollegeSys.WebUI.Infrastructure
{
public class NinjectDependencyResolver : IDependencyResolver
{
private IKernel kernel;
public NinjectDependencyResolver(IKernel kernelParam)
{
kernel = kernelParam;
AddBindings();
}
public object GetService(Type serviceType)
{
return kernel.TryGet(serviceType);
}
public IEnumerable<object> GetServices(Type serviceType)
{
return kernel.GetAll(serviceType);
}
private void AddBindings()
{ // put bindings here
kernel.Bind<ICourseRepository>().To<EFCourseRepository>();
}
}
}
8) My Course Controller under CollegeSys.WebUI
using CollegeSys.Domain.Abstract;
using CollegeSys.Domain.Concrete;
using CollegeSys.WebUI.Models;
using CollegeSys.Domain.Entities;
namespace CollegeSys.WebUI.Controllers
{
public class CourseController : Controller
{
EFCourseRepository repository = new EFCourseRepository();
public CourseController(EFCourseRepository collegeRepository)
{
this.repository = collegeRepository;
}
public ViewResult Index()
{
return View(repository.Courses);
}
public ViewResult List()
{
CoursesListViewModel model = new CoursesListViewModel
{
Courses = repository.Courses.OrderBy(c => c.CourseCode)
};
return View(model);
}
public ViewResult Edit(int courseID)
{
Course course = repository.Courses
.FirstOrDefault(c => c.CourseID == courseID);
return View(course);
}
[HttpPost]
public ActionResult Edit(Course course)
{
if (ModelState.IsValid)
{
repository.SaveCourse(course);
TempData["message"] = string.Format("{0} has been saved", course.CourseName);
return RedirectToAction("Index");
}
else
{ // there is something wrong with the data values
return View(course);
}
}
public ViewResult Create()
{
return View("Edit", new Course());
}
[HttpPost]
public ActionResult Delete(int CourseID)
{
Course DeleteCourse = repository.DeleteCourse(CourseID);
if (DeleteCourse != null)
{
TempData["message"] = string.Format("{0} was deleted",
DeleteCourse.CourseName);
}
return RedirectToAction("Index");
}
public CourseController()
{
}
}
}
Please help on how l can use the stored procedures without changing much of my design pattern. Thank you
