chart绑定mysql数据源_使用ECharts绑定SQL 2008数据库数据显示图表

新建Web页面GetChartData.aspx及GetChartData.aspx.cs，用于从数据库获取数据，代码如下：

protected void Page_Load(object sender, EventArgs e)

{

string type = Request["type"];

if (!string.IsNullOrEmpty(type))

{

switch (type)

{

case "energyData":

GetEnergyDataAjaxData("", "");

break;

case "reportData":

GetReportDataAjaxData("");

break;

}

}

}

private void GetEnergyDataAjaxData(string level, string code)

{

List categoryList = new List();

List seriesList = new List();

List legendList = new List();

DataTable dt = null;

using (SqlConnection con = new SqlConnection("Data Source=.;User ID=sa;Password=111111;Database=XXXXX;"))

{

string strSQL = "select top 12 * from T_EchartData1";

using (SqlDataAdapter adapter = new SqlDataAdapter(strSQL, con))

{

DataSet ds = new DataSet();

adapter.Fill(ds);

dt = ds.Tables[0];

}

}

legendList.Add("今年能耗");

legendList.Add("去年能耗");

Series thisSeriesObj = new Series();

thisSeriesObj.id = 0;

thisSeriesObj.name = "今年能耗";

thisSeriesObj.type = "line"; //线性图呈现

thisSeriesObj.data = new List();

thisSeriesObj.itemStyle = new itemStyle { normal = new normal { color = "#0099FF" } };

Series lastSeriesObj = new Series();

lastSeriesObj.id = 1;

lastSeriesObj.name = "去年能耗";

lastSeriesObj.type = "line"; //线性图呈现

lastSeriesObj.data = new List();

lastSeriesObj.itemStyle = new itemStyle { normal = new normal { color = "#00CC00" } };

foreach (DataRow dr in dt.Rows)

{

categoryList.Add(dr[0].ToString() + "月");

thisSeriesObj.data.Add(dr[1]);

lastSeriesObj.data.Add(dr[2]);

}

seriesList.Add(thisSeriesObj);

seriesList.Add(lastSeriesObj);

var newObj = new

{

category = categoryList,

series = seriesList,

legend = legendList

};

Response.Write(JsonConvert.SerializeObject(newObj, Formatting.None));

Response.End();

}

private void GetReportDataAjaxData(string code)

