General approach to visualize SQL CE data using Fl

问题:

I'm very new to SQL Server, Flot.js, working with databases in general. I'm working on a project in ASP.NET MVC 4 (using VS 2012) which has a Microsoft SQL Server Compact 4.0 database filled with data, and I would like to visually display this data using flot.js.

I haven't been able to find any information on the general steps one would take to manipulate the data. Starting with the DB entries in the SQL CE database, and ending with a JSON file (or a CSV - anything flot.js could use as input), what would be the most common approach that a web developer could tak, using ASP.NET MVC 4?

回答1:

Hi your question is quite broad so I apologise if answer is quite vague in places. As you have an existing database it would make sense to use Entity Framework Database First to map your database to a meaningful context which can then be manipulated in your code. Once you have created an edmx model you can then use it in your controllers to manipulate data:

public class YourController : Controller
{
    private DatabaseEntities db = new DatabaseEntities();

    //.... Your controller actions

Database First allows you to reverse engineer a model from an existing database. The model is stored in an EDMX file (.edmx extension) and can be viewed and edited in the Entity Framework Designer. The classes that you interact with in your application are automatically generated from the EDMX file.

Flot takes data in the following format:

[[1,2],[3,4],[5,5]]  // x, y coordinates
[[1,"a"],[2,"b"],[3,"c"]] // Categories

See the Flot documentation for further details. So using Json will not work with flot directly. You will have to create a controller action that returns the data you require in the correct format:

public string GetData()
{
    var query = db.Table.Where(... // linq query for desired data

    var builder = new StringBuilder();
    builder.Append("[");
    foreach (var item in query)
        builder.AppendFormat("[{0}, {1}], ", item.x, item.y);
    var result = builder.ToString();

    return result;
}

Now on the client side you can make a call from jQuery to get the data and draw the chart:

$(function () {

    $.getJSON("../controller/action", function (data) {

        $.plot("#placeholder", [data], {
                // your chart

Just one way of doing it, but think its a good way using MVC. Hopefully this will give you a good overview and you should have enough info from this to get started at least.