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

sofia

SOLVED CLOSED ACTIVE ANSWERED
User: sofia
Posted: on Oct 26, 2021 11:45 PM
Forum: jQuery and jQuery Plugins
Answer: 1
Views: 1752

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>

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

dharmend...

0 2

Reply
ANSWER
User: dharmendr
Replied: on Oct 27, 2021 04:17 AM
Modified: on Oct 28, 2021 02:11 AM
Report

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

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

[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

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

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

Company

Explore

Follow us

Contact Us