Thursday 31 March 2011

Random Record from Sql Server in ASP.NET


Introduction: Hello friends, in this article i will explain that how we can bind gridview and how  can display random recrd at everytime  or every refresh. Here I am using gridview control to display data, but you can use any other control to display the random record.

Implementation: Create a new website add a page named gridview.aspx. Drag and drop the gridview  at  this page from the toolbox. Below i am giving the html code for the .aspx page.

Code for gridview.aspx page:

<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
        BackColor="#CCCCCC" BorderColor="#999999" BorderStyle="Solid" BorderWidth="3px"
        CellPadding="4" ForeColor="Black" Height="241px"
        Width="374px" CellSpacing="2">
            <RowStyle BackColor="White" />
            <Columns>
                <asp:TemplateField HeaderText="ID">
                    <ItemTemplate>
                        <%#Eval("id"%>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Name">
                    <ItemTemplate>
                        <%#Eval("name"%>
                    </ItemTemplate>
                </asp:TemplateField>
                  <asp:TemplateField HeaderText="Age">
                    <ItemTemplate>
                        <%#Eval("age"%>
                    </ItemTemplate>
                </asp:TemplateField>
                 <asp:TemplateField HeaderText="Salary">
                    <ItemTemplate>
                        <%#Eval("salary")%>
                    </ItemTemplate>
                </asp:TemplateField>
                   <asp:TemplateField HeaderText="Designation">
                    <ItemTemplate>
                        <%#Eval("designation")%>
                    </ItemTemplate>
                </asp:TemplateField>
             
            </Columns>
            <FooterStyle BackColor="#CCCCCC" />
            <PagerStyle BackColor="#CCCCCC" ForeColor="Black" HorizontalAlign="Left" />
            <SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
        </asp:GridView>
    </div>
    </form>
</body>


Code for gridview.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;
using System.Configuration;
using System.Data.Sql;
using System.Data.SqlClient;

public partial class bind_gridview : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Page.IsPostBack == false)
        {
            // here i am calling the function which bind the gridview
            grd_bind();
        }
    }


    private void grd_bind()
    {
        // here i am declare a connectionstring to attach the database
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["cnn"].ConnectionString);
        con.Open();
        // here i am definning the sql query which will used for the binding of the gridview and declare the SqlDataAdapter.
        // At the last of this query i am writting orderby newid(). newid() is a function that will rendom generate the
        // the record everytime or every refresh.
        SqlDataAdapter adp = new SqlDataAdapter("select * from tb_employee_info order by newid()", con);
        // here i am declare the dataset to fill the gridview
        DataSet ds = new DataSet();
        // here i am filling the SqlDataAdapter with the dataset
        adp.Fill(ds, "tb_employee_info");
        //here i am binding the gridview with the dataset ds 
        GridView1.DataSource = ds;
        GridView1.DataBind();
    }
}

SQl script for the table tb_employee_info
/****** Object:  Table [dbo].[tb_employee_info]    Script Date: 03/10/2011 12:29:23 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_employee_info]') AND type in (N'U'))
DROP TABLE [dbo].[tb_employee_info]
GO
/****** Object:  Table [dbo].[tb_employee_info]    Script Date: 03/10/2011 12:29:23 ******/
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_employee_info]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tb_employee_info](
      [id] [bigint] IDENTITY(1,1) NOT NULL,
      [name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [age] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [salary] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [designation] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
END
GO
SET IDENTITY_INSERT [dbo].[tb_employee_info] ON
INSERT [dbo].[tb_employee_info] ([id], [name], [age], [salary], [designation]) VALUES (1, N'bharat', N'25', N'10000', N'developer')
INSERT [dbo].[tb_employee_info] ([id], [name], [age], [salary], [designation]) VALUES (2, N'harish', N'24', N'5000', N'designer')
INSERT [dbo].[tb_employee_info] ([id], [name], [age], [salary], [designation]) VALUES (3, N'rakesh', N'25', N'5600', N'developer')
INSERT [dbo].[tb_employee_info] ([id], [name], [age], [salary], [designation]) VALUES (4, N'vishal', N'23', N'5400', N'tester')
INSERT [dbo].[tb_employee_info] ([id], [name], [age], [salary], [designation]) VALUES (5, N'varun', N'24', N'6500', N'contentwriter')
INSERT [dbo].[tb_employee_info] ([id], [name], [age], [salary], [designation]) VALUES (6, N'sunil', N'21', N'6500', N'peon')
INSERT [dbo].[tb_employee_info] ([id], [name], [age], [salary], [designation]) VALUES (7, N'amar', N'27', N'7500', N'gardner')
INSERT [dbo].[tb_employee_info] ([id], [name], [age], [salary], [designation]) VALUES (8, N'hitesh', N'25', N'10000', N'developer')
INSERT [dbo].[tb_employee_info] ([id], [name], [age], [salary], [designation]) VALUES (9, N'rajan', N'24', N'8800', N'designer')
SET IDENTITY_INSERT [dbo].[tb_employee_info] OFF


Below i am displaying the outout of this program in the image:




Here I am displaying another image as output. The output in this image is different




So it is clear that with the help of above code we can display random record after fetching the data from the database in asp.net.

Conclusion: Dear friends, through this article you have learned that how we can display random record in any control.  Here I am using gridview control to display data, but you can use any other control to display the random record.  



2 comments: