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
