Wednesday, 4 April 2012

Stored Procedures for binding Gridview || How to use Stored Procedures to Retrieve the value to Grid view from database in Asp.net.

Here is the example for binding Gridview using stored procedure in asp.net. In this we can select the records by unique value in table and view in gridview. By entering the user number we can get the user details in gridview. It is just like getting the marks list when hall-ticket number is entered. we can use this application for such purpose.

To implement this concept you need to follow the below steps :

Step1 :
First you need to design a table in Sql Database to save the records in database and create a Stored Procedure.

Step2:
Create a new Asp.net website in Visual Studio and write the following source code in the design part of the Default.aspx page.

Source code :
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div style="font-family: 'Times New Roman', Times, serif; font-size: 25px; font-weight: bold; font-style: italic">
        Stored Procedures<br />
        <br />
    </div>
    <asp:Label ID="Label1" runat="server" Text="Enter User No."></asp:Label>
    <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
    <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Enter" />
    <asp:RangeValidator ID="RangeValidator1" runat="server"
        ControlToValidate="TextBox1" ErrorMessage="1 - 5" MaximumValue="5"
        MinimumValue="1"></asp:RangeValidator>
    <br />
    <br />
    <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333"
        GridLines="None">
        <AlternatingRowStyle BackColor="White" />
        <EditRowStyle BackColor="#2461BF" />
        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
        <RowStyle BackColor="#EFF3FB" />
        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
        <SortedAscendingCellStyle BackColor="#F5F7FB" />
        <SortedAscendingHeaderStyle BackColor="#6D95E1" />
        <SortedDescendingCellStyle BackColor="#E9EBEF" />
        <SortedDescendingHeaderStyle BackColor="#4870BE" />
    </asp:GridView>
    </form>
</body>
</html>


Step3:
Now open the Default.aspx.cs page and write the following source code.

Default.aspx.cs :
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.Data;

public partial class _Default : System.Web.UI.Page
{
    SqlConnection conn = new SqlConnection("Data Source=sridhar;Initial Catalog=Register;User ID=sa;Password=123");
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        conn.Open();
        SqlDataAdapter da = new SqlDataAdapter("userdetails",conn);
        da.SelectCommand.CommandType = CommandType.StoredProcedure;
        da.SelectCommand.Parameters.Add(new SqlParameter("@pno", SqlDbType.Int));
        da.SelectCommand.Parameters["@pno"].Value = (TextBox1.Text).Trim();
        DataSet ds = new DataSet();
        da.Fill(ds,"person");
        GridView1.DataSource = ds.Tables["person"];
        GridView1.DataBind();
        da.Dispose();
        conn.Close();
    }
}


Step4:
Now build the Solution and Debug it for the output.

Output :




2 comments:

  1. Please upload a sample project

    ReplyDelete
  2. same functionality implement in mvc .can u send me sample code of that please

    ReplyDelete

CRUD operations using AngularJS in Asp.Net MVC

In this article I will show you how to perform CRUD operations in Asp.Net MVC5 using AngularJS. Add AngularJS using the Manage Nuget Pack...