Asp.net mvc and sql queries

Question

I develop websites using web forms, now I have a project in which I use the MVC3 Framework with Rzor. My question is about some basic design patterns in MVC. I have a web page where on the left side I will pull categories from the SQL table, in the center I will query another Sql table and a little more throughout the page.

So my question is: what is the best way to convey data on one web page, all these requests are completely independent, do I need to create a new model for each request? or is there a better way to do this?

in WebForms I used custom controls, where each custom control had its own design and Sql queries. I heard about the use of Partial Views in MVC, but I'm not sure, I think it's hard to understand how to transfer data to one web page using different requests and display output on the web page.

thanks

Answer 1

You must create a ViewModel . See Update below.

This is the model representing your page. Elements that you want to show in your view must exist in your ViewModel. You will populate the ViewModel in your controller and display them on the page.

I wrote an example page of a shopping website with categories on the left and Products in the center. Both objects will exist in different tables.

Example:

 class MainPageViewModel { //this data is from a different table. //and goes on the left of the page public string Categories {get; set;} //this data is also from a different table. //and goes on the center of the page public List<Products> Products {get; set;} } 

In your controller:

 public class HomeController : Controller { // GET: /Home/ public ActionResult Index() { MainPageViewModel vm = new MainPageViewModel(); vm.Categories = GetCategories(); //use the GetProducts() to get your products and add them. vm.Products.Add(...); return View(vm); //pass it into the page } string[] GetCategories() { DataTable data = GetDataFromQuery("SELECT * FROM Categories WHERE.."); //convert the data into a string[] and return it.. } //maybe it has to return something else instead of string[]? string[] GetProducts() { DataTable data = GetDataFromQuery("SELECT * FROM Products WHERE.."); //convert the data into a string[] and return it.. } DataTable GetDataFromQuery(string query) { SqlDataAdapter adap = new SqlDataAdapter(query, "<your connection string>"); DataTable data = new DataTable(); adap.Fill(data); return data; } } 

Then, in your opinion, you correctly display it:

 @model MainPageViewModel @{ ViewBag.Title = "MainPage"; } <div id="left-bar"> <ul> @foreach (var category in Model.Categories) { <li>@category</li> } </ul> </div> <div id="center-content"> <ul> @foreach (var product in Model.Products) { <li>@product.Name</li> <li>@product.Price..</li> ..... } </ul> </div> 

---

Refresh

This is your comment in which you mentioned that your tables and database columns change regularly.

I can’t say for sure, but maybe you should not make such tables as everyday ones, maybe there is a better database design that you may have, or maybe RDBMS is not suitable for you, and you should look into NoSql database (e.g. MongoDB )

However, if you continue with the code above, I suggest putting it in your own data layer class.

Also see Dapper for its very subtle level of data access, which simply retrieves objects from the database using sql queries or stored procedures. (Exactly what you need) This is done and used in stackoverflow.