C# ADO.NET 实战：SQL Server 图书管理系统 CRUD 操作与 3 类窗体交互

C# ADO.NET 实战构建SQL Server图书管理系统全栈解决方案1. 项目架构设计与技术选型在开始编码之前我们需要明确系统的整体架构。图书管理系统通常包含以下核心模块数据访问层使用ADO.NET处理与SQL Server的CRUD操作业务逻辑层封装图书管理、用户认证等核心业务规则表示层WinForms窗体应用程序提供用户交互界面技术栈选择数据库SQL Server 2019兼容Express版本.NET版本推荐使用.NET 6长期支持版本开发工具Visual Studio 2022社区版免费且功能完整// 典型的三层架构引用关系 BookManagementSystem.sln ├── BookMS.Data (类库项目) ├── BookMS.Business (类库项目) └── BookMS.WinForms (Windows窗体应用)2. 数据库设计与实现首先创建图书管理系统的数据库结构主要包含以下表CREATE DATABASE BookManagementSystem; GO USE BookManagementSystem; GO -- 管理员表 CREATE TABLE t_admin ( id VARCHAR(20) PRIMARY KEY, psw VARCHAR(50) NOT NULL ); -- 用户表 CREATE TABLE t_user ( id VARCHAR(20) PRIMARY KEY, psw VARCHAR(50) NOT NULL, name NVARCHAR(50) NOT NULL ); -- 图书表 CREATE TABLE t_book ( isbn VARCHAR(20) PRIMARY KEY, title NVARCHAR(100) NOT NULL, author NVARCHAR(50) NOT NULL, publisher NVARCHAR(100) NOT NULL, stock INT NOT NULL DEFAULT 0 ); -- 借阅记录表 CREATE TABLE t_lend ( id INT IDENTITY(1,1) PRIMARY KEY, user_id VARCHAR(20) NOT NULL, book_isbn VARCHAR(20) NOT NULL, lend_date DATETIME NOT NULL DEFAULT GETDATE(), return_date DATETIME NULL, FOREIGN KEY (user_id) REFERENCES t_user(id), FOREIGN KEY (book_isbn) REFERENCES t_book(isbn) );提示在实际项目中应考虑添加索引优化查询性能例如在t_lend表的user_id和book_isbn字段上创建非聚集索引。3. ADO.NET 数据访问层实现创建通用的数据库访问类Dao.cs封装常用数据库操作using System.Data; using System.Data.SqlClient; namespace BookMS.Data { public class Dao { private readonly string _connectionString; public Dao(string connectionString) { _connectionString connectionString; } public SqlConnection GetConnection() { var connection new SqlConnection(_connectionString); if (connection.State ! ConnectionState.Open) { connection.Open(); } return connection; } public int ExecuteNonQuery(string sql, params SqlParameter[] parameters) { using (var connection GetConnection()) using (var command new SqlCommand(sql, connection)) { command.Parameters.AddRange(parameters); return command.ExecuteNonQuery(); } } public DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters) { using (var connection GetConnection()) using (var command new SqlCommand(sql, connection)) { command.Parameters.AddRange(parameters); using (var adapter new SqlDataAdapter(command)) { var dt new DataTable(); adapter.Fill(dt); return dt; } } } public object ExecuteScalar(string sql, params SqlParameter[] parameters) { using (var connection GetConnection()) using (var command new SqlCommand(sql, connection)) { command.Parameters.AddRange(parameters); return command.ExecuteScalar(); } } } }4. 登录认证模块实现登录窗体需要处理两种角色的认证逻辑// LoginForm.cs public partial class LoginForm : Form { private readonly Dao _dao; public LoginForm() { InitializeComponent(); _dao new Dao(ConfigurationManager.ConnectionStrings[BookDB].ConnectionString); } private void btnLogin_Click(object sender, EventArgs e) { string id txtUserId.Text.Trim(); string pwd txtPassword.Text; if (string.IsNullOrEmpty(id) || string.IsNullOrEmpty(pwd)) { MessageBox.Show(用户名和密码不能为空); return; } if (rbAdmin.Checked) { AuthenticateAdmin(id, pwd); } else if (rbUser.Checked) { AuthenticateUser(id, pwd); } else { MessageBox.Show(请选择登录类型); } } private void AuthenticateAdmin(string id, string pwd) { string sql SELECT COUNT(1) FROM t_admin WHERE idid AND pswpwd; var parameters new[] { new SqlParameter(id, id), new SqlParameter(pwd, pwd) }; int count Convert.ToInt32(_dao.ExecuteScalar(sql, parameters)); if (count 0) { this.Hide(); new AdminMainForm().ShowDialog(); this.Close(); } else { MessageBox.Show(管理员账号或密码错误); } } private void AuthenticateUser(string id, string pwd) { string sql SELECT name FROM t_user WHERE idid AND pswpwd; var parameters new[] { new SqlParameter(id, id), new SqlParameter(pwd, pwd) }; var result _dao.ExecuteScalar(sql, parameters); if (result ! null) { UserSession.CurrentUser new UserInfo { Id id, Name result.ToString() }; this.Hide(); new UserMainForm().ShowDialog(); this.Close(); } else { MessageBox.Show(用户账号或密码错误); } } }5. 图书管理模块实现管理员界面包含图书的CRUD操作使用DataGridView展示数据// AdminBookManagementForm.cs public partial class AdminBookManagementForm : Form { private readonly Dao _dao; private DataTable _bookTable; public AdminBookManagementForm() { InitializeComponent(); _dao new Dao(ConfigurationManager.ConnectionStrings[BookDB].ConnectionString); LoadBookData(); } private void LoadBookData() { string sql SELECT isbn AS 书号, title AS 书名, author AS 作者, publisher AS 出版社, stock AS 库存 FROM t_book; _bookTable _dao.ExecuteDataTable(sql); dgvBooks.DataSource _bookTable; dgvBooks.AutoSizeColumnsMode DataGridViewAutoSizeColumnsMode.Fill; } private void btnAdd_Click(object sender, EventArgs e) { using (var form new BookEditForm()) { if (form.ShowDialog() DialogResult.OK) { string sql INSERT INTO t_book (isbn, title, author, publisher, stock) VALUES (isbn, title, author, publisher, stock); var parameters new[] { new SqlParameter(isbn, form.ISBN), new SqlParameter(title, form.Title), new SqlParameter(author, form.Author), new SqlParameter(publisher, form.Publisher), new SqlParameter(stock, form.Stock) }; try { int affected _dao.ExecuteNonQuery(sql, parameters); if (affected 0) { MessageBox.Show(添加图书成功); LoadBookData(); } } catch (SqlException ex) { MessageBox.Show($数据库错误: {ex.Message}); } } } } private void btnDelete_Click(object sender, EventArgs e) { if (dgvBooks.SelectedRows.Count 0) { MessageBox.Show(请选择要删除的图书); return; } string isbn dgvBooks.SelectedRows[0].Cells[书号].Value.ToString(); if (MessageBox.Show($确定要删除书号为 {isbn} 的图书吗?, 确认删除, MessageBoxButtons.YesNo) DialogResult.Yes) { string sql DELETE FROM t_book WHERE isbnisbn; var parameter new SqlParameter(isbn, isbn); try { int affected _dao.ExecuteNonQuery(sql, parameter); if (affected 0) { MessageBox.Show(删除成功); LoadBookData(); } } catch (SqlException ex) { MessageBox.Show($删除失败: {ex.Message}); } } } }6. 用户借阅功能实现普通用户界面提供图书查询和借阅功能// UserBookForm.cs public partial class UserBookForm : Form { private readonly Dao _dao; public UserBookForm() { InitializeComponent(); _dao new Dao(ConfigurationManager.ConnectionStrings[BookDB].ConnectionString); LoadBooks(); } private void LoadBooks(string keyword null) { string sql SELECT isbn AS 书号, title AS 书名, author AS 作者, publisher AS 出版社, stock AS 可借数量 FROM t_book WHERE stock 0; ListSqlParameter parameters new ListSqlParameter(); if (!string.IsNullOrEmpty(keyword)) { sql AND (title LIKE keyword OR author LIKE keyword OR publisher LIKE keyword); parameters.Add(new SqlParameter(keyword, $%{keyword}%)); } var dt _dao.ExecuteDataTable(sql, parameters.ToArray()); dgvBooks.DataSource dt; } private void btnSearch_Click(object sender, EventArgs e) { LoadBooks(txtKeyword.Text.Trim()); } private void btnBorrow_Click(object sender, EventArgs e) { if (dgvBooks.SelectedRows.Count 0) { MessageBox.Show(请选择要借阅的图书); return; } string isbn dgvBooks.SelectedRows[0].Cells[书号].Value.ToString(); int stock Convert.ToInt32(dgvBooks.SelectedRows[0].Cells[可借数量].Value); if (stock 0) { MessageBox.Show(该图书已无库存); return; } string sql INSERT INTO t_lend (user_id, book_isbn) VALUES (userId, isbn); UPDATE t_book SET stock stock - 1 WHERE isbn isbn; var parameters new[] { new SqlParameter(userId, UserSession.CurrentUser.Id), new SqlParameter(isbn, isbn) }; try { using (var connection _dao.GetConnection()) using (var transaction connection.BeginTransaction()) { try { var command new SqlCommand(sql, connection, transaction); command.Parameters.AddRange(parameters); int affected command.ExecuteNonQuery(); transaction.Commit(); MessageBox.Show(借阅成功); LoadBooks(txtKeyword.Text.Trim()); } catch { transaction.Rollback(); throw; } } } catch (SqlException ex) { MessageBox.Show($借阅失败: {ex.Message}); } } }7. 窗体间数据交互与状态管理在WinForms应用中窗体间的数据传递有多种方式通过构造函数传递适合简单数据使用静态类存储共享数据适合全局状态使用事件机制适合松耦合通信示例用户会话管理类// UserSession.cs public static class UserSession { public static UserInfo CurrentUser { get; set; } public static bool IsAdmin CurrentUser?.Id.StartsWith(admin) ?? false; } public class UserInfo { public string Id { get; set; } public string Name { get; set; } }窗体间跳转示例// AdminMainForm.cs private void 图书管理ToolStripMenuItem_Click(object sender, EventArgs e) { var form new AdminBookManagementForm(); form.MdiParent this; form.Show(); } // 在子窗体中刷新父窗体数据 private void SomeChildForm_FormClosed(object sender, FormClosedEventArgs e) { var parent this.MdiParent as AdminMainForm; parent?.RefreshData(); }8. 高级功能与优化建议8.1 使用存储过程提高性能-- 创建借书存储过程 CREATE PROCEDURE sp_borrow_book user_id VARCHAR(20), book_isbn VARCHAR(20) AS BEGIN BEGIN TRANSACTION; BEGIN TRY -- 检查库存 DECLARE stock INT; SELECT stock stock FROM t_book WHERE isbn book_isbn; IF stock IS NULL RAISERROR(图书不存在, 16, 1); IF stock 0 RAISERROR(图书已无库存, 16, 1); -- 插入借阅记录 INSERT INTO t_lend (user_id, book_isbn) VALUES (user_id, book_isbn); -- 更新库存 UPDATE t_book SET stock stock - 1 WHERE isbn book_isbn; COMMIT TRANSACTION; RETURN 0; -- 成功 END TRY BEGIN CATCH ROLLBACK TRANSACTION; RETURN -1; -- 失败 END CATCH ENDC#调用存储过程public int BorrowBook(string userId, string isbn) { using (var connection GetConnection()) using (var command new SqlCommand(sp_borrow_book, connection)) { command.CommandType CommandType.StoredProcedure; command.Parameters.AddWithValue(user_id, userId); command.Parameters.AddWithValue(book_isbn, isbn); var returnParam new SqlParameter(ReturnValue, SqlDbType.Int) { Direction ParameterDirection.ReturnValue }; command.Parameters.Add(returnParam); command.ExecuteNonQuery(); return (int)returnParam.Value; } }8.2 实现数据分页查询public DataTable GetBooksPaged(int pageIndex, int pageSize, out int totalCount) { string sql SELECT COUNT(1) FROM t_book; SELECT isbn, title, author, publisher, stock FROM t_book ORDER BY title OFFSET offset ROWS FETCH NEXT pageSize ROWS ONLY;; var parameters new[] { new SqlParameter(offset, (pageIndex - 1) * pageSize), new SqlParameter(pageSize, pageSize) }; using (var connection GetConnection()) using (var command new SqlCommand(sql, connection)) { command.Parameters.AddRange(parameters); using (var reader command.ExecuteReader()) { // 获取总记录数 reader.Read(); totalCount reader.GetInt32(0); // 获取分页数据 reader.NextResult(); var dt new DataTable(); dt.Load(reader); return dt; } } }8.3 使用异步编程改善用户体验public async TaskDataTable GetBooksAsync(string keyword null) { string sql SELECT * FROM t_book WHERE stock 0; ListSqlParameter parameters new ListSqlParameter(); if (!string.IsNullOrEmpty(keyword)) { sql AND (title LIKE keyword OR author LIKE keyword); parameters.Add(new SqlParameter(keyword, $%{keyword}%)); } using (var connection new SqlConnection(_connectionString)) { await connection.OpenAsync(); using (var command new SqlCommand(sql, connection)) { command.Parameters.AddRange(parameters.ToArray()); using (var reader await command.ExecuteReaderAsync()) { var dt new DataTable(); dt.Load(reader); return dt; } } } } // 窗体中调用 private async void btnSearch_Click(object sender, EventArgs e) { try { dgvBooks.DataSource await _bookService.GetBooksAsync(txtKeyword.Text); } catch (Exception ex) { MessageBox.Show($查询失败: {ex.Message}); } }9. 安全性与异常处理9.1 防范SQL注入始终使用参数化查询// 错误示范 - 存在SQL注入风险 string sql $SELECT * FROM t_user WHERE id{userId} AND psw{password}; // 正确做法 - 参数化查询 string sql SELECT * FROM t_user WHERE idid AND pswpsw; var parameters new[] { new SqlParameter(id, userId), new SqlParameter(psw, password) };9.2 密码安全存储不要明文存储密码应使用哈希算法public static string HashPassword(string password) { using (var sha256 SHA256.Create()) { var hashedBytes sha256.ComputeHash(Encoding.UTF8.GetBytes(password)); return BitConverter.ToString(hashedBytes).Replace(-, ).ToLower(); } } // 使用示例 string hashedPassword HashPassword(user123);9.3 全面的异常处理try { // 数据库操作代码 } catch (SqlException ex) when (ex.Number 547) // 外键约束错误 { MessageBox.Show(操作失败存在关联数据无法删除); } catch (SqlException ex) when (ex.Number 2627) // 主键冲突 { MessageBox.Show(操作失败已存在相同编号的记录); } catch (SqlException ex) { MessageBox.Show($数据库错误: {ex.Message}); LogError(ex); // 记录错误日志 } catch (Exception ex) { MessageBox.Show($系统错误: {ex.Message}); LogError(ex); // 记录错误日志 }10. 部署与配置管理10.1 配置文件管理使用app.config存储连接字符串configuration connectionStrings add nameBookDB connectionStringData Source.;Initial CatalogBookManagementSystem;Integrated SecurityTrue; providerNameSystem.Data.SqlClient / /connectionStrings /configuration代码中读取配置string connectionString ConfigurationManager.ConnectionStrings[BookDB].ConnectionString;10.2 数据库连接最佳实践使用连接池ADO.NET默认启用连接池无需特殊配置及时释放资源使用using语句确保连接及时关闭连接字符串安全不要将敏感信息硬编码在代码中// 最佳实践示例 using (var connection new SqlConnection(connectionString)) using (var command new SqlCommand(sql, connection)) { connection.Open(); // 执行操作 } // 自动关闭连接10.3 应用程序打包部署WinForms应用部署选项ClickOnce部署适合自动更新场景MSI安装包适合传统安装方式独立发布将应用和依赖项打包为一个可执行文件发布配置示例.csprojPropertyGroup OutputTypeWinExe/OutputType TargetFrameworknet6.0-windows/TargetFramework PublishSingleFiletrue/PublishSingleFile SelfContainedfalse/SelfContained RuntimeIdentifierwin-x64/RuntimeIdentifier /PropertyGroup