How to write pure SQL in asp.net mvc?

Question

I've created a controller class as below:

public class CelebrityController : Controller
{
    private MyDatabase db = new MyDatabase();

    public ActionResult Index()
    {
        var query = db.tableCelebrity.Include(t => t.tableMovie);
        return View(query.ToList());
    }
}

Now I want to do the same thing, but using a pure SQL query in the Index method. I am trying to replace code as below:

public class CelebrityController : Controller
    {
        private MyDatabase db = new MyDatabase();

        public ActionResult Index()
        {
            IEnumerable<tableCelebrity> results = db.ExecuteQuery<tableCelebrity>
            (@"SELECT c1.celebid as CelebrityID, t1.movieName as MovieName
                FROM tableCelebrity as c1, tableMovie as t1
                WHERE c1.celebid = t1.celebid");
        }
    }

But this gives me an error at the line db.ExecuteQuery<tableCelebrity>:

Error 'MyProject.Models.MyDatabase' does not contain a definition for 'ExecuteQuery' and no extension method 'ExecuteQuery' accepting a first argument of type 'MyProject.Models.MyDatabase' could be found

Is it possible with ASP.NET MVC?? If yes, then what changes I have to perform in the method?

Please help me to do that.

I am created Details method as below

public ActionResult ShortDetails(int? id, string tagname)
    {
        tblCelebrity tblcelebrity = db.tblCelebrities.SqlQuery<tblCelebrity>("SELECT * FROM dbo.tblCelebrity WHERE Celebrity_ID =" + id);

        if (tblcelebrity == null)
        {
            return HttpNotFound();
        }
        return View(tblcelebrity);
    }

And Details View accepting type as

@model MyProject.Models.tblCelebrity

I also created tblCelebrity class in my project

But `tblCelebrity tblcelebrity = db.tblCelebrities.SqlQuery("SELECT * FROM dbo.tblCelebrity WHERE Celebrity_ID =" + id); this line gives error as The non-generic method 'System.Data.Entity.DbSet.SqlQuery(string, params object[])' cannot be used with type arguments

Mvc has nothing to do with whatever database framework you choose to you. — asawyer
– asawyer, Commented Jan 14, 2014 at 13:43
Use a micro-Orm, they are exactly what you want. Dapper.net, SqlFu, PetaPoco, ServiceStack.OrmLite etc. But you don't want to query the db in your controller. Create at least a repository for that. — MikeSW
– MikeSW, Commented Jan 14, 2014 at 13:52

Radenko Zec · Accepted Answer · 2014-01-14 13:40:41Z

13

You want something similar to this :

using (var context = new BloggingContext())
{
    var blogs = context.Blogs.SqlQuery("SELECT * FROM dbo.Blogs").ToList();
}

You can find tutorial for that here : http://msdn.microsoft.com/en-us/data/jj592907.aspx

answered Jan 14, 2014 at 13:40

Radenko Zec

7,6896 gold badges37 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Mike Perrenoud Over a year ago

At a minimum include the relevant code snippets from the link for posterity--links as answers are not really acceptable here.

user3194155 Over a year ago

sir but views are strongly typed view so when I am using "Select * from dbo.Blogs where ID='1'" that time var blogs contains string data but Details view expecting <Blogs> type data..how to solve this problem

Radenko Zec Over a year ago

You can create C# Class blogs that correspond to result of that view and than use that class for <Blogs>

user3194155 Over a year ago

Error is : The model item passed into the dictionary is of type 'System.Data.Entity.Infrastructure.DbSqlQuery`1[MyProject.Models.tableCelebrity]', but this dictionary requires a model item of type 'MyProject.Models.tblCelebrity'.

user3194155 Over a year ago

I created tblCelebrity class and write query like tblcelebrity blog = db.tblCelebrities.SqlQuery<tblcelebrity>("SELECT * FROM dbo.tblCelebrity WHERE Celebrity_ID =" + id);

|

tCoe · Accepted Answer · 2017-03-09 17:10:17Z

0

When I have received the error previously, it was because the user in the connection string did not have execute permissions on the DB. Once I used a user that had permissions, or used a "GRANT EXECUTION" statement into the query, everything resolved perfectly.

answered Mar 9, 2017 at 17:10

tCoe

4011 gold badge6 silver badges24 bronze badges

Collectives™ on Stack Overflow

How to write pure SQL in asp.net mvc?

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related