1. ASPX数据库配置基础

ASP.NET是微软推出的Web开发框架，而数据库配置是ASPX开发中的核心环节。无论是开发小型网站还是大型企业应用，正确配置数据库连接都是确保应用正常运行的第一步。

1.1 什么是ASPX数据库配置

ASPX数据库配置是指在ASP.NET网页应用中设置与数据库建立连接所需的各种参数和设置。这些配置通常包括数据库服务器地址、数据库名称、用户凭据以及其他连接选项。

1.2 为什么数据库配置如此重要

• 数据交互基础：几乎所有Web应用都需要与数据库交互，存储和检索数据
• 性能影响：正确的配置可以显著提高应用性能
• 安全性：妥善的配置是保障数据安全的第一道防线
• 可维护性：良好的配置使应用更易于维护和更新

2. 常见数据库连接配置

在ASPX开发中，我们可能需要连接多种类型的数据库。下面介绍几种常见数据库的连接配置方法。

2.1 SQL Server连接配置

SQL Server是微软推出的关系型数据库，与ASP.NET集成度最高。

2.1.1 基本连接字符串

<!-- Web.config中的连接字符串配置 --> <connectionStrings> <add name="SqlConn" connectionString="Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;" providerName="System.Data.SqlClient"/> </connectionStrings> 

2.1.2 在ASPX页面中使用

// 引入必要的命名空间 using System.Data; using System.Data.SqlClient; // 在页面加载事件中连接数据库 protected void Page_Load(object sender, EventArgs e) { // 从Web.config获取连接字符串 string connStr = ConfigurationManager.ConnectionStrings["SqlConn"].ConnectionString; // 创建连接对象 SqlConnection sqlConn = new SqlConnection(connStr); try { // 打开数据库连接 sqlConn.Open(); // 执行数据库操作 // 例如：查询数据 string sql = "SELECT * FROM Users"; SqlCommand cmd = new SqlCommand(sql, sqlConn); SqlDataReader reader = cmd.ExecuteReader(); // 处理查询结果 while (reader.Read()) { // 读取数据 string userName = reader["UserName"].ToString(); // 处理数据... } reader.Close(); } catch (Exception ex) { // 处理异常 Response.Write("错误: " + ex.Message); } finally { // 确保连接关闭 if (sqlConn.State == ConnectionState.Open) sqlConn.Close(); } } 

2.2 Oracle数据库连接配置

Oracle是另一款广泛使用的企业级数据库系统。

2.2.1 基本连接字符串

<!-- Web.config中的连接字符串配置 --> <connectionStrings> <add name="OracleConn" connectionString="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=yourHost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=yourServiceName)));User Id=yourUsername;Password=yourPassword;" providerName="Oracle.ManagedDataAccess.Client"/> </connectionStrings> 

2.2.2 在ASPX页面中使用

// 引入必要的命名空间 using Oracle.ManagedDataAccess.Client; protected void Page_Load(object sender, EventArgs e) { // 从Web.config获取连接字符串 string oracleConnStr = ConfigurationManager.ConnectionStrings["OracleConn"].ConnectionString; // 创建连接对象 OracleConnection oracleConn = new OracleConnection(oracleConnStr); try { // 打开数据库连接 oracleConn.Open(); // 查询数据 string oracleCmdStr = "SELECT * FROM Employees WHERE Department = :dept"; OracleCommand cmd = new OracleCommand(oracleCmdStr, oracleConn); // 添加参数 cmd.Parameters.Add(":dept", "IT"); // 执行查询 OracleDataReader reader = cmd.ExecuteReader(); // 处理结果 while (reader.Read()) { string empName = reader["EmployeeName"].ToString(); // 处理数据... } reader.Close(); } catch (Exception ex) { // 处理异常 Response.Write("错误: " + ex.Message); } finally { // 确保连接关闭 if (oracleConn.State == ConnectionState.Open) oracleConn.Close(); } } 

2.3 MySQL连接配置

MySQL是一款流行的开源关系型数据库系统。

2.3.1 基本连接字符串

<!-- Web.config中的连接字符串配置 --> <connectionStrings> <add name="MySqlConn" connectionString="Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;" providerName="MySql.Data.MySqlClient"/> </connectionStrings> 

2.3.2 在ASPX页面中使用

