Most IMPadd delete update inside gridview using store procedure in ASP.NET
Introduction: Hello friends, in this article i will explain that how we can insert,delete,update the data in the gridview. Here i am using the store procedure to insert,delete and update the record. With the help of the store procedure the execution of the sql query make fast. And less the code of the aspx.cs page. So we can say the use of the store procedure in the sql server is very important and also very helpful.
Implementation: create a new website abb a page named gallery.aspx. Drag and drop the two textboxes, a fileuploader and a button from the toolbox named txt_album_name, txt_caption, fileuploader1 and btn_insert respectively. Here i am giving the complete code for the html page.
Code for .aspx page:
<table class="style1">
<tr>
<td class="style2">
Album Name</td>
<td>
<asp:TextBox ID="txt_album_name" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="txt_album_name" ErrorMessage="Please enter the album name"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="style2">
Caption</td>
<td>
<asp:TextBox ID="txt_caption" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
ControlToValidate="txt_caption" ErrorMessage="Please enter the caption"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="style2">
Image</td>
<td>
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server"
ControlToValidate="FileUpload1" ErrorMessage="Please browse the image"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="style3">
</td>
<td class="style4">
<asp:Button ID="btn_insert" runat="server" onclick="btn_insert_Click"
Text="Insert" />
</td>
</tr>
<tr>
<td class="style2" colspan="2">
<asp:GridView ID="GridView1" runat="server"AutoGenerateColumns="False"
onrowcancelingedit="GridView1_RowCancelingEdit"
onrowdeleting="GridView1_RowDeleting"onrowediting="GridView1_RowEditing"
onrowupdating="GridView1_RowUpdating"
onselectedindexchanging="GridView1_SelectedIndexChanging">
<Columns>
<%--here i am using templatefields to for binding the gridview--%>
<asp:TemplateField HeaderText="Album_name">
<EditItemTemplate>
<%--here i am using the textbox in the edititmtemplatefield to upadate the data--%>
<asp:TextBox ID="txt_album_name" runat="server"
Text='<%# Eval("album_name") %>'></asp:TextBox>
<asp:Label ID="Label4" runat="server" Text='<%# Eval("id") %>'Visible="False"></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("album_name") %>'></asp:Label>
<asp:Label ID="Label2" runat="server" Text='<%# Eval("id") %>'Visible="False"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Caption">
<EditItemTemplate>
<asp:TextBox ID="txt_caption" runat="server" Text='<%# Eval("caption") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Eval("caption")%>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Image">
<ItemTemplate>
<%--for displaying the image inside the gidview here i'm using the <img>tag
and specify the path of the folder where image is stored--%>
<img alt ="" src ='images/<%#Eval("image") %>' height="50px"width="50px"/>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Delete">
<%--here i am using the linkbutton to delete the record and specify the command name
of this linkbutton is delete--%>
<ItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server"CausesValidation="False"
CommandName="Delete"
onclientclick="return confirm('are you sure you want to delet this column')">Delete</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Update">
<EditItemTemplate>
<%-- here i am using the s linkbuttons to update the record and a cancel button
and set the commandname of update button is update and the cancel button is cancel --%>
<asp:LinkButton ID="LinkButton3" runat="server"CausesValidation="False"
CommandName="Update">Update</asp:LinkButton>
<asp:LinkButton ID="LinkButton4" runat="server"CausesValidation="False"
CommandName="Cancel">Cancel</asp:LinkButton>
</EditItemTemplate>
<ItemTemplate>
<%--here i am using the linkbutton for edit and specify the command name
of this linkbutton is Edit--%>
<asp:LinkButton ID="LinkButton2" runat="server"CausesValidation="False"
CommandName="Edit">Edit</asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</td>
</tr>
</table>
Code for aspx.cs page:
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;
// use below two namespaces for this program
using System.Data.SqlClient;
using System.IO;
public partial class Gallery : System.Web.UI.Page
{
// here i declare some variables that will be used below inside the code.
String fn;
String path;
SqlConnection cnn = new SqlConnection();
SqlCommand cmd = new SqlCommand();
SqlDataAdapter adp;
DataTable dt;
Int32 id;// id as a integer variable it will be used to catch the id at the time of
//the deletion and updation inside the gridview.
// below the two string variables will be used to update the record , inside the gridvbiew
String album_name;
String caption;
// the image this string type variaple will be used to delete the image from the folder where
//the image will be stored after the record insertion.
String image;
protected void Page_Load(object sender, EventArgs e)
{
//here i declare the connection of the connectionstring to attach the database with the application
cnn.ConnectionString =ConfigurationManager.ConnectionStrings["cnn"].ConnectionString;
cnn.Open();
// if the connection will be closed the below code poen the connection when the page will be loaded.
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
cnn.Close();
if (IsPostBack == false)
{
// here i am calling the function that will bind the gridview
grd_bind();
}
}
protected void btn_insert_Click(object sender, EventArgs e)
{
// inside the first if condition i am declaring the code for the uploading the image.
if (FileUpload1.PostedFile.ContentLength > 0)
{
fn = Path.GetFileName(FileUpload1.FileName);
path = Server.MapPath("images") + "/" + fn;
FileUpload1.SaveAs(path);
}
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
// here i am using the store procedure named tb_gallery_insert to insert the record inside the database.
SqlCommand cmd = new SqlCommand("tb_gallery_insert", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cnn;
// here i am passing the parameters to insert the record
cmd.Parameters.AddWithValue("@album_name", txt_album_name.Text);
cmd.Parameters.AddWithValue("@caption", txt_caption.Text);
cmd.Parameters.AddWithValue("@image", fn);
cmd.ExecuteNonQuery();
cmd.Dispose();
grd_bind();
cnn.Close();
//here i am calling the function that will be used after the insertion of the record
//insert the record inside the database.
clr();
}
// this function will used after the insertion of the record insert the record inside the database.
private void clr()
{
txt_album_name.Text = "";
txt_caption.Text = "";
}
//this function will used to bind the gridview
private void grd_bind()
{
if (cnn.State == ConnectionState.Closed)
{ cnn.Open(); }
// here i am using the sql query to select the gecord from the database and
adp = new SqlDataAdapter("SELECT * FROM tb_gallery ", cnn);
// here i declare the datatable to fill the record
dt = new DataTable();
// here i am filling the dqldataadapter withe the datatable dt
adp.Fill(dt);
// here i am disposing the apd after filling the record
adp.Dispose();
// here i am binding the ghridview with the datatable dt
GridView1.DataSource = dt;
GridView1.DataBind();
}
// this is a gridview's rowdeleting event that will be used to delete of the row record from the database
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
try
{
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
//inside the id variable i am finding the label from the gridview and with the help of this
//label i will fetch the id of the record that i want to delete
id = Convert.ToInt32(((Label)(GridView1.Rows[e.RowIndex].FindControl("label2"))).Text);
// here i am using the tb_gallery_delete store procedure ti delete the record from the database
SqlCommand cmd = new SqlCommand("tb_gallery_delete", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cnn;
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
// this code will be used to delete the image from the folder too
// here i am using the sql query to select the record that will be selected by the user for deletion
SqlDataAdapter adp = new SqlDataAdapter("select * from tb_gallery where id=@id", cnn);
// here i am passing the parameter that will be used by the above sql query named as id
adp.SelectCommand.Parameters.Add("@id", SqlDbType.Int).Value = id;
DataSet ds = new DataSet();
// here i am filling the sqldataadapter with the dataset dt
adp.Fill(ds);
try
{
// here i am using the try catch exception becoz if the image will be not available
// in side the folder. it does not creates the error.
// inside the image variable i am fetching the image path from the database
image = Convert.ToString(ds.Tables[0].Rows[0]["image"]);
// this line will used to delete the image from the folder
// here i am also giving the filder name where you image was stored.
File.Delete(Server.MapPath("images") + "\\" + image);
}
catch { }
cmd.ExecuteNonQuery();
cmd.Dispose();
grd_bind();
}
catch {
}
}
protected void GridView1_RowCancelingEdit(object sender,GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
grd_bind();
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
grd_bind();
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgse)
{
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
//inside the id variable i am finding the label from the gridview and with the help of this
//label i will fetch the id of the record that i want to update
id = Convert.ToInt32(((Label)(GridView1.Rows[e.RowIndex].FindControl("label4"))).Text);
//inside the album_name variable i am finding the textbox from the gridview and with the help of this
//textbox i will find the album name thatthe user want to update
album_name = (((TextBox)(GridView1.Rows[e.RowIndex].FindControl("txt_album_name"))).Text);
//inside the caption variable i am finding the textbox from the gridview and with the help of this
//textbox i will find caption that the user want to update
caption = (((TextBox)(GridView1.Rows[e.RowIndex].FindControl("txt_caption"))).Text);
// here i am using the tb_gallery_update to update the record from the database inside the gridview
SqlCommand cmd = new SqlCommand("tb_gallery_update", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = cnn;
// here i am passing the three variables that will be used to update the record by the store orocedure
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
cmd.Parameters.Add("@album_name", SqlDbType.VarChar, 50).Value = album_name;
cmd.Parameters.Add("@caption", SqlDbType.VarChar, 50).Value = caption;
cmd.ExecuteNonQuery();
cmd.Dispose();
GridView1.EditIndex = -1;
// here i am also calling the function taht will bind the gridview after fetching the record
//from the database
grd_bind();
}
protected void GridView1_SelectedIndexChanging(object sender,GridViewSelectEventArgs e)
{
GridView1.PageIndex = e.NewSelectedIndex;
grd_bind();
}
}
See output in this image:
Sql Script for database:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_Gallery]')AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tb_Gallery](
[id] [int] IDENTITY(1,1) NOT NULL,
[Album_name] [varchar](50) NULL,
[Caption] [varchar](50) NULL,
[Image] [varchar](50) NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_gallery_insert]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[tb_gallery_insert]
@album_name varchar(50),
@caption varchar(50),
@image varchar(50)
AS
insert into tb_gallery values(@album_name,@caption,@image)
RETURN
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_gallery_delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[tb_gallery_delete]
@id int
AS
delete from tb_gallery where id=@id
RETURN
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_gallery_update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[tb_gallery_update]
@id int,
@album_name varchar(50),
@caption varchar(50)
AS
update tb_gallery set album_name=@album_name, caption=@caption where id=@id
RETURN
'
END
No comments:
Post a Comment