具有添加/更新/删除操作的 GridView

引言

在 .NET 2.0 中，微软提供了一个非常常用且有用的工具来以

分层视图显示数据。没错，就是“GridView”。现在，我们可以处理 GridView 的所有操作，这些操作通常用于操作数据库，这意味着不仅可以检索和显示记录，还可以添加、更新和删除记录，就像我们通常所做的那样。

背景

代码使用了以下存储过程。

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

使用代码

代码在 default.aspx 中使用了默认的 GridView 结构，如下所示。

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

}

}



}


 
 
 
 
 
 
 
 
 
 
 
 

祝您编码愉快！