C＃从数据库中的datagridview创建图表 [英] C# create chart from datagridview from database
问题描述
我正在尝试从datagridview创建柱形图，从数据库下载DataSet。是datagridview 2.我写的是公共类void loaddata（）。至于图表是我用chart（）公共void类写的音乐会。我希望展示：
I'm trying create column chart from datagridview which downloads DataSet from database. Is datagridview 2. I've written in public class void loaddata(). As for as chart is concert i wrote by chart() public void class. I want display:
XvalueMember : ORDER_NUMBER
YvalueMember: TOTAL_TIME
有我的代码：
GenerateCharts.cs
There is my code:
GenerateCharts.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Windows.Forms.DataVisualization.Charting;
using iTextSharp.text;
using iTextSharp.text.pdf;
using System.IO;
using System.Text.RegularExpressions;
namespace ControlBase
{
public partial class GenerateCharts : Form
{
public GenerateCharts()
{
InitializeComponent();
}
public void loaddata()
{
try
{
MySqlConnection connection = new MySqlConnection("datasource=localhost;port=3306;username=root;password=");
MySqlDataAdapter adapter2 = new MySqlDataAdapter("SELECT w.FNAME, w.LNAME, z.ORDER_DESC AS 'ORDER DESCRIPTION', o.ORDER_NUMBER AS 'ORDER NUMBER', SEC_TO_TIME(SUM(TIME_TO_SEC(s.BEGIN_DATE) - TIME_TO_SEC(s.END_DATE))) AS 'TOTAL TIME OF ORDER' FROM projekt1.status_order s INNER JOIN projekt1.workers p ON s.ID_WORKER = w.ID_WORKER INNER JOIN projekt1.orders z ON s.ID_ORDER = o.ID_ORDER WHERE p.ID_WORKER ='" + int.Parse(textBox1.Text)+ "' AND BEGIN_DATE >= '" + dateTimePicker1.Value.ToString("yyyy-MM-dd") + "' AND END_DATE <= '" + dateTimePicker2.Value.ToString("yyyy-MM-dd") + "' GROUP BY s.ID_ORDER;", connection);
connection.Open();
DataSet ds2 = new DataSet();
adapter2.Fill(ds2, "status_order");
dataGridView2.DataSource = ds2.Tables["status_order"];
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void chart()
{
MySqlConnection connection = new MySqlConnection();
connection.ConnectionString = "datasource=localhost;port=3306;username=root;password=";
connection.Open();
MySqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT w.FNAME, w.LNAME, z.ORDER_DESC AS 'ORDER DESCRIPTION', o.ORDER_NUMBER AS 'ORDER NUMBER', SEC_TO_TIME(SUM(TIME_TO_SEC(s.BEGIN_DATE) - TIME_TO_SEC(s.END_DATE))) AS 'TOTAL_TIME' FROM projekt1.status_order s INNER JOIN projekt1.workers p ON s.ID_WORKER = w.ID_WORKER INNER JOIN projekt1.orders z ON s.ID_ORDER = o.ID_ORDER WHERE p.ID_WORKER ='" + int.Parse(textBox1.Text)+ "' AND BEGIN_DATE >= '" + dateTimePicker1.Value.ToString("yyyy-MM-dd") + "' AND END_DATE <= '" + dateTimePicker2.Value.ToString("yyyy-MM-dd") + "' GROUP BY s.ID_ORDER;";
MySqlDataReader reader;
reader = cmd.ExecuteReader();
while (reader.Read())
{
chart1.Series[0].Points.AddXY(reader.GetString("ORDER_NUMBER"), reader.GetDateTime("TOTAL_TIME"));
}
}
private void button1_Click(object sender, EventArgs e)
{
chart1.Series.Clear();
chart();
loaddata();
}
}
}
我尝试过：
我试过写公共类图表（）：
What I have tried:
I've tried by writing in public class chart():
public void chart()
{
MySqlConnection connection = new MySqlConnection();
connection.ConnectionString = "datasource=localhost;port=3306;username=root;password=";
connection.Open();
MySqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT w.FNAME, w.LNAME, z.ORDER_DESC AS 'ORDER DESCRIPTION', o.ORDER_NUMBER AS 'ORDER NUMBER', SEC_TO_TIME(SUM(TIME_TO_SEC(s.BEGIN_DATE) - TIME_TO_SEC(s.END_DATE))) AS 'TOTAL_TIME' FROM projekt1.status_order s INNER JOIN projekt1.workers p ON s.ID_WORKER = w.ID_WORKER INNER JOIN projekt1.orders z ON s.ID_ORDER = o.ID_ORDER WHERE p.ID_WORKER ='" + int.Parse(textBox1.Text)+ "' AND BEGIN_DATE >= '" + dateTimePicker1.Value.ToString("yyyy-MM-dd") + "' AND END_DATE <= '" + dateTimePicker2.Value.ToString("yyyy-MM-dd") + "' GROUP BY s.ID_ORDER;";
MySqlDataReader reader;
reader = cmd.ExecuteReader();
while (reader.Read())
{
chart1.Series[0].Points.AddXY(reader.GetString("ORDER_NUMBER"), reader.GetDateTime("TOTAL_TIME"));
}
}
但是启动这个应用程序并试图生成一个列我有错误：
But After launching this app and trying to generate a column i have error:
System.ArgumentOutOfRangeException: "The index was out of range. It must have a non-negative value and less than the size of the collection. Parameter name: index "
我试过从datagridview2获取数据但是它没有用。有人可以解释如何更改该代码吗？任何帮助的thx。
I've tried by getting data from datagridview2 too but it haven't worked. Can someone explain how to change that code? Thx for any help.
推荐答案
您必须做的第一件事是执行参数化查询。这意味着创建一个SqlParameter对象数组，并将它们传递给SqlCommand对象。最后，您可以使用适当的参数名称替换所有硬编码值，如下所示：
The very first thing you must do is performa parameterized query. What that means is creating an array of SqlParameter objects, and passing them to the SqlCommand object. Finally, you can replace all of the hard coded values with the appropriate parameter names, something like this:
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@variable1", datetimepicker1),
new SqlParameter("@variable2", datetimepicker2),
};
string query = "select * from mytable where somefield = @variable1 and someotherfield = @variable2";
using (SqlConnection conn = new SqlConnection(myconnectionstring))
{
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddRange(parameters);
.... the rest of your code
}
}
上面的代码是对于一个真正的数据库，所以你必须让它适应你的嬉皮士我的s-sql东西。
然后如果你的代码仍然失败，请回来告诉我们哪里出现失败，因为我们并不是读者的想法。
The code above is for a real database, so you'll have to adapt it to your hippy-dippy my-sql stuff.
Then if your code is still failing, come back and TELL US WHERE IT'S FAILING, because we're not freakin' mind readers.
这篇关于C＃从数据库中的datagridview创建图表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
