Chart
Control is used full for drawing char in Webpage we can fill data in chart
control and display a graph in page so user can understand easily. Here we will
draw multiline chart in webpage from database so data will be from Sql server
and draw a graph in page using AjaxControlToolKit and chart controls.
Create orders table in your database and
fill some data.
CREATE TABLE [dbo].[orders](
[orderno] [int] NOT NULL,
[orderdate] [datetime] NULL,
[shipcountry] [varchar](10) NULL,
PRIMARY KEY CLUSTERED
(
[orderno] ASC
)WITH
(PAD_INDEX
= OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Step 1: Design your
Webpage like below.
<div style="height: 378px; width: 496px">
<cc1:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
</cc1:ToolkitScriptManager>
<asp:DropDownList ID="ddlCountry1" runat="server">
</asp:DropDownList>
<asp:DropDownList ID="ddlCountry2" runat="server">
</asp:DropDownList>
<asp:Button ID="btnCompare" runat="server" Text="Compare" OnClick="Compare" Height="26px" />
<hr />
<cc1:LineChart ID="LineChart1" runat="server" ChartHeight="300" ChartWidth="450"
ChartType="Basic" ChartTitleColor="#0E426C" Visible="false" CategoryAxisLineColor="#D08AD9"
ValueAxisLineColor="#D08AD9" BaseLineColor="#A156AB">
</cc1:LineChart>
</div>
Step 2: Now register
AjaxControlToolKit in your Webpage.
<%@ RegisterAssembly="AjaxControlToolkit"Namespace="AjaxControlToolkit"TagPrefix="cc1"%>
Step 3: now create a
Connection String Web.Config File so we can use on every page when we want data
connection from database. It is secure to use connection string in Web.Config
file.
<connectionStrings>
<add connectionString="Data Source=ServerName;Initial
Catalog=DatabaseName;Integrated Security=True" providerName="System.Data.SqlClient" name="DBCS"/>
</connectionStrings>
One more thing has to
keep in mind to Create handler in Web.Config file in that System.Web tab.
<httpHandlers>
<add path="ChartImg.axd" verb="GET,HEAD,POST" type="System.Web.UI.DataVisualization.Charting.ChartHttpHandler,
System.Web.DataVisualization, Version=4.0.0.0, Culture=neutral,
PublicKeyToken=31bf3856ad364e35" validate="false"/>
</httpHandlers>
Step 4: now bind DropDownList
control from database in Page_load () event so when page load first time then
we can select data from DropDownList.
protected void Page_Load(object
sender, EventArgs e)
{
if
(!IsPostBack)
{
string
query = "select distinct shipcountry from
orders";
DataTable
dt = GetData(query);
ddlCountry1.DataSource = dt;
ddlCountry1.DataTextField = "shipcountry";
ddlCountry1.DataValueField = "shipcountry";
ddlCountry1.DataBind();
ddlCountry2.DataSource = dt;
ddlCountry2.DataTextField = "shipcountry";
ddlCountry2.DataValueField = "shipcountry";
ddlCountry2.DataBind();
ddlCountry2.Items[1].Selected =
true;
}
}
Step 4: Create GetData () method for
getting data from database and return DataTable and bind DropDownList controls.
private static DataTable
GetData(string query)
{
DataTable
dt = new DataTable();
string
constr = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using
(SqlConnection con = new SqlConnection(constr))
{
using
(SqlCommand cmd = new
SqlCommand(query))
{
using
(SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
sda.SelectCommand =
cmd;
sda.Fill(dt);
}
}
return
dt;
}
}
Step 5: Now Create a query and pass in GetData
() method so based on query we can get data and return DataTable and bind data
in series in chart Controls. Here you can see I have pass two queries for
getting multiple DataTable from database and bind in chart controls. So we can
create line chart series based on selected DropDownList controls value.
protected void Compare(object
sender, EventArgs e)
{
string
query = string.Format("select
datepart(year, orderdate) Year, count(datepart(year, orderdate))
TotalOrders from orders where
shipcountry = '{0}' group by datepart(year, orderdate)",
ddlCountry1.SelectedItem.Value);
DataTable
dt = GetData(query);
string[] x
= new string[dt.Rows.Count];
decimal[]
y = new decimal[dt.Rows.Count];
for
(int i = 0; i < dt.Rows.Count; i++)
{
x[i] =
dt.Rows[i][0].ToString();
y[i] = Convert.ToInt32(dt.Rows[i][1]);
}
LineChart1.Series.Add(new AjaxControlToolkit.LineChartSeries
{ Name = ddlCountry1.SelectedItem.Value, Data = y });
query = string.Format("select datepart(year, orderdate) Year,
count(datepart(year, orderdate)) TotalOrders
from orders where shipcountry = '{0}' group by datepart(year,
orderdate)", ddlCountry2.SelectedItem.Value);
dt = GetData(query);
y = new
decimal[dt.Rows.Count];
for
(int i = 0; i < dt.Rows.Count; i++)
{
x[i] =
dt.Rows[i][0].ToString();
y[i] = Convert.ToInt32(dt.Rows[i][1]);
}
LineChart1.Series.Add(new AjaxControlToolkit.LineChartSeries
{ Name = ddlCountry2.SelectedItem.Value, Data = y });
LineChart1.CategoriesAxis = string.Join(",",
x);
LineChart1.ChartTitle = string.Format("{0}
and {1} Order Distribution", ddlCountry1.SelectedItem.Value,
ddlCountry2.SelectedItem.Value);
LineChart1.Visible = true;
}
Now run your webpage
and select values from DropDownList controls and create multiline chart.
Thanks for comments.....