Populate Timeline Chart from Database using Google API chart and jQuery ChartJS in ASP.Net

Last Reply 3 months ago By dharmendr

Posted 3 months ago

Please assist me in making this type of chart in asp.net using chart.js library.

It may be of project activity chart somewhat like :

1- gannt

2- timeline

Chart Image:

 https://i.ibb.co/sC9rZmC/ganntchart.png

Sample Data :

 

DECLARE @sampleDate table (name nvarchar(200),startingMonth nvarchar(3),endingMonth nvarchar(3),startinDate date,endingDate date);
INSERT INTO @sampleDate VALUES ('Brickwork stage','Jun','Jun','2018-06-14','2018-06-20')
INSERT INTO @sampleDate VALUES ('Building Contract','Mar','Mar','2018-03-01','2018-03-16')
INSERT INTO @sampleDate VALUES ('Doors and windows framing','May','May','2018-05-16','2018-05-16')
INSERT INTO @sampleDate VALUES ('Fixout state','Jul','Jan','2018-07-19','2019-01-05')
INSERT INTO @sampleDate VALUES ('Frames Installation','Apr','May','2018-04-13','2018-05-28')
INSERT INTO @sampleDate VALUES ('Land Excavation','Mar','May','2018-03-26','2018-05-31')
INSERT INTO @sampleDate VALUES ('Land Survey','Mar','May','2018-03-19','2018-05-23')
INSERT INTO @sampleDate VALUES ('Lockup Stage','Jun','Aug','2018-06-06','2018-08-02')
INSERT INTO @sampleDate VALUES ('Practical Completion','Dec','Jan','2018-12-31','2019-01-02')
INSERT INTO @sampleDate VALUES ('Roofing','Jun','Jun','2018-06-26','2018-06-29')
INSERT INTO @sampleDate VALUES ('Slab / Timber Stumps','Apr','Jan','2018-04-17','2019-01-03')
INSERT INTO @sampleDate VALUES ('Supplies','Mar','Jul','2018-03-19','2018-07-02')
INSERT INTO @sampleDate VALUES ('Trusses Installation','May','Jun','2018-05-19','2018-06-01')


SELECT * FROM @sampleDate

 

You are viewing reply posted by: dharmendr 3 months ago.
Posted 3 months ago Modified on 3 months ago

You can use Google API chart.

Refer below link for details.

https://developers.google.com/chart/interactive/docs/gallery/timeline

HTML

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
    google.charts.load('current', { 'packages': ['timeline'] });
    google.charts.setOnLoadCallback(drawChart);
    function drawChart() {
        $.ajax({
            type: "POST",
            url: "Default.aspx/GetChartData",
            data: {},
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (response) {
                var container = document.getElementById('timeline');
                var chart = new google.visualization.Timeline(container);
                var dataTable = new google.visualization.DataTable();
                dataTable.addColumn({ type: 'string', id: 'Name' });
                dataTable.addColumn({ type: 'date', id: 'Start' });
                dataTable.addColumn({ type: 'date', id: 'End' });
                for (var i = 0; i < response.d.length; i++) {
                    var name = response.d[i][0].toString();
                    var start = new Date(response.d[i][1].toString());
                    var end = new Date(response.d[i][2].toString());
                    dataTable.addRows([[name, start, end]]);
                }
                var rowHeight = 40;
                var chartHeight = (dataTable.getNumberOfRows() + 1) * rowHeight;
                var options = {
                    timeline: {
                        groupByRowLabel: true,
                        rowLabelStyle: {
                            fontName: 'Arial'
                            , fontSize: 12
                            , color: 'Black'
                        },
                        barLabelStyle: {
                            fontName: 'Arial'
                            , fontSize: 10
                        }
                    },
                    avoidOverlappingGridLines: true
                    , height: chartHeight
                    , width: '100%'
                    // ,colors: ['Orange'] - Set TimeLine Color
                };
                chart.draw(dataTable, options);
            }, error: function (response) {
                alert(response.responseText);
            }
        });
    }
</script>
<div id="timeline" />

Namespaces

C#

using System.Collections.Generic;
using System.Data;
using System.Web.Services;

VB.Net

Imports System.Collections.Generic
Imports System.Data
Imports System.Web.Services

