Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>
Idioma: En

ASP.Net GridView CRUD operations with Button controls outside of GridView control

In this article, we are going to learn CRUD operations that is insert, select, edit, update and delete with asp.net gridview control using sql Stored Procedure. I have already explained CRUD operations with stored procedure in one of my article, but in this article CRUD (Create, Read, Update, Delete) buttons will be residing outside of GridView control. You can see in below figures, button controls are residing below the gridview controls.

Related Articles

  1. Insert, update, delete, crud operation in ASP.Net GridView with C# using Stored Procedure
  2. LINQ to SQL: GridView select, insert, update and delete using C# with ASP.Net

Creating SQL Table

First, we need to create two database tables. First for storing different City and second for storing employee's details. Below is the script to create table. 


--creating city table
create table tblCity
(
Id int identity primary key,
Name varchar (50) null,
)
go
--inserting Record into city table
insert into tblCity values('Mumbai')
insert into tblCity values('Delhi')
insert into tblCity values('London')
insert into tblCity values('New York')
go
--creating employee table
create table tblEmployee
(
Id int identity primary key,
Name varchar (50) null,
Gender varchar (10) null,
EMail varchar (25) null,
CityId int
)

Creating Standard Procedure

Also, we need to create stored procedure for different CRUD operations. 


create procedure proc_Employee
@Action varchar(10)=null,
@Id int=null,
@Name varchar(50)=null,
@Gender varchar(10)=null,
@EMail varchar(25)=null,
@CityId int=null
as
begin
if(@Action='Select')
begin
--to display all the record
select e.Id,e.Name,e.Gender,e.EMail,c.Name as City from tblEmployee e
join tblCity c on e.CityId=c.id
end
else if(@Action='Add')
begin
--to add a new record
insert into tblEmployee values (@Name,@Gender,@EMail,@CityId) 
end
else if(@Action='Update')
begin
--to update the existing record
update tblEmployee set Name=@Name,@Gender=@Gender,EMail=@EMail,CityId=@CityId where Id=@Id 
end

else if(@Action='Delete')
begin
--to delete a record
delete from  tblEmployee where Id=@Id 
end
else if(@Action='GetCity')
begin
--to get all the city for binding dropdown control
select Id,Name from tblCity
end
end

Creating Connection In Web.Config File

Write your database connection string in web.config file within tag. 



add name="MyConnString" connectionString=    class='string'>"server=XXXX;database=XXXX;uid=XXXX;password=XXXX;" 
    providerName="System.Data.SqlClient"/>

In Designer File

CSS For Buttons

In designer file, add css for button controls. 

="server">
>>
>
>

Creating Gridview Control

Next step is create a GridView control gridEmployee with 5 columns. Generate OnPageIndexChanging event and DataKeyNames for storing Employee's ID. Create 5 button controls for Add, Edit, Update, Delete and Cancel purpose. 


