Monday, 24 June 2013

LINE CHART USING ASP.NET WITH SQL-SERVER


Using Sql Server:
CREATE TABLE [dbo].[tblChart](      [id] [bigint] IDENTITY(1,1) NOT NULL,
      [year] [varchar](50) NULL,
      [sales] [varchar](50) NULL,
      [expences] [varchar](50) NULL,
 CONSTRAINT [PK_tblChart] PRIMARY KEY CLUSTERED(      [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]

SET IDENTITY_INSERT [dbo].[tblChart] ON
INSERT [dbo].[tblChart] ([id], [year], [sales], [expences]) VALUES (1, N'2009', N'5000', N'2136')
INSERT [dbo].[tblChart] ([id], [year], [sales], [expences]) VALUES (2, N'2010', N'9002', N'5063')
INSERT [dbo].[tblChart] ([id], [year], [sales], [expences]) VALUES (3, N'2011', N'8800', N'2225')
SET IDENTITY_INSERT [dbo].[tblChart] OFF


In .aspx Page:
<head id="Head1" runat="server">
    <title>Line Chart </title>
    <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="divLineChart"></div>
    </form>
</body>


In .aspx.cs Page:
  SqlConnection Conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
    StringBuilder str = new StringBuilder();
    protected void Page_Load(object sender, EventArgs e)
    {
        Conn.Open();
        if (!IsPostBack)
        {
            bindChart();
        }
    }
    private void bindChart()
    {
        SqlDataAdapter da = new SqlDataAdapter("select * from tblChart", Conn);
        DataTable dt = new DataTable();
        try
        {
            da.Fill(dt);
            // This code write in javascript code in .aspx page..
            // and we can write in code page also..
            // this code i am catching in the stringbuilder class

            //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));

            // This data is coming from the sql server
            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;
            for (i = 0; i <= dt.Rows.Count - 1; i++)
            {
                str.Append("data.setValue( " + i + "," + 0 + "," + "'" + dt.Rows[i]["year"].ToString() + "');");
                str.Append("data.setValue(" + i + "," + 1 + "," + dt.Rows[i]["sales"].ToString() + ") ;");
                str.Append(" data.setValue(" + i + "," + 2 + "," + dt.Rows[i]["expences"].ToString() + ");");
            }
            str.Append("var chart = new google.visualization.LineChart(document.getElementById('divLineChart'));");
            str.Append("chart.draw(data, {width: 650, height: 300, legend: 'bottom',is3D: false,title: 'Performance',");
            str.Append("vAxis: {title: 'Year', titleTextStyle: {color: 'green'}}");
            str.Append("}); }");
            str.Append("</script>");

            lt.Text = str.ToString().TrimEnd(',').Replace('*', '"');
            Conn.Close();
        }
        catch
        {  }
        finally
        {Conn.Close();}
    }

posted by silambarasanbalu @ 22:09   0 Comments

0 Comments:

Post a Comment

Subscribe to Post Comments [Atom]

<< Home