KPBDOTNET: Gridview Operations - Display, Edit, Update

Stored Procedure to perform Update operation

CREATE PROCEDURE SP_Update_User

-- Add the parameters for the stored procedure here

@User_ID int,@Fname varchar(200),@Lname varchar(200),@Email varchar(500),@ActiveDate datetime,@gender varchar(50),@city varchar(200)

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

update [dbo].[User]

set First_Name=@Fname, Last_Name=@Lname, Email=@Email, Active_Date=@ActiveDate,Gender=@gender,City=@city

where User_ID=@User_ID

END

Gridview design for update operations

<asp:GridView ID="gridData" runat="server" AutoGenerateColumns="False" Width="100%" CellPadding="4"

ForeColor="#333333" GridLines="None" AutoGenerateEditButton="True"

OnRowCancelingEdit="gridData_RowCancelingEdit"

OnRowEditing="gridData_RowEditing"

OnRowUpdating="gridData_RowUpdating"

OnRowDataBound="gridData_RowDataBound" >

<asp:TemplateField >

<asp:Label ID="lblUser_ID" runat="server" Text="User ID"

></asp:Label>

</HeaderTemplate>

<asp:Label ID="lblDataUser_ID" runat="server"

Text='<%#Eval("User_ID") %>' ></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField >

<asp:Label ID="lblFirst_Name" runat="server" Text="First Name"

></asp:Label>

</HeaderTemplate>

<asp:Label ID="lblDataFirst_Name" runat="server"

Text='<%#Eval("First_Name") %>' ></asp:Label>

</ItemTemplate>

<asp:TextBox ID="txtDataFirst_Name" runat="server"

Text='<%#Eval("First_Name") %>'></asp:TextBox>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField >

<asp:Label ID="lblLast_Name" runat="server" Text="Last Name"

></asp:Label>

</HeaderTemplate>

<asp:Label ID="lblDataLast_Name" runat="server"

Text='<%#Eval("Last_Name") %>' ></asp:Label>

</ItemTemplate>

<asp:TextBox ID="txtDataLast_Name" runat="server"

Text='<%#Eval("Last_Name") %>'></asp:TextBox>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField >

<asp:Label ID="lblEmail" runat="server" Text="Email"

></asp:Label>

</HeaderTemplate>

<asp:Label ID="lblDataEmail" runat="server" Text='<%#Eval("Email")

%>' ></asp:Label>

</ItemTemplate>

<asp:TextBox ID="txtDataEmail" runat="server"

Text='<%#Eval("Email") %>'></asp:TextBox>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField >

<asp:Label ID="lblActive_Date" runat="server" Text="Active Date"

></asp:Label>

</HeaderTemplate>

<asp:Label ID="lblDataActive_Date" runat="server" Text=

'<%#Eval("Active_Date") %>' ></asp:Label>

</ItemTemplate>

<asp:TextBox ID="txtDataActive_Date" runat="server"

Text='<%#Eval("Active_Date") %>'></asp:TextBox>

<asp:CalendarExtender ID="CalendarExtender1" runat="server"

TargetControlID="txtDataActive_Date"

SelectedDate='<%#Eval("Active_Date") %>'>

</asp:CalendarExtender>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField >

<asp:Label ID="lblGender" runat="server" Text="Gender"

></asp:Label>

</HeaderTemplate>

<asp:Label ID="lblDataGender" runat="server"

Text='<%#Eval("Gender") %>' ></asp:Label>

</ItemTemplate>

<asp:DropDownList ID="ddlGender" runat="server" >

</asp:DropDownList>

</EditItemTemplate>

</asp:TemplateField>

<asp:TemplateField >

<asp:Label ID="lblCity" runat="server" Text="City" ></asp:Label>

</HeaderTemplate>

<asp:Label ID="lblDataCity" runat="server" Text='<%#Eval("City")

%>' ></asp:Label>

</ItemTemplate>

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

</EditItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

Code Behind

1. Show data on button click event

protected void btnGetData_Click(object sender, EventArgs e)

{

GetDt();

}

We have to bind gridview several times, so create a method for binding data in gridview i.e. GetDt()

private void GetDt()

{

clsConnect connect = new clsConnect();

DataSet ds = connect.getUserData();

gridData.DataSource = ds.Tables[0];

gridData.DataBind();

}

Details of Class Method getUserData()

public DataSet getUserData()

{

ds = new DataSet();

try

{

SqlConnection con=new SqlConnection(connectionString);

con.Open();

SqlDataAdapter da = new SqlDataAdapter("select * from [dbo].[User]", con);

da.Fill(ds);

con.Close();

}

catch (Exception ex)

{

//throw;

}

return ds;

}

For Connection details visit earlier posts

2. Gridview Editing event

We have maintain earlier Gender field value to be show in update method as previous value.

Hence Session["genderVal"] in code below

protected void gridData_RowEditing(object sender, GridViewEditEventArgs e)