form id="form1" runat="server">
div>
table>
tr>
td>
asp:Label ID="lblMessage" runat="server" ForeColor="Red">asp:Label>
td>
tr>
tr>
td>
asp:GridView ID="gridEmployee" runat="server" PageSize="5" AutoGenerateColumns="false" 
ShowFooter="true" AllowPaging="true" BackColor="White" BorderColor="#CC9966" 
BorderStyle="None" BorderWidth="5px" CellPadding="4" 
OnPageIndexChanging="gridEmployee_PageIndexChanging" Width="540px"
DataKeyNames="Id">
FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
RowStyle BackColor="White"/>
EditRowStyle BackColor="#FFFFCC" ForeColor="#330099" />
SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />
PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />
HeaderStyle BackColor="#329bd8" Font-Bold="True" ForeColor="White"/>
Columns>
asp:TemplateField>
ItemTemplate>
asp:CheckBox ID="chkRow" runat="server">asp:CheckBox>
ItemTemplate>
asp:TemplateField>
asp:TemplateField HeaderText="Name">
ItemTemplate>
asp:Label ID="lblName" runat="server" Text=''> asp:Label>
ItemTemplate>
EditItemTemplate>
asp:TextBox ID="etxtName" runat="server" Text=''> asp:TextBox>
EditItemTemplate>
FooterTemplate>
asp:TextBox ID="ftxtName" runat="server"> asp:TextBox>
FooterTemplate>
asp:TemplateField>
asp:TemplateField HeaderText="Gender">
ItemTemplate>
asp:Label ID="lblGender" runat="server" Text=''> asp:Label>
ItemTemplate>
EditItemTemplate>
asp:DropDownList ID="eddlGender" runat="server">
asp:ListItem Value="-Select-">-Select-asp:ListItem>
asp:ListItem Value="Male">Maleasp:ListItem>
asp:ListItem Value="Female">Femaleasp:ListItem>
asp:DropDownList>
EditItemTemplate>
FooterTemplate>
asp:DropDownList ID="fddlGender" runat="server">
asp:ListItem Value="-Select-">-Select-asp:ListItem>
asp:ListItem Value="Male">Maleasp:ListItem>
asp:ListItem Value="Female">Femaleasp:ListItem>
asp:DropDownList>
FooterTemplate>
asp:TemplateField>
asp:TemplateField HeaderText="EMail">
ItemTemplate>
asp:Label ID="lblEMail" runat="server" Text=''> asp:Label>
ItemTemplate>
EditItemTemplate>
asp:TextBox ID="etxtEMail" runat="server" Text=''> asp:TextBox>
EditItemTemplate>
FooterTemplate>
asp:TextBox ID="ftxtEMail" runat="server"> asp:TextBox>
FooterTemplate>
asp:TemplateField>
asp:TemplateField HeaderText="City">
ItemTemplate>
asp:Label ID="lblCity" runat="server" Text=''> asp:Label>
ItemTemplate>
EditItemTemplate>
asp:DropDownList ID="eddlCity" runat="server">
asp:DropDownList>
EditItemTemplate>
FooterTemplate>
asp:DropDownList ID="fddlCity" runat="server">
asp:DropDownList>
FooterTemplate>
asp:TemplateField>
Columns>
asp:GridView>
td>
tr>
tr>
td>
table align="center">
tr style="width:500px;">
td>
asp:Button ID="btnAdd" runat="server" Text="Add" CssClass="button" OnClick="btnAdd_Click" />
td>
td>
asp:Button ID="btnEdit" runat="server" Text="Edit" CssClass="button" OnClick="btnEdit_Click" />
asp:Button ID="btnESave" runat="server" Text="Update" CssClass="button" OnClick="btnESave_Click" />
td>
td>
asp:Button ID="btnDelete" runat="server" Text="Delete" CssClass="button" OnClick="btnDelete_Click" />
td>
td>
asp:Button ID="btnCancel" runat="server" Text="Cancel" CssClass="button" OnClick="btnCancel_Click" />
td>
tr>
table>
td>
tr>
table>
div>
form>

Namespaces Used

Include the below Namespaces. 


using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

Complete C# Code

On the Page_Load event, we are calling GetCity() method which will fetch city data from tblCity table and store city data into static dataset dsCity so that we can use it later.

Next method is BindGrid() which will execute the stored procedure with Select action to fetch all employee's record. If no record is found then we are adding new row to empty dataset and binding it to GridView. Also we are binding City DropDownList in footer row using static dataset dsCity.

As footer row is always enabled, on click of Add button, btnAdd_Click event get fired. We are using GridView.FooterRow.FindControl() to find required TextBox and DropDownList controls and perform server side validation, if validation is successful then executing the stored procedure with Add action to save record into database.
Now, the next step is to Edit a record, we can edit only a single record at a time. We are storing editindex of required row in ViewState so that we can use later while updating the record. We are binding City DropDownList while editing using static dataset dsCity. Also disabling the CheckBox control.
On click of Update button, btnESave_Click event get fired. We are retrieving current row index using ViewState. After that retrieving Employee's Id using GridView.DataKeys. GridView.Rows[editindex].FindControl() is used find the respective TextBox and DropDownList controls. After that, we are calling stored procedure to update the record.
The last step is to Delete the record. Here, we can delete as many records we want by selecting CheckBox control. Main important thing while deleting is to fetch Employee's Id using editindex. 

public partial class WebForm1 : System.Web.UI.Page
{
//retrieving connection string from web.config file
    string MyConnString = ConfigurationManager.ConnectionStrings["MyConnString"].ConnectionString;
    SqlConnection con = null;
    SqlCommand cmd = null;
public static DataSet dsCity=null;
int editindex = -1;
 
protected void Page_Load(object sender, EventArgs e)
{
if(!IsPostBack)
{
            GetCity();
            BindGrid();
// hide Update button on page load
            btnESave.Visible = false;             
}
}
 
private void GetCity()
{
        con = new SqlConnection(MyConnString);
        cmd = new SqlCommand("proc_Employee", con);
        cmd.CommandType = CommandType.StoredProcedure;
        con.Open();
 
// call the GetCity task to get all Cities
        cmd.Parameters.AddWithValue("@Action", "GetCity");
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        sda.Fill(ds);
 
        dsCity = ds.Copy(); // copy city data into static dataset         
}
 
private void BindGrid()
{
        con = new SqlConnection(MyConnString);
        cmd = new SqlCommand("proc_Employee", con);
        cmd.CommandType = CommandType.StoredProcedure;
        con.Open();
// call the select task to get all data
        cmd.Parameters.AddWithValue("@Action", "Select");
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        sda.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
            gridEmployee.DataSource = ds;
            gridEmployee.DataBind();
}
else
{
// add new row when the dataset is having zero record
            ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
            gridEmployee.DataSource = ds;
            gridEmployee.DataBind();
            gridEmployee.Rows[0].Visible = false;
}
 
// find the FooterRow City dropdown and populate it from static dataset
        DropDownList fddlCity = (DropDownList)gridEmployee.FooterRow.FindControl("fddlCity");
if (dsCity.Tables[0].Rows.Count > 0)
{
            fddlCity.DataSource = dsCity.Tables[0];
            fddlCity.DataTextField = "Name";
            fddlCity.DataValueField = "Id";
            fddlCity.DataBind();
            fddlCity.Items.Insert(0, "-Select-");
}
}
 
