Hi elvisidrizi1,
Refer below sample code.
VB.Net
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Drawing
Imports System.Windows.Forms.DataVisualization.Charting
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
LoadYearData()
End Sub
Private Function GetData1() As DataTable
Dim dtChartData As New DataTable()
Using conn As New SqlConnection(constr)
Using cmd As New SqlCommand("SELECT Sum(Price) As Total,
CONVERT(char(7), date, 120) [year],
Sum (CONVERT(INT, CONVERT(VARCHAR(MAX),quantity))) as Lesh
FROM dbo.Expenses
WHERE Date BETWEEN DATEADD(MM, -12, GETDATE()) AND GETDATE()
GROUP BY CONVERT(char(7), date, 120)", conn)
cmd.CommandType = CommandType.Text
conn.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()
dtChartData.Load(reader)
End Using
End Using
Return dtChartData
End Function
Private Sub LoadYearData()
Chart1.DataSource = GetData1()
Chart1.Series("Chart1").Points.Clear()
Chart1.Series("Chart1").XValueMember = "Year"
Chart1.Series("Chart1").YValueMembers = "Total"
Chart1.Series("Chart1").IsValueShownAsLabel = True
Chart1.Series("Chart1").BorderWidth = 2
Chart1.Series("Chart1").ChartType = SeriesChartType.Spline
' Chart1.Series("Chart1").Points.DataBindXY(x, y)
Chart1.Series("Chart1").LabelForeColor = Color.NavajoWhite
Chart1.Series("Chart1").EmptyPointStyle.Color = Color.Red
Chart1.Series("Chart1").EmptyPointStyle.AxisLabel = "Empty"
Chart1.Series("Chart1").EmptyPointStyle.IsValueShownAsLabel = False
Chart1.Legends(0).Enabled = False
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
Chart1.Annotations.Add(New CalloutAnnotation())
Next
End Sub
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 yearMonth As String = result.Series.Points(result.PointIndex).AxisLabel
tooltip.BackColor = Color.LightGray
tooltip.ToolTipTitle = "Data Information"
tooltip.Show(GetDetails(yearMonth), 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 GetDetails(ByVal yearMonth As String) As String
Dim data As String = ""
Dim query As String = "SELECT DISTINCT SUM(Price) AS Total,Department FROM [dbo].[Expenses] "
query += " WHERE CONVERT(char(7), date, 120) = @Month "
query += " GROUP BY Department"
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("@Month", yearMonth.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 department As String = row("Department").ToString().Trim()
Dim total As String = row("Total").ToString()
data += " - " & " | " & total & " - " & " | " & " - " & " | " & department & Environment.NewLine
Next
Return data
End Function
End Class
Screenshot