{

List categoryList = new List();

List seriesList = new List();

List legendList = new List();

DataTable dt = null;

using (SqlConnection con = new SqlConnection("Data Source=.;User ID=sa;Password=111111;Database=AT_DB;"))

{

string strSQL = "select * from T_EchartData2";

using (SqlDataAdapter adapter = new SqlDataAdapter(strSQL, con))

{

DataSet ds = new DataSet();

adapter.Fill(ds);

dt = ds.Tables[0];

}

}

//设置legend数组

legendList.Add("电");

legendList.Add("水");

legendList.Add("燃煤");

legendList.Add("天然气");

legendList.Add("汽油");

legendList.Add("柴油");

legendList.Add("热力");

legendList.Add("其他");

Series dianSeries = new Series();

dianSeries.id = 0;

dianSeries.name = "电";

dianSeries.type = "line"; //线性图呈现

dianSeries.data = new List();

dianSeries.itemStyle = new itemStyle { normal = new normal { color = "#0099FF" } };

Series shuiSeries = new Series();

shuiSeries.id = 1;

shuiSeries.name = "水";

shuiSeries.type = "line"; //线性图呈现

shuiSeries.data = new List();

shuiSeries.itemStyle = new itemStyle { normal = new normal { color = "#00CC00" } };

Series yuanmeiSeries = new Series();

yuanmeiSeries.id = 2;

yuanmeiSeries.name = "燃煤";

yuanmeiSeries.type = "line"; //线性图呈现

yuanmeiSeries.data = new List();

yuanmeiSeries.itemStyle = new itemStyle { normal = new normal { color = "#008080" } };

Series tianranqiSeries = new Series();

tianranqiSeries.id = 3;

tianranqiSeries.name = "天然气";

tianranqiSeries.type = "line"; //线性图呈现

tianranqiSeries.data = new List();

tianranqiSeries.itemStyle = new itemStyle { normal = new normal { color = "#CC6600" } };

Series qiyouSeries = new Series();

qiyouSeries.id = 4;

qiyouSeries.name = "汽油";

qiyouSeries.type = "line"; //线性图呈现

qiyouSeries.data = new List();

qiyouSeries.itemStyle = new itemStyle { normal = new normal { color = "#CC00CC" } };

Series chaiyouSeries = new Series();

chaiyouSeries.id = 5;

chaiyouSeries.name = "柴油";

chaiyouSeries.type = "line"; //线性图呈现

chaiyouSeries.data = new List();

chaiyouSeries.itemStyle = new itemStyle { normal = new normal { color = "#0033CC" } };

Series reliSeries = new Series();

reliSeries.id = 6;

reliSeries.name = "热力";

reliSeries.type = "line"; //线性图呈现

reliSeries.data = new List();

reliSeries.itemStyle = new itemStyle { normal = new normal { color = "#003300" } };

Series qitaSeries = new Series();

qitaSeries.id = 7;

qitaSeries.name = "其他";

qitaSeries.type = "line"; //线性图呈现

qitaSeries.data = new List();

qitaSeries.itemStyle = new itemStyle { normal = new normal { color = "#FF0000" } };

foreach (DataRow dr in dt.Rows)

{

categoryList.Add(string.Format("{0}年{1}月", dr["ReportYear"], dr["ReportMonth"]));

dianSeries.data.Add(string.IsNullOrEmpty(dr["DIAN"].ToString()) ? 0 : dr["DIAN"]);

shuiSeries.data.Add(string.IsNullOrEmpty(dr["SHUI"].ToString()) ? 0 : dr["SHUI"]);

yuanmeiSeries.data.Add(string.IsNullOrEmpty(dr["YUANMEI"].ToString()) ? 0 : dr["YUANMEI"]);

tianranqiSeries.data.Add(string.IsNullOrEmpty(dr["TIANRQ"].ToString()) ? 0 : dr["TIANRQ"]);

qiyouSeries.data.Add(string.IsNullOrEmpty(dr["QIYOU"].ToString()) ? 0 : dr["QIYOU"]);

chaiyouSeries.data.Add(string.IsNullOrEmpty(dr["CHAIYOU"].ToString()) ? 0 : dr["CHAIYOU"]);

reliSeries.data.Add(string.IsNullOrEmpty(dr["RELI"].ToString()) ? 0 : dr["RELI"]);

qitaSeries.data.Add(string.IsNullOrEmpty(dr["QTNY"].ToString()) ? 0 : dr["QTNY"]);

}

//将sereis对象压入sereis数组列表内

seriesList.Add(dianSeries);

seriesList.Add(shuiSeries);

seriesList.Add(yuanmeiSeries);

seriesList.Add(tianranqiSeries);

seriesList.Add(qiyouSeries);

seriesList.Add(chaiyouSeries);

seriesList.Add(reliSeries);

seriesList.Add(qitaSeries);

//因为我们需要返回category和series、legend多个对象 这里我们自己在new一个新的对象来封装这两个对象

var newObj = new

{

category = categoryList,

series = seriesList,

legend = legendList

};

//Response返回新对象的json数据

Response.Write(JsonConvert.SerializeObject(newObj, Formatting.None));

Response.End();

}

}

///

/// 定义一个Series类 设置其每一组sereis的一些基本属性

///

class Series

{

///

/// sereis序列组id

///

public int id

{

get;

set;

}

///

/// series序列组名称

///

public string name

{

get;

set;

}

///

/// series序列组呈现图表类型(line、column、bar等)

///

public string type

{

get;

set;

}

///

/// series序列组的itemstyle

///

public object itemStyle

{

get;

set;

}

///

/// series序列组的数据为数据类型数组

///

public List data

{

get;

set;

}

}

class normal

{

///

/// color

///

public string color

{

get;

set;

}

}

class itemStyle

{

///

/// normal

///

public object normal

{

get;

set;

}