In this blogger we
discuss about functions can have only input parameters allow giving parameters for it, whereas procedures can have I/O parameters. Here functions can be called from procedure for edit and update details by using
different-different parameters, whereas
procedures cannot be called from function. Procedure allows SELECT as well as DML (INSERT/UPDATE/DELETE).
1-First of all
creating table emp_details by using script
USE [mydb]
GO
/****** Object:
Table [dbo].[emp_details] Script Date: 10/06/2016 11:06:57
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER
ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE
[dbo].[emp_details](
[emp_id] [int] IDENTITY(1,1) NOT NULL,
[emp_name] [varchar](50) NULL,
[emp_address] [varchar](50) NULL,
[emp_salary] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
2-Creating
procedure emp_details by using script
GO
/****** Object:
StoredProcedure [dbo].[procemp_details] Script Date:
10/06/2016 10:20:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER
ON
GO
ALTER PROCEDURE
[dbo].[procemp_details]
@Action VARCHAR(10)
,@emp_id INT = NULL
,@emp_name VARCHAR(50) = NULL
,@emp_address VARCHAR(50) = NULL
,@emp_salary INT=NULL
AS
BEGIN
SET NOCOUNT ON;
--SELECT
IF
@Action = 'SELECT'
BEGIN
SELECT emp_id, emp_name,emp_address, emp_salary
FROM emp_deatails
END
--INSERT
IF
@Action = 'INSERT'
BEGIN
INSERT INTO emp_deatails(emp_name,emp_address,emp_salary)
VALUES (@emp_name,@emp_address,@emp_salary)
END
--UPDATE
IF
@Action = 'UPDATE'
BEGIN
UPDATE emp_deatails
SET emp_name =@emp_name, emp_address =@emp_address,emp_salary=@emp_salary
WHERE emp_id =@emp_id
END
--DELETE
IF
@Action = 'DELETE'
BEGIN
DELETE FROM emp_deatails
WHERE emp_id =@emp_id
END
END
3-Create design page
of grid view
Default2.aspx
<%@ Page
Language="C#" AutoEventWireup="true"
CodeFile="Default2.aspx.cs" Inherits="Default2" %>
<!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></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="grd_details" runat="server"
AutoGenerateColumns="false" DataKeyNames="emp_id"
OnRowDataBound="OnRowDataBound" OnRowEditing="OnRowEditing"
OnRowCancelingEdit="OnRowCancelingEdit"
OnRowUpdating="OnRowUpdating" OnRowDeleting="OnRowDeleting"
EmptyDataText="No records has been added.">
<Columns>
<asp:TemplateField HeaderText="Name"
ItemStyle-Width="150">
<ItemTemplate>
<asp:Label ID="lblname" runat="server"
Text='<%#Eval("emp_name") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtname" runat="server"
Text='<%#Eval("emp_name") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Address"
ItemStyle-Width="150">
<ItemTemplate>
<asp:Label ID="lbladdress" runat="server"
Text='<%#Eval("emp_address") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtaddress" runat="server"
Text='<%#Eval("emp_address") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Salary"
ItemStyle-Width="150">
<ItemTemplate>
<asp:Label ID="lblsalary" runat="server"
Text='<%#Eval("emp_salary") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtsalary" runat="server"
Text='<%#Eval("emp_salary") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:CommandField ButtonType="Button"
ShowEditButton="true" ShowDeleteButton="true"
ItemStyle-Width="150" />
</Columns>
</asp:GridView>
<table border="1" cellpadding="0"
cellspacing="0" style="border-collapse: collapse">
<tr>
<td style="width: 150px">
Name:<br />
<asp:TextBox ID="txtName" runat="server"
Width="140" />
</td>
<td style="width: 150px">
Country:<br />
<asp:TextBox ID="txtCountry" runat="server"
Width="140" />
</td>
<td style="width: 150px">
Salary:<br />
<asp:TextBox ID="txtsalary" runat="server"
Width="140" />
</td>
<td style="width: 100px">
<asp:Button ID="btnAdd" runat="server"
Text="Add" OnClick="Insert" />
</td>
<td><asp:Label ID="lbl"
runat="server"></asp:Label></td>
</tr>
</table>
</div>
</form>
</body>
</html>
Default2.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;
using
System.Configuration;
using
System.Data.SqlClient;
public partial class
Default2 : System.Web.UI.Page
{
String s = ConfigurationManager.ConnectionStrings["conn"].ToString();
SqlConnection con;
SqlCommand cmd;
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.grd_details1();
}
}
private void grd_details1()
{
con = new SqlConnection(s);
cmd = new SqlCommand("procemp_details");
cmd.Parameters.AddWithValue("@Action", "SELECT");
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter sda = new SqlDataAdapter(cmd);
cmd.Connection = con;
sda.SelectCommand = cmd;
DataTable dt = new DataTable();
sda.Fill(dt);
grd_details.DataSource = dt;
grd_details.DataBind();
}
protected void Insert(object sender, EventArgs e)
{
string name = txtName.Text;
string country = txtCountry.Text;
string salary=txtsalary.Text;
con = new SqlConnection(s);
cmd = new SqlCommand("procemp_details");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Action", "INSERT");
cmd.Parameters.AddWithValue("@emp_name", name);
cmd.Parameters.AddWithValue("@emp_address", country);
cmd.Parameters.AddWithValue("@emp_salary", salary);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
lbl.Visible = true;
lbl.Text = "Records are Submitted Successfully";
this.grd_details1();
}
protected void OnRowEditing(object sender, GridViewEditEventArgs e)
{
grd_details.EditIndex = e.NewEditIndex;
this.grd_details1();
}
protected void OnRowUpdating(object sender, GridViewUpdateEventArgs e)
{
GridViewRow row = grd_details.Rows[e.RowIndex];
int emp_id = Convert.ToInt32(grd_details.DataKeys[e.RowIndex].Values[0]);
string emp_name = (row.FindControl("txtName") as TextBox).Text;
string emp_address = (row.FindControl("txtaddress") as TextBox).Text;
string emp_salary = (row.FindControl("txtsalary") as TextBox).Text;
con = new SqlConnection(s);
SqlCommand cmd = new SqlCommand("procemp_details");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Action", "UPDATE");
cmd.Parameters.AddWithValue("@emp_id", emp_id);
cmd.Parameters.AddWithValue("@emp_name", emp_name);
cmd.Parameters.AddWithValue("@emp_address", emp_address);
cmd.Parameters.AddWithValue("@emp_salary",emp_salary);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
lbl.Visible = true;
lbl.Text = "Records are updated Successfully";
con.Close();
grd_details.EditIndex = -1;
this.grd_details1();
}
protected void OnRowCancelingEdit(object sender, EventArgs e)
{
grd_details.EditIndex = -1;
this.grd_details1();
}
protected void OnRowDeleting(object sender, GridViewDeleteEventArgs e)
{
int emp_id = Convert.ToInt32(grd_details.DataKeys[e.RowIndex].Values[0]);
con = new SqlConnection(s);
SqlCommand cmd = new SqlCommand("procemp_details");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Action", "DELETE");
cmd.Parameters.AddWithValue("@emp_id", emp_id);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
lbl.Visible = true;
lbl.Text = "Records are Deleted Successfully";
con.Close();
this.grd_details1();
}
protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
string item = e.Row.Cells[0].Text;
foreach (Button button in e.Row.Cells[3].Controls.OfType<Button>())
{
if (button.CommandName == "Delete")
{
button.Attributes["onclick"] = "if(!confirm('Do you want to
delete " + item + "?')){ return false; };";
}
}
}
}
}
In the view of above articles helps all of you.Let me know if any query..........
