Introduction
In .NET 2.0 a very comman and useful tool is provided by MicroSoft to show data in
Hierarchical view.YES it is "GridView".Now It is possible to handle all operations with GridView that use to manipulate database means not only retrive and show record but also ADD , UPDATE,DELETE records as usually we do.
Background
The Code uses fallowing stored procedures.
1-Web_SelectEmployeeDetails
2-Web_EmployeeInsert
3-web_UpdateEmployee
4-web_DeleteEmployee
Web_SelectEmployeeDetails
CREATE PROCEDURE [dbo].[Web_SelectEmployeeDetails]
AS
Select EmployeeID, FirstName,Title,Address,Country,City from Employees
GO
Web_EmployeeInsert
CREATE PROCEDURE dbo.Web_EmployeeInsert
@Name varchar(50),
@LName varchar(50),
@Title nvarchar(25),
@Address nvarchar(50),
@Country varchar(50),
@City varchar(50)
AS
if(@Name='')
select @Name=null
if(@Title='')
select @Title=null
if(@Address='')
select @Address=null
if(@City='')
select @City=null
if(@Country='')
select @Country=null
if(@LName='')
select @LName='HArd-Code'
INSERT INTO Employees(FirstName,LastName,Title,Address,Country,City)
VALUES (@Name,@LName,@Title, @Address, @Country, @City);
SELECT * FROM Employees WHERE (EmployeeID = SCOPE_IDENTITY())
GO
web_UpdateEmployee
CREATE PROCEDURE dbo.web_UpdateEmployee
@EmployeeID int,
@Name varchar(50),
@LName varchar(50),
@Title nvarchar(25),
@Address nvarchar(50),
@Country varchar(50),
@City varchar(50)
AS
UPDATE Employees SET FirstName = @Name,Title = @Title,Address = @Address , Country=@Country, City=@City
WHERE EmployeeID = @EmployeeID
GO
web_DeleteEmployee
CREATE PROCEDURE dbo.web_DeleteEmployee
@EmployeeID int
AS
Delete from Employees WHERE EmployeeID = @EmployeeID
GO
Using the code
The Code uses GridView structure in default.aspx as fallow.
<asp:GridView ID="gvEmployee" runat="server" AutoGenerateColumns="False" ShowFooter="True" DataKeyNames="EmployeeID"
OnRowCommand = "gvEmployee_RowCommand" OnRowCancelingEdit="gvEmployee_RowCancelingEdit" OnRowUpdating="gvEmployee_RowUpdating"
OnRowEditing="gvEmployee_RowEditing" OnRowDeleting="gvEmployee_RowDeleting">
<Columns>
<asp:TemplateField HeaderText="Name">
<EditItemTemplate>
<asp:TextBox ID="txtEditName" runat="server" Text='<%# Bind("FirstName") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtNewName" runat="server"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("FirstName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Title">
<EditItemTemplate>
<asp:TextBox ID="txtEditTitle" runat="server" Text='<%# Bind("Title") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtTitle" runat="server"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("Title") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Address">
<EditItemTemplate>
<asp:TextBox ID="txtEditAddress" runat="server" Text='<%# Bind("Address") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtAddress" runat="server"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("Address") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Country">
<EditItemTemplate>
<asp:TextBox ID="txtEditCountry" runat="server" Text='<%# Bind("Country") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtCountry" runat="server"></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Bind("Country") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="City">
<EditItemTemplate>
<asp:TextBox ID="txtEditCity" runat="server" Text='<%# Bind("City") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtCity" runat="server">
</asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="Label5" runat="server" Text='<%# Bind("City") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField ShowHeader="False">
<EditItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True" CommandName="Update"
Text="Update"></asp:LinkButton>
<asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Cancel"
Text="Cancel"></asp:LinkButton>
</EditItemTemplate>
<FooterTemplate>
<asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="AddNew"
Text="Add New"></asp:LinkButton>
</FooterTemplate>
<ItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="False" CommandName="Edit"
Text="Edit"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:CommandField ShowDeleteButton="True" />
</Columns>
</asp:GridView>
Default.aspx.cs
in Default.aspx.cs
using Employee;
public partial class _Default : System.Web.UI.Page
{
private Employeecs emp;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
FillCustomerInGrid();
}
}
private void FillCustomerInGrid()
{
emp = new Employeecs();
DataTable dtCustomer = new DataTable();
dtCustomer=emp.Fetch();
if (dtCustomer != null)
{
if (dtCustomer.Rows.Count > 0)
{
gvEmployee.DataSource = dtCustomer;
gvEmployee.DataBind();
}
}
else
{
dtCustomer.Rows.Add(dtCustomer.NewRow());
gvEmployee.DataSource = dtCustomer;
gvEmployee.DataBind();
int TotalColumns = gvEmployee.Rows[0].Cells.Count;
gvEmployee.Rows[0].Cells.Clear();
gvEmployee.Rows[0].Cells.Add(new TableCell());
gvEmployee.Rows[0].Cells[0].ColumnSpan = TotalColumns;
gvEmployee.Rows[0].Cells[0].Text = "No Record Found";
}
}
protected void gvEmployee_RowCommand(object sender, GridViewCommandEventArgs e)
{
emp= new Employeecs();
if (e.CommandName.Equals("AddNew"))
{
TextBox txtNewName = (TextBox)gvEmployee.FooterRow.FindControl("txtNewName");
TextBox txtNewTitle = (TextBox)gvEmployee.FooterRow.FindControl("txtTitle");
TextBox txtNewAddress = (TextBox)gvEmployee.FooterRow.FindControl("txtAddress");
TextBox txtNewCountry = (TextBox)gvEmployee.FooterRow.FindControl("txtCountry");
TextBox txtNewCity = (TextBox)gvEmployee.FooterRow.FindControl("txtCity");
emp.Insert(txtNewName.Text, txtNewTitle.Text, txtNewAddress.Text, txtNewCountry.Text, txtNewCity.Text);
FillCustomerInGrid();
}
}
protected void gvEmployee_RowEditing(object sender, GridViewEditEventArgs e)
{
gvEmployee.EditIndex = e.NewEditIndex;
FillCustomerInGrid();
}
protected void gvEmployee_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
gvEmployee.DataKeys[e.RowIndex].Values[0].ToString();
gvEmployee.EditIndex = -1;
FillCustomerInGrid();
}
protected void gvEmployee_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
emp= new Employeecs();
TextBox txtNewName = (TextBox)gvEmployee.Rows[e.RowIndex].FindControl("txtEditName");
//DropDownList cmbNewGender = (DropDownList)GridView1.FooterRow.FindControl("cmbNewGender");
TextBox txtNewTitle = (TextBox)gvEmployee.Rows[e.RowIndex].FindControl("txtEditTitle");
TextBox txtNewAddress = (TextBox)gvEmployee.Rows[e.RowIndex].FindControl("txtEditAddress");
TextBox txtNewCountry = (TextBox)gvEmployee.Rows[e.RowIndex].FindControl("txtEditCountry");
TextBox txtNewCity = (TextBox)gvEmployee.Rows[e.RowIndex].FindControl("txtEditCity");
//DropDownList cmbNewType = (DropDownList)GridView1.FooterRow.FindControl("cmbNewType");
emp.Update(gvEmployee.DataKeys[e.RowIndex].Values[0].ToString(), txtNewName.Text, txtNewTitle.Text, txtNewAddress.Text, txtNewCountry.Text, txtNewCity.Text);
gvEmployee.EditIndex = -1;
FillCustomerInGrid();
}
protected void gvEmployee_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
customer.Delete(gvEmployee.DataKeys[e.RowIndex].Values[0].ToString());
FillCustomerInGrid();
}
}
in App_Code/CustomersCls.cs
using DBLogic;
namespace Employee
{
public class Employeecs
{
DataTable dt;
DataSet ds;
public Employeecs()
{
dt = null;
ds = null;
}
public void Insert(string Name, string Title, string Address, string Country, string City)
{
ds = new DataSet();
dbLayer obj = new dbLayer();
string[,] param = new string[6, 2];
param[0, 0] = "Name";
param[0, 1] = Name;
param[1, 0] = "LName";
param[1, 1] = "";
param[2, 0] = "Title";
param[2, 1] = Title;
param[3, 0] = "Address";
param[3, 1] = Address;
param[4, 0] = "Country";
param[4, 1] = Country;
param[5, 0] = "City";
param[5, 1] = City;
ds = obj.ExecutePro("Web_EmployeeInsert", param);
}
public DataTable Fetch()
{
ds = new DataSet();
dbLayer obj = new dbLayer();
ds=obj.ExecuteProcedure("Web_SelectEmployeeDetails");
dt = ds.Tables[0];
return dt;
}
public void Update(string EmployeeID, string Name, string Title, string Address, string Country, string City)
{
ds = new DataSet();
dbLayer obj = new dbLayer();
string[,] param = new string[7, 2];
param[0, 0] = "EmployeeID";
param[0, 1] = EmployeeID;
param[1, 0] = "Name";
param[1, 1] = Name;
param[2, 0] = "LName";
param[2, 1] = "";
param[3, 0] = "Title";
param[3, 1] = Title;
param[4, 0] = "Address";
param[4, 1] = Address;
param[5, 0] = "Country";
param[5, 1] = Country;
param[6, 0] = "City";
param[6, 1] = City;
ds = obj.ExecutePro("web_UpdateEmployee",param);
}
public void Delete(string EmployeeID)
{
ds = new DataSet();
dbLayer obj = new dbLayer();
string[,] param = new string[1,2];
param[0, 0] = "EmployeeID";
param[0, 1] = EmployeeID;
ds = obj.ExecutePro("web_DeleteEmployee", param);
}
}
}
in DBLogic.cs
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;//it's a free dll from MicroSoft to handle all Sql level procedure executions [add refrence with application first]
namespace DBLogic
{
public class dbLayer
{
#region Member_Varibles
private string strCon = "";
private string strResultSet = "";
private SqlConnection objConnection;
private DataSet dsResultSet;
#endregion
#region Constructor
public dbLayer()
{
//Write here your Connection string Settings as you have coded it in Web.Config
strCon = System.Configuration.ConfigurationManager.ConnectionStrings["connection_string"].ConnectionString;
}
#endregion
#region OPEN_Connection
public void CreateConnection()
{
objConnection = null;
try
{
objConnection = new SqlConnection(strCon);
if (objConnection.State == ConnectionState.Closed || objConnection == null)
{
objConnection.Open();
}
}
catch
{
}
}
#endregion
#region CLOSE_Connection
public void CloseConnection()
{
try
{
if (objConnection.State == ConnectionState.Open || objConnection != null)
{
objConnection.Close();
objConnection = null;
}
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region EXECUTE_StoredProcedure
public DataSet ExecuteProcedure(string spName)
{
dsResultSet = null;
try
{
dsResultSet = new DataSet();
CreateConnection();
dsResultSet = SqlHelper.ExecuteDataset(objConnection, CommandType.StoredProcedure, spName);
}
catch (Exception ex)
{
throw ex;
}
finally
{
CloseConnection();
}
return dsResultSet;
}
#endregion
public DataSet ExecutePro( string sPname,string [,] Param)
{
dsResultSet = null;
SqlParameter[] paramcollection = null;
try
{
dsResultSet = new DataSet();
if (Param.Length > 0)
{
paramcollection = new SqlParameter[(Param.Length / 2)];
for (int i = 0; i < (Param.Length / 2); i++)
{
paramcollection[i] = new SqlParameter("@" + Param[i, 0], Param[i, 1]);
}
}
CreateConnection();
dsResultSet = SqlHelper.ExecuteDataset(objConnection, CommandType.StoredProcedure, sPname, paramcollection);
}
catch (Exception ex)
{
throw ex;
}
finally
{
CloseConnection();
}
return dsResultSet;
}
}
}
Enjoy Happy Coding :)
