Hello Together,
i am building a web application with ASP.net to display some data from SQL-Server in a pie Chart. To generate the chart i am using the Highcharts javascript libary.
I pass the values from my Serverside to a <asp:Hiddenfield/> in a JSON format. The JSON-String is buildet on the Server-Side(I dont know if this is the best way to pass a JSON-String to the Client Side?)
Later on clientside i am watching on the hiddenfild and pull every 500ms the data from the <asp:Hiddenfield/>
My Code works fine, but i dont know if this method to pull the data is the right one or if its ok for my SQL-Server if i pull every secound from the Database.
Maybe you can give me a little statement to this :)
I am new in ASP.Net so sorry for my bad programming.
HTML-Code:
<asp:ScriptManager runat="server">
</asp:ScriptManager>
<div id="page-content-wrapper">
<div class="page-content inset" data-spy="scroll" data-target="#spy">
<div class="row">
<div id="anch11">
<h1 class="text-center">Report Übersicht:</h1>
</div>
</div>
<div class="row">
<div class="col-sm-12">
<asp:UpdatePanel runat="server" ID="UpdatePanel1">
<ContentTemplate>
<asp:Timer ID="Timer1" Interval="1000" runat="Server">
</asp:Timer>
<asp:HiddenField ID="jsonstring" runat="server" />
</ContentTemplate>
</asp:UpdatePanel>
</div>
</div>
<div class="row">
<div class="col-sm-12">
<div id="container" style="min-width: 310px; max-width: 900px; height: 700px; margin: 0 auto"></div>
</div>
</div>
</div>
</div>
Javascript-Code:
$(function () {
var chart = new Highcharts.Chart({
chart: {
renderTo: 'container',
type: 'pie'
},
title: {
text: '<span style="font-size:24px">NOS-Datenbank User aktivitäten'
},
subtitle: {
text: 'Klick'
},
plotOptions: {
series: {
dataLabels: {
enabled: true,
format: '<span style="font-size:14px">{point.name}: {point.y:.f} st.'
}
}
},
tooltip: {
headerFormat: '<span style="font-size:18px">{series.name}</span><br>',
pointFormat: '<span style="color:{point.color}">{point.name}</span>: <b>{point.y:.f}</b> gesamt<br/>'
},
series: [{
name: 'Nutzer',
colorByPoint: true,
data: []
}],
drilldown: {
series: []
}
});
setInterval(function () {
var jsonstringdata = document.getElementById('jsonstring').value;
var teststr = '[{"name":"Peter Mafei", "y": 21, "drilldown":"Peter Mafei"},{"name":"Kevin Kutschenreiter", "y": 23, "drilldown":"Kevin Kutschenreiter"}]';
var Ojson = JSON.parse(jsonstringdata);
chart.series[0].setData(Ojson);
}, 500);
});
Server Side VB.Net Code
Public Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
Me.GetSqlData()
End Sub
Protected Sub Timer1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer1.Tick
Me.GetSqlData()
End Sub
Private Sub GetSqlData()
Dim con As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("ERData").ConnectionString)
con.Open()
Dim cmd As SqlCommand
cmd = New SqlCommand("SELECT TOP 10 O_User, COUNT(O_User) as CountedUser FROM dbo.DBOpened GROUP BY O_User ORDER BY CountedUser DESC", con)
cmd.Connection = con
cmd.ExecuteNonQuery()
Dim dt As New DataTable()
dt.Columns.AddRange(New DataColumn() {New DataColumn("First", GetType(String))})
Dim dr As SqlDataReader = cmd.ExecuteReader()
Do While dr.Read()
dt.Rows.Add("{""" & "name""" & ":""" & dr("O_User") & """, ""y" & """:" & " " & dr("CountedUser") & ", " & """drilldown""" & ":""" & dr("O_User") & """},")
Loop
Dim JsonStr As String = ""
For Each dr1 As DataRow In dt.Rows
JsonStr &= dr1.Item(0).ToString
Next
Dim JsonStrhalf As String
JsonStrhalf = JsonStr.Remove(JsonStr.Length - 1)
jsonstring.Value = "[" & JsonStrhalf & "]"
SqlConnection.ClearPool(con)
dr.Close()
con.Close()
con.Dispose()
End Sub