Display details from database in Windows Chart Annotations on Mouse Move using C# and VB.Net

Last Reply 7 months ago By dharmendr

Posted 7 months ago

Hello everyone,

How do I fill my text Annotations chart with SQL information in vb

I have some charts in my vb.net forms which I would like to make them more sophisticated. I have been googling but I haven't found anything so specific. I just want to know if it's possible and what method should I use or which direction should I go to.

With the help of the internet, I connected my SQL values with my data charts in Vb.net. 

Here is some of the code that I use it: 

    Private Function GetData1() As DataTable
        Dim dtChartData As New DataTable()
        Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("db").ConnectionString)
            Using cmd As New SqlCommand("Chart_daily1", conn) ' Total working hours in the month -- step3
                cmd.CommandType = CommandType.StoredProcedure
                conn.Open()
                'Counts Year only'
                cmd.Parameters.AddWithValue("@DateD", DateTimePickerM.Text)
                cmd.Parameters.AddWithValue("@Department", ComboBoxDepartment.Text)
                Dim reader As SqlDataReader = cmd.ExecuteReader()
                dtChartData.Load(reader)
            End Using
        End Using

        Return dtChartData
    End Function

 Chartday.DataSource = GetData1()
 Chartday.Series("Day").Points.Clear()
 Chartday.Series("Day").XValueMember = "Day"
 Chartday.Series("Day").YValueMembers = "Total"
 Chartday.Series("Day").ChartType = SeriesChartType.Spline
 Chartday.Series("Day").IsValueShownAsLabel = True

Here is some simple form that I use to show simple information : 

 Private Sub LoaddayData()
     Dim MyData(12) As Single
     For i = 1 To 12
         MyData(i) = CSng(5 * Math.Sin(i / 2))
     Next

     With Chartday.Series("Day")
         .BorderWidth = 2
         .MarkerStyle = DataVisualization.Charting.MarkerStyle.Circle
         .MarkerSize = 8
         For m = 1 To 12
             .Points.AddXY(m, MyData(m))
         Next
     End With

     Dim PC As New CalloutAnnotation
     With PC
         Chartday.Annotations.Add(PC)
     End With
 end sub

 Private Sub Chartday_MouseMove(sender As Object, e As MouseEventArgs) Handles Chartday.MouseMove
        Dim Result As HitTestResult = Chartday.HitTest(e.X, e.Y)
      
        If Result.ChartElementType = ChartElementType.DataPoint Then
            Chartday.Series("Day").Points(Result.PointIndex).XValue.ToString()

            Dim thisPt As New PointF(CSng(Chartday.Series("Day").Points(Result.PointIndex).XValue),
                                    CSng(Chartday.Series("Day").Points(Result.PointIndex).YValues(0)))
            Dim ta As New CalloutAnnotation
            With ta
                .AnchorDataPoint = Chartday.Series("Day").Points(Result.PointIndex)
                .X = thisPt.X + 1
                .Y = thisPt.Y + 1
                .Text = thisPt.ToString 
                .CalloutStyle = CalloutStyle.SimpleLine
                .ForeColor = Color.NavajoWhite
                .Font = New Font("Tahoma", 12, FontStyle.Bold)
            End With
            Chartday.Annotations(0) = ta
            Chartday.Invalidate()
        End If  
    End Sub

This is a simple form on how to show me only the information that is already in Charts.

Posted 7 months ago Modified on 7 months ago

Hi elvisidrizi1,

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

For changing the CalloutAnnotation Text get the details from the DataPoint like department, Year and based on that change the Text.

You can fetch the details based on department, Year from database and set the Text accordingly.

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
{
    private string constr = "Data Source=####/SQLEXPRESS;Initial Catalog=Expenses;Trusted_Connection=yes;"
    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;
        float[] MyData = new float[Chart1.Series.Count];
        for (var i = 0; i < Chart1.Series.Count; i++)
        {
            MyData[i] = Convert.ToSingle(5 * Math.Sin(i / (double)2));
            var withBlock = Chart1.Series[i];
            withBlock.BorderWidth = 2;
            withBlock.MarkerStyle = MarkerStyle.Circle;
            withBlock.MarkerSize = 8;
        }
    }
    private DataTable GetData(string query)
    {
        DataTable dt = new DataTable();
        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;
    }

    private void Chart1_MouseMove(object sender, MouseEventArgs e)
    {
        Chart1.Annotations.Clear();
        HitTestResult result = Chart1.HitTest(e.X, e.Y);
        if (result.ChartElementType == ChartElementType.DataPoint)
        {
            string department = result.Series.Name;
            string year = result.Series.Points[result.PointIndex].AxisLabel;
            double total = result.Series.Points[result.PointIndex].YValues[0];
            PointF thisPt = new PointF(Convert.ToSingle(result.Series.Points[result.PointIndex].XValue), 
                                        Convert.ToSingle(result.Series.Points[result.PointIndex].YValues[0]));
            CalloutAnnotation ca = new CalloutAnnotation()
            {
                AnchorDataPoint = result.Series.Points[result.PointIndex],
                X = thisPt.X + 1,
                Y = thisPt.Y + 1,
                CalloutStyle = CalloutStyle.SimpleLine,
                ForeColor = Color.Red,
                Font = new Font("Tahoma", 8, FontStyle.Bold),
                Name = department,
                Text = GetDetails(year, department)
            };
            Chart1.Annotations[department] = ca;
            Chart1.Invalidate();
        }
    }

    private string GetDetails(string year, string department)
    {
        string data = "";
        string query = "SELECT Date,Description,Price,Quantity FROM [dbo].[Expenses] WHERE CONVERT(char(7), Date, 120) = @Year AND LTRIM(RTRIM(Department)) = @Dept";
        DataTable dt = new DataTable();
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand command = new SqlCommand(query, con))
            {
                command.CommandType = CommandType.Text;
                command.Parameters.AddWithValue("@Year", year.Trim());
                command.Parameters.AddWithValue("@Dept", department.Trim());
                using (SqlDataAdapter sda = new SqlDataAdapter(command))
                {
                    sda.Fill(dt);
                }
            }
        }

        foreach (DataRow row in dt.Rows)
        {
            string date = Convert.ToDateTime(row["Date"]).ToString("dd/MM/yyyy");
            string description = row["Description"].ToString();
            string price = row["Price"].ToString() == "" ? "0" : row["Price"].ToString();
            string quantity = row["Quantity"].ToString() == "" ? "0" : row["Quantity"].ToString();
            data = data + date + " - " + description + " - " + price + " - " + quantity + Environment.NewLine;
        }
        return data;
    }
}

