Where Do I Put a Database Query in MVC

Where do I put a database query in MVC?

Materials on the subject of MVC

You could have listed the books you were reading, because most (if not all) php books, which touch on MVC, are wrong.

If you want to become a better developer, i would recommend for you to start with article by Marting Fowler - GUI Architectures. Followed by book from same author - "Patterns of Enterprise Application Architecture". Then the next step would be for you to research SOLID principles and understand how to write code which follows Law of Demeter. This should cover the basics =]

Can I use MVC with PHP ?

Not really. At least not the classical MVC as it was defined for Smalltalk.

Instead in PHP you have 4 other patterns which aim for the same goal: MVC Model2, MVP, MVVM and HMVC. Again, I am too lazy to write about differences one more time, so I'll just link to an old comment of mine.

What is Model ?

First thing you must understand is that Model in MVC is not a class or an object. It is a layer which contains multitude of classes. Basically model layer is all of the layers combined (though, the second layer there should be called "Domain Object Layer", because it contains "Domain Model Objects"). If you care to read quick summary on what is contained in each part of Model layer, you can try reading this old comment (skip to "side note" section).

Model layer consists of all the 3 concentric circles

The image is taken from Service Layer article on Fowler's site.

What does the Controllers do ?

Controller has one major responsibilities in MVC (I'm gonna talk about Model2 implementation here):

Execute commands on structures from model layer (services or domain objects), which change the state of said structures.

It usually have a secondary responsibility: to bind (or otherwise pass) structures from Model layer to the View, but it becomes a questionable practice, if you follow SRP

Where do I put SQL related code ?

The storage and retrieval of information is handled at the Data Source Layer, and is usually implemented as DataMapper (do not confuse with ORMs, which abuse that name).

Here is how a simplified use of it would look like:

$mapper = $this->mapperFactory->build(Model\Mappers\User::class);
$user = $this->entityFactory->build(Model\Entities\User::class);

$user->setId(42);
$mapper->fetch($user);

if ($user->isBanned() && $user->hasBannExpired()){
    $user->setStatus(Model\Mappers\User::STATUS_ACTIVE);
}

$mapper->store($user);

As you see, at no point the Domain Object is even aware, that the information from it was stored. And neither it cases about where you put the data. It could be stored in MySQL or PostgreSQL or some noSQL database. Or maybe pushed to remote REST API. Or maybe the mapper was a mock for testing. All you would need to do, to replace the mapper, is provide this method with different factory.

Also, please see these related posts:

understanding MVC Views in PHP
testable Controllers with dependencies
how should services communicate between each other?
MVC for advanced PHP developers

Database Queries in MVC Model

In a MVC project if I put LINQ queries in Model, is it against the MVC Pattern?

No, it's not against the MVC pattern. Database queries are perfectly fine in the Model. Obviously a clear distinction should be made between the Model and the View Model that you are passing to your views. The view model should not contain any database specific stuff.

Or should I move these methods (GetNameById, GetAuthorById) to Controller?

Absolutely not. The controller's responsibility is not to query a database. A controller responsibility is to talk to the Model, build a view model and pass this view model to the view. A controller shouldn't even know what a database is.

ASP.NET MVC - Where to put database queries

The simple way to handle this with a repository pattern. This is not the best way to do it. But will give you an idea how you can handle this with the repository pattern.

create a repository to do all your db transactions

public interface IRepository
{
  Order GetOrder(int orderId);
}
public class Repository : IRepository
{
   YourDBContext db;
   public Repository()
   {
      db = new YourDBContext ();
   }
   public User GetOrder(int orderId)
   {
      return db.Orders.FirstOrDefault(s=>s.OrderID==orderId);
   }
}

You may create this in the same project (under a "Data access logic") or create a separate class library for this (and refer it to wherever you use it).

And now in your controller, after importing the necessary namespaces, Just create an object of your repository and call the method you are interested in

public OrderController :Controller
{
  protected IRepository repo;
  public OrderController()
  {
    repo=new Repository();
  }
  public OrderController(IRepository repositary)
  {
    // This constructor is for your Unit test project, 
    // you can pass a mock repository here
    // Read dependency injection
    repo=repository;
  }
  public ActionResult details(int id)
  {
    var order=repo.GetOrder(id);
    if(order!=null)
    {
      return View(order);
    }
  }
}

You may consider using a view-model if think your view need it. in that case you need to read the property values from your domain object and set it the the instance of your view-model and return that to your view.

You may move the code to different classes/ layers/projects as your code/functionality grows.

Where queries should be placed in ASP.NET MVC

Your question is about infrastructure of a project that depends on lots of factors.

Considering the information you have provided in your question:

I have a plan to also convert this project in Desktop Application in future.

for the sake of usability, you should have your Data Access Layer in a separate project.

If you are interested in obtaining more information about the infrastructure of a project, I recommend to you: Microsoft Spain - Domain Oriented N-Layered .NET 4.0 Sample App that is well documented and gives you much information that you need to produce an enterprise level application.

Calling a Query that Returns in MVC 5

It looks like the table name you're after isn't "tblProduct" - I'm guessing it's more likely to be "tblProducts", so if you amend your SQL query it should work.

This is assuming you've created that table in the sql database - hard to tell without looking at the schema

string query = "select * from tblProducts where FREETEXT  (Meta, 'branded')";
IEnumerable<tblProduct> data = db.Database.SqlQuery<tblProduct>(query);

ps. the error looks like it's coming from the return statement because that's where the query is getting actually executed against the db.

Search query database first mvc

The problem is that the model on your page is looking for a List<Loan>, and you're only sending it a Loan. As of the time that I posted this answer, you don't have your controller method or view as part of your question, so I would change your query to this:

private List<Loan> Gethistory(string searchString)
{
    var loan = db.Loans
                 .Where(a => a.MemberId
                              .ToString()
                              .Contains(searchString) && 
                             a.Book.OnLoan == 1)
                 .OrderByDescending(a => a.LoanId)
                 .FirstOrDefault();

    return new List<Loan> { loan };
}

As Stefan said in his answer, SQL doesn't have a LastOrDefault method, so you need to write your query in a way that is easier for EF to convert to SQL.

Alternatively, you could change your View to have a model that is a single Loan, though that would make the (presumable) grid you're displaying them/it in a little less intuitive.

display more than one database query in .net MVC

You have to create a new class with all the properties you want to display in your View.

Example:

public class StudentModel {
        public int Id { get; set; }
        public string Title { get; set; }
        public string Name { get; set; }
        public strign Surname { get; set; }
    }
    public class MarkModel {
        public int Id { get; set; }
        public int StudentId { get; set; }
        public int SubjectId { get; set; }
        public int Mark { get; set; }
    }
    public class ResultModel
    {

      public StudentModel Student { get; set; }

      public List<MarkModel> Marks { get; set; }

    }

    public class HomeController : Controller
    {
        // GET: Home
        public ActionResult Index()
        {
            //int sNumber = 1;
           var model= new ResultModel{
              Student = new StudentModel(),
              Marks = new List<MarkModel>();
           }

            string constr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
            using (MySqlConnection con = new MySqlConnection(constr))
            {
                string queryStudent = "SELECT id, title, `first name`, surname FROM `STUDENT` WHERE Id=1";
                using (MySqlCommand cmd = new MySqlCommand(queryStudent))
                {
                    cmd.Connection = con;               
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {    
                               model.student.Id = Convert.ToInt32(sdr["id"]),
                               model.student.Title = sdr["title"].ToString(),
                               model.student.Name = sdr["first name"].ToString(),
                               model.student.Surname = sdr["surname"].ToString()                     
                        }
                    }               
                }

                string queryMarks = "SELECT Id, `StudentId`, StudentId,Mark FROM `MARK` WHERE StudentId=1";
                using (MySqlCommand cmd = new MySqlCommand(queryMarks))
                {
                    cmd.Connection = con;               
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            model.Marks.Add(new MarkModel
                            {
                               Id = Convert.ToInt32(sdr["Id"]),
                               StudentId = Convert.ToInt32(sdr["StudentId"]),
                               StudentId = Convert.ToInt32(sdr["StudentId"]),
                               Mark = Convert.ToInt32(sdr["Mark"]),
                            });
                        }
                    }               
                } 
            }

            return View(model);
        }

Where Do I Put a Database Query in MVC