如何在ASP.NET的Chart控件中集成计算得到的均值

我已经通过SQL在ASP.NET应用中计算出了数据集的均值，现在需要通过后端C#代码把这个均值数据绑定到Chart控件上，目前还没找到可行的解决方案。

下面是我的SQL数据源配置：

<asp:SqlDataSource ID="SqlDataSource20" runat="server" 
                    ConnectionString="<%$ ConnectionStrings:BuyoffAndMonitoringConnectionString11 %>" 
                    SelectCommand="
                            WITH NumberedRows AS (
                            SELECT *, 
                            (ROW_NUMBER() OVER (ORDER BY DateTime) - 1) / 3 AS GroupNumMean
                             FROM [DEMUX]
                              ),
                     MeanValues AS (
                             SELECT 
                                GroupNumMean,
                                AVG([RX1_Gap1]) AS Mean
                                FROM NumberedRows
                                GROUP BY GroupNumMean
                                )
                            SELECT 
                                L.[DateTime],
                                L.[Shift],
                                L.[ID_No],
                                L.[SN],
                                L.[RX1_Gap1],
                                L.[RX1_Gap1_Min],
                                L.[RX1_Gap1_Max],
                                L.[Result],
                                ROUND(M.Mean, 4) AS Mean
                                FROM NumberedRows L
                                JOIN MeanValues M ON L.GroupNumMean = M.GroupNumMean
                                ORDER BY L.[DateTime] DESC;"
                    FilterExpression="Shift Like '%{0}%'">
             <FilterParameters>
         <asp:ControlParameter ControlID="DropDownList14" Name="Shift" PropertyName="SelectedValue" Type="String" />
        </FilterParameters>
    </asp:SqlDataSource>

我尝试了以下C#代码，但没有成功：

if (dv != null && dv.Count > 0)
{
    DataTable dataTable = dv.ToTable();

    int numberOfValuesToShow = Convert.ToInt32(ddlNumberOfValues.SelectedValue);

    // Group the data by Mean and calculate date ranges
    var meanData = dataTable.AsEnumerable()
    .Where(row => row["Mean"] != DBNull.Value && float.TryParse(row["Mean"].ToString(), out _))
        .GroupBy(row => Convert.ToSingle(row["Mean"]))
        .Select(grp => new
        {
            Mean= grp.Key,
            StartDate = grp.Min(row => row.Field<DateTime>("TestTimeStamp")),
            EndDate = grp.Max(row => row.Field<DateTime>("TestTimeStamp"))
        })
        .Take(numberOfValuesToShow) // Only take the first 5 groups
        .ToList();

    string[] c = new string[meanData .Count];
    float[] d = new float[meanData .Count];

    for (int i = 0; i < meanData .Count; i++)
    {
        c[i] = meanData [i].StartDate.ToString(); 
        d[i] = meanData [i].Mean;
    }

    Chart8.Series[3].Points.DataBindXY(c, d); // Assuming Series[3] is for Mean
}
if (dv != null && dv.Count > 0)
{
    DataTable dataTable = dv.ToTable();

    int numberOfValuesToShow = Convert.ToInt32(ddlNumberOfValues.SelectedValue);

    // Group the data by Mean and calculate date ranges
    var cpkData = dataTable.AsEnumerable()
    .Where(row => row["Mean"] != DBNull.Value && float.TryParse(row["Mean"].ToString(), out _))
        .GroupBy(row => Convert.ToSingle(row["Mean"]))
        .Select(grp => new
        {
            Mean= grp.Key,
            StartDate = grp.Min(row => row.Field<DateTime>("TestTimeStamp")),
            EndDate = grp.Max(row => row.Field<DateTime>("TestTimeStamp"))
        })
        .Take(numberOfValuesToShow) // Only take the first 5 groups
        .ToList();

    string[] c = new string[meanData.Count];
    float[] d = new float[meanData.Count];

    for (int i = 0; i < meanData .Count; i++)
    {
        c[i] = meanData [i].StartDate.ToString(); // or cpkData[i].EndDate.ToString() depending on your requirement
        d[i] = meanData [i].Mean;
    }

    Chart8.Series[3].Points.DataBindXY(c, d); // Assuming Series[3] is for Mean
}

问题分析 #

你的代码存在几个关键问题：

• 重复逻辑：两段几乎完全一样的代码块重复执行，造成冗余且可能覆盖之前的绑定结果。
• 字段名不匹配：SQL查询中日期字段是DateTime，但代码里用了TestTimeStamp，会导致字段找不到报错。
• 分组逻辑错误：按Mean分组没有意义——同一组的Mean值本身就是相同的，应该保留原始数据的时序关系，而非按均值分组。
• 数据绑定方式冗余：手动转数组再绑定可以简化，直接用Linq结果绑定更高效。

修正后的代码 #

if (dv != null && dv.Count > 0)
{
    DataTable dataTable = dv.ToTable();
    int numberOfValuesToShow;
    // 避免整数转换报错
    if (!int.TryParse(ddlNumberOfValues.SelectedValue, out numberOfValuesToShow))
    {
        numberOfValuesToShow = 5; // 设置默认值
    }

    // 提取时序化的均值数据，保留原始时间顺序
    var meanData = dataTable.AsEnumerable()
        .Where(row => row["Mean"] != DBNull.Value && float.TryParse(row["Mean"].ToString(), out _))
        .Select(row => new 
        {
            MeanValue = Convert.ToSingle(row["Mean"]),
            RecordTime = row.Field<DateTime>("DateTime")
        })
        .OrderByDescending(item => item.RecordTime) // 和SQL排序逻辑一致
        .Take(numberOfValuesToShow)
        .ToList();

    // 清空旧数据再绑定
    Chart8.Series["MeanSeries"].Points.Clear();
    // 直接绑定Linq结果，无需手动转数组
    Chart8.Series["MeanSeries"].Points.DataBind(meanData, "RecordTime", "MeanValue", "");

    // 可选：配置Series样式（示例为折线图）
    Chart8.Series["MeanSeries"].ChartType = System.Web.UI.DataVisualization.Charting.SeriesChartType.Line;
    Chart8.Series["MeanSeries"].BorderWidth = 2;
    Chart8.Series["MeanSeries"].Color = System.Drawing.Color.Red;

    // 可选：自定义日期显示格式
    foreach (var point in Chart8.Series["MeanSeries"].Points)
    {
        point.AxisLabel = point.XValue.ToString("yyyy-MM-dd HH:mm");
    }
}

额外注意事项 #

• Series名称确认：确保Chart8中存在名为MeanSeries的Series，也可以用你原来的Series[3]，但用名称更直观。
• 空值处理：增加int.TryParse确保下拉框值转换安全，避免运行时错误。
• Chart初始化：绑定前清空Series的旧数据，避免重复叠加。

内容的提问来源于stack exchange，提问作者Teh Teh