How to Create BarChart from Database in Asp.Net

In this example we will see how to create bar chart In dot net from database. Chart controls are used for creating chart in webpage so user can see easily in page and read data from that page. It’s easy to understand in from design. Using ajaxcontroltoolkit we will draw bar chart in webpage. So let’s start how to draw bar chart in Webpage. In this example we will see Publisher example for based on country we will see data State wise and total amount of that state in country. So when we select Country from dropdown controls then we can see state wise data and amounts of particular state of that country.

First Create table in your database and fill data name it “publishers”.

CREATE TABLE [dbo].[publishers](

      [pub_id] [int] IDENTITY(1,1) NOT NULL,

      [pub_name] [varchar](50) NOT NULL,

      [state] [varchar](50) NOT NULL,

      [country] [varchar](50) NOT NULL,

      [Amounts] [bigint] NULL,

 CONSTRAINT [PK_publishers] PRIMARY KEY CLUSTERED

(

      [pub_id] 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>

        <cc1:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">

        </cc1:ToolkitScriptManager>

        <asp:DropDownList ID="ddlCountries" runat="server" OnSelectedIndexChanged="ddlCountries_SelectedIndexChanged"

            AutoPostBack="true">

        </asp:DropDownList>

        <hr />

        <cc1:BarChart ID="BarChart1" runat="server" ChartHeight="300" ChartWidth="450" ChartType="Column"

            ChartTitleColor="#0E426C" Visible="false" CategoryAxisLineColor="Red" ValueAxisLineColor="Red"

            BaseLineColor="#A156AB">

        </cc1:BarChart>

    </div>

For Char Controls you have to Register AjaxControltoolKit in your Webpage.

<%@Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>

Now Go to Code behind and Code for your Bar Chart. First we will get data from database and bind in our dropdown controls so based on Country we can see data in bar chart.

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;

            }

        }

Now we will bind data in dropdown control and we will call this method in page load so when the first time page call this method call.

protected void Page_Load(object sender, EventArgs e)

        {

            if (!IsPostBack)

            {

                string query = "select distinct country from publishers";

                DataTable dt = GetData(query);

                ddlCountries.DataSource = dt;

                ddlCountries.DataTextField = "country";

                ddlCountries.DataValueField = "country";

                ddlCountries.DataBind();

                ddlCountries.Items.Insert(0, new ListItem("Select", ""));

            }

        }

Now we will code for selection changed event of dropdown control when we changed country from that control that time we have to bind bar chart in webpage.

protected void ddlCountries_SelectedIndexChanged(object sender, EventArgs e)

        {

            string query = string.Format("select state,Amounts from publishers where country = '{0}' group by state,Amounts", ddlCountries.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]);

            }

            BarChart1.Series.Add(new AjaxControlToolkit.BarChartSeries { Data = y });

            BarChart1.CategoriesAxis = string.Join(",", x);

            BarChart1.ChartTitle = string.Format("{0} Order Distribution", ddlCountries.SelectedItem.Value);

            if (x.Length > 3)

            {

                BarChart1.ChartWidth = (x.Length * 100).ToString();

            }

            BarChart1.Visible = ddlCountries.SelectedItem.Value != "";

        }

Here in this Selection Changed Event we have Bind Bar Chart Control from Ajaxtoolkit. Here we have create Query and get data from that query and after bind that particular columns what we have want for series in bar chart controls. Now see in your browser for Bar chart Control.

 

Author : Umesh Patel

Related Posts