ASP.NET + Access数据库增删改查案例 - 详细教程

{"title":"ASP.NET + Access数据库增删改查案例 - 详细教程","description":"本教程将带您逐步完成使用ASP.NET和Access数据库构建一个简单的员工管理系统，涵盖数据查询、添加、更新和删除等常见操作。","keywords":"ASP.NET, Access, 数据库, 增删改查, CRUD, 教程, 案例, 示例","content":"以下是一个使用ASP.NET和Access数据库进行增删改查的案例：\n\n1. 创建数据库表\n\n首先，在Access数据库中创建一个名为"Employees"的表，表结构如下：\n\n| ID | Name | Age | Department |\n| --- | ------- | --- | ---------- |\n| 1 | John | 25 | HR |\n| 2 | Mary | 30 | IT |\n| 3 | Michael | 35 | Sales |\n\n2. 创建ASP.NET页面\n\n在ASP.NET项目中创建一个名为"EmployeeManagement.aspx"的页面，页面包含一个GridView控件和一些文本框和按钮用于输入和操作数据。\n\n3. 查询数据\n\n在页面的Page_Load事件中，使用ADO.NET连接到Access数据库，并执行查询语句获取所有员工数据，并将结果绑定到GridView控件上。\n\ncsharp\nprotected void Page_Load(object sender, EventArgs e)\n{\n string connectionString = \"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\path\\to\\database.accdb\";\n string query = \"SELECT * FROM Employees\";\n \n using (OleDbConnection connection = new OleDbConnection(connectionString))\n {\n connection.Open();\n OleDbDataAdapter adapter = new OleDbDataAdapter(query, connection);\n DataTable dt = new DataTable();\n adapter.Fill(dt);\n GridView1.DataSource = dt;\n GridView1.DataBind();\n }\n}\n\n\n4. 添加数据\n\n在页面中添加一个"Add"按钮和相应的事件处理程序，以便在点击按钮时将输入的员工数据插入到数据库中。\n\ncsharp\nprotected void AddButton_Click(object sender, EventArgs e)\n{\n string connectionString = \"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\path\\to\\database.accdb\";\n string insertQuery = \"INSERT INTO Employees (Name, Age, Department) VALUES (@Name, @Age, @Department)\";\n \n using (OleDbConnection connection = new OleDbConnection(connectionString))\n {\n connection.Open();\n OleDbCommand command = new OleDbCommand(insertQuery, connection);\n command.Parameters.AddWithValue(@\"Name\", NameTextBox.Text);\n command.Parameters.AddWithValue(@\"Age\", AgeTextBox.Text);\n command.Parameters.AddWithValue(@\"Department\", DepartmentTextBox.Text);\n command.ExecuteNonQuery();\n }\n \n // 刷新GridView\n Page_Load(sender, e);\n}\n\n\n5. 更新数据\n\n在GridView控件中添加一个"Edit"按钮和相应的事件处理程序，以便在点击按钮时将选中的员工数据加载到输入文本框中，然后可以进行修改并更新到数据库中。\n\ncsharp\nprotected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)\n{\n GridView1.EditIndex = e.NewEditIndex;\n Page_Load(sender, e);\n}\n\nprotected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)\n{\n string connectionString = \"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\path\\to\\database.accdb\";\n string updateQuery = \"UPDATE Employees SET Name=@Name, Age=@Age, Department=@Department WHERE ID=@ID\";\n \n GridViewRow row = GridView1.Rows[e.RowIndex];\n int id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value);\n string name = (row.FindControl(@\"NameTextBox\") as TextBox).Text;\n int age = Convert.ToInt32((row.FindControl(@\"AgeTextBox\") as TextBox).Text);\n string department = (row.FindControl(@\"DepartmentTextBox\") as TextBox).Text;\n \n using (OleDbConnection connection = new OleDbConnection(connectionString))\n {\n connection.Open();\n OleDbCommand command = new OleDbCommand(updateQuery, connection);\n command.Parameters.AddWithValue(@\"Name\", name);\n command.Parameters.AddWithValue(@\"Age\", age);\n command.Parameters.AddWithValue(@\"Department\", department);\n command.Parameters.AddWithValue(@\"ID\", id);\n command.ExecuteNonQuery();\n }\n \n GridView1.EditIndex = -1;\n Page_Load(sender, e);\n}\n\n\n6. 删除数据\n\n在GridView控件中添加一个"Delete"按钮和相应的事件处理程序，以便在点击按钮时删除选中的员工数据。\n\ncsharp\nprotected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)\n{\n string connectionString = \"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\path\\to\\database.accdb\";\n string deleteQuery = \"DELETE FROM Employees WHERE ID=@ID\";\n \n int id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value);\n \n using (OleDbConnection connection = new OleDbConnection(connectionString))\n {\n connection.Open();\n OleDbCommand command = new OleDbCommand(deleteQuery, connection);\n command.Parameters.AddWithValue(@\"ID\", id);\n command.ExecuteNonQuery();\n }\n \n Page_Load(sender, e);\n}\n\n\n通过以上步骤，你可以在ASP.NET页面中实现对Access数据库的增删改查操作。