JP-Consultants: 2013-06-09

Database:

tbl_Gallery

tb_gallery_insert

CREATE PROCEDURE [dbo].[tb_gallery_insert]

@album_name varchar(50),

@caption varchar(50),

@image varchar(50)

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)

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

delete from tb_gallery where id=@id

RETURN

.Aspx

<tr>

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>

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>

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>

<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>

<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">

<%--here i am using templatefields to for binding the gridview--%>

<asp:TemplateField HeaderText="Album_name">

<%--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>

<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">

<asp:TextBox ID="txt_caption" runat="server" Text='<%# Eval("caption") %>'></asp:TextBox>

</EditItemTemplate>

<asp:Label ID="Label3" runat="server" Text='<%# Eval("caption") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Image">

<%--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--%>

<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">

<%-- 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>

<%--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>

.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

JP-Consultants

Thursday, 13 June 2013

Insert,Update,Delete with in GridView in WebForm

SharePoint tenant opt-out for modern lists is retiring in 2019

Search This Blog