Database:
tbl_Gallery
tb_gallery_insert
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
tb_gallery_update
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
tb_gallery_delete
CREATE PROCEDURE [dbo].[tb_gallery_delete]
@id int
AS
delete from tb_gallery where
id=@id
RETURN
.Aspx
<table class="style1"
align="center">
<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" />
<asp:Button ID="btnfind"
runat="server"
onclick="Button1_Click"
Text="Find"
/>
<asp:Label ID="lblMsg" runat="server"
Text="lblmes"></asp:Label>
</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" CellPadding="4"
ForeColor="#333333" GridLines="None">
<AlternatingRowStyle BackColor="White"
/>
<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 edit Itmtemplatefield 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>
<EditRowStyle BackColor="#7C6F57"
/>
<FooterStyle BackColor="#1C5E55"
Font-Bold="True"
ForeColor="White"
/>
<HeaderStyle BackColor="#1C5E55"
Font-Bold="True"
ForeColor="White"
/>
<PagerStyle BackColor="#666666"
ForeColor="White"
HorizontalAlign="Center"
/>
<RowStyle BackColor="#E3EAEB"
/>
<SelectedRowStyle BackColor="#C5BBAF"
Font-Bold="True"
ForeColor="#333333"
/>
<SortedAscendingCellStyle BackColor="#F8FAFA"
/>
<SortedAscendingHeaderStyle BackColor="#246B61"
/>
<SortedDescendingCellStyle BackColor="#D4DFE1"
/>
<SortedDescendingHeaderStyle BackColor="#15524A"
/>
</asp:GridView>
</td>
</tr>
</table>
.CS file
using System;
using
System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using
System.Web.UI.WebControls;
using
System.Web.UI.WebControls.WebParts;
using System.Data;
using
System.Data.SqlClient;
using
System.Configuration;
using System.Xml.Linq;
namespace Asp.net_Sample
{
public partial class CRUD : System.Web.UI.Page
{
// here i declare some
variables that will be used below inside the code.
String fn;
String path;
String File;
//String GetFileName;
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 gridview
String album_name;
String caption;
// the image this string
type variable 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 connection string to attach the database with the application
cnn.ConnectionString = ConfigurationManager.ConnectionStrings["jayaConnectionString"].ConnectionString;
cnn.Open();
// if the connection will
be closed the below code 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 = 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);
int
i=cmd.ExecuteNonQuery();
if (i > 0)
{
lblMsg.Text = "successfully
Inserted";
}
else
{
lblMsg.Text = "Not
Inserted";
}
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();
}
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 record 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 sqldata Adapter withe the
datatable dt
adp.Fill(dt);
// here i am disposing the
apd after filling the record
adp.Dispose();
// here i am binding the gridview
with the datatable dt
GridView1.DataSource = dt;
GridView1.DataBind();
}
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, GridViewUpdateEventArgs e)
{
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 that the 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 stored Procedure
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_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 folder name where
you image was stored.
File=Server.MapPath("Images")
+ "\\" + image;
}
catch { }
cmd.ExecuteNonQuery();
cmd.Dispose();
grd_bind();
}
catch
{
}}
protected void GridView1_SelectedIndexChanging(object sender, GridViewSelectEventArgs
e)
{
GridView1.PageIndex =
e.NewSelectedIndex;
grd_bind();
}}}
Output