VB.Net

Public Class Form1
    Dim constr As String = "Data Source=####/SQLEXPRESS;Initial Catalog=Expenses;Trusted_Connection=yes;"
    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
        Dim MyData As Single() = New Single(Chart1.Series.Count - 1) {}
        For i = 0 To Chart1.Series.Count - 1
            MyData(i) = Convert.ToSingle(5 * Math.Sin(i / CDbl(2)))
            Dim withBlock = Chart1.Series(i)
            withBlock.BorderWidth = 2
            withBlock.MarkerStyle = MarkerStyle.Circle
            withBlock.MarkerSize = 8
        Next
    End Sub
    Private Function GetData(ByVal query As String) As DataTable
        Dim dt As DataTable = New DataTable()
        Using con As SqlConnection = New SqlConnection(constr)
            Using command As New SqlCommand(query, con)
                command.CommandType = CommandType.StoredProcedure
                command.Parameters.AddWithValue("@DateFrom", "2020-01-01")
                command.Parameters.AddWithValue("@DateTo", "2020-07-07")
                Using sda As SqlDataAdapter = New SqlDataAdapter(command)
                    sda.Fill(dt)
                End Using
            End Using
        End Using
        Return dt
    End Function

    Private Sub Chart1_MouseMove(ByVal sender As Object, ByVal e As MouseEventArgs) Handles Chart1.MouseMove
        Chart1.Annotations.Clear()
        Dim result As HitTestResult = Chart1.HitTest(e.X, e.Y)
        If result.ChartElementType = ChartElementType.DataPoint Then
            Dim department As String = result.Series.Name
            Dim year As String = result.Series.Points(result.PointIndex).AxisLabel
            Dim total As Double = result.Series.Points(result.PointIndex).YValues(0)
            Dim thisPt As PointF = New PointF(Convert.ToSingle(result.Series.Points(result.PointIndex).XValue),
                                              Convert.ToSingle(result.Series.Points(result.PointIndex).YValues(0)))
            Dim ca As CalloutAnnotation = New CalloutAnnotation With {
                .AnchorDataPoint = result.Series.Points(result.PointIndex),
                .X = thisPt.X + 1,
                .Y = thisPt.Y + 1,
                .CalloutStyle = CalloutStyle.SimpleLine,
                .ForeColor = Color.Red,
                .Font = New Font("Tahoma", 8, FontStyle.Bold),
                .Name = department,
                .Text = GetDetails(year, department)
            }
            Chart1.Annotations(department) = ca
            Chart1.Invalidate()
        End If
    End Sub

    Private Function GetDetails(ByVal year As String, ByVal department As String) As String
        Dim data As String = ""
        Dim query As String = "SELECT Date,Description,Price,Quantity FROM [dbo].[Expenses] WHERE CONVERT(char(7), Date, 120) = @Year AND LTRIM(RTRIM(Department)) = @Dept"
        Dim dt As DataTable = New DataTable()
        Using con As SqlConnection = New SqlConnection(constr)
            Using command As New SqlCommand(query, con)
                command.CommandType = CommandType.Text
                command.Parameters.AddWithValue("@Year", year.Trim())
                command.Parameters.AddWithValue("@Dept", department.Trim())
                Using sda As SqlDataAdapter = New SqlDataAdapter(command)
                    sda.Fill(dt)
                End Using
            End Using
        End Using

        For Each row As DataRow In dt.Rows
            Dim [date] As String = Convert.ToDateTime(row("Date")).ToString("dd/MM/yyyy")
            Dim description As String = row("Description")
            Dim price As String = If(row("Price").ToString = "", 0, row("Price").ToString)
            Dim quantity As String = If(row("Quantity").ToString = "", 0, row("Quantity").ToString)
            data = data & [date] & " - " & description & " - " & price & " - " & quantity & Environment.NewLine
        Next
        Return data
    End Function
End Class

Screenshot