Getting data from a database with the MVC model

Question

I am creating an application which will have a very large database to draw it's information from (a student registration system to be exact). The database is created with Microsoft SQL Server if that matters. I plan to implement the MVC pattern in this, but I am unsure of how to handle accessing the database.

My current understanding of how it should work is this (please correct me if there is a better way to do it):

1. I have a model (for example User) that contains a few attributes and some business logic.
2. When I need to view or edit the data in the table, I get the relevant data and put it in a model, change it and then 'save' the changes to the database. So for example if I need to change a student's name I will search the database for that student (by ID number) and instantiate a new User object. I would then change the Name property of the User object and run a 'save' method that uploads the data to the database.

I guess I have two questions: firstly am I understanding how the MVC pattern works with data driven applications and secondly where does the code for getting the data from the database to instantiate a model go? Does it go in the controller for said model? Does it go in some other static class? Is it a constructor for the model?

Answer 1

You actually need to create a Data Access Layer:

1- Create a New Class Library [ProjectName].DataAccess

2- Create a Class Named UserManager.

3- Create a Method called AddUser that takes the User Model as an argument. and inside that method, you write the logic to insert the user to the database.

Depending on the Scale of your system, you might want to Create a Bussiness Layer (Class Library) that you use in your controller in order to Call one of the data Access Layers (SQL or Oracle or etc). If you reach that complexity, you might need to create interfaces with (AddUser, GetUser, DeleteUser) and provide both SQL and Oracle Class Implementation of that interface and choose one from your configuration.

If you need more details, please let me know.

Edit After your Comment:

You may have a GetUsers() and GetUserByID(int id) or by Name Whatever. That all depends on what you need in your UI and what functionalities you're giving to the user.

However, you should not Query all data from database and then filter what the user requested via code, you should only query the data you need from database. Unless you're showing the complete list in UI and then providing Search functionality on the screen.

Whether to pull a complete table from the database is something to think about before doing. In real applications, you have to consider the size of the table, if you have a huge one you may need to do some pre-filtering (where the end-user selects a department for example and then you Call GetUsersByDepartment(int depId)). Anyway, you can have a option to query all the users if the end-user requests that through your app screen, but he might need to wait for the data to be pulled. All these methods should be in you Data Access Layer for now considering your case of not having a Business Layer.