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 addressdesignationcreatetime) values (@name@address@designation getdate ()) end else if (@status = 'Update' ) begin Update employee set name =@name address=@addressdesignation=@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.