How to plot aggregated Value Column Charts from Database C#
Simple (Easy 3 steps) and short video to illustrate high overview of using SQL to aggregate monthly counts of transactions from database into a Windows form (Winform) column chart. #Aggregate #Winform #Chart #VS2015
This tutorial assumes you are familiar with C# codes to execute SQL commands using SqlDataAdaptor to populate the gridview (using DataTable as DataSource) and as well plot Column Chart using SqlDataAdaptor to fill DataSet or DataTable and using them as DataSource on a Window Form (Winform). I've included some reference link hopefully it is helpful for Beginner to Window form to understand as well. Hopefully this video provides some learning content.
Please like the video if it helps you :)
Steps generally as below
SQL query to get required transactions with the desired year
use SqlDataAdaptor to fill a Gridview
(You can Control+click on this link for reference • How To Show Data In DataGridview In C# Fro... )
Edit the query to form the Aggregated one to get the counts and months
(use SQL "Count()" and "Group by" to get the count, Month() to return month integer value from Date)
Use SqlDataAdaptor to fill a DataSet or DataTable
Use the DataSet/DataTable as the Data Source of the column chart
(You can Control+click on this link for reference • How to Display Data in Chart control from ... )
Format the chart accordingly
If you need to format x-axis to a certain date range, the below sample C# codes maybe helpful. It appears as date in monthly interval. Note that your database query result should be within this range as well.
/////// C# codes below
DateTime DateStart = new DateTime(2017, 01, 01);
DateTime DateEnd = new DateTime(2018, 01, 01);
DateEnd = DateEnd.AddDays(-1);
chartColumn.ChartAreas[0].AxisX.Minimum = DateStart.ToOADate();
chartColumn.ChartAreas[0].AxisX.Maximum = DateEnd.Date.ToOADate();
chartColumn.ChartAreas[0].AxisX.IntervalType = DateTimeIntervalType.Months;
chartColumn.ChartAreas[0].AxisX.Interval = 1;
////////
Hope these inputs are helpful for you.
0:00 - Introduction or Overview - VS2015 Winform
0:29 - Database(mdf) SQL Server Raw entries for a year
0:52 - Aggregate monthly count
1:29 - SQL Query to get Aggregated Count ( SQL "Count()" and "Group By")
2:00 - SqlDataAdaptor to plot to column chart using Aggregated Count and month
Keywords:
#aggregate
#chart
#winform
#windowsform
#csharp
#Visualstudio
#database
#mdf
#SqlDataAdaptor
#datatable
#dataset
#count()
#Groupby
#sql
#sqlquery
#beginner
#monthlyCount
#monthly