// 引入必要的命名空间 using MySql.Data.MySqlClient; protected void Page_Load(object sender, EventArgs e) { // 从Web.config获取连接字符串 string mysqlConnStr = ConfigurationManager.ConnectionStrings["MySqlConn"].ConnectionString; // 创建连接对象 MySqlConnection mysqlConn = new MySqlConnection(mysqlConnStr); try { // 打开数据库连接 mysqlConn.Open(); // 查询数据 string mysqlCmdStr = "SELECT * FROM Products WHERE Category = @category"; MySqlCommand cmd = new MySqlCommand(mysqlCmdStr, mysqlConn); // 添加参数 cmd.Parameters.AddWithValue("@category", "Electronics"); // 执行查询 MySqlDataReader reader = cmd.ExecuteReader(); // 处理结果 while (reader.Read()) { string productName = reader["ProductName"].ToString(); decimal price = Convert.ToDecimal(reader["Price"]); // 处理数据... } reader.Close(); } catch (Exception ex) { // 处理异常 Response.Write("错误: " + ex.Message); } finally { // 确保连接关闭 if (mysqlConn.State == ConnectionState.Open) mysqlConn.Close(); } } 

3. 数据库连接字符串详解

连接字符串是数据库配置的核心部分，它包含了连接数据库所需的所有参数。下面详细解析各种数据库连接字符串的组成部分。

3.1 SQL Server连接字符串参数

示例连接字符串：

<!-- Windows身份验证 --> <add name="SqlConn_Windows" connectionString="Server=myServerAddress;Database=myDataBase;Integrated Security=True;" providerName="System.Data.SqlClient"/> <!-- SQL Server身份验证 --> <add name="SqlConn_SQL" connectionString="Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;" providerName="System.Data.SqlClient"/> 

3.2 Oracle连接字符串参数

示例连接字符串：

<!-- 基本Oracle连接 --> <add name="OracleConn" connectionString="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myHost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=myServiceName)));User Id=myUsername;Password=myPassword;" providerName="Oracle.ManagedDataAccess.Client"/> <!-- 简化版Oracle连接（使用TNS名称） --> <add name="OracleConn_TNS" connectionString="Data Source=myTNSName;User Id=myUsername;Password=myPassword;" providerName="Oracle.ManagedDataAccess.Client"/> 

3.3 MySQL连接字符串参数

示例连接字符串：

<!-- 基本MySQL连接 --> <add name="MySqlConn" connectionString="Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;" providerName="MySql.Data.MySqlClient"/> <!-- 带端口号和字符集的MySQL连接 --> <add name="MySqlConn_Full" connectionString="Server=myServerAddress;Port=3306;Database=myDataBase;Uid=myUsername;Pwd=myPassword;CharSet=utf8;" providerName="MySql.Data.MySqlClient"/> 

4. 数据库操作基本方法

在ASPX中配置好数据库连接后，我们需要执行各种数据库操作。下面介绍常见的数据库操作方法。

4.1 查询数据

查询是数据库操作中最常用的功能，用于从数据库中检索数据。

4.1.1 使用SqlDataReader查询数据

