我将为您设计一个医院药库管理系统，包含完整的Visual Studio C# Windows窗体应用、SQL数据库结构和Excel数据导入功能。 ## 一、SQL数据库设计 ```sql -- 创建数据库 CREATE DATABASE HospitalPharmacyDB; GO USE HospitalPharmacyDB; GO -- 1. 药品分类表 CREATE TABLE DrugCategory ( CategoryID INT PRIMARY KEY IDENTITY(1,1), CategoryCode VARCHAR(20) NOT NULL UNIQUE, CategoryName NVARCHAR(50) NOT NULL, ParentCategoryID INT NULL, Description NVARCHAR(200), FOREIGN KEY (ParentCategoryID) REFERENCES DrugCategory(CategoryID) ); -- 2. 药品信息表 CREATE TABLE DrugInfo ( DrugID INT PRIMARY KEY IDENTITY(1,1), DrugCode VARCHAR(30) NOT NULL UNIQUE, DrugName NVARCHAR(100) NOT NULL, GenericName NVARCHAR(100), CategoryID INT NOT NULL, Specification NVARCHAR(100), Unit NVARCHAR(20), Manufacturer NVARCHAR(100), ApprovalNumber VARCHAR(50), PurchasePrice DECIMAL(10,2), RetailPrice DECIMAL(10,2), StockAlert INT DEFAULT 100, StorageCondition NVARCHAR(50), IsPrescription BIT DEFAULT 1, Status INT DEFAULT 1, -- 1:正常 0:停用 CreateTime DATETIME DEFAULT GETDATE(), FOREIGN KEY (CategoryID) REFERENCES DrugCategory(CategoryID) ); -- 3. 供应商表 CREATE TABLE Supplier ( SupplierID INT PRIMARY KEY IDENTITY(1,1), SupplierCode VARCHAR(20) NOT NULL UNIQUE, SupplierName NVARCHAR(100) NOT NULL, ContactPerson NVARCHAR(50), Phone VARCHAR(20), Address NVARCHAR(200), Email VARCHAR(50), TaxNumber VARCHAR(30), BankAccount VARCHAR(50), Status INT DEFAULT 1, CreateTime DATETIME DEFAULT GETDATE() ); -- 4. 入库记录表 CREATE TABLE StockIn ( InID INT PRIMARY KEY IDENTITY(1,1), InNo VARCHAR(30) NOT NULL UNIQUE, SupplierID INT NOT NULL, InType INT NOT NULL, -- 1:采购入库 2:退货入库 3:调拨入库 InDate DATETIME DEFAULT GETDATE(), TotalAmount DECIMAL(12,2), OperatorID INT, Remark NVARCHAR(500), CreateTime DATETIME DEFAULT GETDATE(), FOREIGN KEY (SupplierID) REFERENCES Supplier(SupplierID) ); -- 5. 入库明细表 CREATE TABLE StockInDetail ( DetailID INT PRIMARY KEY IDENTITY(1,1), InID INT NOT NULL, DrugID INT NOT NULL, BatchNo VARCHAR(30), ProductionDate DATE, ExpiryDate DATE, Quantity INT NOT NULL, UnitPrice DECIMAL(10,2), Amount DECIMAL(12,2), StorageLocation NVARCHAR(50), Remark NVARCHAR(200), FOREIGN KEY (InID) REFERENCES StockIn(InID), FOREIGN KEY (DrugID) REFERENCES DrugInfo(DrugID) ); -- 6. 出库记录表 CREATE TABLE StockOut ( OutID INT PRIMARY KEY IDENTITY(1,1), OutNo VARCHAR(30) NOT NULL UNIQUE, OutType INT NOT NULL, -- 1:门诊发药 2:住院发药 3:科室领用 4:退货出库 DepartmentID INT, PatientID VARCHAR(30), OutDate DATETIME DEFAULT GETDATE(), TotalAmount DECIMAL(12,2), OperatorID INT, Remark NVARCHAR(500), CreateTime DATETIME DEFAULT GETDATE() ); -- 7. 出库明细表 CREATE TABLE StockOutDetail ( DetailID INT PRIMARY KEY IDENTITY(1,1), OutID INT NOT NULL, DrugID INT NOT NULL, BatchNo VARCHAR(30), Quantity INT NOT NULL, UnitPrice DECIMAL(10,2), Amount DECIMAL(12,2), Remark NVARCHAR(200), FOREIGN KEY (OutID) REFERENCES StockOut(OutID), FOREIGN KEY (DrugID) REFERENCES DrugInfo(DrugID) ); -- 8. 库存表 CREATE TABLE Inventory ( InventoryID INT PRIMARY KEY IDENTITY(1,1), DrugID INT NOT NULL, BatchNo VARCHAR(30), ProductionDate DATE, ExpiryDate DATE, CurrentStock INT DEFAULT 0, AvailableStock INT DEFAULT 0, LockedStock INT DEFAULT 0, StorageLocation NVARCHAR(50), LastUpdateTime DATETIME DEFAULT GETDATE(), UNIQUE (DrugID, BatchNo), FOREIGN KEY (DrugID) REFERENCES DrugInfo(DrugID) ); -- 9. 盘点记录表 CREATE TABLE InventoryCheck ( CheckID INT PRIMARY KEY IDENTITY(1,1), CheckNo VARCHAR(30) NOT NULL UNIQUE, CheckDate DATETIME DEFAULT GETDATE(), CheckPerson NVARCHAR(50), TotalDrugs INT, ProfitAmount DECIMAL(12,2), LossAmount DECIMAL(12,2), Status INT DEFAULT 0, -- 0:未审核 1:已审核 Remark NVARCHAR(500), CreateTime DATETIME DEFAULT GETDATE() ); -- 10. 盘点明细表 CREATE TABLE InventoryCheckDetail ( DetailID INT PRIMARY KEY IDENTITY(1,1), CheckID INT NOT NULL, DrugID INT NOT NULL, BatchNo VARCHAR(30), BookStock INT, ActualStock INT, Difference INT, UnitPrice DECIMAL(10,2), Amount DECIMAL(12,2), Reason NVARCHAR(200), FOREIGN KEY (CheckID) REFERENCES InventoryCheck(CheckID), FOREIGN KEY (DrugID) REFERENCES DrugInfo(DrugID) ); -- 11. 操作员表 CREATE TABLE Operator ( OperatorID INT PRIMARY KEY IDENTITY(1,1), OperatorCode VARCHAR(20) NOT NULL UNIQUE, OperatorName NVARCHAR(50) NOT NULL, Password VARCHAR(50) NOT NULL, Department NVARCHAR(50), Role INT DEFAULT 1, -- 1:操作员 2:管理员 Status INT DEFAULT 1, LastLoginTime DATETIME, CreateTime DATETIME DEFAULT GETDATE() ); -- 12. 系统日志表 CREATE TABLE SystemLog ( LogID INT PRIMARY KEY IDENTITY(1,1), OperatorID INT, LogType INT, -- 1:登录 2:操作 3:错误 LogContent NVARCHAR(500), IPAddress VARCHAR(20), LogTime DATETIME DEFAULT GETDATE() ); -- 插入初始数据 INSERT INTO Operator (OperatorCode, OperatorName, Password, Role) VALUES ('admin', '系统管理员', '123456', 2); INSERT INTO Operator (OperatorCode, OperatorName, Password) VALUES ('user001', '张三', '123456'); INSERT INTO DrugCategory (CategoryCode, CategoryName) VALUES ('CAT001', '抗生素类'), ('CAT002', '心脑血管类'), ('CAT003', '消化系统类'), ('CAT004', '呼吸系统类'), ('CAT005', '神经系统类'); INSERT INTO Supplier (SupplierCode, SupplierName, ContactPerson, Phone) VALUES ('SUP001', '华北制药有限公司', '李经理', '13800138001'), ('SUP002', '华东医药集团', '王总', '13900139002'), ('SUP003', '华南生物制药', '张主任', '13700137003'); ``` ## 二、C# Windows窗体应用主要代码 ### 1. 数据库连接类 (DBHelper.cs) ```csharp using System; using System.Data; using System.Data.SqlClient; using System.Configuration; namespace HospitalPharmacySystem { public class DBHelper { private static string connectionString = ConfigurationManager.ConnectionStrings["PharmacyDB"].ConnectionString; public static SqlConnection GetConnection() { return new SqlConnection(connectionString); } public static DataTable ExecuteQuery(string sql, params SqlParameter[] parameters) { using (SqlConnection conn = GetConnection()) { conn.Open(); using (SqlCommand cmd = new SqlCommand(sql, conn)) { if (parameters != null) cmd.Parameters.AddRange(parameters); SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); adapter.Fill(dt); return dt; } } } public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters) { using (SqlConnection conn = GetConnection()) { conn.Open(); using (SqlCommand cmd = new SqlCommand(sql, conn)) { if (parameters != null) cmd.Parameters.AddRange(parameters); return cmd.ExecuteNonQuery(); } } } public static object ExecuteScalar(string sql, params SqlParameter[] parameters) { using (SqlConnection conn = GetConnection()) { conn.Open(); using (SqlCommand cmd = new SqlCommand(sql, conn)) { if (parameters != null) cmd.Parameters.AddRange(parameters); return cmd.ExecuteScalar(); } } } } } ``` ### 2. 主窗体 (MainForm.cs) ```csharp using System; using System.Windows.Forms; namespace HospitalPharmacySystem { public partial class MainForm : Form { private string currentUser; public MainForm(string userName) { InitializeComponent(); currentUser = userName; lblWelcome.Text = $"欢迎您，{userName}"; InitializeMenu(); } private void InitializeMenu() { // 主菜单项 ToolStripMenuItem fileMenu = new ToolStripMenuItem("文件(&F)"); ToolStripMenuItem drugMenu = new ToolStripMenuItem("药品管理(&D)"); ToolStripMenuItem stockMenu = new ToolStripMenuItem("库存管理(&S)"); ToolStripMenuItem purchaseMenu = new ToolStripMenuItem("采购管理(&P)"); ToolStripMenuItem outMenu = new ToolStripMenuItem("出库管理(&O)"); ToolStripMenuItem reportMenu = new ToolStripMenuItem("报表统计(&R)"); ToolStripMenuItem systemMenu = new ToolStripMenuItem("系统(&Y)"); // 药品管理子菜单 drugMenu.DropDownItems.Add("药品信息管理", null, (s, e) => OpenForm(new DrugInfoForm())); drugMenu.DropDownItems.Add("药品分类管理", null, (s, e) => OpenForm(new CategoryForm())); drugMenu.DropDownItems.Add("供应商管理", null, (s, e) => OpenForm(new SupplierForm())); drugMenu.DropDownItems.Add("-"); drugMenu.DropDownItems.Add("药品查询", null, (s, e) => OpenForm(new DrugQueryForm())); // 库存管理子菜单 stockMenu.DropDownItems.Add("入库管理", null, (s, e) => OpenForm(new StockInForm())); stockMenu.DropDownItems.Add("库存查询", null, (s, e) => OpenForm(new InventoryQueryForm())); stockMenu.DropDownItems.Add("库存盘点", null, (s, e) => OpenForm(new InventoryCheckForm())); stockMenu.DropDownItems.Add("库存预警", null, (s, e) => OpenForm(new StockAlertForm())); stockMenu.DropDownItems.Add("-"); stockMenu.DropDownItems.Add("库存调拨", null, (s, e) => OpenForm(new StockTransferForm())); // 采购管理子菜单 purchaseMenu.DropDownItems.Add("采购计划", null, (s, e) => OpenForm(new PurchasePlanForm())); purchaseMenu.DropDownItems.Add("采购订单", null, (s, e) => OpenForm(new PurchaseOrderForm())); purchaseMenu.DropDownItems.Add("采购入库", null, (s, e) => OpenForm(new PurchaseInForm())); // 出库管理子菜单 outMenu.DropDownItems.Add("门诊发药", null, (s, e) => OpenForm(new OutpatientDispenseForm())); outMenu.DropDownItems.Add("住院发药", null, (s, e) => OpenForm(new InpatientDispenseForm())); outMenu.DropDownItems.Add("科室领用", null, (s, e) => OpenForm(new DepartmentUseForm())); outMenu.DropDownItems.Add("出库查询", null, (s, e) => OpenForm(new StockOutQueryForm())); // 报表统计子菜单 reportMenu.DropDownItems.Add("库存报表", null, (s, e) => OpenForm(new InventoryReportForm())); reportMenu.DropDownItems.Add("出入库报表", null, (s, e) => OpenForm(new InOutReportForm())); reportMenu.DropDownItems.Add("药品销售统计", null, (s, e) => OpenForm(new DrugSalesForm())); reportMenu.DropDownItems.Add("供应商统计", null, (s, e) => OpenForm(new SupplierReportForm())); // 系统子菜单 systemMenu.DropDownItems.Add("用户管理", null, (s, e) => OpenForm(new UserManageForm())); systemMenu.DropDownItems.Add("修改密码", null, (s, e) => OpenForm(new ChangePasswordForm())); systemMenu.DropDownItems.Add("-"); systemMenu.DropDownItems.Add("数据备份", null, (s, e) => BackupDatabase()); systemMenu.DropDownItems.Add("数据恢复", null, (s, e) => RestoreDatabase()); systemMenu.DropDownItems.Add("-"); systemMenu.DropDownItems.Add("退出系统", null, (s, e) => Application.Exit()); // 添加到菜单栏 menuStrip1.Items.AddRange(new ToolStripItem[] { fileMenu, drugMenu, stockMenu, purchaseMenu, outMenu, reportMenu, systemMenu }); } private void OpenForm(Form form) { form.MdiParent = this; form.WindowState = FormWindowState.Maximized; form.Show(); } private void BackupDatabase() { SaveFileDialog saveFileDialog = new SaveFileDialog(); saveFileDialog.Filter = "备份文件 (*.bak)|*.bak"; if (saveFileDialog.ShowDialog() == DialogResult.OK) { string backupPath = saveFileDialog.FileName; string sql = $"BACKUP DATABASE HospitalPharmacyDB TO DISK = '{backupPath}'"; try { DBHelper.ExecuteNonQuery(sql); MessageBox.Show("数据库备份成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show($"备份失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); } } } private void RestoreDatabase() { OpenFileDialog openFileDialog = new OpenFileDialog(); openFileDialog.Filter = "备份文件 (*.bak)|*.bak"; if (openFileDialog.ShowDialog() == DialogResult.OK) { string restorePath = openFileDialog.FileName; if (MessageBox.Show("确定要恢复数据库吗？此操作将覆盖现有数据！", "警告", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.Yes) { string sql = $"USE master; RESTORE DATABASE HospitalPharmacyDB FROM DISK = '{restorePath}' WITH REPLACE;"; try { DBHelper.ExecuteNonQuery(sql); MessageBox.Show("数据库恢复成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { MessageBox.Show($"恢复失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); } } } } } } ``` ### 3. 药品信息管理窗体 (DrugInfoForm.cs) ```csharp using System; using System.Data; using System.Windows.Forms; using System.Data.SqlClient; namespace HospitalPharmacySystem { public partial class DrugInfoForm : Form { private DataTable drugData; private int currentPage = 1; private int pageSize = 20; public DrugInfoForm() { InitializeComponent(); LoadCategories(); LoadDrugData(); } private void LoadCategories() { string sql = "SELECT CategoryID, CategoryName FROM DrugCategory ORDER BY CategoryCode"; DataTable dt = DBHelper.ExecuteQuery(sql); cmbCategory.DataSource = dt; cmbCategory.DisplayMember = "CategoryName"; cmbCategory.ValueMember = "CategoryID"; cmbCategory.SelectedIndex = -1; } private void LoadDrugData() { string whereClause = BuildWhereClause(); string sql = $@"SELECT d.*, c.CategoryName FROM DrugInfo d LEFT JOIN DrugCategory c ON d.CategoryID = c.CategoryID WHERE 1=1 {whereClause} ORDER BY d.DrugCode OFFSET {(currentPage - 1) * pageSize} ROWS FETCH NEXT {pageSize} ROWS ONLY"; drugData = DBHelper.ExecuteQuery(sql); dgvDrugs.DataSource = drugData; // 设置列标题 dgvDrugs.Columns["DrugID"].Visible = false; dgvDrugs.Columns["DrugCode"].HeaderText = "药品编码"; dgvDrugs.Columns["DrugName"].HeaderText = "药品名称"; dgvDrugs.Columns["CategoryName"].HeaderText = "分类"; dgvDrugs.Columns["Specification"].HeaderText = "规格"; dgvDrugs.Columns["Unit"].HeaderText = "单位"; dgvDrugs.Columns["Manufacturer"].HeaderText = "生产厂家"; dgvDrugs.Columns["PurchasePrice"].HeaderText = "采购价"; dgvDrugs.Columns["RetailPrice"].HeaderText = "零售价"; dgvDrugs.Columns["StockAlert"].HeaderText = "库存预警"; dgvDrugs.Columns["Status"].HeaderText = "状态"; } private string BuildWhereClause() { string where = ""; if (!string.IsNullOrWhiteSpace(txtDrugCode.Text)) where += $" AND d.DrugCode LIKE '%{txtDrugCode.Text}%'"; if (!string.IsNullOrWhiteSpace(txtDrugName.Text)) where += $" AND d.DrugName LIKE '%{txtDrugName.Text}