Show (Display) Chart DataPoint details on hover using C# and VB.Net in Windows Application

Last Reply 6 months ago By dharmendr

Posted 6 months ago

Hello Everyone, 

I have this code that shows me information in my chart for one table information and I need it to make it work for my other chart, but I can't seem to make it work.

my date format is in this format '2020-05' and I am trying to get from that the days. Can I get the days from that type of format? I need all the days with records within that month to be shown in my datachart.

I used this code to make it work, but it doesn't show me any information.

  Private prevPosition As Point? = Nothing
    Private tooltip As ToolTip = New ToolTip()
    Private Sub Chart1_MouseMove(ByVal sender As Object, ByVal e As MouseEventArgs) Handles Chart1.MouseMove
        Dim pos = e.Location
        If prevPosition.HasValue AndAlso pos = prevPosition.Value Then Return
        tooltip.RemoveAll()
        prevPosition = pos
        Dim results = Chart1.HitTest(pos.X, pos.Y, False, ChartElementType.DataPoint)
        For Each result In results
            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 yVal = result.ChartArea.AxisY.PixelPositionToValue(pos.Y)
                tooltip.BackColor = Color.LightGray
                tooltip.ToolTipTitle = "Data Information"
                tooltip.Show(GetDetailss(year, department), Chart1, pos.X, pos.Y - 15)
            End If
        Next
    End Sub

    Private Sub toolTip_Draw(ByVal sender As Object, ByVal e As DrawToolTipEventArgs)
        Dim f As Font = New Font("Arial", 8.0F)
        Dim b As Brush = New SolidBrush(tooltip.BackColor)
        e.Graphics.FillRectangle(b, e.Bounds)
        e.DrawBackground()
        e.DrawBorder()
        e.Graphics.DrawString(e.ToolTipText, f, SystemBrushes.ActiveCaptionText, e.Bounds)
    End Sub

    Private Function GetDetailss(ByVal year As String, ByVal department As String) As String
        Dim data As String = ""
        Dim constr As String = "Data Source=#####,1433;Network Library=DBMSSOCN;Initial Catalog=Expenses;User ID=###;Password=####;"
       
        Dim query As String = "SELECT Date,Description,Price,Quantity FROM [dbo].[ShareCost] WHERE DATEPART(DAY,Date) = @Year AND LTRIM(RTRIM(Username)) = @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

The data that I want to be shown is recorded by day.

Best Regards, Elvis

You are viewing reply posted by: dharmendr 6 months ago.
Posted 6 months ago Modified on 6 months ago

Hi elvisidrizi1,

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

Code

C#

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

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();

        Random rnd = new Random();
        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.Series[0].Color = Color.Red;
        Chart1.Series[1].Color = Color.Green;
        Chart1.Series[2].Color = Color.Yellow;
        Chart1.Series[3].Color = Color.Violet;
        Chart1.Series[4].Color = Color.Orange;
        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 Point? prevPosition = default(Point?);
    private ToolTip tooltip = new ToolTip();
    private void Chart1_MouseMove(object sender, MouseEventArgs e)
    {
        var pos = e.Location;
        if (prevPosition.HasValue && pos == prevPosition.Value)
        {
            tooltip.RemoveAll();
            prevPosition = pos;
            return;
        }
        var results = Chart1.HitTest(pos.X, pos.Y, false, ChartElementType.DataPoint);
        foreach (var result in results)
        {
            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];
                var yVal = result.ChartArea.AxisY.PixelPositionToValue(pos.Y);
                tooltip.BackColor = Color.LightGray;
                tooltip.ToolTipTitle = "Data Information";
                tooltip.Show(GetDetails(year, department), Chart1, pos.X, pos.Y - 15);
            }
        }
    }

    private string GetDetails(string year, string department)
    {
        string data = "";
        string query = "SELECT Date,Description,Price,Quantity FROM [dbo].[Expenses] WHERE DATEPART(Year,Date) = @Year AND DATEPART(Month,Date) = @Month 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().Split('-')[0]);
                command.Parameters.AddWithValue("@Month", year.Trim().Split('-')[1]);
                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

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

Public Class Form
    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()
        Dim rnd As Random = New Random()
        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.Series(0).Color = Color.Red
        Chart1.Series(1).Color = Color.Green
        Chart1.Series(2).Color = Color.Yellow
        Chart1.Series(3).Color = Color.Violet
        Chart1.Series(4).Color = Color.Orange
        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 prevPosition As Point? = Nothing
    Private tooltip As ToolTip = New ToolTip()
    Private Sub Chart1_MouseMove(ByVal sender As Object, ByVal e As MouseEventArgs) Handles Chart1.MouseMove
        Dim pos = e.Location
        If prevPosition.HasValue AndAlso pos = prevPosition.Value Then
            tooltip.RemoveAll()
            prevPosition = pos
            Return
        End If
        Dim results = Chart1.HitTest(pos.X, pos.Y, False, ChartElementType.DataPoint)
        For Each result In results
            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 yVal = result.ChartArea.AxisY.PixelPositionToValue(pos.Y)
                tooltip.BackColor = Color.LightGray
                tooltip.ToolTipTitle = "Data Information"
                tooltip.Show(GetDetails(year, department), Chart1, pos.X, pos.Y - 15)
            End If
        Next
    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 DATEPART(Year,Date) = @Year AND DATEPART(Month,Date) = @Month 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().Split("-")(0))
                command.Parameters.AddWithValue("@Month", year.Trim().Split("-")(1))
                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