Display a Bootstrap bar chart according to the database data values using master page in ASP.NET C#.

Deepak Tewatia
1 month ago
31
0
1
Article

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.