Code

C#

[WebMethod]
public static List<object> GetChartData()
{
    // Get your DataTable from DataBase.
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[] { 
        new DataColumn("Name"), new DataColumn("StartMonth"), new DataColumn("EndMonth" ), new DataColumn("StartDate"), new DataColumn("EndDate") });
    dt.Rows.Add("Brickwork stage", "Jun", "Jun", "2018-06-14", "2018-06-20");
    dt.Rows.Add("Building Contract", "Mar", "Mar", "2018-03-01", "2018-03-16");
    dt.Rows.Add("Doors and windows framing", "May", "May", "2018-05-16", "2018-05-16");
    dt.Rows.Add("Fixout state", "Jul", "Jan", "2018-07-19", "2019-01-05");
    dt.Rows.Add("Frames Installation", "Apr", "May", "2018-04-13", "2018-05-28");
    dt.Rows.Add("Land Excavation", "Mar", "May", "2018-03-26", "2018-05-31");
    dt.Rows.Add("Land Survey", "Mar", "May", "2018-03-19", "2018-05-23");
    dt.Rows.Add("Lockup Stage", "Jun", "Aug", "2018-06-06", "2018-08-02");
    dt.Rows.Add("Practical Completion", "Dec", "Jan", "2018-12-31", "2019-01-02");
    dt.Rows.Add("Roofing", "Jun", "Jun", "2018-06-26", "2018-06-29");
    dt.Rows.Add("Slab / Timber Stumps", "Apr", "Jan", "2018-04-17", "2019-01-03");
    dt.Rows.Add("Supplies", "Mar", "Jul", "2018-03-19", "2018-07-02");
    dt.Rows.Add("Trusses Installation", "May", "Jun", "2018-05-19", "2018-06-01");

    List<object> chartData = new List<object>();
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        chartData.Add(new object[] { dt.Rows[i]["Name"], dt.Rows[i]["StartDate"], dt.Rows[i]["EndDate"] });
    }

    return chartData;
}

VB.Net

<WebMethod()>
Public Shared Function GetChartData() As List(Of Object)
    ' Get your DataTable from DataBase.
    Dim dt As DataTable = New DataTable()
    dt.Columns.AddRange(New DataColumn() {
        New DataColumn("Name"), New DataColumn("StartMonth"), New DataColumn("EndMonth"), New DataColumn("StartDate"), New DataColumn("EndDate")})
    dt.Rows.Add("Brickwork stage", "Jun", "Jun", "2018-06-14", "2018-06-20")
    dt.Rows.Add("Building Contract", "Mar", "Mar", "2018-03-01", "2018-03-16")
    dt.Rows.Add("Doors and windows framing", "May", "May", "2018-05-16", "2018-05-16")
    dt.Rows.Add("Fixout state", "Jul", "Jan", "2018-07-19", "2019-01-05")
    dt.Rows.Add("Frames Installation", "Apr", "May", "2018-04-13", "2018-05-28")
    dt.Rows.Add("Land Excavation", "Mar", "May", "2018-03-26", "2018-05-31")
    dt.Rows.Add("Land Survey", "Mar", "May", "2018-03-19", "2018-05-23")
    dt.Rows.Add("Lockup Stage", "Jun", "Aug", "2018-06-06", "2018-08-02")
    dt.Rows.Add("Practical Completion", "Dec", "Jan", "2018-12-31", "2019-01-02")
    dt.Rows.Add("Roofing", "Jun", "Jun", "2018-06-26", "2018-06-29")
    dt.Rows.Add("Slab / Timber Stumps", "Apr", "Jan", "2018-04-17", "2019-01-03")
    dt.Rows.Add("Supplies", "Mar", "Jul", "2018-03-19", "2018-07-02")
    dt.Rows.Add("Trusses Installation", "May", "Jun", "2018-05-19", "2018-06-01")
    Dim chartData As List(Of Object) = New List(Of Object)()
    For i As Integer = 0 To dt.Rows.Count - 1
        chartData.Add(New Object() {dt.Rows(i)("Name"), dt.Rows(i)("StartDate"), dt.Rows(i)("EndDate")})
    Next

    Return chartData
End Function

Screenshot