Dynamically bind data from multiple tables on Google Line chart using jQuery Ajax in ASP.Net

sofia
 
on Oct 26, 2021 11:45 PM
452 Views

Hi dharmendr sir,

I have to make google line chart  populated dynamically from my database

My database name is testrecord have two table.

Table1 name test have two column.

Ipaddress       name

12.44.21.09      asd

12.44.21.10      ghh

12.44.21.11      etee

12.44.21.12    qwr

12.44.21.13     wet

And so on

Table2 name is record include

Ipaddress         pingtime                          ipstatus       millisecond

12.44.21.09      10/11/2021 10:24:10             up                  0

12.44.21.10      10/11/2021 10:24:10             up                  26

12.44.21.11     10/11/2021 10:24:10             up                  1

12.44.21.12      10/11/2021 10:24:10             down             0

12.44.21.13      10/11/2021 10:24:10             up                  0

12.44.21.09      10/11/2021 11:34:10             up                  300

12.44.21.10      10/11/2021 11:34:10             up                  5000

I have to draw linechart against each ipaddress. I have to take pingtime on x-axis and millisecond on y-axis, but the condition should be if millisecond is zero(0) and ipstatus is up then line shouldn’t show it down.

I have to take ipaddress from table1 and pingtime , ipstatus and millisecond from table2, because in table2 there are multiple record against each ipaddress.

i have to show last 7 day record on chart.

Please help me to update my code according to above condition.

My code is attach below.

javascript

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
    google.load("visualization", "1", { packages: ["corechart"] });
    google.setOnLoadCallback(drawChart);
    function drawChart() {
        $.ajax({
            type: "POST",
            url: "graph.aspx/GetIpAddress",
            data: {},
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (r) {
                for (var i = 0; i < r.d.length; i++) {
                    MakeAjaxCall(r.d[i], $("#chart" + (i + 1))[0]);
                }
            },
            failure: function (r) {
                alert(r.responsetext);
            },
            error: function (r) {
                alert(r.responsetext);
            }
        });
    }

    function MakeAjaxCall(ipAddress, dvElement) {
        $.ajax({
            type: "POST",
            url: "graph.aspx/GetChartData",
            data: '{ipAddress:"' + ipAddress + '"}',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (r) {
                var data = new google.visualization.DataTable();
                data.addColumn('date', 'pingtime');
                data.addColumn('number', 'ipstatus');
                for (var i = 0; i < r.d.length; i++) {
                    data.addRow([new Date(parseInt(r.d[i][0].substr(6))), parseInt(r.d[i][1])]);
                }

                var maxValue = Math.max.apply(Math, r.d.map(function (x) { return x[1]; }));
                var tick = [];
                for (var i = 0; i <= (maxValue / 25) + 1; i++) {
                    tick.push(i * 25);
                }

                var chart = new google.visualization.LineChart(dvElement);
                var options = {
                    title: 'ipaddress : ' + ipAddress,
                    width: 400,
                    height: 400,
                    bar: { groupWidth: "95%" },
                    legend: { position: "none" },
                    isStacked: true,
                    vAxis: {
                        viewWindow: {
                            min: 0,
                            max: maxValue
                        },
                        ticks: tick,
                        title: 'IPSTATUS',
                        textStyle:
                        {
                            fontStyle: "bold",
                            italic: true,
                            fontSize: 12,
                            color: '#0000FF'
                        },
                        titleTextStyle:
                        {
                            fontStyle: "normal",
                            fontWeight: 'bold',
                            italic: true,
                            fontSize: 12,
                            color: '#0000FF'
                        }
                    },
                    hAxis: {
                        title: 'Time',
                        titleTextStyle:
                        {
                            fontStyle: "normal",
                            fontWeight: 'bold',
                            italic: true,
                            color: '#0000FF'
                        }
                    }
                };

                chart.draw(data, options);
            },
            failure: function (r) {
                alert(r.responseText);
            },
            error: function (r) {
                alert(r.responseText);
            }
        });
    }
</script>

c#

namespace NodesDetails
{
    public partial class graph : System.Web.UI.Page
    {
        [WebMethod]
        public static List<object> GetChartData(string ipAddress)
        {
            string query = string.Format("SELECT [pingtime],[ipstatus] FROM [dbo].[record] WHERE ipaddress='{0}' AND CONVERT(DATE,pingtime,103) BETWEEN CONVERT(DATE,DATEADD(DAY, -7, GETDATE()),103) AND CONVERT(DATE,GETDATE(),103) ORDER BY CONVERT(DATETIME,pingtime,103) ASC", ipAddress);
            string constr = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;
            List<object> chartData = new List<object>();
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    con.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            chartData.Add(new object[] { Convert.ToDateTime(sdr["pingtime"]), sdr["ipstatus"] });
                        }
                    }
                    con.Close();

                    return chartData;
                }
            }
        }

        [WebMethod]
        public static List<string> GetIpAddress()
        {
            string query = "SELECT DISTINCT ipaddress FROM [dbo].[record]";
            string constr = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;
            List<string> ips = new List<string>();
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    con.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            ips.Add(sdr["ipaddress"].ToString());
                        }
                    }
                    con.Close();

                    return ips;
                }
            }
        }
    }
}

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Oct 27, 2021 04:17 AM
on Oct 28, 2021 02:11 AM

Hi sofia,

Refer below code.

HTML

<table>
    <tr>
        <td><div id="chart1"></div></td>
        <td><div id="chart2"></div></td>
        <td><div id="chart3"></div></td>
    </tr>
    <tr>
        <td><div id="chart4"></div></td>
        <td><div id="chart5"></div></td>
        <td><div id="chart6"></div></td>
    </tr>
