C# 从 SQL 服务器数据库创建 JSON 结果

Creating a JSON result from SQL server database

提问by Simon Kiely

I have an SQL server with the following layout

我有一个具有以下布局的 SQL 服务器

Table (    id int
           title varchar(40),
           start Date(),
           end Date(),
           allDay bool,
           username varchar(40)
      );

I have gotten the following code from this blogto create a JSON object from the data I wish to use, however his data is stored differently. How do I create the same object, extracted from my database?

我从这个博客中获得了以下代码，用于根据我希望使用的数据创建一个 JSON 对象，但是他的数据的存储方式不同。如何创建从我的数据库中提取的相同对象？

I am guessing I need to make the file a .cshtml file rather than a .js file and use this :

我猜我需要将文件设为 .cshtml 文件而不是 .js 文件并使用它：

@{
    var db = Database.Open("events"); 
    var selectQueryString = "SELECT * FROM events";
}

 @foreach(var row in db.Query(selectQueryString)){ }

But how do I adapt this code to produce the same JSON object?

但是我如何修改这段代码以生成相同的 JSON 对象？

Here is the relevant code from the blog, my attempt is below :

这是博客中的相关代码，我的尝试如下：

public JsonResult GetEvents(double start, double end)
{
    var userName = Session["UserName"] as string;
    if(string.IsNullOrEmpty(userName))
    {
        return null;
    }

    var fromDate = ConvertFromUnixTimestamp(start);
    var toDate = ConvertFromUnixTimestamp(end);

    var rep = Resolver.Resolve<IEventRepository>();
    var events = rep.ListEventsForUser(userName,fromDate,toDate);

    var eventList = from e in events
                    select new {
                                id = e.Id,
                                title = e.Title,
                                start = e.FromDate.ToString("s"),
                                end = e.ToDate.ToString("s"),
                                allDay = false
                            }; 

    var rows = eventList.ToArray();
    return Json(rows,JsonRequestBehavior.AllowGet);           
}

Edit :

编辑 ：

I am now working with the following .cshtml code for the GetEvents command, but it will not work. Does anybody have any ideas ?

我现在正在使用以下用于 GetEvents 命令的 .cshtml 代码，但它不起作用。有人有任何想法吗？

   @{ 
        var origin = new DateTime(1970, 1, 1, 0, 0, 0, 0);
        var fromDate = origin.AddSeconds((Request["start"]));
        var toDate = origin.AddSeconds(Request["end"]);

        var db = Database.Open("events");
        var result = db.Query("SELECT * FROM events");
        var data = result.Select(x => new 
        {
            id = x.id,
            title = x.title,
            start = x.start.ToString("s"),
            end = x.end.ToString("s"),
            allDay = false            
        }).ToArray();

        Json.Write(data, Response.Output);
        Response.ContentType = "application/json";
    }

采纳答案by Darin Dimitrov

There are no controllers and actions in WebMatrix WebPages. You need to write a separate .cshtmlpage that will query the database and serve the JSON to the response:

WebMatrix 网页中没有控制器和动作。您需要编写一个单独的.cshtml页面来查询数据库并将 JSON 提供给响应：

@{
    var db = Database.Open("events");
    var result = db.Query("SELECT * FROM events");
    var data = result.Select(x => new 
    {
        id = x.id,
        title = x.title,
        start = x.start.ToString("s"),
        end = x.end.ToString("s"),
        allDay = false            
    }).ToArray();

    Json.Write(data, Response.Output);
    Response.ContentType = "application/json";
}

and then in another page in which you want to display the calendar you could configure it:

然后在另一个要显示日历的页面中，您可以对其进行配置：

$(document).ready(function() {             
    $('#calendar').fullCalendar({ 
        theme: true, 
        header: { 
            left: '', 
            center: '', 
            right: '' 
        }, 
        defaultView: 'agendaDay', 
        editable: false, 
        events: '/events.cshtml' 
    }); 
});

---

---

UPDATE: Here's an example of how you could use parametrized queries:

更新：这是一个如何使用参数化查询的示例：

@{

    var origin = new DateTime(1970, 1, 1, 0, 0, 0, 0);
    var fromDate = origin.AddSeconds(int.Parse(Request["start"]));
    var toDate = origin.AddSeconds(int.Parse(Request["end"]));
    var db = Database.Open("events");
    var sql = "SELECT * FROM events WHERE start >= @0 AND end <= @1";
    var result = db.Query(sql, fromDate, toDate);
    var data = result.Select(x => new 
    {
        id = x.id,
        title = x.title,
        start = x.start.ToString("s"),
        end = x.end.ToString("s"),
        allDay = false            
    }).ToArray();

    Json.Write(data, Response.Output);
    Response.ContentType = "application/json";
}

Now you could query the page like this: /events.cshtml?start=5&end=10

现在你可以像这样查询页面： /events.cshtml?start=5&end=10

回答by Vitthal

DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)

SELECT  @listCol = STUFF(( SELECT distinct  '], [' + [PSize]
                           FROM     Pattern
                         FOR
                           XML PATH('')
                         ), 1, 2, '') + ']'


SET @query = 'SELECT * FROM
      (SELECT PColour as Colour_Size_Matrix, PSize, PCode
            FROM Pattern
            ) src
PIVOT (Count(PCode) FOR PSize
IN (' + @listCol + ')) AS pvt'

EXECUTE ( @query )

I want the result of this query as JSON

我希望此查询的结果为 JSON