protected void btnAdd_Click(object sender, EventArgs e)
{
        TextBox ftxtName = (TextBox)(gridEmployee.FooterRow.FindControl("ftxtName"));
        DropDownList fddlGender = (DropDownList)(gridEmployee.FooterRow.FindControl("fddlGender"));
        TextBox ftxtEMail = (TextBox)(gridEmployee.FooterRow.FindControl("ftxtEMail"));
        DropDownList fddlCity = (DropDownList)(gridEmployee.FooterRow.FindControl("fddlCity"));
 
if (ftxtName.Text.Trim() == string.Empty)
{
            lblMessage.Text = "Please enter Name";
            ftxtName.Focus();
return;
}
if (fddlGender.SelectedIndex==0)
{
            lblMessage.Text = "Please select Gender";
            fddlGender.Focus();
return;
}
if (!Regex.IsMatch(ftxtEMail.Text.Trim(), @"^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$"
        , RegexOptions.IgnoreCase))
{
            lblMessage.Text = "Please enter valid EMail";
            ftxtEMail.Focus();
return;
}
if (fddlCity.SelectedIndex==0)
{
            lblMessage.Text = "Please select City";
            fddlCity.Focus();
return;
}
 
        con = new SqlConnection(MyConnString);
        cmd = new SqlCommand("proc_Employee", con);
        cmd.CommandType = CommandType.StoredProcedure;
        con.Open();
// call the select task to get all data
        cmd.Parameters.AddWithValue("@Action", "Add");
        cmd.Parameters.AddWithValue("@Name", ftxtName.Text.Trim());
        cmd.Parameters.AddWithValue("@Gender", fddlGender.SelectedItem.Text);
        cmd.Parameters.AddWithValue("@EMail", ftxtEMail.Text.Trim());
        cmd.Parameters.AddWithValue("@CityId", fddlCity.SelectedItem.Value);          
        cmd.ExecuteNonQuery();
        con.Close();
        BindGrid();           
        lblMessage.Text = "Record saved successfully";
}
 
protected void btnEdit_Click(object sender, EventArgs e)
{
int rowCount = 0;
        foreach (GridViewRow gvrow in gridEmployee.Rows)
{
            CheckBox chkRow = (CheckBox)gvrow.FindControl("chkRow");
if (chkRow.Checked && chkRow != null)
{
//finding rowindex for edit
                editindex = gvrow.RowIndex;
                rowCount++;
if (rowCount>1)
{
break;
}
}
}
if (rowCount>1)
{
            lblMessage.Text = "Please select single record to Edit";
}
else if(rowCount==0)
{
            lblMessage.Text = "Please select record to Edit";
}
else
{
            ViewState["editindex"] = editindex;
 
            gridEmployee.EditIndex = editindex;
            BindGrid();
 
// bind city dropdown while editing
            DropDownList eddlCity = (DropDownList)(gridEmployee.Rows[editindex].FindControl("eddlCity"));
if (dsCity.Tables[0].Rows.Count > 0)
{
                eddlCity.DataSource = dsCity.Tables[0];
                eddlCity.DataTextField = "Name";
                eddlCity.DataValueField = "Id";
                eddlCity.DataBind();
                eddlCity.Items.Insert(0, "-Select-");
}
 
// disable all row checkboxes while editing
            foreach (GridViewRow gvrow in gridEmployee.Rows)
{
                CheckBox chkRow = (CheckBox)gvrow.FindControl("chkRow");
                chkRow.Enabled = false;
}
 
//hide footer row while editing
            gridEmployee.FooterRow.Visible = false;
 
// hide and disable respective buttons
            btnAdd.Enabled

Related Articles



This post first appeared on ASPArticles, please read the originial post: here

Share the post

ASP.Net GridView CRUD operations with Button controls outside of GridView control

×

Subscribe to Asparticles

Get updates delivered right to your inbox!

Thank you for your subscription

×