asp.net 中利用GridView实现增加，修改，删除

1、创建数据库表

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Table1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Employees] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Position] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[Team] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

2、ASPX代码 <asp:GridView ID="GridViewEmployee" runat="server" AutoGenerateColumns="False" ShowFooter="True" onrowcancelingedit="GridViewEmployee_RowCancelingEdit" onrowediting="GridViewEmployee_RowEditing" onrowupdating="GridViewEmployee_RowUpdating" onrowdeleting="GridViewEmployee_RowDeleting"> <Columns> <asp:TemplateField HeaderText="Employee Name"> <EditItemTemplate> <asp:TextBox ID="TextBoxEditEmployee" runat="server" Text='<%# Bind("Employees") %>'/> </EditItemTemplate> <ItemTemplate> <asp:Label ID="LabelEmployee" runat="server" Text='<%# Bind("Employees") %>'/> </ItemTemplate> <FooterTemplate> <asp:TextBox ID="TextBoxEmployee" runat="server"/> </FooterTemplate> </asp:TemplateField > <asp:TemplateField HeaderText="Position"> <EditItemTemplate> <asp:TextBox ID="TextBoxEditPosition" runat="server" Text='<%# Bind("Position") %>'/> </EditItemTemplate> <ItemTemplate> <asp:Label ID="LabelPosition" runat="server" Text='<%# Bind("Position") %>'/> </ItemTemplate> <FooterTemplate> <asp:TextBox ID="TextBoxPosition" runat="server"/> </FooterTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Team Name"> <EditItemTemplate> <asp:TextBox ID="TextBoxEditTeam" runat="server" Text='<%# Bind("Team") %>'/> </EditItemTemplate> <ItemTemplate> <asp:Label ID="LabelTeam" runat="server" Text='<%# Bind("Team") %>'/> </ItemTemplate> <FooterTemplate> <asp:TextBox ID="TextBoxTeam" runat="server"/> </FooterTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Employee ID"> <ItemTemplate> <asp:Label ID="LabelID" runat="server" Text='<%# Bind("Id") %>'/> </ItemTemplate> <FooterTemplate> <asp:Button ID="Button1" runat="server" Text="增 加" OnClick="Button1_Click" /> </FooterTemplate> </asp:TemplateField> <asp:CommandField ShowEditButton="True" ShowDeleteButton HeaderText="操作" /> </Columns> </asp:GridView>

3、后台C#代码：

protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindGridView();
}
}

private string GetConnectionString()
{
//Where MyConsString is the connetion string that was set up in the web config file
//return System.Configuration.ConfigurationManager.ConnectionStrings["MyConsString"].ConnectionString;
return "Data Source=127.0.0.1;Initial Catalog=testdb;User ID=sa;PassWord=newman2007";
}
private void DeleteRecord(string ID)
{
SqlConnection connection = new SqlConnection(GetConnectionString());
string sqlStatement = "DELETE FROM Table1 WHERE Id = @Id";

try
{
connection.Open();
SqlCommand cmd = new SqlCommand(sqlStatement, connection);
cmd.Parameters.AddWithValue("@Id", ID);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Deletion Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
connection.Close();
}
}

private void BindGridView()
{
DataTable dt = new DataTable();
SqlConnection connection = new SqlConnection(GetConnectionString());
try
{
connection.Open();
string sqlStatement = "SELECT * FROM Table1";
SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
sqlDa.Fill(dt);
if (dt.Rows.Count > 0)
{
GridViewEmployee.DataSource = dt;
GridViewEmployee.DataBind();
}
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Fetch Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
connection.Close();
}
}

private void AddNewRecord(string employee, string position, string team)
{
SqlConnection connection = new SqlConnection(GetConnectionString());
string sqlStatement = "INSERT INTO Table1" +
"(Employees,Position,Team)" +
"VALUES (@Employees,@Position,@Team)";
try
{

connection.Open();
SqlCommand cmd = new SqlCommand(sqlStatement, connection);
cmd.Parameters.AddWithValue("@Employees", employee);
cmd.Parameters.AddWithValue("@Position", position);
cmd.Parameters.AddWithValue("@Team", team);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Insert/Update Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
connection.Close();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
//Extract the TextBoxes that is located under the footer template
TextBox tbEmployee = (TextBox)GridViewEmployee.FooterRow.Cells[0].FindControl("TextBoxEmployee");
TextBox tbPosition = (TextBox)GridViewEmployee.FooterRow.Cells[1].FindControl("TextBoxPosition");
TextBox tbTeam = (TextBox)GridViewEmployee.FooterRow.Cells[2].FindControl("TextBoxTeam");
if (tbEmployee.Text.Trim() != "" && tbPosition.Text.Trim() != "" && tbTeam.Text.Trim() != "")
{
//call the method for adding new records to database and pass the necessary parameters
AddNewRecord(tbEmployee.Text, tbPosition.Text, tbTeam.Text);
//Re-Bind the GridView to reflect the changes made
BindGridView();
}
}

private void UpdateRecord(string id, string employee, string position, string team)
{
SqlConnection connection = new SqlConnection(GetConnectionString());
string sqlStatement = "UPDATE Table1 " +
"SET Employees = @Employees, Position = @Position, Team = @Team " +
"WHERE Id = @Id";
try
{
connection.Open();
SqlCommand cmd = new SqlCommand(sqlStatement, connection);
cmd.Parameters.AddWithValue("@Employees", employee);
cmd.Parameters.AddWithValue("@Position", position);
cmd.Parameters.AddWithValue("@Team", team);
cmd.Parameters.AddWithValue("@Id", id);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Insert/Update Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
connection.Close();
}
}

protected void GridViewEmployee_RowEditing(object sender, GridViewEditEventArgs e)
{
GridViewEmployee.EditIndex = e.NewEditIndex; // turn to edit mode
BindGridView(); // Rebind GridView to show the data in edit mode
}

protected void GridViewEmployee_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridViewEmployee.EditIndex = -1; //swicth back to default mode
BindGridView(); // Rebind GridView to show the data in default mode
}

protected void GridViewEmployee_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
//Accessing Edited values from the GridView
string id = ((Label)GridViewEmployee.Rows[e.RowIndex].Cells[3].FindControl("LabelID")).Text; //ID
string employee = ((TextBox)GridViewEmployee.Rows[e.RowIndex].Cells[0].FindControl("TextBoxEditEmployee")).Text; //Employee
string position = ((TextBox)GridViewEmployee.Rows[e.RowIndex].Cells[1].FindControl("TextBoxEditPosition")).Text; //Position
string team = ((TextBox)GridViewEmployee.Rows[e.RowIndex].Cells[2].FindControl("TextBoxEditTeam")).Text; //Team

UpdateRecord(id, employee, position, team); // call update method

GridViewEmployee.EditIndex = -1; //Turn the Grid to read only mode

BindGridView(); // Rebind GridView to reflect changes made

Response.Write("Update Seccessful!");

}

protected void GridViewEmployee_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
//get the ID of the selected row
string id = ((Label)GridViewEmployee.Rows[e.RowIndex].Cells[3].FindControl("LabelID")).Text;
DeleteRecord(id); //call the method for delete

BindGridView(); // Rebind GridView to reflect changes made

}