If I understood you correctly, what you might want to do is to create an Interface which will define common tasks for database contexts and will be implemented by SqlDbLayer which will actually work with database context. So by this approach, you can switch between databases easily or use several ones.
For example, your DAL may look like this:
Database layer interface
public interface IDbLayer
{
public Task<IEnumerable<Student>> GetStudents();
public Task<IEnumerable<Subject>> GetSubjects();
public Task<IEnumerable<Enrollment>> GetEnrollments();
// Student
public Task<Student> GetStudent(int? id);
public Task<Student> AddStudent(Student newStudent);
public Task<Student> EditStudent(Student studentToEdit);
public Task<Student> DeleteStudent(int? id);
// Course
public Task<Subject> GetSubject(int? id);
public Task<Subject> AddSubject(Subject newSubject);
public Task<Subject> EditSubject(Subject subjectToEdit);
public Task<Subject> DeleteSubject(int? id);
// Enrollment
public Task<Enrollment> GetEnrollment(int? id);
public Task<Enrollment> AddEnrollment(Enrollment newEnrollment);
public Task<Enrollment> EditEnrollment(Enrollment enrollmentToEdit);
public Task<Enrollment> DeleteEnrollment(int? grade);
}
SQL Database layer
public class SqlDbLayer : IDbLayer
{
public readonly SchoolDbContext _context;
public SqlDbLayer(SchoolDbContext context)
{
_context = context;
}
public async Task<IEnumerable<Student>> GetStudents()
{
return await _context.Students
.Include(s => s.Study)
.OrderBy(s => s.FirstName)
.ThenBy(s => s.LastName)
.ThenBy(s => s.IndexNumber)
.ThenBy(s => s.Study.Name)
.ToListAsync();
}
public async Task<IEnumerable<Subject>> GetSubjects()
{
return await _context.Subjects
.OrderBy(s => s.Name)
.ToListAsync();
}
public async Task<IEnumerable<Enrollment>> GetEnrollments(int? id)
{
if(id != null)
{
return await _context.Enrollments
.Include(e => e.Student)
.ThenInclude(e => e.Study)
.Include(e => e.Subject)
.Include(e => e.Instructor)
.OrderBy(e => e.IdEnrollment)
.Where(e => e.IdStudent == id)
.ToListAsync();
}
else
{
return await _context.Enrollments
.Include(e => e.Student)
.ThenInclude(e => e.Study)
.Include(e => e.Subject)
.Include(e => e.Instructor)
.OrderBy(e => e.IdEnrollment)
.ToListAsync();
}
}
public async Task<Student> GetStudent(int? id)
{
var students = await _context.Students.Include(s => s.Study).ToListAsync();
return students.Find(s => s.IdStudent == id);
}
public async Task<Subject> GetSubject(int? id)
{
var subjects = await _context.Subjects.ToListAsync();
return subjects.Find(s => s.IdSubject == id);
}
public async Task<Enrollment> GetEnrollment(int? id)
{
var enrollemnts = await _context.Enrollments
.Include(e => e.Student)
.Include(e => e.Subject)
.ToListAsync();
return enrollemnts.Find(s => s.IdEnrollment == id);
}
public async Task<Student> AddStudent(Student newStudent)
{
_context.Students.Add(newStudent);
await _context.SaveChangesAsync();
return newStudent;
}
public async Task<Subject> AddSubject(Subject newSubject)
{
_context.Subjects.Add(newSubject);
await _context.SaveChangesAsync();
return newSubject;
}
public async Task<Enrollment> AddEnrollment(Enrollment newEnrollment)
{
_context.Enrollments.Add(newEnrollment);
await _context.SaveChangesAsync();
return newEnrollment;
}
public async Task<Student> DeleteStudent(int? id)
{
var studentToDelete = await _context.Students.FindAsync(id);
_context.Students.Remove(studentToDelete);
await _context.SaveChangesAsync();
// int g = 0;
_context.Remove(studentToDelete);
return null;
}
public async Task<Subject> DeleteSubject(int? id)
{
var subjectToDelete = _context.Subjects.Find(id);
_context.Subjects.Remove(subjectToDelete);
await _context.SaveChangesAsync();
_context.Remove(subjectToDelete);
return null;
}
public async Task<Enrollment> DeleteEnrollment(int? id)
{
var enrollmentToDelete = _context.Enrollments.Find(id);
_context.Enrollments.Remove(enrollmentToDelete);
await _context.SaveChangesAsync();
_context.Remove(enrollmentToDelete);
return null;
}
public async Task<Student> EditStudent(Student studentToEdit)
{
_context.Update(studentToEdit);
await _context.SaveChangesAsync();
return null;
}
public async Task<Subject> EditSubject(Subject subjectToEdit)
{
_context.Update(subjectToEdit);
await _context.SaveChangesAsync();
return null;
}
public async Task<Enrollment> EditEnrollment(Enrollment enrollmentToEdit)
{
_context.Update(enrollmentToEdit);
await _context.SaveChangesAsync();
return null;
}
}
Database context
public class SchoolDbContext : DbContext
{
public SchoolDbContext(DbContextOptions options) : base(options)
{
}
public DbSet<Student> Students { get; set; }
public DbSet<Subject> Subjects { get; set; }
public DbSet<Enrollment> Enrollments { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Student>().ToTable("Student", schema: "School");
modelBuilder.Entity<Subject>().ToTable("Course", schema: "School");
modelBuilder.Entity<Enrollment>().ToTable("Enrollment", schema: "School");
base.OnModelCreating(modelBuilder);
}
}
So here your DbContext which defines database is injected by SqlDbLayer and implements common tasks for all databases defined in IDbLayer, and you could define different database contexts and use SqlDbLayer to implement them. You might also want to inject the IDbLayer and SqlDbLayer in your Startup, for example:
public void ConfigureServices(IServiceCollection services)
{
services.AddDbContext<SchoolDbContext>
(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
services.AddScoped<IDbLayer, SqlDbLayer>();
services.AddControllersWithViews();
}
And you would consume your database contexts through IDbLayer interface you defined. For example:
public class StudentsController : Controller
{
public readonly IDbLayer _context;
public StudentsController(IDbLayer context)
{
_context = context;
}
public async Task<IActionResult> Index(string studyName, string searchString)
{
var students = await _context.GetStudents();
var studies = await _context.GetStudies();
IQueryable<string> studyQuery = (from s in students
orderby s.Study.Name
select s.Study.Name).AsQueryable();
if (!string.IsNullOrEmpty(searchString))
{
students = students.Where(s => s.IndexNumber == searchString).ToList();
}
if (!string.IsNullOrEmpty(studyName))
{
students = students.Where(s => s.Study.Name == studyName).ToList();
}
ViewBag.Students = students;
ViewBag.Studies = studies;
var studentStudyView = new StudentStudyViewModel
{
StudentsList = students.ToList(),
Studies = new SelectList(studyQuery.Distinct().ToList()),
StudentsCount = students.Count()
};
// int g = 0;
return View(studentStudyView);
}
public async Task<IActionResult> Details(int? id)
{
if(id == null)
{
return NotFound();
}
var student = await _context.GetStudent(id);
if(student == null)
{
return NotFound();
}
return View(student);
}
public async Task<IActionResult> Create()
{
ViewBag.Studies = await _context.GetStudies();
return View();
}
[HttpPost]
public async Task<IActionResult> Create(Student studentToAdd)
{
if (!ModelState.IsValid)
{
return View("Create", studentToAdd);
}
await _context.AddStudent(studentToAdd);
//int g = 0;
return RedirectToAction("Create"); // 302
}
I hope my point was actually related to your question.
