C# WinForm SQL Server 2019 学生管理系统3层架构重构与性能优化实战当你的学生管理系统开始出现响应迟缓、代码维护困难时或许正是考虑架构升级的最佳时机。本文将带你从零开始将一个传统的单层WinForm应用重构为规范的三层架构并针对SQL Server 2019进行深度性能调优。1. 三层架构设计原理与项目结构三层架构的核心在于职责分离。我们将其划分为表示层(UI)处理用户交互业务逻辑层(BLL)执行业务规则数据访问层(DAL)与数据库交互典型项目结构如下StudentManagement/ ├── StudentManagement.UI/ # WinForm项目 ├── StudentManagement.BLL/ # 类库项目 ├── StudentManagement.DAL/ # 类库项目 ├── StudentManagement.Model/ # 实体类库 └── StudentManagement.Tests/ # 单元测试项目关键代码结构对比原始结构三层架构Form直接调用SQLUI→BLL→DAL业务逻辑散落在窗体事件中集中到BLL服务类硬编码SQL字符串参数化查询2. 数据访问层(DAL)的现代化改造2.1 通用数据访问类设计public class SqlHelper { private static readonly string connStr ConfigurationManager.ConnectionStrings[StudentDB].ConnectionString; public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters) { using (var conn new SqlConnection(connStr)) { conn.Open(); using (var cmd new SqlCommand(sql, conn)) { cmd.Parameters.AddRange(parameters); return cmd.ExecuteNonQuery(); } } } public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters) { using (var conn new SqlConnection(connStr)) { conn.Open(); using (var cmd new SqlCommand(sql, conn)) { cmd.Parameters.AddRange(parameters); using (var adapter new SqlDataAdapter(cmd)) { var dt new DataTable(); adapter.Fill(dt); return dt; } } } } }2.2 实体类与DAL的映射// 学生实体 public class Student { public string Sno { get; set; } public string Sname { get; set; } public string Ssex { get; set; } public int Sage { get; set; } public string Sdept { get; set; } } // 学生DAL public class StudentDal { public int AddStudent(Student student) { const string sql INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES(Sno,Sname,Ssex,Sage,Sdept); var parameters new[] { new SqlParameter(Sno, student.Sno), new SqlParameter(Sname, student.Sname), new SqlParameter(Ssex, student.Ssex), new SqlParameter(Sage, student.Sage), new SqlParameter(Sdept, student.Sdept) }; return SqlHelper.ExecuteNonQuery(sql, parameters); } }3. 业务逻辑层(BLL)的最佳实践BLL层应当包含数据验证规则业务处理流程事务控制异常处理典型学生管理业务类public class StudentService { private readonly StudentDal _studentDal new StudentDal(); public bool AddStudent(Student student) { // 业务规则验证 if (string.IsNullOrWhiteSpace(student.Sno)) throw new ArgumentException(学号不能为空); if (student.Sage 15 || student.Sage 30) throw new ArgumentException(年龄范围应在15-30岁之间); // 检查学号是否已存在 if (_studentDal.Exists(student.Sno)) throw new InvalidOperationException(该学号已存在); // 执行添加 return _studentDal.AddStudent(student) 0; } public ListStudent GetStudentsByDepartment(string department) { if (string.IsNullOrWhiteSpace(department)) return _studentDal.GetAll(); return _studentDal.GetByDepartment(department); } }4. SQL Server 2019性能优化策略4.1 索引优化方案关键表建议索引-- 学生表索引 CREATE INDEX IX_Student_Sno ON Student(Sno); CREATE INDEX IX_Student_Sdept ON Student(Sdept); -- 成绩表复合索引 CREATE INDEX IX_SC_Sno_Cno ON SC(Sno, Cno);4.2 查询性能对比测试优化前后查询对比10000条数据查询类型原始方案优化方案性能提升按学号查询120ms5ms24倍按院系统计300ms20ms15倍多表联查800ms50ms16倍4.3 使用内存优化表对于高频更新的表考虑使用内存优化表-- 启用数据库内存优化 ALTER DATABASE StudentDB ADD FILEGROUP StudentDB_MemoryOpt CONTAINS MEMORY_OPTIMIZED_DATA; ALTER DATABASE StudentDB ADD FILE (NAMEStudentDB_MemoryOpt, FILENAMED:\Data\StudentDB_MemoryOpt.ndf) TO FILEGROUP StudentDB_MemoryOpt; -- 创建内存优化版学生表 CREATE TABLE dbo.Student_InMemory ( Sno CHAR(9) PRIMARY KEY NONCLUSTERED, Sname NVARCHAR(20) COLLATE Chinese_PRC_CI_AS NOT NULL, Ssex CHAR(2) COLLATE Chinese_PRC_CI_AS, Sage INT, Sdept NVARCHAR(20) COLLATE Chinese_PRC_CI_AS, INDEX IX_Student_Sdept HASH(Sdept) WITH (BUCKET_COUNT 10000) ) WITH (MEMORY_OPTIMIZED ON, DURABILITY SCHEMA_AND_DATA);5. WinForm表示层优化技巧5.1 数据绑定最佳实践// 优化后的数据绑定方式 private void LoadStudents() { var students _studentService.GetAllStudents(); // 使用BindingList支持排序和过滤 var bindingList new BindingListStudent(students); var source new BindingSource(bindingList, null); dataGridView1.DataSource source; // 优化列显示 dataGridView1.AutoGenerateColumns false; dataGridView1.Columns.Clear(); dataGridView1.Columns.Add(new DataGridViewTextBoxColumn { DataPropertyName Sno, HeaderText 学号, Width 100 }); // 添加其他列... }5.2 异步加载模式private async void Form_Load(object sender, EventArgs e) { try { loadingPanel.Visible true; // 异步加载数据 var students await Task.Run(() _studentService.GetAllStudents()); dataGridView1.DataSource students; } catch (Exception ex) { MessageBox.Show($加载失败: {ex.Message}); } finally { loadingPanel.Visible false; } }6. 高级架构扩展6.1 依赖注入实现// 安装NuGet包Microsoft.Extensions.DependencyInjection public static class ServiceProvider { private static IServiceProvider _provider; public static void Configure() { var services new ServiceCollection(); services.AddTransientIStudentDal, StudentDal(); services.AddTransientIStudentService, StudentService(); services.AddTransientMainForm(); _provider services.BuildServiceProvider(); } public static T GetServiceT() _provider.GetServiceT(); } // 在Program.cs中 static class Program { [STAThread] static void Main() { ServiceProvider.Configure(); Application.EnableVisualStyles(); Application.SetCompatibleTextRenderingDefault(false); Application.Run(ServiceProvider.GetServiceMainForm()); } }6.2 缓存策略实现public class CachedStudentService : IStudentService { private readonly IStudentService _decorated; private readonly MemoryCache _cache MemoryCache.Default; public CachedStudentService(IStudentService decorated) { _decorated decorated; } public Student GetStudent(string sno) { string cacheKey $student_{sno}; if (_cache.Contains(cacheKey)) return (Student)_cache.Get(cacheKey); var student _decorated.GetStudent(sno); if (student ! null) { _cache.Add(cacheKey, student, DateTimeOffset.Now.AddMinutes(10)); } return student; } }7. 安全加固方案7.1 SQL注入防护// 不安全的写法 string sql $SELECT * FROM Student WHERE Sno{txtSno.Text}; // 安全的参数化查询 string sql SELECT * FROM Student WHERE SnoSno; var parameter new SqlParameter(Sno, txtSno.Text);7.2 连接字符串保护!-- 在app.config中加密连接字符串 -- connectionStrings configProtectionProviderDataProtectionConfigurationProvider EncryptedData !-- 加密后的连接字符串 -- /EncryptedData /connectionStrings8. 部署与监控8.1 性能计数器监控// 创建自定义性能计数器 if (!PerformanceCounterCategory.Exists(StudentApp)) { var counters new CounterCreationDataCollection { new CounterCreationData { CounterName StudentsQueried, CounterHelp Number of student queries, CounterType PerformanceCounterType.NumberOfItems32 } }; PerformanceCounterCategory.Create(StudentApp, Student application counters, PerformanceCounterCategoryType.SingleInstance, counters); } // 使用计数器 private static readonly PerformanceCounter StudentQueryCounter new PerformanceCounter(StudentApp, StudentsQueried, false); public ListStudent GetStudents() { StudentQueryCounter.Increment(); // 查询逻辑... }8.2 数据库连接优化配置!-- 在app.config中配置连接池 -- connectionStrings add nameStudentDB connectionStringData Source.;Initial CatalogStudentDB; Integrated SecurityTrue;Max Pool Size200;Min Pool Size20; Connect Timeout30; providerNameSystem.Data.SqlClient / /connectionStrings9. 现代化改造路线图第一阶段完成基础三层架构重构第二阶段引入依赖注入和单元测试第三阶段实现前端与后端分离可选WinUI或Blazor第四阶段微服务化改造针对大型系统graph TD A[单层架构] -- B[三层架构] B -- C[依赖注入] C -- D[领域驱动设计] D -- E[微服务架构]10. 常见问题解决方案问题1跨层传递大量参数繁琐方案使用DTO(Data Transfer Object)模式public class StudentDto { public string Sno { get; set; } public string Name { get; set; } // 其他属性... public static StudentDto FromEntity(Student student) { return new StudentDto { Sno student.Sno, Name student.Sname // 映射其他属性 }; } }问题2BLL层过于臃肿方案引入领域驱动设计(DDD)概念public class Student : IAggregateRoot { public string Sno { get; private set; } public string Name { get; private set; } // 领域行为 public void ChangeName(string newName) { if (string.IsNullOrWhiteSpace(newName)) throw new ArgumentException(姓名不能为空); Name newName; } }问题3批量操作性能差方案使用表值参数(TVP)批量操作-- 创建表类型 CREATE TYPE StudentTableType AS TABLE ( Sno CHAR(9), Sname NVARCHAR(20), Ssex CHAR(2), Sage INT, Sdept NVARCHAR(20) );public void BatchAddStudents(ListStudent students) { using (var conn new SqlConnection(connStr)) { conn.Open(); using (var cmd new SqlCommand(sp_BatchAddStudents, conn)) { cmd.CommandType CommandType.StoredProcedure; var tvp new DataTable(); tvp.Columns.Add(Sno, typeof(string)); tvp.Columns.Add(Sname, typeof(string)); // 添加其他列... foreach (var student in students) { tvp.Rows.Add(student.Sno, student.Sname, /* 其他属性 */); } var param new SqlParameter(StudentList, tvp) { TypeName StudentTableType, SqlDbType SqlDbType.Structured }; cmd.Parameters.Add(param); cmd.ExecuteNonQuery(); } } }11. 前沿技术集成11.1 使用Dapper优化数据访问// 安装NuGet包Dapper public class DapperStudentDal { private readonly string _connStr; public DapperStudentDal(string connStr) { _connStr connStr; } public Student GetStudent(string sno) { using (var conn new SqlConnection(_connStr)) { return conn.QueryFirstOrDefaultStudent( SELECT * FROM Student WHERE SnoSno, new { Sno sno }); } } public ListStudent GetStudentsByDepartment(string dept) { using (var conn new SqlConnection(_connStr)) { return conn.QueryStudent( SELECT * FROM Student WHERE SdeptDept, new { Dept dept }).ToList(); } } }11.2 使用Entity Framework Core作为备选方案// 安装NuGet包Microsoft.EntityFrameworkCore.SqlServer public class StudentContext : DbContext { public DbSetStudent Students { get; set; } public DbSetCourse Courses { get; set; } public DbSetSC SCs { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder options) options.UseSqlServer(YourConnectionString); } // 使用示例 using (var context new StudentContext()) { var csStudents context.Students .Where(s s.Sdept CS) .OrderBy(s s.Sname) .ToList(); }12. 性能监控与调优实战12.1 使用SQL Server Profiler捕获问题查询典型性能问题模式缺少索引的频繁查询N1查询问题不合理的锁等待12.2 查询执行计划分析关键指标检查高成本的表扫描(Table Scan)键查找(Key Lookup)排序(Sort)操作预估行数与实际行数差异-- 获取查询计划 SET STATISTICS PROFILE ON; SELECT * FROM Student WHERE Sdept CS; SET STATISTICS PROFILE OFF;12.3 使用扩展事件(XEvents)监控-- 创建扩展事件会话监控长时间运行查询 CREATE EVENT SESSION [LongRunningQueries] ON SERVER ADD EVENT sqlserver.sql_statement_completed ( WHERE ([duration] 1000000) -- 超过1秒的查询 ) ADD TARGET package0.event_file(SET filenameNLongRunningQueries); GO ALTER EVENT SESSION [LongRunningQueries] ON SERVER STATE START;13. 现代化日志记录方案13.1 使用Serilog进行结构化日志记录// 安装NuGet包Serilog, Serilog.Sinks.File public static ILogger CreateLogger() { return new LoggerConfiguration() .MinimumLevel.Information() .WriteTo.File(logs/student-.log, rollingInterval: RollingInterval.Day, outputTemplate: {Timestamp:yyyy-MM-dd HH:mm:ss} [{Level}] {Message}{NewLine}{Exception}) .CreateLogger(); } // 使用示例 private static readonly ILogger Logger LogHelper.CreateLogger(); try { _studentService.AddStudent(student); } catch (Exception ex) { Logger.Error(ex, 添加学生失败 {Student}, student); throw; }13.2 日志分析建议关键日志事件应包括关键业务操作性能敏感操作耗时异常情况重要状态变更14. 自动化测试策略14.1 单元测试示例[TestClass] public class StudentServiceTests { private IStudentService _studentService; private MockIStudentDal _mockDal; [TestInitialize] public void Setup() { _mockDal new MockIStudentDal(); _studentService new StudentService(_mockDal.Object); } [TestMethod] public void AddStudent_Should_Reject_EmptySno() { // Arrange var student new Student { Sno , Sname 测试 }; // Act Assert Assert.ThrowsExceptionArgumentException(() _studentService.AddStudent(student)); } [TestMethod] public void GetStudentsByDepartment_Should_Filter_Correctly() { // Arrange var testData new ListStudent { new Student { Sno 001, Sdept CS }, new Student { Sno 002, Sdept MA } }; _mockDal.Setup(d d.GetAll()).Returns(testData); // Act var result _studentService.GetStudentsByDepartment(CS); // Assert Assert.AreEqual(1, result.Count); Assert.AreEqual(001, result[0].Sno); } }14.2 集成测试方案[TestClass] public class StudentDalIntegrationTests { private string _connStr; [TestInitialize] public void Setup() { _connStr ConfigurationManager.ConnectionStrings[TestDB].ConnectionString; ResetTestDatabase(); } [TestMethod] public void AddStudent_Should_Work() { // Arrange var dal new StudentDal(_connStr); var student new Student { Sno TEST001, Sname 集成测试, Ssex 男, Sage 20, Sdept CS }; // Act var result dal.AddStudent(student); // Assert Assert.IsTrue(result 0); var added dal.GetStudent(TEST001); Assert.IsNotNull(added); Assert.AreEqual(集成测试, added.Sname); } private void ResetTestDatabase() { // 初始化测试数据库的代码... } }15. 容器化部署方案15.1 Docker化SQL Server# docker-compose.yml version: 3.8 services: sqlserver: image: mcr.microsoft.com/mssql/server:2019-latest environment: SA_PASSWORD: YourStrongPassw0rd ACCEPT_EULA: Y ports: - 1433:1433 volumes: - sqlvolume:/var/opt/mssql volumes: sqlvolume:15.2 WinForm应用的容器化考虑虽然传统WinForm应用不完全适合容器化但可以考虑将后端服务容器化使用Windows容器打包WinForm应用或迁移到跨平台方案如Avalonia16. 架构演进建议随着系统规模扩大可考虑以下演进路径模块化按功能拆分为独立模块CQRS分离读写模型事件驱动引入领域事件微服务按业务边界拆分服务// 简单领域事件实现示例 public interface IDomainEvent { } public class StudentCreatedEvent : IDomainEvent { public Student Student { get; } public StudentCreatedEvent(Student student) { Student student; } } public class StudentService { private readonly IEventDispatcher _dispatcher; public StudentService(IEventDispatcher dispatcher) { _dispatcher dispatcher; } public void AddStudent(Student student) { // 添加学生逻辑... // 发布事件 _dispatcher.Dispatch(new StudentCreatedEvent(student)); } }17. 代码质量保障17.1 静态代码分析推荐工具SonarQube全面的代码质量检测Roslyn分析器实时代码检查ArchUnitNET架构规则验证17.2 代码审查要点重点关注层间耦合度异常处理完整性性能敏感操作安全漏洞18. 用户界面现代化18.1 WinForm UI升级方案使用现代控件库DevExpressTelerik UISyncfusionMVVM模式应用使用MVVM Light Toolkit数据绑定优化// 简单MVVM实现 public class StudentViewModel : INotifyPropertyChanged { private string _sno; public string Sno { get _sno; set { _sno value; OnPropertyChanged(); } } public event PropertyChangedEventHandler PropertyChanged; protected virtual void OnPropertyChanged([CallerMemberName] string propertyName null) { PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName)); } }18.2 响应式布局技巧private void Form_SizeChanged(object sender, EventArgs e) { // 控制DataGridView随窗体缩放 dataGridView1.Width this.ClientSize.Width - 40; dataGridView1.Height this.ClientSize.Height - 100; // 按钮位置调整 btnAdd.Location new Point( this.ClientSize.Width - btnAdd.Width - 20, this.ClientSize.Height - btnAdd.Height - 20); }19. 数据库迁移策略19.1 版本化迁移方案推荐工具DbUp.NET专用数据库迁移工具Flyway支持SQL Server的Java方案EF Core迁移如果使用EF Core// DbUp示例 var upgrader DeployChanges.To .SqlDatabase(connectionString) .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly()) .LogToConsole() .Build(); var result upgrader.PerformUpgrade(); if (!result.Successful) { Console.ForegroundColor ConsoleColor.Red; Console.WriteLine(result.Error); Console.ResetColor(); return -1; } Console.ForegroundColor ConsoleColor.Green; Console.WriteLine(Success!); Console.ResetColor();19.2 数据迁移注意事项停机计划评估是否需要停机回滚方案准备数据回滚脚本性能影响大数据量迁移策略验证方案数据一致性检查20. 持续集成与交付20.1 CI/CD流水线设计典型阶段构建恢复NuGet包编译解决方案测试运行单元测试和集成测试代码分析执行静态代码分析部署数据库迁移和应用部署# Azure Pipelines示例 trigger: - main pool: vmImage: windows-latest variables: solution: **/*.sln buildPlatform: Any CPU buildConfiguration: Release steps: - task: NuGetToolInstaller1 - task: NuGetCommand2 inputs: restoreSolution: $(solution) - task: VSBuild1 inputs: solution: $(solution) msbuildArgs: /p:DeployOnBuildtrue /p:WebPublishMethodPackage /p:PackageAsSingleFiletrue /p:SkipInvalidConfigurationstrue /p:PackageLocation$(build.artifactStagingDirectory) platform: $(buildPlatform) configuration: $(buildConfiguration) - task: VSTest2 inputs: platform: $(buildPlatform) configuration: $(buildConfiguration) - task: PublishBuildArtifacts1 inputs: PathtoPublish: $(Build.ArtifactStagingDirectory) ArtifactName: drop publishLocation: Container20.2 环境配置管理推荐模式环境变量区分开发/测试/生产配置转换使用configTransform密钥管理使用Azure Key Vault等方案!-- app.Debug.config -- connectionStrings add nameStudentDB connectionStringData Source(localdb)\MSSQLLocalDB;Initial CatalogStudentDB_Dev;Integrated SecurityTrue xdt:TransformSetAttributes xdt:LocatorMatch(name)/ /connectionStrings21. 技术债务管理21.1 技术债务识别常见技术债务类型架构债务不合理的层间依赖测试债务缺乏自动化测试代码债务重复代码、过长方法文档债务缺少系统文档21.2 偿还策略优先级评估基于影响和修复成本增量偿还每次迭代解决部分问题预防机制代码审查和静态分析// 技术债务标记示例 [TechnicalDebt(2024-12-31, 需要重构为异步模式, DebtSeverity.Medium)] public ListStudent GetAllStudents() { // 同步实现... }22. 扩展性设计22.1 插件架构实现// 插件接口定义 public interface IStudentPlugin { string Name { get; } void Execute(Student student); } // 插件加载器 public class PluginLoader { public IEnumerableIStudentPlugin LoadPlugins(string path) { var plugins new ListIStudentPlugin(); foreach (var dll in Directory.GetFiles(path, *.dll)) { var assembly Assembly.LoadFrom(dll); foreach (var type in assembly.GetTypes()) { if (typeof(IStudentPlugin).IsAssignableFrom(type)) { var plugin (IStudentPlugin)Activator.CreateInstance(type); plugins.Add(plugin); } } } return plugins; } } // 使用示例 var plugins _pluginLoader.LoadPlugins(Plugins); foreach (var plugin in plugins) { plugin.Execute(selectedStudent); }22.2 功能开关实现public class FeatureToggle { private static readonly Dictionarystring, bool _features new Dictionarystring, bool { [AdvancedReporting] false, [NewSearchAlgorithm] true }; public static bool IsEnabled(string feature) { return _features.TryGetValue(feature, out var enabled) enabled; } } // 使用示例 if (FeatureToggle.IsEnabled(NewSearchAlgorithm)) { // 新算法实现 } else { // 旧算法实现 }23. 国际化与本地化23.1 多语言支持方案// 资源文件结构 Resources/ ├── Strings.resx # 默认语言 └── Strings.zh-CN.resx # 中文资源 // 动态切换语言 public static void SetCulture(CultureInfo culture) { Thread.CurrentThread.CurrentCulture culture; Thread.CurrentThread.CurrentUICulture culture; // 刷新已打开的窗体 foreach (Form form in Application.OpenForms) { ComponentResourceManager resources new ComponentResourceManager(form.GetType()); resources.ApplyResources(form, $this); ApplyResourcesToControls(form.Controls, resources); } } private static void ApplyResourcesToControls(Control.ControlCollection controls, ComponentResourceManager resources) { foreach (Control ctrl in controls) { resources.ApplyResources(ctrl, ctrl.Name); ApplyResourcesToControls(ctrl.Controls, resources); } }23.2 本地化最佳实践分离可翻译文本所有UI字符串放入资源文件文化敏感格式日期、数字、货币格式化布局适应性考虑文本长度差异本地化测试验证各语言下的显示效果24. 无障碍访问支持24.1 WinForm无障碍特性// 设置控件无障碍属性 button1.AccessibleName 添加学生按钮; button1.AccessibleDescription 点击此按钮添加新学生记录; // 支持键盘导航 this.KeyPreview true; this.KeyDown (s, e) { if (e.KeyCode Keys.F1) { // 显示帮助 } }; // 高对比度支持 protected override void OnSystemColorsChanged(EventArgs e) { base.OnSystemColorsChanged(e); if (SystemInformation.HighContrast) { ApplyHighContrastTheme(); } }24.2 测试验证方法键盘导航测试仅用键盘完成所有操作屏幕阅读器测试使用NVDA等工具验证颜色对比检查确保足够的对比度缩放测试高DPI下的显示效果25. 安全加固进阶25.1 数据加密方案// 敏感数据加密 public static string Encrypt(string plainText) { using (Aes aes Aes.Create()) { aes.Key GetEncryptionKey(); aes.IV GetInitializationVector(); ICryptoTransform encryptor aes.CreateEncryptor(aes.Key, aes.IV); using (MemoryStream ms new MemoryStream()) { using (CryptoStream cs new CryptoStream(ms, encryptor, CryptoStreamMode.Write)) { using (StreamWriter sw new StreamWriter(cs)) { sw.Write(plainText); } return Convert.ToBase64String(ms.ToArray()); } } } } // 连接字符串保护 public static string GetConnectionString() { var encrypted ConfigurationManager.ConnectionStrings[StudentDB].ConnectionString; return Decrypt(encrypted); }25.2 安全审计实现-- 创建审计表 CREATE TABLE SecurityAudit ( Id INT IDENTITY PRIMARY KEY, EventTime DATETIME NOT NULL DEFAULT GETDATE(), UserName NVARCHAR(100) NOT NULL, ActionType NVARCHAR(50) NOT NULL, TableName NVARCHAR(100), RecordId NVARCHAR(100), OldValues NVARCHAR(MAX), NewValues NVARCHAR(MAX) ); -- 创建审计触发器 CREATE TRIGGER tr_Student_Audit ON Student AFTER INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON; DECLARE ActionType NVARCHAR(10); IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) SET ActionType UPDATE; ELSE IF EXISTS (SELECT * FROM inserted) SET ActionType INSERT; ELSE SET ActionType DELETE; INSERT INTO SecurityAudit(UserName, ActionType, TableName, RecordId, OldValues, NewValues) SELECT SYSTEM_USER, ActionType, Student, ISNULL(i.Sno, d.Sno), (SELECT d.* FOR JSON PATH), (SELECT i.* FOR JSON PATH) FROM inserted i FULL OUTER JOIN deleted d ON i.Sno d.Sno; END;26. 性能优化深度策略26.1 查询优化进阶技巧参数嗅探问题解决-- 使用本地变量避免参数嗅探问题 DECLARE Dept NVARCHAR(20) CS; SELECT * FROM Student WHERE Sdept Dept;查询存储提示-- 强制使用特定索引 SELECT * FROM Student WITH (INDEX(IX_Student_Sdept)) WHERE Sdept CS;统计信息更新-- 手动更新统计信息 UPDATE STATISTICS Student WITH FULLSCAN;26.2 应用程序缓存策略// 使用MemoryCache实现本地缓存 public class StudentCache { private static readonly MemoryCache _cache new MemoryCache(StudentCache); private static readonly TimeSpan _defaultExpiration TimeSpan.FromMinutes(10); public static Student GetStudent(string sno) { if (_cache.TryGetValue(sno, out Student student)) return student; student _studentDal.GetStudent(sno); if (student ! null) { _cache.Set(sno, student, _defaultExpiration); } return student; } public static void Invalidate(string sno) { _cache.Remove(sno); } }27. 大数据量处理方案27.1 分页查询优化// 使用ROW_NUMBER()实现高效分页 public ListStudent GetStudents(int pageIndex, int pageSize, out int totalCount) { const string sql SELECT COUNT(*) FROM Student; WITH StudentCTE AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY Sno) AS RowNum FROM Student ) SELECT * FROM StudentCTE WHERE RowNum BETWEEN Start AND End; using (var conn new SqlConnection(_