locked
help with implementing query RRS feed

  • 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]
    Wednesday, January 21, 2015 12:29 PM

Answers

  • 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]";
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 21, 2015 2:10 PM
  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 21, 2015 4:03 PM

All replies

  • 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/

    Wednesday, January 21, 2015 1:02 PM
  • 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]";
    }
    Wednesday, January 21, 2015 1:10 PM
  • 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]";
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 21, 2015 2:10 PM
  • User1853794821 posted

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

    var examsQueryResults=db.Query(sql)

    Wednesday, January 21, 2015 2:40 PM
  • 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?

    Wednesday, January 21, 2015 3:21 PM
  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 21, 2015 4:03 PM