mysql - vb.net 按 X 轴日期排序图表

Question

我有一个图表，其中月份标记在 x 轴上，y 值是整数。

Private MonthProfit As SortedDictionary(Of String, Integer) = New SortedDictionary(Of String, Integer)

Private Sub frmChart_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim Query As String = "SELECT Profit, SaleDate FROM tblpayment"

        Using Conn As New MySqlConnection(MySQL.ConnectionDetails)
            Using Comm As New MySqlCommand()
                With Comm
                    .Connection = Conn
                    .CommandText = Query
                    .CommandType = CommandType.Text
                End With
                Try
                    Conn.Open()
                    Dim Reader As MySqlDataReader = Comm.ExecuteReader
                    While Reader.Read OrElse (Reader.NextResult And Reader.Read)
                        If MonthProfit.ContainsKey(MonthName(Reader.GetDateTime(1).Month)) Then
                            MonthProfit(MonthName(Reader.GetDateTime(1).Month)) += Reader.GetInt32(0)
                        Else
                            MonthProfit.Add(MonthName(Reader.GetDateTime(1).Month), 0)
                            MonthProfit(MonthName(Reader.GetDateTime(1).Month)) += Reader.GetInt32(0)
                        End If
                    End While
                Catch ex As MySqlException

                End Try
            End Using
        End Using

        For Each index In MonthProfit.ToArray
            'chMain.Series.Add(index.Key)
            chMain.Series("Profit").Points.AddXY(index.Key, index.Value)
        Next
        chMain.Series("Profit").Sort(pointSortOrder:=DataVisualization.Charting.PointSortOrder.Ascending, sortBy:="Axislabel")

    End Sub

我似乎无法弄清楚如何做到这一点，所以我很感激能为我指明正确方向的一些帮助。

---

固定的解决方案:

Private Sub frmChart_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim Query As String = "SELECT MONTHNAME(SaleDate) AS MonthName, Profit FROM tblpayment ORDER BY SaleDate ASC"

    Using Conn As New MySqlConnection(MySQL.ConnectionDetails)
        Using Comm As New MySqlCommand()
            With Comm
                .Connection = Conn
                .CommandText = Query
                .CommandType = CommandType.Text
            End With
            Try
                Conn.Open()
                Dim Reader As MySqlDataReader = Comm.ExecuteReader
                While Reader.Read OrElse (Reader.NextResult And Reader.Read)
                    If MonthProfit.ContainsKey(Reader.GetString(0)) Then
                        MonthProfit(Reader.GetString(0)) += Reader.GetInt32(1)
                    Else
                        MonthProfit.Add(Reader.GetString(0), 0)
                        MonthProfit(Reader.GetString(0)) += Reader.GetInt32(1)
                    End If
                End While
            Catch ex As Exception
                MsgBox(ex.GetBaseException.ToString)
            End Try
        End Using
    End Using

    For Each index In MonthProfit.ToArray
        chMain.Series(0).Points.AddXY(index.Key, index.Value)
    Next

    chMain.Series(0).XValueMember = "Month Name"
    chMain.Series(0).YValueMembers = "Profit"
    chMain.ChartAreas(0).AxisX.Interval = 1

End Sub

Answer 1

您可以简单地向查询添加排序，也可以使用 DataTable 和 TableAdapter 并以这种方式将数据绑定(bind)到您的图表:

Dim ConnectionString As String = "your connection string"
Dim CommandText As String = "SELECT Profit, MONTHNAME(SaleDate) AS MonthName " & _
                            "FROM tblpayment ORDER BY SaleDate ASC"
Dim Adapter As New MySqlDataAdapter(CommandText, ConnectionString)
Dim Table As New DataTable()
Adapter.Fill(Table)

Me.Chart1.Series(0).XValueMember = "MonthName"
Me.Chart1.Series(0).YValueMembers = "Profit"
Me.Chart1.ChartAreas(0).AxisX.Interval = 1
Me.Chart1.DataSource = Table
Me.Chart1.DataBind()