Create a procedure to fetch data from the database in SQL
ALTER Proc [dbo].[Proc_Dashboard_Audit]
AS
BEGIN
-- To Get Dashboard Chart Graph
;WITH Months AS
(
-- Anchor member: start with the current month
SELECT
MONTH(GETDATE()) AS Monthnumber,
DATENAME(month, GETDATE()) AS [Month],
1 AS number
UNION ALL
-- Recursive member: add subsequent months
SELECT
MONTH(DATEADD(month, number, GETDATE())) AS Monthnumber,
DATENAME(month, DATEADD(month, number, GETDATE())) AS [Month],
number + 1
FROM
Months
WHERE
number < 12
)
-- Select the records for each month and count occurrences
SELECT
--m.Monthnumber,
m.[Month],
COUNT(a.AuditID) AS AuditCount
FROM
Months m
LEFT JOIN
AuditProgram a ON m.Monthnumber = MONTH(a.EnteredDt)
AND YEAR(a.EnteredDt) = YEAR(GETDATE())
AND Syncoperation <> 'D'
GROUP BY
m.Monthnumber,
m.[Month]
ORDER BY
m.Monthnumber;
END
- After creating the above procedure, create a new application containing a master page in Visual Studio.
- Add a reference to Newtonsoft.Json from the NuGet Package Manager in your solution.
- Add a new content page by right-clicking on the master page in your solution.
Step 1. Master Page Setup
Ensure that jQuery and Flot libraries are correctly included in your Site. Master files are being loaded before your custom script.
Site.Master
<!DOCTYPE html>
<html lang="en">
<head runat="server">
<title>My ASP.NET Application</title>
<asp:ContentPlaceHolder ID="head" runat="server">
</asp:ContentPlaceHolder>
<link href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" rel="stylesheet" />
<!-- Include jQuery -->
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<!-- Include Flot -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/flot/0.8.3/jquery.flot.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/flot/0.8.3/jquery.flot.categories.min.js"></script>
</head>
<body>
<form id="form1" runat="server">
<div class="container mt-5">
<asp:ContentPlaceHolder ID="MainContent" runat="server">
</asp:ContentPlaceHolder>
</div>
</form>
<script>
$(function () {
jQuery(function () {
var data = '<%= ChartData_BAR %>';
data = JSON.parse(data);
console.log(data); // Debugging: Log the data to check if it's correct
jQuery.plot("#bar-chart", [{ data: data, bars: { show: true } }], {
grid: {
borderWidth: 1,
borderColor: '#f3f3f3',
tickColor: '#f3f3f3'
},
series: {
bars: {
show: true,
barWidth: 0.5,
align: 'center'
}
},
colors: ['#3c8dbc'],
xaxis: {
mode: 'categories',
tickLength: 0
}
});
});
})
</script>
</body>
</html>
Step 2. Master Page Code-Behind
Retrieve data from the database and serialize it to JSON format in your Site. Master. cs.
Site.Master. cs
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using Newtonsoft.Json;
namespace YourNamespace
{
public partial class SiteMaster : System.Web.UI.MasterPage
{
public string ChartData { get; private set; }
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
LoadChartData();
}
}
private void LoadChartData()
{
string connectionString = "Your_Connection_String_Here";
using (SqlCommand cmd = new SqlCommand("Proc_Dashboard_Audit", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
if (con.State == ConnectionState.Open)
con.Close();
con.Open();
DataTable dt_chart_Bar = new DataTable();
using (SqlDataAdapter adapt = new SqlDataAdapter(cmd))
{
adapt.Fill(dt_chart_Bar);
}
if (dt_chart_Bar.Rows.Count > 0)
{
var data = new List<object>();
foreach (DataRow dr in dt_chart_Bar.Rows)
{
data.Add(new { Label = dr["Month"].ToString(), Value = dr["AuditCount"].ToString() });
}
// Convert data to JSON format required by Flot
var flotData = new List<object>();
foreach (var item in data)
{
flotData.Add(new object[] { ((dynamic)item).Label, ((dynamic)item).Value });
}
// Convert data to JSON string
ChartData_BAR = JsonConvert.SerializeObject(flotData);
}
con.Close();
}
}
}
}
Step 3. Content Page Setup
Set up the content page to include a chart container and the JavaScript to plot the chart.
Default.aspx
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
<!-- Content Wrapper. Contains page content -->
<div class="content-wrapper">
<!-- Main content Start -->
<section class="content">
<div class="container-fluid">
<div class="row">
<!-- Bar chart -->
<div id="target_audit_prg" class="card card-info card-outline fade-in-out">
<div class="card-header">
<h3 class="card-title">
<i class="far fa-chart-bar"></i>
Bar Chart
</h3>
<div class="card-tools">
<button type="button" class="btn btn-tool" data-card-widget="collapse">
<i class="fas fa-minus"></i>
</button>
<button type="button" class="btn btn-tool" data-card-widget="remove">
<i class="fas fa-times"></i>
</button>
</div>
</div>
<div class="card-body">
<div id="bar-chart" class="chart-container" style="height: 300px;"></div>
</div>
<!-- /.card-body-->
</div>
<!-- /.card -->
</div>
</div>
<!-- /.container-fluid -->
</section>
</div>
</asp:Content>
By following these steps, you should be able to retrieve data from the database and display it in a bar chart using jQuery Flot in an ASP.NET C# application.
Site.Master
Default. aspx Content Page
Site.Master.cs
Output
Join the conversation! Your thoughts help the community grow.