Tuesday 9 August 2011

Line chart in asp.net


Introduction: Hello dears, in this article i will explain that how we can create Visualization: line Chart using asp.net. This is very helpful artucle for the asp.net deveploer to creating a mast graph in asp.net. This type of chart you can create to showing purchase and sale or sale and expences or yes or no etc in different years.

Implementation: Here i am taking the reference from the below link . http://code.google.com/apis/chart/interactive/docs/gallery/barchart.html
Google use javascript code for the implementation of this graph. I am using same javascript code but am giving also my asp.net experience with this code. And create the same graph in Asp.Net.
Code for .aspx page:
<head id="Head1" runat="server">
<title>Bar Graph using Google Visualization</title>
         <%--the below two javascripts  are needed to run this program you can
      also download this script from the below link --%>

     <script type="text/javascript" src="https://www.google.com/jsapi"></script>
</head>
<body>
    <form id="form1" runat="server">
  <div>

        <asp:Literal ID="lt" runat="server"></asp:Literal>
    </div>
   
    <div id="chart_div"></div>

    </form>
</body>
Code for aspx.cs page:

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Text;


public partial class chart_sale_purchage : System.Web.UI.Page
{
    // complete code of Visualization: Combo Chart
    SqlConnection con = new SqlConnection();
    // here am declairing the stringbuilder class
    StringBuilder str = new StringBuilder();
    protected void Page_Load(object sender, EventArgs e)
    {
        // here i am declare connection 
        con.ConnectionString = ConfigurationManager.ConnectionStrings["cn"].ConnectionString;
        con.Open();
        if (Page.IsPostBack == false)
        {
            // here i am calling function  chart_bind(); in the page load event of the page
            chart_bind();
        }

    }
    private void chart_bind()
    {
        // here i am using SqlDataAdapter for the sql server select query
        SqlDataAdapter adp = new SqlDataAdapter("select top(7)* from tb_exp", con);
        // here am taking datatable
        DataTable dt = new DataTable();
        try
        {
            // here datatale dt is fill wit the adp
            adp.Fill(dt);
            // this string m catching in the stringbuilder class
            // in the str m writing same javascript code that is given by the google.
            // but m changing  only below line
            //data.addColumn('string'(datatype), 'Year'(columnname according to the sql table));
            //data.addColumn('number'(datatype), 'Sales'(columnname according to the sql table));
            //data.addColumn('number'(datatype), 'Expenses'(columnname according to the sql table));
            // my data that will come from the sql server
            // in the below line i need " in place of *
            // stringbuilder can't return us " so at the last line i am
            // replacing * with the " using Replace('*', '"'); function
            // and other code is same like the google code
            str.Append(@"<script type=text/javascript> google.load( *visualization*, *1*, {packages:[*corechart*]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Year');
        data.addColumn('number', 'Sales');
        data.addColumn('number', 'Expenses');

        data.addRows(" + dt.Rows.Count + ");");

            Int32 i;
            //here i am using for loop to fetch multiple recorod from the database
            for (i = 0; i <= dt.Rows.Count - 1; i++)
            {
                // i need this type of output " data.setValue(0, 0, '2004');",(1, 0, '2005');" * so on  in the first line so for this
                //m using i for the 1& 2 and so on . and after this i am writting zero and after this year using datatable
                str.Append("data.setValue( " + i + "," + 0 + "," + "'" + dt.Rows[i]["year"].ToString() + "');");
                // i need this type of output " data.setValue(0, 1, 'sales');",(1, 1, 'sales');" * so on  in the first line so for this
                //m using i for the 1& 2 and so on . and after this i am writting 1 and after this sales using datatable
                str.Append("data.setValue(" + i + "," + 1 + "," + dt.Rows[i]["sales"].ToString() + ") ;");
                // i need this type of output " data.setValue(0,2, 'expences');",(1, 2, 'sales');" * so on  in the first line so for this
                //m using i for the 1& 2 and so on . and after this i am writting 2 and after this expences using datatable
                str.Append(" data.setValue(" + i + "," + 2 + "," + dt.Rows[i]["expences"].ToString() + ");");



            }
            // the other code same like as google's javascript code
                  str.Append("   var chart = new google.visualization.LineChart(document.getElementById('chart_div'));");
            // in the below line you can set width height of the chart according to your need
            str.Append(" chart.draw(data, {width: 600, height: 240, title: 'Company Performance',");
            //  str.Append(" var chart = new google.visualization.BarChart(document.getElementById('chart_div'));");
            str.Append("vAxis: {title: 'Year', titleTextStyle: {color: 'green'}}");
            str.Append("}); }");
            str.Append("</script>");
            lt.Text = str.ToString().TrimEnd(',').Replace('*', '"');


            con.Close();
        }
        catch
        {

        }
        finally
        {
            con.Close();
        }

    }
}
See output in this image:

Sql Script for database:

/****** Object:  Table [dbo].[tb_exp]    Script Date: 07/07/2011 16:40:07 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_exp]') AND type in (N'U'))
DROP TABLE [dbo].[tb_exp]
GO
/****** Object:  Table [dbo].[tb_exp]    Script Date: 07/07/2011 16:40:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_exp]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tb_exp](
      [id] [bigint] IDENTITY(1,1) NOT NULL,
      [year] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [sales] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [expences] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_tb_exp] PRIMARY KEY CLUSTERED
(
      [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)
END
GO
SET IDENTITY_INSERT [dbo].[tb_exp] ON
INSERT [dbo].[tb_exp] ([id], [year], [sales], [expences]) VALUES (1, N'2009', N'5000', N'2136')
INSERT [dbo].[tb_exp] ([id], [year], [sales], [expences]) VALUES (2, N'2010', N'9002', N'5063')
INSERT [dbo].[tb_exp] ([id], [year], [sales], [expences]) VALUES (3, N'2011', N'8800', N'2225')
SET IDENTITY_INSERT [dbo].[tb_exp] OFF

Conclusion
Through this article, you have learned how to create Visualization: line Chart using the google’s javascript code as well as asp.net code.

Okas  guys, enjoy the code. Meet in next article again.

Thanx