How to Create a Google Chart from a Database in ASP.NET MVC Using SQL Server with Entity Framework Core

Creating Google Charts from a Database in ASP.NET MVC

Introduction

Google Charts is a powerful tool for creating a wide range of charts and graphs. In this article, we will explore how to create a Google Chart using data extracted from a SQL database in an ASP.NET MVC application.

Prerequisites

Before we begin, you will need the following:

An ASP.NET MVC 5 project
A SQL Server database
The Entity Framework library for .NET
A basic understanding of C# and SQL

If you are new to these technologies, do not worry. We will take it one step at a time.

Step 1: Setting Up Your Database

First things first, let’s set up our database. In this example, we’ll be using SQL Server with Entity Framework.

Create a New Database

To create a new database in SQL Server, follow these steps:

Open your SQL Server Management Studio (SSMS)
Connect to your server and select the “New Database” option
Choose a name for your database and click “Create”

Create Your Table

Next, we’ll create our table. In this example, we’re creating a simple table with three columns:

Column Name	Data Type	Description

We can create this table in SSMS using the following SQL:

CREATE TABLE Sessions (
    Id INT PRIMARY KEY,
    Session NVARCHAR(255),
    StartDate DATE,
    EndDate DATE
)

Insert Some Data

Once we have our table created, let’s insert some data. We’ll use the following script:

INSERT INTO Sessions (Id, Session, StartDate, EndDate)
VALUES 
(1, 'Baltimore Ravens', '2000-08-05', '2001-01-05'),
(2, 'New England Patriots', '2001-08-05', '2002-01-05'),
(3, 'Tampa Bay Buccaneers', '2002-08-05', '2003-01-05')

Step 2: Setting Up Your ASP.NET MVC Project

Now that we have our database set up, let’s move on to setting up our ASP.NET MVC project.

Create a New MVC Project in Visual Studio

To create a new MVC project in Visual Studio, follow these steps:

Open Visual Studio
Choose “ASP .NET Web Application”
Select the “.NET Framework” option and then select the “MVC” template
Click “OK”

Install Entity Framework

Next, we’ll need to install the Entity Framework library for .NET.

In Solution Explorer, right-click on your project and choose “Manage NuGet Packages”. Search for “Entity Framework” and click on it. Choose the latest version of Entity Framework Core.

Step 3: Creating a Data Model

Now that we have our ASP.NET MVC project set up, let’s create a data model.

Create a New Class

In Solution Explorer, right-click on your project and choose “Add” > “Class”. Name this class Session.

public class Session
{
    public int Id { get; set; }
    public string SessionName { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }
}

Create a Data Context

Next, we’ll create a data context. This will be used to interact with our database.

public class SessionsDbContext : DbContext
{
    public DbSet<Session> Sessions { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Data Source=(localdb)\mssqllocaldb;Initial Catalog=SessionsDb;Integrated Security=True");
    }
}

Step 4: Creating a Controller

Now that we have our data model and data context set up, let’s create a controller.

Create a New Controller

In Solution Explorer, right-click on your project and choose “Add” > “Controller”. Name this controller SessionsController.

public class SessionsController : Controller
{
    private readonly SessionsDbContext _context;

    public SessionsController(SessionsDbContext context)
    {
        _context = context;
    }

    public ActionResult Index()
    {
        var sessions = _context.Sessions.ToList();
        return View(sessions);
    }
}

Create a View

Next, we’ll create a view for our controller.

In Solution Explorer, right-click on your project and choose “Add” > “View”. Name this view Index.cshtml.

@model IEnumerable<Session>

<h2>Sessions</h2>
<a href="@Url.Action("Create", "Sessions")">Create New Session</a>

<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.SessionName)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.StartDate)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.EndDate)
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.SessionName)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.StartDate)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.EndDate)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { id=item.Id }) |
            @Html.ActionLink("Details", "Details", new { id=item.Id }) |
            @Html.ActionLink("Delete", "Delete", new { id=item.Id })
        </td>
    </tr>
}

</table>

Step 5: Creating a Google Chart

Now that we have our data model and controller set up, let’s create a Google Chart.

Create a New View

In Solution Explorer, right-click on your project and choose “Add” > “View”. Name this view GoogleChart.cshtml.

@model IEnumerable<Session>

<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>

<div id="chart_div"></div>

<script type="text/javascript">
    google.charts.load('current', { 'packages': ['timeline'] });
    google.charts.setOnLoadCallback(drawChart);

    function drawChart() {
        var data = new google.visualization.DataTable({

            cols: [
                { id: 'Session', label: 'Session', type: 'string' },
                { id: 'start', label: 'Season Start Date', type: 'date' },
                { id: 'end', label: 'Season End Date', type: 'date' }
            ],

            rows: [
                @foreach (var item in Model) {
                    <tr>
                        <td>@item.SessionName</td>
                        <td>@item.StartDate.ToString("yyyy-MM-dd")</td>
                        <td>@item.EndDate.ToString("yyyy-MM-dd")</td>
                    </tr>
                }
            ]
        });

        var options = {
            height: 450,
            timeline: {
                groupByRowLabel: true
            }
        };

        var chart = new google.visualization.Timeline(document.getElementById('chart_div'));

        chart.draw(data, options);
    }
</script>

Step 6: Creating an Action in Your Controller

Finally, let’s create an action in your controller.

In Solution Explorer, right-click on your project and choose “Add” > “Controller Action”. Name this action GoogleChart.

public ActionResult GoogleChart()
{
    var sessions = _context.Sessions.ToList();
    return View(sessions);
}

Conclusion

That’s it! We have now created a Google Chart from our database in ASP.NET MVC.

You can test your application by navigating to the /Sessions/GoogleChart URL and viewing the GoogleChart.cshtml view.

I hope this tutorial was helpful.

Last modified on 2023-06-08