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

        }