Monday 26 March 2012

Chart with negative and positive values


Introduction- Through this article, i will show how , we can create dynamic chart with the negative and positive Data. This article will very helpful for all the .net developers to create attractive chart to show the negative and positive data. And I convert the it into .net using the sql server query and the stringbuilder class of the visual studio. Below I am also giving the link to download the complete folder of this article with the sql server script.
Implementation- create a website , add page named negative_chart.aspx. place a literal control at the design side of this page. Below I am giving the complete code for .aspx page and also .aspx.cs page.All the .js files that I am using in the .aspx are available inside the downloaded folder.




Code for negative_chart.aspx Page-

<head runat="server">
  <title>Highcharts Example</title>
           
           
              <%--Graphs_start these below javascripts a necessary for this charts--%>
   
     

    <script src="js_charts/modules/ajax_jscript.js" type="text/javascript"></script>
     
            <script type="text/javascript" src="js_charts/highcharts.js"></script>
           
            <!-- 1a) Optional: add a theme file you can also add the theme file to change the design for the chart -->
           
            <%--  <script type="text/javascript" src="js_charts/themes/gray.js"></script>--%>
                        <%--<script type="text/javascript" src="js_charts/themes/grid.js"></script>--%>
     
           
            <!-- 1b) Optional: the exporting module -->
            <script type="text/javascript" src="js_charts/modules/exporting.js"></script>
            <%--Graphs_end--%>
           
           
</head>
<body runat="server" visible="true">
     <form id="form1" runat="server">
    <div>
    <asp:Literal ID="lt" runat="server"></asp:Literal>
    <%--below m giving a div named container. the chart will print inside this div--%>
 <div id="container" style="width: 800px; height: 285px;"></div>
  <center><asp:Label ID="lbl_msg" runat="server" Text="" CssClass="" Visible ="false"></asp:Label></center>
    </div>
    </form>
</body>
</html>


Code for negative_chart.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.Text;
using System.Data.SqlClient;
using System.IO;
using System.Data.Sql;
using System.Collections.Generic;
public partial class negative_chart : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection();
    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
            bind_chart();
        }
    }
    private void bind_chart()
    {
        // here i am using SqlDataAdapter for the sql server select query
        SqlDataAdapter adp = new SqlDataAdapter(@"select  * from tb_student_detail
           order by tb_student_detail.id desc", 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 high charts

            // here i am checking the rows that will come in side the dt class.
            // if any  row will come then the else condition will perforne. otherewise the if condition
            // will performtheir work and display the msg inside the label that  No data available
            if (dt.Rows.Count == 0)
            {
                // if there will be no any record come inside the dt then this condition will work
                // and label print a message that No data available.
                lbl_msg.Visible = true;

                lbl_msg.Text = "No data available";
            }
            else
            {
                lbl_msg.Visible = false;
                // this javascript given by the highcharts and in this code i using values come from the database.
                //
                str.Append(@"<script type=text/javascript>
           
                  var chart;
                  $(document).ready(function() {
                        chart = new Highcharts.Chart({
                              chart: {
                                    renderTo: 'container',
                                    defaultSeriesType: 'column'
                              },
                              title: {
                                    text: 'Column chart with negative values'
                              },
                              xAxis: {
                                    categories: ['Science', 'English', 'Math', 'Biology', 'Botany']
                              },
                              tooltip: {
                                    formatter: function() {
                                          return ''+
                                                 this.series.name +': '+ this.y +'';
                                    }
                              },
                              credits: {
                                    enabled: false
                              },
                              series: [
                              ");
                Int32 i;
                for (i = 0; i <= dt.Rows.Count - 1; i++)
                {

                    str.Append("{name: '" + (dt.Rows[i]["name"].ToString()) + "', data: [" + (dt.Rows[i]["science"].ToString()) + "," + (dt.Rows[i]["english"].ToString()) + "," + (dt.Rows[i]["math"].ToString()) + "," + (dt.Rows[i]["biology"].ToString()) + "," + (dt.Rows[i]["botany"].ToString()) + "]},");
                }

                int remove_comma;

                remove_comma = str.Length - 1;



                str.Remove(remove_comma, 1);

                        str.Append(@"]
                        });
                       
                       
                  });
                       
            </script>
            ");
            }

            // here i am print the complete string in side the literal control
            // here i am using the literal control  because this control does not  creata span tag like label.
            lt.Text = str.ToString();




        }
        catch
        {
       
        }
        finally
        {


        }
    }
}

See output in this image:

Simple display of the chart

Print  option inside the chart:
 You can also donload png jpg or pdf of the chart:



Database Script- create a table in database named tb_register. Here I am giving you to complete database script of the table tb_register. See below:
/****** Object:  Table [dbo].[tb_student_detail]    Script Date: 04/10/2012 18:13:44 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_student_detail]') AND type in (N'U'))
DROP TABLE [dbo].[tb_student_detail]
GO
/****** Object:  Table [dbo].[tb_student_detail]    Script Date: 04/10/2012 18:13:44 ******/
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_student_detail]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tb_student_detail](
      [id] [bigint] IDENTITY(1,1) NOT NULL,
      [name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [science] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [english] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [math] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [biology] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [botany] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_tb_student_detail] 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_student_detail] ON
INSERT [dbo].[tb_student_detail] ([id], [name], [science], [english], [math], [biology], [botany]) VALUES (1, N'vikas', N'-45', N'20', N'-56', N'75', N'89')
INSERT [dbo].[tb_student_detail] ([id], [name], [science], [english], [math], [biology], [botany]) VALUES (2, N'ragubeer', N'-25', N'29', N'79', N'36', N'96')
INSERT [dbo].[tb_student_detail] ([id], [name], [science], [english], [math], [biology], [botany]) VALUES (3, N'akash', N'65', N'59', N'89', N'63', N'35')
INSERT [dbo].[tb_student_detail] ([id], [name], [science], [english], [math], [biology], [botany]) VALUES (4, N'vimal', N'-35', N'-59', N'23', N'46', N'87')
INSERT [dbo].[tb_student_detail] ([id], [name], [science], [english], [math], [biology], [botany]) VALUES (5, N'nisha', N'-65', N'99', N'93', N'56', N'21')
INSERT [dbo].[tb_student_detail] ([id], [name], [science], [english], [math], [biology], [botany]) VALUES (6, N'manav', N'52', N'39', N'63', N'21', N'38')
INSERT [dbo].[tb_student_detail] ([id], [name], [science], [english], [math], [biology], [botany]) VALUES (7, N'susheel', N'-96', N'-54', N'-63', N'29', N'36')
SET IDENTITY_INSERT [dbo].[tb_student_detail] OFF




Conclusion- Through this article, you have learned how we can display negative and positive chart in .net. If you have any idea to improve this article, please send , at my mail id bharti22200@yahoo.com, I will try to improve my next article.

5 comments:

  1. very nice article boss , it really helps me and string builder is too good.

    ReplyDelete
    Replies
    1. Thanks..jaskirat.. You are right, the stringbulder can really help to improve the coding technique.

      Regards
      Bharat Bhushan

      Delete