help with implementing query

Question

User-2060576634 posted

hi.. does anyone now how to implement this code in razor markup? works fine with sql compact edition but encounters errors in razor..

select
    e.ExamId,
    e.Title,
    e.[Date],
    MAX(case when s.[Subject] = 'Math' then c.[Text] end) as Math,
    MAX(case when s.[Subject] = 'Physics' then c.[Text] end) as Physics,
    MAX(case when s.[Subject] = 'Chemistry' then c.[Text] end) as Chemistry
from Exams as e
inner join Contents as c
    on c.ExamId = e.ExamId
inner join Subjects as s
    on s.SubjectId = c.SubjectId
group by
    e.ExamId,
    e.Title,
    e.[Date]

Answer 1

User1853794821 posted

You cannot do SQL queries directly in razor/c# code.  You have to use the DataBase class to couple your razor code and your SQL Server database.

The correct way is to put the query into a string variable.  Then open up the database helper and use its Query method to run the query in the string.

Take a look at: 

http://www.asp.net/web-pages/overview/data/5-working-with-data

http://www.asp.net/web-pages/overview/api-reference/asp-net-web-pages-api-reference#Data

https://www.simple-talk.com/dotnet/asp.net/webmatrix-website-development-made-easier/

Answer 2

User-2060576634 posted

I use sth like this but every line of it shows errors

@{
var db = Database.Open("mydatabase"); 
var sql = " select
    e.ExamId,
    e.Title,
    e.[Date],
    MAX(case when s.[Subject] = 'Math' then c.[Text] end) as Math,
    MAX(case when s.[Subject] = 'Physics' then c.[Text] end) as Physics,
    MAX(case when s.[Subject] = 'Chemistry' then c.[Text] end) as Chemistry
from Exams as e
inner join Contents as c
    on c.ExamId = e.ExamId
inner join Subjects as s
    on s.SubjectId = c.SubjectId
group by
    e.ExamId,
    e.Title,
    e.[Date]";
}

Answer 3

User-821857111 posted

If you want to break a string over multiple lines in C# you have to use the @ character to denote a verbatim string literal:

var sql = @" select
    e.ExamId,
    e.Title,
    e.[Date],
    MAX(case when s.[Subject] = 'Math' then c.[Text] end) as Math,
    MAX(case when s.[Subject] = 'Physics' then c.[Text] end) as Physics,
    MAX(case when s.[Subject] = 'Chemistry' then c.[Text] end) as Chemistry
from Exams as e
inner join Contents as c
    on c.ExamId = e.ExamId
inner join Subjects as s
    on s.SubjectId = c.SubjectId
group by
    e.ExamId,
    e.Title,
    e.[Date]";

Answer 4

User1853794821 posted

Of course, you will still nbeed to do the database Query method invocation:

var examsQueryResults=db.Query(sql)

Answer 5

User-2060576634 posted

thanks alot.. did the trick.. but one last question:

in order to display an html table derived from the query above, using one foreach loop would be enough.but would it be a wrong approach to write a seperate query for each of our html tables columns?

Answer 6

User-821857111 posted

would it be a wrong approach to write a seperate query for each of our html tables columns?

Yes. You should be able to flatten the data in your query and get all you need in one go.