</table>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
    google.load("visualization", "1", { packages: ["corechart"] });
    google.setOnLoadCallback(drawChart);
    function drawChart() {
        $.ajax({
            type: "POST",
            url: "Default.aspx/GetIpAddress",
            data: {},
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (r) {
                for (var i = 0; i < r.d.length; i++) {
                    MakeAjaxCall(r.d[i], $("#chart" + (i + 1))[0]);
                }
            },
            failure: function (r) {
                alert(r.responsetext);
            },
            error: function (r) {
                alert(r.responsetext);
            }
        });
    }

    function MakeAjaxCall(ipAddress, dvElement) {
        $.ajax({
            type: "POST",
            url: "Default.aspx/GetChartData",
            data: '{ipAddress:"' + ipAddress + '"}',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (r) {
                var data = new google.visualization.DataTable();
                data.addColumn('date', 'pingtime');
                data.addColumn('number', 'ipstatus');
                for (var i = 0; i < r.d.length; i++) {
                    data.addRow([new Date(parseInt(r.d[i][0].substr(6))), parseInt(r.d[i][1])]);
                }

                var maxValue = Math.max.apply(Math, r.d.map(function (x) { return x[1]; }));
                var tick = [];
                for (var i = 0; i <= (maxValue / 25) + 1; i++) {
                    tick.push(i * 25);
                }

                var chart = new google.visualization.LineChart(dvElement);
                var options = {
                    title: 'ipaddress : ' + ipAddress,
                    width: 400,
                    height: 400,
                    bar: { groupWidth: "95%" },
                    legend: { position: "none" },
                    isStacked: true,
                    vAxis: {
                        viewWindow: {
                            min: 0,
                            max: maxValue
                        },
                        ticks: tick,
                        title: 'IPSTATUS',
                        textStyle:
                        {
                            fontStyle: "bold",
                            italic: true,
                            fontSize: 12,
                            color: '#0000FF'
                        },
                        titleTextStyle:
                        {
                            fontStyle: "normal",
                            fontWeight: 'bold',
                            italic: true,
                            fontSize: 12,
                            color: '#0000FF'
                        }
                    },
                    hAxis: {
                        title: 'Time',
                        titleTextStyle:
                        {
                            fontStyle: "normal",
                            fontWeight: 'bold',
                            italic: true,
                            color: '#0000FF'
                        }
                    }
                };

                chart.draw(data, options);
            },
            failure: function (r) {
                alert(r.responseText);
            },
            error: function (r) {
                alert(r.responseText);
            }
        });
    }
</script>

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services

Code

C#

[WebMethod]
public static List<object> GetChartData(string ipAddress)
{
    string query = string.Format("SELECT CONVERT(VARCHAR(20),pingtime,113) pingtime,[millisecond] FROM [dbo].[record] WHERE (millisecond > 0 AND ipstatus = 'up') AND CONVERT(DATE,pingtime,103) BETWEEN CONVERT(DATE,DATEADD(DAY, -7, GETDATE()),103) AND CONVERT(DATE,GETDATE(),103) AND ipaddress='{0}' ORDER BY ipaddress, CONVERT(DATETIME,pingtime,103) ASC", ipAddress);
    string constr = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;
    List<object> chartData = new List<object>();
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            con.Open();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    chartData.Add(new object[] { Convert.ToDateTime(sdr["pingtime"]), sdr["millisecond"] });
                }
            }
            con.Close();

            return chartData;
        }
    }
}

[WebMethod]
public static List<string> GetIpAddress()
{
    string query = "SELECT DISTINCT ipaddress FROM [dbo].[record]";
    string constr = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;
    List<string> ips = new List<string>();
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            con.Open();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    ips.Add(sdr["ipaddress"].ToString());
                }
            }
            con.Close();

            return ips;
        }
    }
}

VB.Net

<WebMethod>
Public Shared Function GetChartData(ByVal ipAddress As String) As List(Of Object)
    Dim query As String = String.Format("SELECT CONVERT(VARCHAR(20),pingtime,113) pingtime,[millisecond] FROM [dbo].[record] WHERE (millisecond > 0 AND CONVERT(DATE,pingtime,103) BETWEEN CONVERT(DATE,DATEADD(DAY, -7, GETDATE()),103) AND CONVERT(DATE,GETDATE(),103) AND ipstatus = 'up') AND ipaddress='{0}' ORDER BY ipaddress, CONVERT(DATETIME,pingtime,103) ASC", ipAddress)
    Dim constr As String = ConfigurationManager.ConnectionStrings("dbcs").ConnectionString
    Dim chartData As List(Of Object) = New List(Of Object)()
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand(query)
            cmd.CommandType = CommandType.Text
            cmd.Connection = con
            con.Open()
            Using sdr As SqlDataReader = cmd.ExecuteReader()
                While sdr.Read()
                    chartData.Add(New Object() {Convert.ToDateTime(sdr("pingtime")), sdr("millisecond")})
                End While
            End Using
            con.Close()
            Return chartData
        End Using
    End Using
End Function

<WebMethod>
Public Shared Function GetIpAddress() As List(Of String)
    Dim query As String = "SELECT DISTINCT ipaddress FROM [dbo].[record]"
    Dim constr As String = ConfigurationManager.ConnectionStrings("dbcs").ConnectionString
    Dim ips As List(Of String) = New List(Of String)()
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand(query)
            cmd.CommandType = CommandType.Text
            cmd.Connection = con
            con.Open()
            Using sdr As SqlDataReader = cmd.ExecuteReader()
                While sdr.Read()
                    ips.Add(sdr("ipaddress").ToString())
                End While
            End Using
            con.Close()
            Return ips
        End Using
    End Using
End Function

Screenshot