{

gridData.EditIndex = e.NewEditIndex;

//gender

Label thisGender = (Label)gridData.Rows[e.NewEditIndex].FindControl("lblDataGender");

string genderVal = thisGender.Text;

Session["genderVal"] = genderVal;

GetDt();

}

3. RowDataBound event: This event is used to bind fields for controls available in update mode of gridview.

protected void gridData_RowDataBound(object sender, GridViewRowEventArgs e)

{

if (gridData.EditIndex == e.Row.RowIndex && e.Row.RowType ==

DataControlRowType.DataRow)

{

try

{

DropDownList ddlGender = (DropDownList)e.Row.FindControl("ddlGender");

if (ddlGender != null)

{

ddlGender.Items.Add("M");

ddlGender.Items.Add("F");

}

//set selected field

if (Session["genderVal"] != null)

{

for (int i = 0; i < ddlGender.Items.Count; i++)

{

if (ddlGender.Items[i].Text.Trim().Equals(Session["genderVal"].ToString()))

{

ddlGender.Items[i].Selected = true;

break;

}

catch (Exception ex)

{

//throw;

}

4. Update Event

protected void gridData_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

if (e.RowIndex!=-1 && gridData.Rows[e.RowIndex].RowType==DataControlRowType.DataRow )

{

try

{

//1. get PKey field value

Label lblDataUser_ID = (Label)gridData.Rows[e.RowIndex].FindControl("lblDataUser_ID");

int User_ID = Convert.ToInt32(lblDataUser_ID.Text);

//2. get new values for First_Name Last_Name Email Active_Date Gender City

TextBox txtDataFirst_Name = (TextBox)gridData.Rows[e.RowIndex].FindControl("txtDataFirst_Name");

TextBox txtDataLast_Name = (TextBox)gridData.Rows[e.RowIndex].FindControl("txtDataLast_Name");

TextBox txtDataEmail = (TextBox)gridData.Rows[e.RowIndex].FindControl("txtDataEmail");

TextBox txtDataActive_Date = (TextBox)gridData.Rows[e.RowIndex].FindControl("txtDataActive_Date");

DropDownList ddlGender = (DropDownList)gridData.Rows[e.RowIndex].FindControl("ddlGender");

TextBox txtDataCity = (TextBox)gridData.Rows[e.RowIndex].FindControl("txtDataCity");

//update Method

clsConnect cc = new clsConnect();

cc.UpdateData(User_ID, txtDataFirst_Name.Text, txtDataLast_Name.Text, txtDataEmail.Text,

Convert.ToDateTime(txtDataActive_Date.Text), ddlGender.SelectedItem.Text, txtDataCity.Text);

}

catch (Exception)

{

//throw;

}

gridData.EditIndex = -1;

GetDt();

}

5. Update Method

public void UpdateData(int User_ID,string First_Name,string Last_Name,string Email,DateTime Active_Date,string Gender,string City)

{

ds = new DataSet();

try

{

//SP_Update_User has following parameters

//@User_ID int,@Fname varchar(200),@Lname varchar(200),@Email

// varchar(500),

//@ActiveDate datetime,@gender varchar(50),@city varchar(200)

SqlConnection con = new SqlConnection(connectionString);

con.Open();

SqlCommand cmd = new SqlCommand("SP_Update_User", con);

cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter da = new SqlDataAdapter(cmd);

SqlParameter UserID = new SqlParameter();

UserID.ParameterName = "@User_ID";

UserID.DbType = DbType.Int32;

UserID.Value = User_ID;

cmd.Parameters.Add(UserID);

SqlParameter Fname = new SqlParameter();

Fname.ParameterName = "@Fname";

Fname.DbType = DbType.String;

Fname.Value = First_Name;

cmd.Parameters.Add(Fname);

SqlParameter Lname = new SqlParameter();

Lname.ParameterName = "@Lname";

Lname.DbType = DbType.String;

Lname.Value = Last_Name;

cmd.Parameters.Add(Lname);

SqlParameter spEmail = new SqlParameter();

spEmail.ParameterName = "@Email";

spEmail.DbType = DbType.String;

spEmail.Value = Email;

cmd.Parameters.Add(spEmail);

SqlParameter ActiveDate = new SqlParameter();

ActiveDate.ParameterName = "@ActiveDate";

ActiveDate.DbType = DbType.DateTime;

ActiveDate.Value = Active_Date;

cmd.Parameters.Add(ActiveDate);

SqlParameter gender = new SqlParameter();

gender.ParameterName = "@gender";

gender.DbType = DbType.String;

gender.Value = Gender;

cmd.Parameters.Add(gender);

SqlParameter city = new SqlParameter();

city.ParameterName = "@city";

city.DbType = DbType.String;

city.Value = City;

cmd.Parameters.Add(city);

cmd.ExecuteNonQuery();

con.Close();

}

catch (Exception ex)

{

// throw;

// return ds;

}

6. Cancel Edit Event

protected void gridData_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

{

gridData.EditIndex = -1;

GetDt();

}

KPBDOTNET

Pages

Wednesday, December 3, 2014

Gridview Operations - Display, Edit, Update

No comments:

Post a Comment

Blog Archive