StoredProcedure with GridView

Question

Hi
I am working in sqlserver and i want to use storedprocedure with gridview please define how can we edit update delete data in gridview using storedprocedure?

Why are we use CommandType? Can i call storedprocedure without use CommandType?

Satyapriya Nayak · Answer

Hi David, Try this... Normal 0 Stored Procedure CREATE PROCEDURE [dbo].[employee_pro] ( @empid as int =0, @name as varchar (50)= '' , @address as varchar (50)= '' , @designation as varchar (50)= '' , @status as varchar (50)= '' ) AS BEGIN SET NOCOUNT ON ; if (@status = 'Display' ) begin Select * from employee end else if (@status = 'Add' ) begin Insert into employee( name ,address,designation,createtime) values (@name,@address,@designation, getdate ()) end else if (@status = 'Update' ) begin Update employee set name =@name, address=@address,designation=@designation where empid=@empid end else if (@status = 'Delete' ) begin Delete from employee where empid=@empid end END Now lets move to the coding part StoredProcedure.aspx @ Page Language ="C#" AutoEventWireup ="true" CodeBehind ="StoredProcedure.aspx.cs" Inherits ="Insert_update_delete_Stored_Pro.StoredProcedure" %> 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 > Untitled Page title > head > body > form id ="form1" runat ="server"> div > asp : GridView ID ="GridView1" runat ="server" AllowPaging ="true" ShowFooter ="true" PageSize ="5" AutoGenerateColumns ="false" onpageindexchanging ="GridView1_PageIndexChanging" onrowcancelingedit ="GridView1_RowCancelingEdit" onrowcommand ="GridView1_RowCommand" onrowdeleting ="GridView1_RowDeleting" onrowediting ="GridView1_RowEditing" onrowupdating ="GridView1_RowUpdating" HeaderStyle-BackColor ="Red" HeaderStyle-ForeColor ="White" BackColor ="#FFCC66"> AlternatingRowStyle BackColor ="#FFFFCC" /> Columns > asp : TemplateField HeaderText ="Empid"> ItemTemplate > asp : Label ID ="lblempid" runat ="server" Text =' #Eval("empid") %> '> asp : Label > ItemTemplate > FooterTemplate > asp : Label ID ="lblAdd" runat ="server"> asp : Label > FooterTemplate > asp : TemplateField > asp : TemplateField HeaderText ="Name"> ItemTemplate > asp : Label ID ="lblname" runat ="server" Text =' #Eval("name") %> '> asp : Label > ItemTemplate > EditItemTemplate > asp : TextBox ID ="txtname" runat ="server" Text =' #Eval("name") %> '> asp : TextBox > EditItemTemplate > FooterTemplate > asp : TextBox ID ="txtAddname" runat ="server"> asp : TextBox > FooterTemplate > asp : TemplateField > asp : TemplateField HeaderText ="Address"> ItemTemplate > asp : Label ID ="lbladdress" runat ="server" Text =' #Eval("address") %> '> asp : Label > ItemTemplate > EditItemTemplate > asp : TextBox ID ="txtaddress" runat ="server" Text =' #Eval("address") %> '> asp : TextBox > EditItemTemplate > FooterTemplate > asp : TextBox ID ="txtAddaddress" runat ="server"> asp : TextBox > FooterTemplate > asp : TemplateField > asp : TemplateField HeaderText ="Designation"> ItemTemplate > asp : Label ID ="lblesignation" runat ="server" Text =' #Eval("designation") %> '> asp : Label > ItemTemplate > EditItemTemplate > asp : TextBox ID ="txtdesignation" runat ="server" Text =' #Eval("designation") %> '> asp : TextBox > EditItemTemplate > FooterTemplate > asp : TextBox ID ="txtAdddesignation" runat ="server"> asp : TextBox > FooterTemplate > asp : TemplateField > asp : TemplateField HeaderText ="Create Time"> ItemTemplate > asp : Label ID ="lblcreatetime" runat ="server" Text =' #Eval("createtime") %> '> asp : Label > ItemTemplate > asp : TemplateField > asp : TemplateField HeaderText ="Edit"> ItemTemplate > asp : LinkButton ID ="btnEdit" Text ="Edit" runat ="server" CommandName ="Edit" /> br /> asp : LinkButton ID ="btnDelete" Text ="Delete" runat ="server" CommandName ="Delete"/> ItemTemplate > EditItemTemplate > asp : LinkButton ID ="btnUpdate" Text ="Update" runat ="server" CommandName ="Update" /> br /> asp : LinkButton ID ="btnCancel" Text ="Cancel" runat ="server" CommandName ="Cancel" /> EditItemTemplate > FooterTemplate > asp : Button ID ="btnAddRecord" runat ="server" Text ="Add" CommandName ="Add"> asp : Button > FooterTemplate > asp : TemplateField > Columns > asp : GridView > div > form > body > html > StoredProcedure.aspx.cs using System; using System.Collections; 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; namespace Insert_update_delete_Stored_Pro { public partial class StoredProcedure : System.Web.UI. Page { string strConnString = ConfigurationManager .ConnectionStrings[ "ConnectionString" ].ConnectionString; SqlCommand com; SqlDataAdapter sqlda; DataSet ds; protected void Page_Load( object sender, EventArgs e) { if (!IsPostBack) { BindGrid(); } } protected void BindGrid() { SqlConnection con = new SqlConnection (strConnString); com = new SqlCommand (); con.Open(); com.Connection = con; com.CommandText = "employee_pro" ; com.CommandType = CommandType .StoredProcedure; com.Parameters.Add( new SqlParameter ( "@status" , SqlDbType .VarChar, 50)); com.Parameters[ "@status" ].Value = "Display" ; sqlda = new SqlDataAdapter (com); ds = new DataSet (); sqlda.Fill(ds); GridView1.DataSource = ds; GridView1.DataBind(); con.Close(); } protected void GridView1_PageIndexChanging( object sender, GridViewPageEventArgs e) { GridView1.PageIndex = e.NewPageIndex; BindGrid(); } protected void GridView1_RowCancelingEdit( object sender, GridViewCancelEditEventArgs e) { GridView1.EditIndex = -1; BindGrid(); } protected void GridView1_RowCommand( object sender, GridViewCommandEventArgs e) { if (e.CommandName.Equals( "Add" )) { TextBox txtname = ( TextBox )GridView1.FooterRow.FindControl( "txtAddname" ); TextBox txtaddress = ( TextBox )GridView1.FooterRow.FindControl( "txtAddaddress" ); TextBox txtdesignation = ( TextBox )GridView1.FooterRow.FindControl( "txtAdddesignation" ); string name, address, designation; name = txtname.Text; address = txtaddress.Text; designation = txtdesignation.Text; Addemployee(name, address, designation); GridView1.EditIndex = -1; BindGrid(); } } protected void Addemployee( string name, string address, string designation) { SqlConnection con = new SqlConnection (strConnString); con.Open(); com = new SqlCommand (); com.CommandText = "employee_pro" ; com.CommandType = CommandType .StoredProcedure; com.Connection = con; com.Parameters.Add( new SqlParameter ( "@status" , SqlDbType .VarChar, 50)); com.Parameters.Add( new SqlParameter ( "@name" , SqlDbType .VarChar, 50)); com.Parameters.Add( new SqlParameter ( "@address" , SqlDbType .VarChar, 50)); com.Parameters.Add( new SqlParameter ( "@designation" , SqlDbType .VarChar, 50)); com.Parameters[ "@status" ].Value = "Add" ; com.Parameters[ "@name" ].Value = name; com.Parameters[ "@address" ].Value = address; com.Parameters[ "@designation" ].Value = designation; sqlda = new SqlDataAdapter (com); ds = new DataSet (); sqlda.Fill(ds); con.Close(); } protected void GridView1_RowDeleting( object sender, GridViewDeleteEventArgs e) { Label empid = ( Label )GridView1.Rows[e.RowIndex].FindControl( "lblempid" ); string eid = empid.Text; Deleteemployee(eid); GridView1.EditIndex = -1; BindGrid(); } protected void Deleteemployee( string empid) { SqlConnection con = new SqlConnection (strConnString); com = new SqlCommand (); com.CommandText = "employee_pro" ; com.CommandType = CommandType .StoredProcedure; com.Connection = con; com.Parameters.Add( new SqlParameter ( "@status" , SqlDbType .VarChar, 50)); com.Parameters.Add( new SqlParameter ( "@empid" , SqlDbType .Int)); com.Parameters[ "@status" ].Value = "Delete" ; com.Parameters[ "@empid" ].Value = empid; sqlda = new SqlDataAdapter (com); ds = new DataSet (); sqlda.Fill(ds); con.Close(); } protected void GridView1_RowEditing( object sender, GridViewEditEventArgs e) { GridView1.EditIndex = e.NewEditIndex; BindGrid(); } protected void GridView1_RowUpdating( object sender, GridViewUpdateEventArgs e) { Label empid = ( Label )GridView1.Rows[e.RowIndex].FindControl( "lblempid" ); TextBox name = ( TextBox )GridView1.Rows[e.RowIndex].FindControl( "txtname" ); TextBox address = ( TextBox )GridView1.Rows[e.RowIndex].FindControl( "txtaddress" ); TextBox designation = ( TextBox )GridView1.Rows[e.RowIndex].FindControl( "txtdesignation" ); string eid = empid.Text; string ename = name.Text; string eaddress = address.Text; string edesignation = designation.Text; Updateemployee(eid, ename, eaddress, edesignation); GridView1.EditIndex = -1; BindGrid(); } protected void Updateemployee( string empid, string name, string address, string designation) { SqlConnection con = new SqlConnection (strConnString); com = new SqlCommand (); con.Open(); com.Connection = con; com.CommandText = "employee_pro" ; com.CommandType = CommandType .StoredProcedure; com.Parameters.Add( new SqlParameter ( "@empid" , SqlDbType .Int)); com.Parameters.Add( new SqlParameter ( "@status" , SqlDbType .VarChar, 50)); com.Parameters.Add( new SqlParameter ( "@name" , SqlDbType .VarChar, 50)); com.Parameters.Add( new SqlParameter ( "@address" , SqlDbType .VarChar, 50)); com.Parameters.Add( new SqlParameter ( "@designation" , SqlDbType .VarChar, 50)); com.Parameters[ "@empid" ].Value = Convert .ToInt32(empid.ToString()); com.Parameters[ "@status" ].Value = "Update" ; com.Parameters[ "@name" ].Value = name; com.Parameters[ "@address" ].Value = address; com.Parameters[ "@designation" ].Value = designation; sqlda = new SqlDataAdapter (com); ds = new DataSet (); sqlda.Fill(ds); con.Close(); } } } Thanks If this post helps you mark it as answer

Juan Van · Answer

ComandType defines what type of input to expect for the DBServer Server. Here is a demo on U/D/E in grid View. http://www.dotnetfunda.com/articles/article29.aspx Then run the StoreProcedure to update the called database.

StoredProcedure with GridView

Answers (2)