<div>I want to run a query in mvc and get data and store it in datatable. Is this possible in mvc?</div> <div></div> <div>I am trying something like this but i am getting error.</div> <div>DataTable d = db.Database.SqlQuery<DataTable>("select
c1, c2 from dt join dt1 on condition").ToList();</div> <div></div> <div>How can i execute a query and get data using in mvc? i have tried using sqlcommand and old asp.net technique it works fine. </div> <div>but i would
like to know what is the best way to run raw sql queries in mvc.</div> <div></div> <div>i am very new to mvc please guide me.</div>
DbContext.Database.SqlQuery is to allow the use of EF on non entity queries. If you want to use DataTable just use ADO.NET.
It's best to be explicit about the error you have but miore likely the issue is that rather than filling a DataSet DbContext.Database.SqlQuery try to map column names to properties of the DataTable object.
Are you really sur you don't want to use EF or some other ORM?
How can i execute a query and get data using in mvc
MVC has nothing to do with it. ADO.NET, SQL Command Objects, inline T-SQL and a datareader can be used at anytime without an ORM even being involved and the DTO pattern
If you find the post has answered your issue, then please mark post as 'answered'.
You could refer to my reply in
this thread, and refer to the following code to execute sql command.
using (DBEntities dBEntities = new DBEntities())
{
dBEntities.Database.ExecuteSqlCommand("insert into Table1 values(1088, 'Conditioner', 'expense4',590);");
var query = dBEntities.Table1.SqlQuery("select * from Table1").ToList<Table1>();
var query2 = dBEntities.Database.SqlQuery<Table1>("select * from Table1").ToList<Table1>();
var query3 = dBEntities.Database.SqlQuery<tablevm>("select t1.id, t1.product, SUM(t1.price) as price from Table1 as t1 group by t1.id, t1.product").ToList<tablevm>();
}
Best regards,
Dillion
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
None
0 Points
3 Posts
Run raw sql queries in MVC
Sep 07, 2018 02:45 PM|Taurus_amat|LINK
All-Star
48290 Points
17987 Posts
Re: Run raw sql queries in MVC
Sep 07, 2018 04:10 PM|PatriceSc|LINK
Hi,
DbContext.Database.SqlQuery is to allow the use of EF on non entity queries. If you want to use DataTable just use ADO.NET.
It's best to be explicit about the error you have but miore likely the issue is that rather than filling a DataSet DbContext.Database.SqlQuery try to map column names to properties of the DataTable object.
Are you really sur you don't want to use EF or some other ORM?
Contributor
4863 Points
4121 Posts
Re: Run raw sql queries in MVC
Sep 07, 2018 06:01 PM|DA924|LINK
How can i execute a query and get data using in mvc
MVC has nothing to do with it. ADO.NET, SQL Command Objects, inline T-SQL and a datareader can be used at anytime without an ORM even being involved and the DTO pattern
All-Star
45489 Points
7008 Posts
Microsoft
Re: Run raw sql queries in MVC
Sep 27, 2018 08:45 AM|Zhi Lv - MSFT|LINK
Hi Taurus_amat,
You could refer to my reply in this thread, and refer to the following code to execute sql command.
using (DBEntities dBEntities = new DBEntities()) { dBEntities.Database.ExecuteSqlCommand("insert into Table1 values(1088, 'Conditioner', 'expense4',590);"); var query = dBEntities.Table1.SqlQuery("select * from Table1").ToList<Table1>(); var query2 = dBEntities.Database.SqlQuery<Table1>("select * from Table1").ToList<Table1>(); var query3 = dBEntities.Database.SqlQuery<tablevm>("select t1.id, t1.product, SUM(t1.price) as price from Table1 as t1 group by t1.id, t1.product").ToList<tablevm>(); }Best regards,
Dillion