protected void btnQuery_Click(object sender, EventArgs e) { string connStr = ConfigurationManager.ConnectionStrings["SqlConn"].ConnectionString; using (SqlConnection conn = new SqlConnection(connStr)) { try { conn.Open(); // SQL查询语句 string sql = "SELECT UserID, UserName, Email, CreateDate FROM Users WHERE Status = @Status"; using (SqlCommand cmd = new SqlCommand(sql, conn)) { // 添加参数，防止SQL注入 cmd.Parameters.AddWithValue("@Status", 1); using (SqlDataReader reader = cmd.ExecuteReader()) { // 创建表格显示数据 StringBuilder sb = new StringBuilder(); sb.Append("<table border='1'>"); sb.Append("<tr><th>ID</th><th>用户名</th><th>邮箱</th><th>创建日期</th></tr>"); while (reader.Read()) { sb.Append("<tr>"); sb.Append("<td>" + reader["UserID"] + "</td>"); sb.Append("<td>" + reader["UserName"] + "</td>"); sb.Append("<td>" + reader["Email"] + "</td>"); sb.Append("<td>" + reader["CreateDate"] + "</td>"); sb.Append("</tr>"); } sb.Append("</table>"); // 在页面上显示结果 lblResult.Text = sb.ToString(); } } } catch (Exception ex) { lblResult.Text = "查询出错: " + ex.Message; } } } 

4.1.2 使用DataSet和DataAdapter查询数据

protected void btnQueryWithDataSet_Click(object sender, EventArgs e) { string connStr = ConfigurationManager.ConnectionStrings["SqlConn"].ConnectionString; try { using (SqlConnection conn = new SqlConnection(connStr)) { // SQL查询语句 string sql = "SELECT * FROM Products WHERE Category = @Category AND Price < @MaxPrice"; using (SqlCommand cmd = new SqlCommand(sql, conn)) { // 添加参数 cmd.Parameters.AddWithValue("@Category", "Electronics"); cmd.Parameters.AddWithValue("@MaxPrice", 1000); using (SqlDataAdapter adapter = new SqlDataAdapter(cmd)) { DataSet ds = new DataSet(); adapter.Fill(ds, "Products"); // 绑定数据到GridView gvProducts.DataSource = ds.Tables["Products"]; gvProducts.DataBind(); } } } } catch (Exception ex) { lblMessage.Text = "查询出错: " + ex.Message; } } 

4.2 插入数据

插入数据用于向数据库表中添加新记录。

protected void btnInsert_Click(object sender, EventArgs e) { string connStr = ConfigurationManager.ConnectionStrings["SqlConn"].ConnectionString; try { using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); // SQL插入语句 string sql = @"INSERT INTO Users (UserName, Password, Email, Status, CreateDate) VALUES (@UserName, @Password, @Email, @Status, @CreateDate); SELECT SCOPE_IDENTITY();"; // 返回新插入记录的ID using (SqlCommand cmd = new SqlCommand(sql, conn)) { // 添加参数 cmd.Parameters.AddWithValue("@UserName", txtUserName.Text); cmd.Parameters.AddWithValue("@Password", txtPassword.Text); // 实际应用中应该加密 cmd.Parameters.AddWithValue("@Email", txtEmail.Text); cmd.Parameters.AddWithValue("@Status", 1); cmd.Parameters.AddWithValue("@CreateDate", DateTime.Now); // 执行插入并获取新ID int newUserId = Convert.ToInt32(cmd.ExecuteScalar()); lblMessage.Text = "用户添加成功，新用户ID: " + newUserId; } } } catch (Exception ex) { lblMessage.Text = "插入出错: " + ex.Message; } } 

4.3 更新数据

更新数据用于修改数据库中已存在的记录。

protected void btnUpdate_Click(object sender, EventArgs e) { string connStr = ConfigurationManager.ConnectionStrings["SqlConn"].ConnectionString; try { using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); // SQL更新语句 string sql = @"UPDATE Users SET UserName = @UserName, Email = @Email, Status = @Status WHERE UserID = @UserID"; using (SqlCommand cmd = new SqlCommand(sql, conn)) { // 添加参数 cmd.Parameters.AddWithValue("@UserID", hfUserId.Value); // 假设使用隐藏字段存储用户ID cmd.Parameters.AddWithValue("@UserName", txtUserName.Text); cmd.Parameters.AddWithValue("@Email", txtEmail.Text); cmd.Parameters.AddWithValue("@Status", chkStatus.Checked ? 1 : 0); // 执行更新 int rowsAffected = cmd.ExecuteNonQuery(); if (rowsAffected > 0) lblMessage.Text = "用户信息更新成功！"; else lblMessage.Text = "未找到要更新的用户记录。"; } } } catch (Exception ex) { lblMessage.Text = "更新出错: " + ex.Message; } } 

4.4 删除数据

删除数据用于从数据库中移除记录。

protected void btnDelete_Click(object sender, EventArgs e) { string connStr = ConfigurationManager.ConnectionStrings["SqlConn"].ConnectionString; try { using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); // SQL删除语句 string sql = "DELETE FROM Users WHERE UserID = @UserID"; using (SqlCommand cmd = new SqlCommand(sql, conn)) { // 添加参数 cmd.Parameters.AddWithValue("@UserID", hfUserId.Value); // 假设使用隐藏字段存储用户ID // 执行删除 int rowsAffected = cmd.ExecuteNonQuery(); if (rowsAffected > 0) lblMessage.Text = "用户删除成功！"; else lblMessage.Text = "未找到要删除的用户记录。"; } } } catch (Exception ex) { lblMessage.Text = "删除出错: " + ex.Message; } } 

5. 数据库安全配置

数据库安全是Web应用开发中不可忽视的重要环节。下面介绍几种提高数据库安全性的方法。

5.1 连接字符串加密

为了保护数据库连接信息，我们可以对Web.config中的连接字符串进行加密。

5.1.1 使用ASP.NET IIS注册工具加密

// 打开命令提示符（管理员模式） // 加密连接字符串 aspnet_regiis -pe "connectionStrings" -app "/YourWebApplicationName" // 解密连接字符串 aspnet_regiis -pd "connectionStrings" -app "/YourWebApplicationName" 

5.1.2 编程方式加密连接字符串

// 加密方法 private void EncryptConnectionString() { Configuration config = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath); ConfigurationSection section = config.GetSection("connectionStrings"); if (!section.SectionInformation.IsProtected) { section.SectionInformation.ProtectSection("DataProtectionConfigurationProvider"); config.Save(); } } // 解密方法 private void DecryptConnectionString() { Configuration config = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath); ConfigurationSection section = config.GetSection("connectionStrings"); if (section.SectionInformation.IsProtected) { section.SectionInformation.UnprotectSection(); config.Save(); } } 

5.2 参数化查询防止SQL注入

SQL注入是一种常见的网络攻击方式，通过参数化查询可以有效防止。

// 不安全的查询方式（容易受到SQL注入攻击） string sql = "SELECT * FROM Users WHERE UserName = '" + txtUserName.Text + "' AND Password = '" + txtPassword.Text + "'"; // 安全的参数化查询方式 string sql = "SELECT * FROM Users WHERE UserName = @UserName AND Password = @Password"; using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.AddWithValue("@UserName", txtUserName.Text); cmd.Parameters.AddWithValue("@Password", txtPassword.Text); // 执行查询... } 

5.3 最小权限原则

为数据库用户设置最小必要的权限，避免使用过高权限的账户。

-- 创建具有有限权限的用户 CREATE USER WebAppUser WITH PASSWORD = 'StrongPassword123!'; -- 仅授予必要的权限 GRANT SELECT, INSERT, UPDATE, DELETE ON Users TO WebAppUser; GRANT SELECT ON Products TO WebAppUser; GRANT EXECUTE ON usp_GetUserOrders TO WebAppUser; -- 拒绝不必要的权限 DENY CREATE TABLE TO WebAppUser; DENY ALTER DATABASE TO WebAppUser; 

5.4 使用存储过程

存储过程可以提高安全性，减少SQL注入风险，并提高性能。

// 创建存储过程（在SQL Server中） /* CREATE PROCEDURE usp_GetUserDetails @UserID INT AS BEGIN SELECT UserID, UserName, Email, Status, CreateDate FROM Users WHERE UserID = @UserID END */ // 在ASPX中调用存储过程 protected void GetUserDetails(int userId) { string connStr = ConfigurationManager.ConnectionStrings["SqlConn"].ConnectionString; try { using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); using (SqlCommand cmd = new SqlCommand("usp_GetUserDetails", conn)) { cmd.CommandType = CommandType.StoredProcedure; // 添加参数 cmd.Parameters.AddWithValue("@UserID", userId); using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { txtUserName.Text = reader["UserName"].ToString(); txtEmail.Text = reader["Email"].ToString(); chkStatus.Checked = Convert.ToBoolean(reader["Status"]); } } } } } catch (Exception ex) { lblMessage.Text = "获取用户详情出错: " + ex.Message; } } 

6. 常见问题及解决方案

在ASPX数据库配置和使用过程中，可能会遇到各种问题。下面列出一些常见问题及其解决方案。

6.1 连接超时问题

问题：当数据库操作耗时较长时，可能会出现连接超时错误。

解决方案：

// 在连接字符串中设置超时时间（单位：秒） <add name="SqlConn" connectionString="Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;Connect Timeout=30;" providerName="System.Data.SqlClient"/> // 或者在代码中设置命令超时时间 using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.CommandTimeout = 60; // 设置命令超时时间为60秒 // 执行命令... } 

6.2 连接池问题

问题：数据库连接过多或连接泄漏可能导致连接池耗尽。

解决方案：

// 在连接字符串中配置连接池 <add name="SqlConn" connectionString="Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;Max Pool Size=100;Min Pool Size=10;Pooling=true;" providerName="System.Data.SqlClient"/> // 确保正确关闭连接（使用using语句自动释放资源） using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); // 执行数据库操作... } // 连接会自动关闭并返回到连接池 

6.3 字符编码问题

问题：插入或查询包含特殊字符的数据时出现乱码。

解决方案：

// 在连接字符串中指定字符集 <add name="MySqlConn" connectionString="Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;CharSet=utf8mb4;" providerName="MySql.Data.MySqlClient"/> // 在代码中正确处理编码 string sql = "INSERT INTO Articles (Title, Content) VALUES (@Title, @Content)"; using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.AddWithValue("@Title", txtTitle.Text); cmd.Parameters.AddWithValue("@Content", txtContent.Text); // ASP.NET默认使用UTF-16编码 cmd.ExecuteNonQuery(); } 

6.4 数据库事务处理

问题：需要确保多个数据库操作要么全部成功，要么全部失败。

解决方案：

protected void TransferMoney(string fromAccount, string toAccount, decimal amount) { string connStr = ConfigurationManager.ConnectionStrings["SqlConn"].ConnectionString; using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); // 开始事务 SqlTransaction transaction = conn.BeginTransaction(); try { // 从转出账户扣款 string sql1 = "UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountNumber = @AccountNumber"; using (SqlCommand cmd1 = new SqlCommand(sql1, conn, transaction)) { cmd1.Parameters.AddWithValue("@Amount", amount); cmd1.Parameters.AddWithValue("@AccountNumber", fromAccount); cmd1.ExecuteNonQuery(); } // 向转入账户存款 string sql2 = "UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountNumber = @AccountNumber"; using (SqlCommand cmd2 = new SqlCommand(sql2, conn, transaction)) { cmd2.Parameters.AddWithValue("@Amount", amount); cmd2.Parameters.AddWithValue("@AccountNumber", toAccount); cmd2.ExecuteNonQuery(); } // 记录交易日志 string sql3 = @"INSERT INTO TransactionLog (FromAccount, ToAccount, Amount, TransactionDate) VALUES (@FromAccount, @ToAccount, @Amount, @TransactionDate)"; using (SqlCommand cmd3 = new SqlCommand(sql3, conn, transaction)) { cmd3.Parameters.AddWithValue("@FromAccount", fromAccount); cmd3.Parameters.AddWithValue("@ToAccount", toAccount); cmd3.Parameters.AddWithValue("@Amount", amount); cmd3.Parameters.AddWithValue("@TransactionDate", DateTime.Now); cmd3.ExecuteNonQuery(); } // 提交事务 transaction.Commit(); lblMessage.Text = "转账成功！"; } catch (Exception ex) { // 回滚事务 transaction.Rollback(); lblMessage.Text = "转账失败: " + ex.Message; } } } 

7. 实例演示：用户管理系统

下面通过一个完整的用户管理系统实例，综合运用前面介绍的ASPX数据库配置和操作知识。

7.1 数据库表设计

-- 创建用户表 CREATE TABLE Users ( UserID INT PRIMARY KEY IDENTITY(1,1), UserName NVARCHAR(50) NOT NULL UNIQUE, Password NVARCHAR(100) NOT NULL, Email NVARCHAR(100) NOT NULL, Status BIT NOT NULL DEFAULT 1, CreateDate DATETIME NOT NULL DEFAULT GETDATE(), LastLoginDate DATETIME NULL ); -- 创建用户角色表 CREATE TABLE UserRoles ( RoleID INT PRIMARY KEY IDENTITY(1,1), RoleName NVARCHAR(50) NOT NULL UNIQUE, Description NVARCHAR(200) NULL ); -- 创建用户角色关联表 CREATE TABLE UserInRoles ( ID INT PRIMARY KEY IDENTITY(1,1), UserID INT NOT NULL, RoleID INT NOT NULL, FOREIGN KEY (UserID) REFERENCES Users(UserID), FOREIGN KEY (RoleID) REFERENCES UserRoles(RoleID) ); 

7.2 Web.config配置

<?xml version="1.0" encoding="utf-8"?> <configuration> <connectionStrings> <add name="SqlConn" connectionString="Server=.SQLEXPRESS;Database=UserManagementDB;Integrated Security=True;" providerName="System.Data.SqlClient"/> </connectionStrings> <system.web> <compilation debug="true" targetFramework="4.7.2"/> <httpRuntime targetFramework="4.7.2"/> <pages> <namespaces> <add namespace="System.Data"/> <add namespace="System.Data.SqlClient"/> </namespaces> </pages> </system.web> </configuration> 

7.3 用户列表页面 (UserList.aspx)

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="UserList.aspx.cs" Inherits="UserManagementSystem.UserList" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title>用户列表</title> <style> table { border-collapse: collapse; width: 100%; } th, td { border: 1px solid #ddd; padding: 8px; text-align: left; } th { background-color: #f2f2f2; } tr:nth-child(even) { background-color: #f9f9f9; } .active { color: green; } .inactive { color: red; } </style> </head> <body> <form id="form1" runat="server"> <div> <h1>用户列表</h1> <asp:Button ID="btnAddUser" runat="server" Text="添加新用户" OnClick="btnAddUser_Click" /> <asp:GridView ID="gvUsers" runat="server" AutoGenerateColumns="False" OnRowCommand="gvUsers_RowCommand" OnRowDataBound="gvUsers_RowDataBound"> <Columns> <asp:BoundField DataField="UserID" HeaderText="ID" /> <asp:BoundField DataField="UserName" HeaderText="用户名" /> <asp:BoundField DataField="Email" HeaderText="邮箱" /> <asp:TemplateField HeaderText="状态"> <ItemTemplate> <asp:Label ID="lblStatus" runat="server" Text='<%# Eval("Status") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:BoundField DataField="CreateDate" HeaderText="创建日期" DataFormatString="{0:yyyy-MM-dd HH:mm}" /> <asp:TemplateField HeaderText="操作"> <ItemTemplate> <asp:LinkButton ID="btnEdit" runat="server" CommandName="EditUser" CommandArgument='<%# Eval("UserID") %>' Text="编辑"></asp:LinkButton> <asp:LinkButton ID="btnDelete" runat="server" CommandName="DeleteUser" CommandArgument='<%# Eval("UserID") %>' Text="删除" OnClientClick="return confirm('确定要删除此用户吗？');"></asp:LinkButton> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:Label ID="lblMessage" runat="server" ForeColor="Red"></asp:Label> </div> </form> </body> </html> 

7.4 用户列表后台代码 (UserList.aspx.cs)

using System; using System.Data; using System.Data.SqlClient; using System.Web.UI; using System.Web.UI.WebControls; namespace UserManagementSystem { public partial class UserList : Page { protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { BindUserList(); } } private void BindUserList() { string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["SqlConn"].ConnectionString; try { using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); string sql = @"SELECT u.UserID, u.UserName, u.Email, u.Status, u.CreateDate, STUFF(( SELECT ', ' + r.RoleName FROM UserInRoles ur INNER JOIN UserRoles r ON ur.RoleID = r.RoleID WHERE ur.UserID = u.UserID FOR XML PATH('') ), 1, 2, '') AS Roles FROM Users u"; using (SqlCommand cmd = new SqlCommand(sql, conn)) { SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); adapter.Fill(dt); gvUsers.DataSource = dt; gvUsers.DataBind(); } } } catch (Exception ex) { lblMessage.Text = "加载用户列表出错: " + ex.Message; } } protected void gvUsers_RowDataBound(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow) { // 设置状态标签的样式 Label lblStatus = (Label)e.Row.FindControl("lblStatus"); bool status = Convert.ToBoolean(DataBinder.Eval(e.Row.DataItem, "Status")); if (status) { lblStatus.Text = "活跃"; lblStatus.CssClass = "active"; } else { lblStatus.Text = "禁用"; lblStatus.CssClass = "inactive"; } } } protected void gvUsers_RowCommand(object sender, GridViewCommandEventArgs e) { int userId = Convert.ToInt32(e.CommandArgument); if (e.CommandName == "EditUser") { Response.Redirect("UserEdit.aspx?UserID=" + userId); } else if (e.CommandName == "DeleteUser") { DeleteUser(userId); BindUserList(); // 重新绑定数据 } } private void DeleteUser(int userId) { string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["SqlConn"].ConnectionString; try { using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); // 使用事务确保删除操作的一致性 SqlTransaction transaction = conn.BeginTransaction(); try { // 删除用户角色关联 string sql1 = "DELETE FROM UserInRoles WHERE UserID = @UserID"; using (SqlCommand cmd1 = new SqlCommand(sql1, conn, transaction)) { cmd1.Parameters.AddWithValue("@UserID", userId); cmd1.ExecuteNonQuery(); } // 删除用户 string sql2 = "DELETE FROM Users WHERE UserID = @UserID"; using (SqlCommand cmd2 = new SqlCommand(sql2, conn, transaction)) { cmd2.Parameters.AddWithValue("@UserID", userId); int rowsAffected = cmd2.ExecuteNonQuery(); if (rowsAffected > 0) { transaction.Commit(); lblMessage.Text = "用户删除成功！"; } else { transaction.Rollback(); lblMessage.Text = "未找到要删除的用户。"; } } } catch (Exception ex) { transaction.Rollback(); lblMessage.Text = "删除用户时出错: " + ex.Message; } } } catch (Exception ex) { lblMessage.Text = "数据库连接出错: " + ex.Message; } } protected void btnAddUser_Click(object sender, EventArgs e) { Response.Redirect("UserEdit.aspx"); } } } 

7.5 用户编辑页面 (UserEdit.aspx)

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="UserEdit.aspx.cs" Inherits="UserManagementSystem.UserEdit" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title>用户编辑</title> <style> .form-group { margin-bottom: 15px; } label { display: inline-block; width: 120px; } .checkbox-group { margin-top: 10px; } .checkbox-group label { display: inline; width: auto; margin-right: 10px; } </style> </head> <body> <form id="form1" runat="server"> <div> <h1>用户编辑</h1> <div class="form-group"> <label>用户名:</label> <asp:TextBox ID="txtUserName" runat="server" MaxLength="50"></asp:TextBox> <asp:RequiredFieldValidator ID="rfvUserName" runat="server" ControlToValidate="txtUserName" ErrorMessage="用户名不能为空" Display="Dynamic" ForeColor="Red"></asp:RequiredFieldValidator> </div> <div class="form-group"> <label>密码:</label> <asp:TextBox ID="txtPassword" runat="server" TextMode="Password" MaxLength="100"></asp:TextBox> <asp:RequiredFieldValidator ID="rfvPassword" runat="server" ControlToValidate="txtPassword" ErrorMessage="密码不能为空" Display="Dynamic" ForeColor="Red"></asp:RequiredFieldValidator> </div> <div class="form-group"> <label>确认密码:</label> <asp:TextBox ID="txtConfirmPassword" runat="server" TextMode="Password" MaxLength="100"></asp:TextBox> <asp:CompareValidator ID="cvPassword" runat="server" ControlToValidate="txtConfirmPassword" ControlToCompare="txtPassword" ErrorMessage="两次输入的密码不一致" Display="Dynamic" ForeColor="Red"></asp:CompareValidator> </div> <div class="form-group"> <label>邮箱:</label> <asp:TextBox ID="txtEmail" runat="server" MaxLength="100"></asp:TextBox> <asp:RequiredFieldValidator ID="rfvEmail" runat="server" ControlToValidate="txtEmail" ErrorMessage="邮箱不能为空" Display="Dynamic" ForeColor="Red"></asp:RequiredFieldValidator> <asp:RegularExpressionValidator ID="revEmail" runat="server" ControlToValidate="txtEmail" ErrorMessage="邮箱格式不正确" ValidationExpression="w+([-+.']w+)*@w+([-.]w+)*.w+([-.]w+)*" Display="Dynamic" ForeColor="Red"></asp:RegularExpressionValidator> </div> <div class="form-group"> <label>状态:</label> <asp:CheckBox ID="chkStatus" runat="server" Text="启用" Checked="true" /> </div> <div class="form-group"> <label>角色:</label> <div class="checkbox-group"> <asp:CheckBoxList ID="cblRoles" runat="server" RepeatDirection="Horizontal" RepeatLayout="Flow"> </asp:CheckBoxList> </div> </div> <div class="form-group"> <asp:Button ID="btnSave" runat="server" Text="保存" OnClick="btnSave_Click" /> <asp:Button ID="btnCancel" runat="server" Text="取消" OnClick="btnCancel_Click" CausesValidation="false" /> </div> <asp:HiddenField ID="hfUserId" runat="server" /> <asp:Label ID="lblMessage" runat="server" ForeColor="Red"></asp:Label> </div> </form> </body> </html> 

7.6 用户编辑后台代码 (UserEdit.aspx.cs)

using System; using System.Data; using System.Data.SqlClient; using System.Web.UI; using System.Web.UI.WebControls; namespace UserManagementSystem { public partial class UserEdit : Page { protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { // 检查是否有用户ID参数 if (!string.IsNullOrEmpty(Request.QueryString["UserID"])) { hfUserId.Value = Request.QueryString["UserID"]; LoadUserData(); } BindRoles(); } } private void BindRoles() { string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["SqlConn"].ConnectionString; try { using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); string sql = "SELECT RoleID, RoleName FROM UserRoles"; using (SqlCommand cmd = new SqlCommand(sql, conn)) { SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); adapter.Fill(dt); cblRoles.DataSource = dt; cblRoles.DataTextField = "RoleName"; cblRoles.DataValueField = "RoleID"; cblRoles.DataBind(); } } } catch (Exception ex) { lblMessage.Text = "加载角色列表出错: " + ex.Message; } } private void LoadUserData() { string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["SqlConn"].ConnectionString; int userId; if (!int.TryParse(hfUserId.Value, out userId)) { lblMessage.Text = "无效的用户ID"; return; } try { using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); // 获取用户基本信息 string sql = "SELECT UserName, Email, Status FROM Users WHERE UserID = @UserID"; using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.AddWithValue("@UserID", userId); using (SqlDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { txtUserName.Text = reader["UserName"].ToString(); txtEmail.Text = reader["Email"].ToString(); chkStatus.Checked = Convert.ToBoolean(reader["Status"]); } else { lblMessage.Text = "未找到指定的用户"; return; } } } // 获取用户角色 sql = @"SELECT ur.RoleID FROM UserInRoles ur WHERE ur.UserID = @UserID"; using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.AddWithValue("@UserID", userId); using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { int roleId = Convert.ToInt32(reader["RoleID"]); // 选中对应的角色复选框 foreach (ListItem item in cblRoles.Items) { if (item.Value == roleId.ToString()) { item.Selected = true; } } } } } } } catch (Exception ex) { lblMessage.Text = "加载用户数据出错: " + ex.Message; } } protected void btnSave_Click(object sender, EventArgs e) { if (!Page.IsValid) return; string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["SqlConn"].ConnectionString; try { using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); SqlTransaction transaction = conn.BeginTransaction(); try { int userId; bool isEditMode = !string.IsNullOrEmpty(hfUserId.Value) && int.TryParse(hfUserId.Value, out userId); if (isEditMode) { // 更新用户信息 string sql = @"UPDATE Users SET UserName = @UserName, Email = @Email, Status = @Status"; // 如果提供了新密码，则更新密码 if (!string.IsNullOrEmpty(txtPassword.Text)) { sql += ", Password = @Password"; } sql += " WHERE UserID = @UserID"; using (SqlCommand cmd = new SqlCommand(sql, conn, transaction)) { cmd.Parameters.AddWithValue("@UserName", txtUserName.Text); cmd.Parameters.AddWithValue("@Email", txtEmail.Text); cmd.Parameters.AddWithValue("@Status", chkStatus.Checked); if (!string.IsNullOrEmpty(txtPassword.Text)) { // 实际应用中应该对密码进行加密 cmd.Parameters.AddWithValue("@Password", txtPassword.Text); } cmd.Parameters.AddWithValue("@UserID", userId); int rowsAffected = cmd.ExecuteNonQuery(); if (rowsAffected == 0) { transaction.Rollback(); lblMessage.Text = "更新用户失败，用户可能不存在"; return; } } // 删除用户原有角色 string deleteRolesSql = "DELETE FROM UserInRoles WHERE UserID = @UserID"; using (SqlCommand cmd = new SqlCommand(deleteRolesSql, conn, transaction)) { cmd.Parameters.AddWithValue("@UserID", userId); cmd.ExecuteNonQuery(); } } else { // 添加新用户 string sql = @"INSERT INTO Users (UserName, Password, Email, Status, CreateDate) VALUES (@UserName, @Password, @Email, @Status, @CreateDate); SELECT SCOPE_IDENTITY();"; using (SqlCommand cmd = new SqlCommand(sql, conn, transaction)) { cmd.Parameters.AddWithValue("@UserName", txtUserName.Text); // 实际应用中应该对密码进行加密 cmd.Parameters.AddWithValue("@Password", txtPassword.Text); cmd.Parameters.AddWithValue("@Email", txtEmail.Text); cmd.Parameters.AddWithValue("@Status", chkStatus.Checked); cmd.Parameters.AddWithValue("@CreateDate", DateTime.Now); userId = Convert.ToInt32(cmd.ExecuteScalar()); } } // 添加用户角色 foreach (ListItem item in cblRoles.Items) { if (item.Selected) { string insertRoleSql = "INSERT INTO UserInRoles (UserID, RoleID) VALUES (@UserID, @RoleID)"; using (SqlCommand cmd = new SqlCommand(insertRoleSql, conn, transaction)) { cmd.Parameters.AddWithValue("@UserID", userId); cmd.Parameters.AddWithValue("@RoleID", Convert.ToInt32(item.Value)); cmd.ExecuteNonQuery(); } } } // 提交事务 transaction.Commit(); if (isEditMode) lblMessage.Text = "用户信息更新成功！"; else lblMessage.Text = "用户添加成功！"; // 3秒后跳转到用户列表页面 ClientScript.RegisterStartupScript(this.GetType(), "redirect", "setTimeout(function() { window.location.href = 'UserList.aspx'; }, 3000);", true); } catch (Exception ex) { transaction.Rollback(); lblMessage.Text = "保存用户信息时出错: " + ex.Message; } } } catch (Exception ex) { lblMessage.Text = "数据库连接出错: " + ex.Message; } } protected void btnCancel_Click(object sender, EventArgs e) { Response.Redirect("UserList.aspx"); } } } 

8. 总结

通过本篇ASPX数据库配置全攻略，我们详细介绍了ASPX开发中数据库配置的各个方面，包括：

1. 基础数据库配置：了解了ASPX数据库配置的基本概念和重要性
2. 多种数据库连接方式：掌握了SQL Server、Oracle和MySQL等常见数据库的连接配置方法
3. 连接字符串详解：深入理解了各种数据库连接字符串的参数和用法
4. 数据库操作方法：学习了查询、插入、更新和删除数据的基本操作
5. 安全配置：掌握了连接字符串加密、参数化查询、最小权限原则等安全措施
6. 常见问题解决：了解了连接超时、连接池、字符编码等常见问题的解决方案
7. 实际应用案例：通过用户管理系统的完整实例，综合运用了所学知识

数据库配置是ASPX开发中的基础技能，掌握这些知识将帮助你构建更加安全、高效和稳定的Web应用。希望本篇攻略能为你的ASPX开发之路提供有力的支持！