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

elvisidrizi1
 
on Jul 24, 2020 07:15 AM
Sample_572606.zip
1237 Views

Hello Everyone, 

I have a chart in vb.net that shows me data taken from SQL, the data is correct expect they don’t merge their data together, there are duplicated on the dates which lines have.

SELECT SUM(Price) AS 'Total'
    ,Department as 'Department'
FROM     [dbo].[Expenses]
WHERE    Department IN ('Electronics' ,'Grocery','Other','Vacation','FixCost','Clothes')
AND CONVERT(char(10), date, 120) >= '2020-05' And CONVERT(char(10), date, 120) <= '2020-08'
GROUP BY  Department

 

    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()
        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 String() = (From p In dt.AsEnumerable()
                                 Where p.Field(Of String)("Department") = department
                                 Order By p.Field(Of String)("year")
                                 Select p.Field(Of String)("year")).ToArray

            Dim y As Decimal() = (From p In dt.AsEnumerable()
                                  Where p.Field(Of String)("Department") = department
                                  Order By p.Field(Of String)("year")
                                  Select p.Field(Of Decimal)("Total")).ToArray()

            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.ChartAreas(0).AxisX.Interval = 1
            Chart1.ChartAreas(0).AxisX.IntervalType = DateTimeIntervalType.Months
        Next
        Chart1.Legends(0).Enabled = True
    End Sub

This is the code that I have and i tried something but nothing changes.

Thanks a lot and have a nice weekend.

Blessings

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jul 25, 2020 12:50 AM
on Jul 27, 2020 02:04 AM

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