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

Last Reply 9 days ago By dharmendr

Posted 11 days 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 9 days ago.
Posted 9 days ago Modified on 7 days 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