Wednesday, 16 April 2014

Show All Data and Get Single Record from DB using Gridview & DropDownList in ASP.NET

Ans:-
1.Here i have taken one Gridview to show all the records of user from DB.
2.and then taken one DropDownList and another Grodview to show the paricular user records from DB while selecting the DropDownList.

See Below Snaps of UI..


Default.aspx:-

 <%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>User Provider Policy Plan Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <center>
    <div>
    <b>Get All User Details:</b>
    <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
    </div>
    <br />
    <div>
    <b>Get User Details By ID:</b>
    <br />
        <asp:TextBox ID="txtUserID" runat="server"></asp:TextBox>
        &nbsp;
        <asp:Button ID="btnSubmit" runat="server" Text="Submit"
            onclick="btnSubmit_Click" />
        <br />
    <asp:GridView ID="GridView2" runat="server">
        </asp:GridView>
    </div>
    <div>
     <asp:Label ID="lblMsg" runat="server" Text="Label"></asp:Label>
    </div>
    </center>
    </form>
</body>
</html>

Default.aspx.cs:-

using System;
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.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    Class1 obj_Class1 = new Class1();
    SqlConnection cn;
    SqlDataAdapter da;
    SqlCommand cmd;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            GetUserDetails();
        }
    }
    void GetUserDetails()
    {
        cn = new SqlConnection(obj_Class1.GetConnection());
        cmd = new SqlCommand();
        cmd.Connection = cn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "spGetUserDetails";
        da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        cn = new SqlConnection(obj_Class1.GetConnection());
        cmd = new SqlCommand();
        cmd.Connection = cn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "spGetUserDetailsByID";
        cmd.Parameters.Add("@UserID", SqlDbType.Int).Value=txtUserID.Text;
        da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        try
        {
            cn.Open();
            da.Fill(dt);    
        }
        catch (Exception ex)
        {
            lblMsg.Text = ex.Message;
        }
        finally
        {
            cn.Close();
        }
        GridView2.DataSource = dt;
        GridView2.DataBind();
    }
}

Class1.cs:-

using System;
using System.Data;
using System.Configuration;
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;

/// <summary>
/// Summary description for Class1
/// </summary>
public class Class1
{
public Class1()
{
//
// TODO: Add constructor logic here
//
}
    public string GetConnection()
    {
        string strCon = "server=.;database=subsdb;user id=sa;pwd=123";
        return strCon;
    }
}

Tables & SP's;-

create table tbluser(uid int constraint p primary key,uname varchar(30))

create table tblprovider(providerid int constraint p1 primary key,providername varchar(30),uid int constraint f11 foreign key references tbluser(uid)on delete cascade on update cascade)

create table tblpolicy(policyid int constraint p2 primary key,policyname varchar(30),uid int constraint f12 foreign key references tbluser(uid)on delete cascade on update cascade)

create table tblplan(planid int constraint p3 primary key,planname varchar(30),uid int constraint f13 foreign key references tbluser(uid)on delete cascade on update cascade)

insert into tbluser values(2,'pravin')
insert into tblprovider values(103,'eye hospital',2)
insert into tblpolicy values(1003,'policy3',2)
insert into tblplan values(10003,'plan3',2)
insert into tblplan(policyid) values(1001)

--for add column & datatype to the table
alter table tblpolicy
add providerid int

-- add foreign key to the already existing column
alter table tblpolicy
add constraint f15 foreign key(providerid) references tblprovider(providerid)

--for renaming column
sp_rename 'tblprovider.pname','providername'

create proc spGetUserDetails
as
begin
select u.uname,pro.providername,pol.policyname,pl.planname from tbluser u
inner join tblprovider pro on u.uid=pro.uid inner join tblpolicy pol
on u.uid=pol.uid inner join tblplan pl on u.uid=pl.uid
end

alter proc spGetUserDetailsByID(@UserID int)
as
begin
select u.uname,pro.providername,pol.policyname,pl.planname from tbluser u
inner join tblprovider pro on u.uid=pro.uid inner join tblpolicy pol
on u.uid=pol.uid inner join tblplan pl on u.uid=pl.uid where u.uid=@UserID;
end

exec spGetUserDetailsByID 1
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)