Wednesday 28 March 2012

Bind dropdownlist in asp.net


Introduction: Hello friends, in this article i will explain that how we can bind dropdownlost dynamically in asp.net.This article is very useful for the all the .net developer. In this article i’ll the logic to bind the dropdownlist with the column of a table of the sql server in asp.net. 

Implementation: create a new website add a page bind_dropdownlist.aspxmpage. Drag and drop a dropdownlist  from the toolbox inside the  <body> body tag at the .aspx page.  Below  i am giving the complete code for the html page and .cs page. 

Code for bind_dropdownlist.aspx page

<head runat="server">
    <title>Bind Dropdownlist</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:DropDownList ID="dd_name" runat="server">
        </asp:DropDownList>
    </div>
    </form>
</body>
</html>


Code for bind_dropdownlist.aspx.cs page

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;


public partial class bind_dropdownlist : System.Web.UI.Page
{
    SqlCommand cmd = new SqlCommand();
    SqlConnection con = new SqlConnection();
    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_dd();
        }
    }

    private void bind_dd()
    {
       
        cmd = new SqlCommand();
        // here inside the cmd i am definning sql query to select the name from the table
        cmd.CommandText = "select name from tb_student_detail  group by name order by name";
        cmd.Connection = con;
        // here i am declairing the SqlDataReader to catch the values that will come from the sql query
        SqlDataReader dd_values;
        // here am declairing the ExecuteReader to execute SqlDataReade
        dd_values = cmd.ExecuteReader();
        // here i am binding the dropdownlist with the SqlDataReader
        dd_name.DataSource = dd_values;
        // here  i am binding the dropdownlist with the cloumn name of the table to show the
        // name inside the dropdownlist
        dd_name.DataValueField = "name";
        dd_name.DataBind();
        // after binding the dropdown list here i am closing and disposing  the SqlDataReader
        dd_values.Dispose();
        dd_values.Close();


    }
}
Database Script- create a table in database named tb_register. Here I am giving you to complete database script of the table tb_student_detail. 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



See output in this image:



Conclusion: In above code, I have been explained that how  we can bind dropdownlist dynamically in asp.net. This code is very helpful for every .net developer. Gud bye and take care developers.






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.