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;
namespace storprocedure00
{
public partial class storedproc : System.Web.UI.Page
{
SqlConnection con = new SqlConnection("data source=DESKTOP-AD5CCNP\\
SQLEXPRESS; initial catalog=aktest66; integrated security=true");
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack )
{
Show();
Showcountry();
}

public void Show()

{

con.Open();
SqlCommand cmd = new SqlCommand("tbl_join", con);

cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);

con.Close();
grdvu.DataSource = dt;
grdvu.DataBind();
}
public void Showcountry()
{

try
{
con.Open();
SqlCommand cmd = new SqlCommand("usp_tbl_country", con);

cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
con.Close();

ddlcountry.DataValueField = "country_id";
ddlcountry.DataTextField = "country_name";
ddlcountry.DataSource = dt;
ddlcountry.DataBind();
ddlcountry.Items.Insert(0, new ListItem("--select--", "0"));
 }
catch (Exception ex)
{

throw ex;
}

}
protected void btnsubmit_Click(object sender, EventArgs e)
{
string pp = ""; // loop ki vlues ko store krne ke liye ek string
variavle le rhe hain

for (int i = 0; i < 6; i++)

{
if (cblhobbies.Items[i].Selected == true)
{
pp += cblhobbies.Items[i].Text + ",";
}

}
pp = pp.TrimEnd(',');
if (btnsubmit.Text == "Submit")
{
con.Open();
SqlCommand cmd = new SqlCommand("stuin", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@name", textname.Text);
cmd.Parameters.AddWithValue("@gender", rblgender.SelectedValue);
cmd.Parameters.AddWithValue("@country", ddlcountry.SelectedValue);
cmd.Parameters.AddWithValue("@hobbies", pp );
cmd.ExecuteNonQuery();
con.Close();
Show();
textname.Text = "";
rblgender.ClearSelection();
ddlcountry.SelectedValue = "0";
cblhobbies.ClearSelection();
}
else if (btnsubmit .Text =="Update")

{
con.Open();
SqlCommand cmd = new SqlCommand("stuupdate", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@name", textname.Text);
cmd.Parameters.AddWithValue("@gender", rblgender.SelectedValue);
cmd.Parameters.AddWithValue("@country", ddlcountry.SelectedValue);
cmd.Parameters.AddWithValue("@hobbies", pp );
cmd.Parameters.AddWithValue("@argu_id", ViewState["abc"]);
cmd.ExecuteNonQuery();
con.Close();
Show();
textname.Text = "";
rblgender.ClearSelection();
ddlcountry.SelectedValue = "0";
cblhobbies.ClearSelection();
 }

protected void grdvu_RowCommand(object sender, GridViewCommandEventArgs e)

{
if(e.CommandName =="D")
{

con.Open();
SqlCommand cmd = new SqlCommand("studelete", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@argu_id", e.CommandArgument);

cmd.ExecuteNonQuery();
con.Close();
Show();

}
else if (e.CommandName =="E")
{

con.Open();
SqlCommand cmd = new SqlCommand("stuedit", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@argu_id", e.CommandArgument);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);

con.Close();
textname.Text = dt.Rows[0]["Name"].ToString();
rblgender.SelectedValue = dt.Rows[0]["Gender"].ToString();
ddlcountry.SelectedValue = dt.Rows[0]["Country"].ToString();
cblhobbies.SelectedValue = dt.Rows[0]["Hobbies"].ToString();
ViewState["abc"] = e.CommandArgument;
btnsubmit.Text = "Update";
}
}
}
}

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
%%
create database aktest66
use aktest66
create table tbl_employee
(id int primary key identity(1,1),
Name varchar(90),
Gender int,
Country int,
Hobbies varchar(90))

select*from tbl_employee join tblcountry on Country=country_id

select*from tblcountry

create table tblcountry

(country_id int primary key identity(1,1),
country_name varchar(80))

insert into tblcountry (country_name)values('delhi'),('ahk'),('dhdh')

insert into tblcountry (country_name)values('usa')

insert into tblcountry (country_name)values('uk')

insert into tblcountry (country_name)values('japan')

insert into tblcountry (country_name)values('nepal')

insert into tblcountry (country_name)values('germany')

insert into tblcountry (country_name)values('denmark')

create proc usp_tbl_country

as
begin

select*from tblcountry
end

create proc tbl_join

as
begin
select*from tbl_employee join tblcountry on Country=country_id
end

create proc stuin

@name varchar(90),
@gender int,
@country int,
@hobbies varchar(90)
as
begin
insert into tbl_employee
(Name,Gender ,Country ,Hobbies )values(@name,@gender,@country,@hobbies)
end
create proc stuupdate

@name varchar(90),
@gender int ,
@country int,
@hobbies varchar(90),
@argu_id int
as
begin
update tbl_employee set Name =@name , Gender =@gender ,Country=@country,
Hobbies=@hobbies where id=@argu_id
end

create proc studelete

@argu_id int
as
begin
delete from tbl_employee where id = @argu_id
end

create proc stuedit

@argu_id int
as
begin
select*from tbl_employee where id =@argu_id
end

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@@@@@@@@@@@@@@@@@@@

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="storedproc.aspx.cs"

Inherits="storprocedure00.storedproc" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table border ="1" style ="background-color :darksalmon">
<tr>
<td colspan ="2"><h1>Student Form</h1></td>
</tr>
<tr>
 <td>Name:</td>
<td><asp:TextBox ID ="textname" runat
="server"></asp:TextBox></td>
</tr>
<tr>
<td>Gender:</td>
<td><asp:RadioButtonList ID="rblgender" runat ="server"
RepeatColumns="3">
<asp:ListItem Text ="male" Value ="1"></asp:ListItem>
<asp:ListItem Text ="female" Value ="2"></asp:ListItem>
<asp:ListItem Text ="other" Value ="3"></asp:ListItem>
</asp:RadioButtonList></td>
</tr>
<tr>
<td>Country:</td>
<td><asp:DropDownList ID="ddlcountry" runat ="server"
></asp:DropDownList></td>
</tr>

<tr>
<td>Hobbies:</td>
<td><asp:CheckBoxList ID ="cblhobbies" runat ="server" RepeatColumns ="6">
<asp:ListItem Text ="reading" Value="1"></asp:ListItem>
<asp:ListItem Text ="cooking" Value ="2"></asp:ListItem>
<asp:ListItem Text ="dancing" Value="3"></asp:ListItem>
<asp:ListItem Text ="singing" Value="4"></asp:ListItem>
<asp:ListItem Text ="acting" Value ="5"></asp:ListItem>
<asp:ListItem Text ="reading" Value
="6"></asp:ListItem></asp:CheckBoxList></td>
</tr>
<tr>
<td></td>
<td><asp:Button ID="btnsubmit" runat ="server" Text ="Submit"
OnClick ="btnsubmit_Click" /></td>
</tr>
<tr>
<td></td>
<td><asp:GridView ID ="grdvu" runat ="server" Text="Show"
AutoGenerateColumns ="false" OnRowCommand="grdvu_RowCommand" >
<Columns>
<asp:TemplateField HeaderText ="Student id" >
<ItemTemplate >
<%#Eval("id") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText ="Student Name" >
<ItemTemplate >
<%#Eval("Name") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText ="Student Gender" >
<ItemTemplate >
<%#Eval("Gender").ToString() =="1" ?
"male":Eval ("Gender").ToString ()=="2"?"female" :"other" %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText ="Student Country" >
<ItemTemplate >
<%#Eval("country_name") %>
 </ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText ="Student Hobbies" >
<ItemTemplate >
<%#Eval("Hobbies") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField >
<ItemTemplate >
<asp:LinkButton ID="btndelete" runat ="server"
Text ="Delete" CommandArgument =' <%#Eval("id") %>' CommandName
="D"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField >
<ItemTemplate >
<asp:LinkButton ID="btnedit" runat ="server" Text
="Edit" CommandArgument =' <%#Eval("id") %>' CommandName ="E"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>

</Columns>
</asp:GridView></td>
</tr>
</table>
</div>
</form>
</body>
</html>