Populate (Bind) multi series Spline Chart from database using C# and VB.Net in Windows Application

Hi elvisidrizi1,

Check this example. Now please take its reference and correct your code.

SQL

CREATE PROCEDURE [dbo].[Chart_Monthly1]
(
    @DateFrom VARCHAR(50) = NULL
   ,@DateTo  VARCHAR(50) = NULL
)
AS
BEGIN
    SELECT SUM(Price) AS 'Total'
         ,CONVERT(CHAR(7), DATE, 120) AS 'year'
         ,RTRIM(LTRIM(Department)) AS 'Department'
    FROM [dbo].[Expenses]
    WHERE Department IN ('Electronics' ,'Grocery','Other','Vacation','FixCost','Clothes')
    AND CONVERT(CHAR(10), DATE, 120) >= @DateFrom And CONVERT(CHAR(10), DATE, 120) <= @DateTo
    GROUP BY CONVERT(CHAR(7), DATE, 120), Department
END

Namespaces

C#

using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms.DataVisualization.Charting;

VB.Net

Imports System.Data
Imports System.Data.SqlClient
Imports System.Drawing
Imports System.Windows.Forms.DataVisualization.Charting

Code

C#

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        DataCharts();
    }

    private void DataCharts()
    {
        DataTable dt = GetData("Chart_Monthly1");
        List<string> departments = (from p in dt.AsEnumerable()
                                    select p.Field<string>("Department")).Distinct().ToList();

        List<string> years = (from p in dt.AsEnumerable()
                                orderby p.Field<string>("year")
                                select p.Field<string>("year")).Distinct().ToList();

        if (Chart1.Series.Count() == 1)
        {
            Chart1.Series.Remove(Chart1.Series[0]);
        }
        Chart1.Series.Clear();

        foreach (string department in departments)
        {
            List<string> x = new List<string>();
            List<decimal> y = new List<decimal>();
            foreach (string year in years)
            {
                x.Add(year);
                y.Add((from p in dt.AsEnumerable()
                        where p.Field<string>("Department") == department & p.Field<string>("year") == year
                        orderby p.Field<string>("year")
                        select p.Field<decimal>("Total")).FirstOrDefault());
            }
            Chart1.Series.Add(new Series(department));
            Chart1.Series[department].IsValueShownAsLabel = true;
            Chart1.Series[department].BorderWidth = 2;
            Chart1.Series[department].ChartType = SeriesChartType.Spline;
            Chart1.Series[department].Points.DataBindXY(x, y);
            Chart1.Series[department].LabelForeColor = Color.MediumSeaGreen;
            Chart1.Series[department].EmptyPointStyle.Color = Color.Red;
            Chart1.Series[department].EmptyPointStyle.AxisLabel = "Empty";
        }
        Chart1.Legends[0].Enabled = true;
    }
    private DataTable GetData(string query)
    {
        DataTable dt = new DataTable();      
        string constr = "Data Source=####/SQLEXPRESS;Initial Catalog=Expenses;Trusted_Connection=yes;";
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand command = new SqlCommand(query, con))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@DateFrom", "2020-01-01");
                command.Parameters.AddWithValue("@DateTo", "2020-07-07");
                using (SqlDataAdapter sda = new SqlDataAdapter(command))
                {
                    sda.Fill(dt);
                }
            }
        }
        return dt;
    }
}

VB.Net

Public Class Form1
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        DataCharts()
    End Sub
    Private Sub DataCharts()
        Dim dt As DataTable = GetData("Chart_Monthly1")
        Dim departments As List(Of String) = (From p In dt.AsEnumerable()
                                              Select p.Field(Of String)("Department")).Distinct().ToList()

        Dim years As List(Of String) = (From p In dt.AsEnumerable()
                                        Order By p.Field(Of String)("year")
                                        Select p.Field(Of String)("year")).Distinct().ToList()

        If Chart1.Series.Count() = 1 Then
            Chart1.Series.Remove(Chart1.Series(0))
        End If
        Chart1.Series.Clear()

        For Each department As String In departments
            Dim x As List(Of String) = New List(Of String)()
            Dim y As List(Of Decimal) = New List(Of Decimal)()
            For Each year As String In years
                x.Add(year)
                y.Add((From p In dt.AsEnumerable()
                       Where p.Field(Of String)("Department") = department And p.Field(Of String)("year") = year
                       Order By p.Field(Of String)("year")
                       Select p.Field(Of Decimal)("Total")).FirstOrDefault())
            Next
            Chart1.Series.Add(New Series(department))
            Chart1.Series(department).IsValueShownAsLabel = True
            Chart1.Series(department).BorderWidth = 2
            Chart1.Series(department).ChartType = SeriesChartType.Spline
            Chart1.Series(department).Points.DataBindXY(x, y)
            Chart1.Series(department).LabelForeColor = Color.MediumSeaGreen
            Chart1.Series(department).EmptyPointStyle.Color = Color.Red
            Chart1.Series(department).EmptyPointStyle.AxisLabel = "Empty"
        Next
        Chart1.Legends(0).Enabled = True

    End Sub
    Private Function GetData(ByVal query As String) As DataTable
        Dim dt As DataTable = New DataTable()        
        Dim constr As String = "Data Source=####/SQLEXPRESS;Initial Catalog=Expenses;Trusted_Connection=yes;"
        Using con As SqlConnection = New SqlConnection(constr)
            Using command As New SqlCommand(query, con)
                command.CommandType = CommandType.StoredProcedure
                command.Parameters.AddWithValue("@DateFrom", "2021-01-01")
                command.Parameters.AddWithValue("@DateTo", "2021-07-07")
                Using sda As SqlDataAdapter = New SqlDataAdapter(command)
                    sda.Fill(dt)
                End Using
            End Using
        End Using
        Return dt
    End Function
End Class

Screenshot