Hi.Ltd.SqlServer 专题 · 第 6 期NuGetHi.Ltd.SqlServer 2026.9.7.1642目标框架net462/net481/net6.0-windows7.0/net8.0-windows7.0依赖Hi.LtdSuccessedContent、Microsoft.Data.SqlClient读者工控上位机 / HMI / 部署前体检 / 换线脚本 / 复杂报表 / 维护窗索引 / MES 线边库本期目标架构 CRUDExistsSchema/CreateSchema/DropSchema清空对象前勿删勿删系统架构。同步SchemaSynchronizer/Factory.Synchronize/Factory.InitialSqlServerSchemaSyncOptions/SchemaSyncReport含Ensure*、SyncTableSchemaWhenExists、ValidateModelsBeforeSync、StandardBuiltInTables等。只读顾问SchemaStartupAdvisor——Diagnose/DiagnoseAllProfiles/DiagnoseSingle/BuildOverallRecommendation/QueryExists/QueryTvpColumns不改库。自定义 SQLIExecute/IExecuteAsync——ExecuteScalar*→ResultobjectExecuteNonQuery*→ResultintExecuteReader*→ResultListDictionarystring,objectTransaction*/NoneDataBase*族CommandTimeout默认约30秒configureCommand绑参数。可组合事务ExecuteInTransaction。索引维护IIndexMaintenance——RebuildIndex/ReorganizeIndex/UpdateStatistics。全程SuccessedContent禁止拼接用户输入禁止把顾问建议无评审当生产 ALTER。读完你应能开机跑 Diagnose 写日志维护窗按报告开关做一次 Synchronize用参数化 ExecuteReader 出班次汇总在事务里组合多步在维护窗重建关键索引。适用范围该用慎用产线部署前 / 开机自检 / 多 Profile 漂移排查把顾问建议无评审直接当生产SyncTableSchemaWhenExists复杂报表、调用既有 MES 存储过程、换线脚本拼接扫码/用户输入造成注入维护窗口 REBUILD/REORGANIZE/UPDATE STATISTICS业务高峰对大表 REBUILD 且无 ONLINE 评估CancellationToken取消长查询把ExecuteReader全表拉进内存当「流式」——实际物化为 ListNoneDataBase*查实例级信息DropSchema 时架构内仍有对象删系统架构外层 Result 成功但 Report 内失败当告警把 README 裸int/object返回抄进产线——DLL 是 Result怎么用最小 → 进阶0. 连接与引用usingSystem;usingSystem.Collections.Generic;usingSystem.Text;usingSystem.Threading;usingSystem.Threading.Tasks;usingMicrosoft.Data.SqlClient;usingHi.Ltd;usingHi.Ltd.SqlServer;varsqlFactory.SqlServer;// 或 SqlEntities.CreateNew() 后配置连接Resultboolconnsql.Connection();if(!conn.Successed||!conn.Content)thrownewInvalidOperationException(conn.Message);1. Schema CRUDResultboolexsql.ExistsSchema(LineA_Mes,mes);if(!ex.Successed){ex.Message.Error();return;}if(!ex.Content){Resultboolcrsql.CreateSchema(mes);if(!cr.Successed||!cr.Content)$创建架构失败:{cr.Message}.Error();}// DropSchema先清空架构内对象勿删系统架构dbo 等// Resultbool dp sql.DropSchema(mes);ExistsSchema(database, schema)CreateSchema(schemaName)DropSchema(schemaName)。删除敏感影响该架构下表/视图/过程等。不存在时 Drop 仍可能成功幂等——审计仍要前后 Exists。2. SchemaSyncOptions 与 Factory.SynchronizevaroptnewSchemaSyncOptions{EnsureDatabasetrue,LazyCreateDatabasetrue,// 仅在确有结构步骤时才建库EnsureTabletrue,SyncTableSchemaWhenExistsfalse,// 生产默认 false维护窗评审后再开EnsureTableTypetrue,EnsureMergeProceduretrue,EnsureForeignKeystrue,ValidateModelsBeforeSynctrue,StandardBuiltInTablesStandardTable.IcsDevice|StandardTable.IcsDataPoint,AutoOnlyfalse// IncludeOnlyTableNames / ExcludeTableNames / TypeFilter / IncludeNamespacePrefixes// ColumnTypeMismatch列型/可空不一致策略};ResultSchemaSyncReportsyncFactory.Synchronize(Factory.SqlServer,opt);if(!sync.Successed){$同步调用失败:{sync.Message}.Error();return;}SchemaSyncReportreportsync.Content;// 外层成功不代表报告内无告警逐条看 SchemaSyncLogEntry / Successed 字段以类型为准亦可用Factory.InitialSqlServer(assembly, opt)做程序集初始化。相关SchemaSyncLogEntry/SchemaSyncLogLevel、SchemaColumnDriftChecker、ModelValidator/ModelValidationReport、ColumnTypeMismatchMode。铁律SyncTableSchemaWhenExiststrue会走 Alter 纠偏存在类型收缩丢精度风险——仅维护窗、有备份、有评审。3. SchemaStartupAdvisor强烈建议开箱调用SchemaAdvisorReportadvSchemaStartupAdvisor.Diagnose(Factory.SqlServer,database:LineA_Mes);Console.WriteLine(adv);if(adv.HasIssues)$存在 Schema 问题建议:{adv.OverallRecommendation}.Warn();stringadviceSchemaStartupAdvisor.BuildOverallRecommendation(adv,hasAutoModels:true,syncTableSchemaWhenExists:false);advice.Info();// 多产线 Profileforeach(varkvinSchemaStartupAdvisor.DiagnoseAllProfiles())$Profile{kv.Key}:{kv.Value}.Info();SchemaAdvisorItemoneSchemaStartupAdvisor.DiagnoseSingle(Factory.SqlServer,typeof(WorkOrderRow),LineA_Mes);// one.TableFullName / TvpExists / TvpDifferences / ProcedureExists / Suggestions// 辅助varcolsSchemaStartupAdvisor.QueryTvpColumns(Factory.SqlServer,LineA_Mes,dbo,WorkOrderType);XML 明确Diagnose 只读 INFORMATION_SCHEMA / sys不修改任何对象Synchronize 才真正 CREATE/ALTER。适合健康检查端点、开机面板、CI。4. IExecute / IExecuteAsyncResult 族勿抄 README 裸类型方法族返回ExecuteScalar*ResultobjectExecuteNonQuery*ResultintExecuteReader*ResultListDictionarystring,objectTransaction*/NoneDataBase*同上 Result 族*AsyncTaskResult…CancellationTokenCommandTimeout属性默认约 30 秒重载可传commandTimeoutReader/Scalar 可有ActionSqlCommand configureCommand。asyncTaskShowShiftYieldAsync(ISqlServersql,stringshiftCode,CancellationTokenct){ResultListDictionarystring,objectrowsawaitsql.ExecuteReaderAsync(database:LineA_Mes,commandText:SELECT SkuCode, SUM(ActualQty) AS Qty FROM dbo.WorkOrder WHERE ShiftCode shift GROUP BY SkuCode,commandTimeout:60,configureCommand:cmd{cmd.Parameters.AddWithValue(shift,shiftCode);},cancellationToken:ct);if(!rows.Successed){rows.Message.Error();return;}foreach(varrowinrows.Content)Console.WriteLine(${row[SkuCode]}{row[Qty]});}Resultobjectcntsql.ExecuteScalar(LineA_Mes,SELECT COUNT(1) FROM dbo.WorkOrder WHERE Statuss,commandTimeout:30,configureCommand:cmdcmd.Parameters.AddWithValue(s,0));if(cnt.Successed)Console.WriteLine(cnt.Content);Resultintnsql.ExecuteNonQuery(LineA_Mes,UPDATE dbo.WorkOrder SET Statusns WHERE OrderCodecode,30);// 推荐同样用 configureCommand 绑参的重载若当前重载无 configure改用带 Action 的 Reader/Scalar 模式或参数化拼接禁止NoneDataBaseExecuteScalar(SELECT VERSION)等无当前业务库上下文适实例级查询。TransactionExecute*单命令事务包装失败回滚见文档约定。Scalar 注意XML 称主要用于判断等场景返回最大字符数约 2033——大文本别靠 Scalar。5. ExecuteInTransaction可组合多步Resultboolrsql.ExecuteInTransaction((conn,tran){using(varcmd1conn.CreateCommand()){cmd1.Transactiontran;cmd1.CommandTextUPDATE dbo.WorkOrder SET Status1 WHERE Idid;cmd1.Parameters.AddWithValue(id,1001);cmd1.ExecuteNonQuery();}using(varcmd2conn.CreateCommand()){cmd2.Transactiontran;cmd2.CommandTextINSERT INTO dbo.WorkOrderLog(WorkOrderId,Message,Ts) VALUES(id,m,SYSUTCDATETIME());cmd2.Parameters.AddWithValue(id,1001);cmd2.Parameters.AddWithValue(m,完工);cmd2.ExecuteNonQuery();}returnnewResultbool(true);// 以 Hi.Ltd.Result 构造为准失败返回 Successedfalse},database:LineA_Mes);if(!r.Successed||!r.Content)$事务失败(已回滚):{r.Message}.Error();回调应返回ResultT失败时事务已回滚XML。不要在回调里另开连接不顾tran。6. 索引维护 IIndexMaintenanceResultintrosql.ReorganizeIndex(LineA_Mes,dbo,WorkOrder,IX_WorkOrder_OrderCode);Resultintrbsql.RebuildIndex(LineA_Mes,dbo,WorkOrder,IX_WorkOrder_OrderCode);Resultintstsql.UpdateStatistics(LineA_Mes,dbo,WorkOrder,);// 空整表统计标识符会校验。通用CREATE INDEX需自写 SQL 经ExecuteNonQuery已知能力边界。高峰 REBUILD 评估 ONLINE 与窗口碎片不高优先 REORGANIZE。手写对照优势手写本期开机手写一堆存在性 SQLSchemaStartupAdvisor.Diagnose散落脚本建表建类型建过程Synchronize Options 开关ADO 样板 忘判返回Execute*统一 Result手写 SqlTransaction 易漏回滚ExecuteInTransaction索引维护脚本各厂不一Rebuild/Reorganize/UpdateStatistics仍须自控注入、物化内存、危险 Sync 开关、索引窗口、权限分离。完整样例开机体检 参数化汇总 事务完工usingSystem.Text;usingSystem.Threading;usingSystem.Threading.Tasks;usingMicrosoft.Data.SqlClient;usingHi.Ltd;usingHi.Ltd.SqlServer;staticclassBootAndShift{publicstaticvoidBootCheck(){varreportSchemaStartupAdvisor.Diagnose(Factory.SqlServer,LineA_Mes);varsbnewStringBuilder();sb.AppendLine(report?.ToString());sb.AppendLine(SchemaStartupAdvisor.BuildOverallRecommendation(report,hasAutoModels:true,syncTableSchemaWhenExists:false));sb.ToString().Info();foreach(varkvinSchemaStartupAdvisor.DiagnoseAllProfiles())($Profile{kv.Key}:{kv.Value}).Info();}publicstaticasyncTaskShiftReportAsync(ISqlServersql,stringshift,CancellationTokenct){varrowsawaitsql.ExecuteReaderAsync(LineA_Mes,SELECT SkuCode, SUM(ActualQty) AS Qty FROM dbo.WorkOrder WHERE ShiftCodeshift GROUP BY SkuCode,60,cmdcmd.Parameters.AddWithValue(shift,shift),ct);if(!rows.Successed)thrownewInvalidOperationException(rows.Message);foreach(varrowinrows.Content)Console.WriteLine(${row[SkuCode]}{row[Qty]});}publicstaticvoidCompleteOrder(ISqlServersql,intid){varrsql.ExecuteInTransaction((conn,tran){usingvaruconn.CreateCommand();u.Transactiontran;u.CommandTextUPDATE dbo.WorkOrder SET Status1 WHERE Idid AND Status0;u.Parameters.AddWithValue(id,id);if(u.ExecuteNonQuery()!1)returnnewResultbool(false);// 构造方式以实际 Result API 为准usingvarlogconn.CreateCommand();log.Transactiontran;log.CommandTextINSERT INTO dbo.WorkOrderLog(WorkOrderId,Message,Ts) VALUES(id,N完工,SYSUTCDATETIME());log.Parameters.AddWithValue(id,id);log.ExecuteNonQuery();returnnewResultbool(true);},LineA_Mes);if(!r.Successed||!r.Content)thrownewInvalidOperationException(r.Message);}}避坑清单顾问说要 Sync → 生产直接开SyncTableSchemaWhenExists丢精度。外层 Result 成功但报告有问题 → 当告警。README 把 Execute* 当裸返回 → 按 Successed/Content。字符串拼 SQL → 扫码内容禁止直接拼。Reader 物化 List → 超大结果改分页或自写流式。DropSchema 残留对象 / 删系统架构。高峰 REBUILD 大表。事务回调另开连接不挂 transaction。Scalar 当大文本通道约 2033 限制。超时不调commandTimeout、不优化 SQL。取消不传CancellationToken。把 Diagnose 当会改库。索引名/表名未校验的手工拼接维护 API 会校验标识符自写 SQL 自行负责。权限运行账号与部署账号混用。多 Profile 只检默认库。异常点与应对现象应对模型校验失败读ModelValidationReport补 Key/TableTVP 列与表不一致第5期 UpdateTableType UpdateProcedure超时加大超时优化索引取消传入 CT权限授予对应 DDL/DML兼容级别错误如扩展事件号展开 Result / ResultDiagnostics 排障FAQQAdvisor 会改库吗A不会。只读体检变更走 Synchronize/Alter/手工脚本。Q和 ITools 比AITools 单表白名单只读统计第7期复杂 SQL 用本期 Execute。QTransactionExecute 与 ExecuteInTransactionA前者偏单命令事务包装后者回调内组合多命令。多步业务优先后者。QCREATE INDEX 有封装吗A维护面是 Rebuild/Reorganize/UpdateStatistics创建索引用 ExecuteNonQuery 自写。下期预告第7期ITools、内置表与视图全文