尧图网站建设 
示例工程数据库教程后端【免费下载链接】sql-server-samplesAzure Data SQL Samples - Official Microsoft GitHub Repository containing code samples for SQL Server, Azure SQL, Azure Synapse, and Azure SQL Edge项目地址https://gitcode.com/gh_mirrors/sq/sql-server-samples点击查看免费下载本篇技术指南围绕 sql-server-samples 仓库中的 belgrade-product-catalog-demo 示例展开讲解如何用 ASP.NET Core Web API 构建一个产品目录Product Catalog应用并在一套数据库上同时落地 SQL Server 2016/Azure SQL Database 的四大核心能力JSON 函数、时态表Temporal Tables、动态数据脱敏Data Masking与行级安全Row-Level Security。读完本文你将掌握完整的建库脚本、JSON 存取存储过程、时态表回溯与恢复、脱敏与多租户数据隔离的实现细节以及对应的 C# 端到端调用方式可直接迁移到自己的多租户商品管理或 SaaS 业务中。关于本示例适用环境与能力矩阵项目说明适用版本SQL Server 2016或更高版本、Azure SQL Database关键技术JSON 函数、时态表、数据脱敏、行级安全RLS编程语言C#ASP.NET Core、HTML/JavaScript、Transact-SQL作者Jovan Popovic示例的四大技术主题与业务落点JSON 功能用于实现 Web API为前端 HTML 应用提供结构灵活的产品数据时态表展示数据变更轨迹、按时间点回溯快照、恢复产品历史版本数据脱敏对公司的 Email、Phone、Postcode 字段按规则打码非授权用户看到的是掩码后的值行级安全按当前登录公司CompanyID隔离产品数据不同公司只能看到自己的产品。项目整体架构前端使用 JQuery / JQuery UI 库配合 JQuery DataTable 组件展示与操作表格数据服务端使用 ASP.NET Core Web API通过 JSON 函数将数据库查询结果直接格式化为 JSON 响应并将客户端提交的 JSON 请求反序列化后写入数据库SQL Server 时态表负责追踪历史、按时间点展示过去快照、恢复产品旧版本数据脱敏隐藏公司敏感信息行级安全隔离各公司数据。代码位于 samples/demos/belgrade-product-catalog-demo数据库脚本全部集中在 sql-scripts 目录编号即执行顺序。环境准备软件前置条件SQL Server 2016或更高版本或一个 Azure SQL Database 实例ASP.NET Core 1.0 SDK或更高版本。可选Visual Studio 2015 Update 3或更高版本或 Visual Studio Code 编辑器。Azure 前置条件拥有创建 Azure SQL Database 的权限若选择云端部署。运行本示例完整步骤第 1 步创建数据库并设置兼容级别在 SQL Server 2016 或 Azure SQL Database 上创建数据库并将兼容级别设置为130对应 SQL Server 2016确保 JSON、时态表等新功能可用ALTER DATABASE ProductCatalog SET COMPATIBILITY_LEVEL 130;第 2 步执行初始化脚本使用 SQL Server Management StudioSSMS或 Visual Studio / SQL Server Data Tools 连接到数据库按顺序执行以下脚本位于 sql-scripts 目录脚本作用1 setup.sql创建登录名/用户/角色创建并填充 Product、Company 表创建 JSON 存取存储过程3 setup-temporal.sql创建历史表 History.Product 并开启系统版本控制5>dotnet restore # 还原全部 NuGet 包 dotnet build # 编译项目备选方案用 Visual Studio 2015 Update 3或更高打开根目录的ProductCatalog.xproj在项目上右键选择 “Restore Packages” 还原依赖。第 4 步修改连接字符串定位项目中的 appsettings.json将连接字符串改为指向你的数据库。默认值指向本机 SQLEXPRESS 实例的 ProductCatalog 数据库并使用集成安全Windows 身份验证{ ConnectionStrings: { BelgradeDemo: Server.\\SQLEXPRESS;DatabaseProductCatalog;Integrated Securitytrue;Application NameBelgrade } }修改后重新构建CtrlShiftB、右键项目 Build或命令行dotnet build。第 5 步运行应用Visual Studio 中按F5或CtrlF5或在项目根目录命令行执行dotnet run打开/index.html即可体验完整功能查看全部产品、增删改产品点击绿色 () 按钮查看某行数据的所有历史变更通过滑块JQuery UI Slider向左拖动回溯到过去某个时间点从历史中恢复某个版本编辑任意产品并验证公司信息已被脱敏以不同公司身份登录验证只能看到当前公司的产品数据。数据库层实现详解源码级JSON 功能从建表、导入到存储过程1 setup.sql 定义了两个包含 JSON 列的核心表。Product表把Data、Tags两列声明为nvarchar用于存放半结构化 JSONCREATE TABLE Product ( ProductID int DEFAULT (NEXT VALUE FOR ProductId) PRIMARY KEY, Name nvarchar(50) NOT NULL, Color nvarchar(15) NULL, Size nvarchar(5) NULL, Price money NOT NULL, Quantity int NULL, CompanyID int, Data nvarchar(4000), Tags nvarchar(4000), DateModified datetime2(0) NOT NULL DEFAULT (GETUTCDATE()) )关键点SQL Server 不要求 JSON 列使用专用类型普通nvarchar即可存放 JSON 文本ProductID通过序列ProductId起始值 29自动生成。随后用OPENJSON把一组 JSON 数组批量导入表INSERT INTO Product (ProductID, Name, Color, Size, Price, Quantity, CompanyID, Data, Tags, DateModified) SELECT ProductID, Name, Color, Size, Price, Quantity, CompanyID, Data, Tags, DateModified FROM OPENJSON (products) WITH( ProductID int, Name nvarchar(50), Color nvarchar(15), Size nvarchar(5), Price money, Quantity int, CompanyID int, Data nvarchar(MAX) AS JSON, Tags nvarchar(MAX) AS JSON, DateModified datetime2(0) )注意AS JSON关键字它告诉OPENJSON该列本身是 JSON 对象/数组而非标量从而保留嵌套结构Data中的ManufacturingCost、MadeInTags数组等。Company表同样通过OPENJSON导入三家示例公司A Datum Corporation、Contoso, Ltd.、Consolidated Messenger。同脚本还创建了两个面向 JSON 输入的存储过程Web API 正是通过它们完成产品的新增与更新新增产品InsertProductFromJson接收完整 JSON 对象用OPENJSON ... WITH提取字段后插入OUTPUT INSERTED.ProductID返回新行 ID。其中Name、Price使用了Nstrict $.Name/Nstrict $.Price路径表达式——strict表示 JSON 中缺少该属性时报错保证关键字段必填CREATE PROCEDURE [dbo].InsertProductFromJson) AS BEGIN INSERT INTO dbo.Product(Name, Color, Size, Price, Quantity, CompanyID, Data, Tags) OUTPUT INSERTED.ProductID SELECT Name, Color, Size, Price, Quantity, CompanyID, Data, Tags FROM OPENJSON(ProductJson) WITH ( Name nvarchar(100) Nstrict $.Name, Color nvarchar(30), Size nvarchar(10), Price money Nstrict $.Price, Quantity int, CompanyID int, Data nvarchar(max) AS JSON, Tags nvarchar(max) AS JSON) as json END更新产品UpdateProductFromJson接收 ProductID 与 JSON更新标量列并用ISNULL(json.Data, dbo.Product.Data)保持“未提交字段保留原值”的语义即 JSON 中未携带的Data/Tags不会被清空CREATE PROCEDURE dbo.UpdateProductFromJson(ProductID int, ProductJson NVARCHAR(MAX)) AS BEGIN UPDATE dbo.Product SET Name json.Name, Color json.Color, Size json.Size, Price json.Price, Quantity json.Quantity, Data ISNULL(json.Data, dbo.Product.Data), Tags ISNULL(json.Tags, dbo.Product.Tags) FROM OPENJSON(ProductJson) WITH ( Name nvarchar(100) Nstrict $.Name, Color nvarchar(30), Size nvarchar(10), Price money Nstrict $.Price, Quantity int, Data nvarchar(max) AS JSON, Tags nvarchar(max) AS JSON) as json WHERE dbo.Product.ProductID ProductID ENDJSON 查询与操作演示2 json.sql 系统演示了 SQL Server 2016 的 JSON 全家族函数1. 用FOR JSON PATH把查询结果直接格式化为 JSON 响应Web API 的数据库侧输出select ProductID, Name, Color, Size, Price, Quantity, JSON_VALUE(Data, $.MadeIn) as MadeIn, JSON_QUERY(Tags) as Tags from Product FOR JSON PATHJSON_VALUE提取 JSON 中的标量值如$.MadeInJSON_QUERY原样返回 JSON 对象/数组片段如Tags数组避免被转义成字符串单行输出用FOR JSON PATH, WITHOUT_ARRAY_WRAPPER去掉外层数组方括号。2. 在查询任意位置使用 JSON 数据——筛选、分组、聚合、排序均可select JSON_VALUE(Data, $.Type) as Type, Color, AVG( cast(JSON_VALUE(Data, $.ManufacturingCost) as float) ) as Cost from Product group by JSON_VALUE(Data, $.Type), Color having JSON_VALUE(Data, $.Type) is not null order by JSON_VALUE(Data, $.Type)3. 用JSON_MODIFY就地更新 JSON 字段——修改标量值或向数组追加元素update Product set Data JSON_MODIFY(Data, $.ManufacturingCost, 10), Tags JSON_MODIFY(Tags, append $, new) where ProductID 164.OPENJSON把 JSON 转换为关系行集单对象转一行、数组转多行、嵌套路径$.Data.Type提取深层字段甚至可以像查表一样对 JSON 数组做GROUP BY/HAVING聚合分析。5.CROSS APPLY OPENJSON展开 JSON 数组做存在性检索——找出所有带promo标签的产品select ProductID, Name, Color, Size, Price, Quantity, Tags from Product CROSS APPLY OPENJSON(Tags) where value promo6. 规避FOR JSON PATH对嵌套 JSON 的转义问题直接select ... Data, Tags时JSON 列会被当作字符串转义改用JSON_QUERY(Data) as Data, JSON_QUERY(Tags) as Tags后嵌套对象才会作为真正的 JSON 结构输出。时态表历史追踪、回溯与恢复3 setup-temporal.sql 将Product表升级为系统版本控制时态表并预置了 2015-05 至 2016-02 期间的多版本历史数据通过OPENJSON导入History.Product。开启时态表的三个关键动作-- 1. 添加有效时间列默认最大日期表示“至今仍有效” ALTER TABLE Product ADD ValidTo datetime2(0) NOT NULL CONSTRAINT Product_ValidTo_EndTime DEFAULT (9999-12-31 23:59:59) -- 2. 指定时间段Period ALTER TABLE Product ADD PERIOD FOR SYSTEM_TIME (DateModified, ValidTo) -- 3. 开启系统版本控制并绑定历史表 ALTER TABLE Product SET (SYSTEM_VERSIONING ON (HISTORY_TABLE History.Product))时态表要求两个datetime2列分别代表版本生效起始时间这里复用已有的DateModified与失效时间ValidTo系统会在每次 UPDATE/DELETE 时自动把旧版本写入History.Product。脚本还封装了三个应用直接调用的数据库对象版本差异函数dbo.diff_Product(id, date)把当前版本与过去某时间点版本分别转成 JSON再对OPENJSON展开的键值集合做 JOIN返回所有发生变化的列新值 v1、旧值 v2create function dbo.diff_Product (id int, date datetime2(0)) returns table as return ( select v1.[key] as [Column], v1.value as v1, v2.value as v2 from OPENJSON( (select * from Product where ProductID id FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) v1 join OPENJSON( (select * from Product for system_time as of date where ProductID id FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) v2 on v1.[key] v2.[key] where v1.value v2.value )dbo.GetProducts/dbo.GetProductsAsOf分别返回“当前产品 全部历史”和“某时间点的产品 相对当前版本的差异”并用FOR JSON AUTO, ROOT(data)直接产出前端可用的 JSONcreate procedure dbo.GetProductsAsOf (date datetime2) as begin select Product.ProductID, Product.Name, Product.Color, Product.Price, Product.Quantity, JSON_VALUE(Product.Data, $.MadeIn) as MadeIn, JSON_QUERY(Product.Tags) as Tags, Product.DateModified, ProductDifferences.* from Product FOR SYSTEM_TIME AS OF date outer apply dbo.diff_Product(Product.ProductID, date) as ProductDifferences order by Product.ProductID asc FOR JSON AUTO, ROOT(data) enddbo.RestoreProduct(productid, date)用MERGE把指定时间点的历史版本覆盖写回当前表命中则更新、未命中则插入实现“一键恢复旧版本”create procedure dbo.RestoreProduct (productid int, date datetime2) as begin MERGE Product USING ( select ProductID, Name, Color, Size, Price, Quantity, CompanyID, Tags, Data from Product FOR SYSTEM_TIME AS OF date where ProductID productid) AS Restored (ProductID, Name, Color, Size, Price, Quantity, CompanyID, Tags, Data) ON (Product.ProductID Restored.ProductID) WHEN MATCHED THEN update set Name restored.Name, Color restored.Color, Size restored.Size, Price restored.Price, Quantity restored.Quantity, CompanyId restored.CompanyID, Data restored.Data, Tags restored.Tags WHEN NOT MATCHED THEN insert (ProductID, Name, Color, Size, Price, Quantity, CompanyID, Data, Tags) values (ProductID, Name, Color, Size, Price, Quantity, CompanyID, Data, Tags); end4 temporal.sql 提供完整的验证脚本核心用法一览-- 回溯查看 2015-07-28 13:20:00 时刻的产品列表 select ProductID, Name, Color, Size, Price, Quantity from Product FOR SYSTEM_TIME AS OF 2015-07-28 13:20:00; -- 差异产品 17 当前版本与历史版本的差异列 select * from dbo.diff_Product(17, 2015-07-28 13:20:00); -- 全量历史产品 17 的完整变更轨迹 select ProductID, Name, Color, Size, Price, Quantity, DateModified, ValidTo from Product FOR SYSTEM_TIME ALL where productid 17 order by DateModified desc; -- 恢复用 2015-11-07 03:40:09 的版本覆盖当前版本 exec RestoreProduct 17, 2015-11-07 03:40:09;脚本最后还演示了配合窗口函数LAG/LEAD的进阶分析——找出价格突变的“尖峰”记录上一价格与下一价格相同、且与当前价格偏差 ≥10%可用于价格异常监控。动态数据脱敏Email、Phone、Postcode 打码5>ALTER TABLE Company ALTER COLUMN Email ADD MASKED WITH (FUNCTION email()) ALTER TABLE Company ALTER COLUMN Phone ADD MASKED WITH (FUNCTION partial(5,XXXXXXX,2)) ALTER TABLE Company ALTER COLUMN Postcode ADD MASKED WITH (FUNCTION default())三种脱敏函数的效果函数说明效果示例email()邮箱脱敏仅保留首字母与域名msavicdatum.com→mXXXdatum.compartial(5,XXXXXXX,2)保留前 5 个字符与后 2 个字符中间用掩码替换(381) 555-7639→(381) XXXXXXX39default()按类型默认掩码字符串列显示XXXX46077→XXXX脱敏是“声明式”的未授权用户无UNMASK权限执行SELECT * FROM Company时SSMS/驱动层自动返回掩码值而授权用户看到明文。脚本注释中给出了验证方式——用只具备基本权限的WebUser身份执行查询EXECUTE AS USER WebUser; SELECT * FROM Company; REVERT;行级安全按公司隔离产品数据6 rls.sql 实现多租户隔离每个会话通过SESSION_CONTEXT携带当前公司 ID安全策略据此过滤可见行。第 1 步创建安全谓词函数必须SCHEMABINDING。CompanyID -1视为管理员可访问全部公司数据CREATE FUNCTION dbo.pUserCanAccessCompanyData(CompanyID int) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT 1 as canAccess WHERE SESSION_CONTEXT(NCompanyID) -1 OR CAST(SESSION_CONTEXT(NCompanyID) as int) CompanyID)第 2 步创建安全策略并添加过滤谓词作用在Product上CREATE SECURITY POLICY dbo.ClientAccessPolicy ADD FILTER PREDICATE dbo.pUserCanAccessCompanyData(CompanyID) ON dbo.Product WITH (StateON)第 3 步策略扩展——对历史表加过滤谓词防止通过时态回溯跨公司查看、对 Product 加阻止谓词BLOCK PREDICATE禁止写入他公司数据、对 Company 加过滤谓词公司列表也只显示自己-- 历史数据同样隔离 ALTER SECURITY POLICY dbo.ClientAccessPolicy ADD FILTER PREDICATE dbo.pUserCanAccessCompanyData(CompanyID) ON History.Product -- 阻止跨公司写入 ALTER SECURITY POLICY dbo.ClientAccessPolicy ADD BLOCK PREDICATE dbo.pUserCanAccessCompanyData(CompanyID) ON dbo.Product -- 公司表仅可见当前公司 ALTER SECURITY POLICY dbo.ClientAccessPolicy ADD FILTER PREDICATE dbo.pUserCanAccessCompanyData(CompanyID) ON dbo.Company验证方式脚本注释中给出设置会话上下文后SELECT * FROM Product会随 CompanyID 变化返回不同子集777等不存在的公司则看不到任何数据EXEC sp_set_session_context CompanyID, 1 SELECT * FROM Product服务端实现ASP.NET Core Web API 如何衔接数据库数据访问与依赖注入Startup.cs 展示了两种数据访问方式并存Entity Framework Coreservices.AddDbContextProductCatalogContext(...)用于 ProductCatalogController.cs 中的常规 CRUDAdd、GetAll、GetByIdBelgrade SqlClient 的IQueryPipe/ICommand直接执行存储过程并以流式方式把FOR JSON结果写到响应配合AddRls(CompanyID, () GetCompanyIdFromSession(sp))可从 HTTP Session 读取 CompanyID 写入SESSION_CONTEXT与服务端 RLS 策略联动代码中该调用被注释作为接入示例保留。会话与中间件管线app.UseSession()启用 SessionUseStaticFiles()提供 wwwroot 下的静态页面UseMvc默认路由指向ProductCatalog控制器的Index动作。日志落库Serilog NLogStartup构造函数中配置了 Serilog 的 MSSqlServer sink把应用日志写入数据库Logs表由 1 setup.sql 创建的Logs表结构——Message、Level、EventTime、LogEvent四列并通过ColumnOptions移除默认的 Id/Properties/MessageTemplate/Exception 列、仅保留 JSON 格式的LogEvent日志表与配置保持一致var columnOptions new ColumnOptions(); columnOptions.Store.Remove(StandardColumn.Id); columnOptions.Store.Remove(StandardColumn.Properties); columnOptions.Store.Remove(StandardColumn.MessageTemplate); columnOptions.Store.Remove(StandardColumn.Exception); columnOptions.TimeStamp.ColumnName EventTime; columnOptions.Store.Add(StandardColumn.LogEvent); Log.Logger new LoggerConfiguration() .WriteTo.MSSqlServer(Configuration[ConnectionStrings:BelgradeDemo], Logs, columnOptions: columnOptions, autoCreateSqlTable: false) .CreateLogger();同时nlog.config与NLog.Extensions.Logging也被引入作为备选日志通道构造函数中保留了相关注释说明。日志写入的是同一BelgradeDemo连接字符串指向的数据库。控制器与前端页面ProductCatalogController.cs 提供Index返回产品列表视图、AddPOST 新增并重定向、GetAllJSON 列表、GetById按 ID 查询未找到返回 404等动作Models.cs 中ProductCatalogContext映射 EF 实体。前端页面位于 wwwrootindex.html产品列表 CRUD 历史差异展示temporal.html时态回溯滑块与版本恢复界面dashboard.html、report-multibar.html、report-pie.html基于 NVD3 的报表页对应 JS 逻辑分布在 products.js、products-crud.js、products-temporal.js、rls.js、dashboard.js 中。进阶脚本同库内的扩展演示除四大主题外sql-scripts 还附带了一批编号脚本可在此产品目录场景上继续探索 SQL Server 更多能力xtp.sql在xtp架构下创建内存优化表MEMORY_OPTIMIZEDON与原生编译存储过程NATIVE_COMPILATIONATOMIC WITH (TRANSACTION ISOLATION LEVEL SNAPSHOT, LANGUAGE Nus_english)演示 JSON 存取过程在高吞吐场景的版本bcp.sql创建 Orders/OrderLines 表并演示BULK INSERT配合EXTERNAL DATA SOURCEAzure Blob Storage从云端批量加载数据支持指定FORMATFILE与TABLOCKstring-agg.sql用STRING_AGG拼接每家公司的产品名称列表结合STRING_ESCAPE(...,json)手工构造合法 JSON 数组以及基于订单关联的“买了也买”Customers Also Buy推荐查询A1 fraud-detection.sql、A2 compare-products.sql、A3 setup-temporal-3-part-name.sql欺诈检测、产品对比与时态表三段式命名相关扩展bcp 目录下提供 bcp 导出/导入订单数据的批处理脚本含 README 与 bcp.tt 模板test/ProductListComparison.jmxJMeter 压测脚本可用于对比不同数据访问路径的产品列表查询性能。设计说明与限制README 明确声明本示例的目标是演示 SQL Server/Azure SQL Database 功能的最小可运行代码并非通用 Web 开发架构最佳实践模板。它刻意保持了精简——只包含构建 REST API 所需的最少代码没有使用 Repository 等分层模式应用通过 ASP.NET Core 内置的依赖注入DI机制组织服务但这并非必需。你可以轻松修改代码以适配自有应用架构。相关资源本示例依赖的组件官方资料与仓库内关联内容前端组件JQuery DataTables 行展开row_details 示例、JQuery UI Slider仓库内相关示例JSON 功能合集见 samples/features/json时态表产品目录见 samples/features/temporal/product-catalogSQL 图Graph与更多演示见 samples/demos。常见问题排查要点兼容级别未设置为 130JSON 函数、时态表等语法会报错或不生效务必先执行ALTER DATABASE ... SET COMPATIBILITY_LEVEL 130连接字符串指向错误实例默认是.\SQLEXPRESS Windows 集成安全Azure SQL Database 需要改用Servertcp:...database.windows.net,1433;User ID...;Password...形式的 SQL 身份验证RLS 下看不到数据确认已EXEC sp_set_session_context CompanyID, id设置会话上下文且该 ID 与Product.CompanyID匹配管理员使用-1脱敏未生效确认当前登录用户没有UNMASK权限脱敏仅对无权限用户生效时态表恢复RestoreProduct通过MERGE写回当前表恢复动作本身也会作为新版本写入历史表这是时态表的正常行为。赞分享示例工程数据库教程后端【免费下载链接】sql-server-samplesAzure Data SQL Samples - Official Microsoft GitHub Repository containing code samples for SQL Server, Azure SQL, Azure Synapse, and Azure SQL Edge项目地址https://gitcode.com/gh_mirrors/sq/sql-server-samples点击查看免费下载相关推荐WideWorldImporters 行级安全性Row-Level Security实战指南基于 SQL Server 2016 与 Azure SQL Database 的 T-SQL 演示WideWorldImporters 行级安全性Row Level Security实战指南基于 SQL Server 2016 与 Azure SQL示例工程数据库教程后端使用 ASP.NET Core 与 SQL Server FOR JSON 构建 ReactJS 评论应用sql-server-samples 中的 JSON 前后端集成实战使用 ASP.NET Core 与 SQL Server FOR JSON 构建 ReactJS 评论应用sql server samples 中的 JSON示例工程数据库教程后端SQL Server JSON 与 Node.js 实战构建基于 Express 4 jQuery/Bootstrap 的产品目录应用SQL Server JSON 与 Node.js 实战构建基于 Express 4 jQuery/Bootstrap 的产品目录应用 导读 本文基于 A示例工程数据库教程后端创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考 ASP.NET Core 产品目录应用实战：在 SQL Server 2016/Azure SQL Database 中融合 JSON、时态表、数据脱敏与行级安全
示例工程数据库教程后端【免费下载链接】sql-server-samplesAzure Data SQL Samples - Official Microsoft GitHub Repository containing code samples for SQL Server, Azure SQL, Azure Synapse, and Azure SQL Edge项目地址https://gitcode.com/gh_mirrors/sq/sql-server-samples点击查看免费下载本篇技术指南围绕 sql-server-samples 仓库中的 belgrade-product-catalog-demo 示例展开讲解如何用 ASP.NET Core Web API 构建一个产品目录Product Catalog应用并在一套数据库上同时落地 SQL Server 2016/Azure SQL Database 的四大核心能力JSON 函数、时态表Temporal Tables、动态数据脱敏Data Masking与行级安全Row-Level Security。读完本文你将掌握完整的建库脚本、JSON 存取存储过程、时态表回溯与恢复、脱敏与多租户数据隔离的实现细节以及对应的 C# 端到端调用方式可直接迁移到自己的多租户商品管理或 SaaS 业务中。关于本示例适用环境与能力矩阵项目说明适用版本SQL Server 2016或更高版本、Azure SQL Database关键技术JSON 函数、时态表、数据脱敏、行级安全RLS编程语言C#ASP.NET Core、HTML/JavaScript、Transact-SQL作者Jovan Popovic示例的四大技术主题与业务落点JSON 功能用于实现 Web API为前端 HTML 应用提供结构灵活的产品数据时态表展示数据变更轨迹、按时间点回溯快照、恢复产品历史版本数据脱敏对公司的 Email、Phone、Postcode 字段按规则打码非授权用户看到的是掩码后的值行级安全按当前登录公司CompanyID隔离产品数据不同公司只能看到自己的产品。项目整体架构前端使用 JQuery / JQuery UI 库配合 JQuery DataTable 组件展示与操作表格数据服务端使用 ASP.NET Core Web API通过 JSON 函数将数据库查询结果直接格式化为 JSON 响应并将客户端提交的 JSON 请求反序列化后写入数据库SQL Server 时态表负责追踪历史、按时间点展示过去快照、恢复产品旧版本数据脱敏隐藏公司敏感信息行级安全隔离各公司数据。代码位于 samples/demos/belgrade-product-catalog-demo数据库脚本全部集中在 sql-scripts 目录编号即执行顺序。环境准备软件前置条件SQL Server 2016或更高版本或一个 Azure SQL Database 实例ASP.NET Core 1.0 SDK或更高版本。可选Visual Studio 2015 Update 3或更高版本或 Visual Studio Code 编辑器。Azure 前置条件拥有创建 Azure SQL Database 的权限若选择云端部署。运行本示例完整步骤第 1 步创建数据库并设置兼容级别在 SQL Server 2016 或 Azure SQL Database 上创建数据库并将兼容级别设置为130对应 SQL Server 2016确保 JSON、时态表等新功能可用ALTER DATABASE ProductCatalog SET COMPATIBILITY_LEVEL 130;第 2 步执行初始化脚本使用 SQL Server Management StudioSSMS或 Visual Studio / SQL Server Data Tools 连接到数据库按顺序执行以下脚本位于 sql-scripts 目录脚本作用1 setup.sql创建登录名/用户/角色创建并填充 Product、Company 表创建 JSON 存取存储过程3 setup-temporal.sql创建历史表 History.Product 并开启系统版本控制5>dotnet restore # 还原全部 NuGet 包 dotnet build # 编译项目备选方案用 Visual Studio 2015 Update 3或更高打开根目录的ProductCatalog.xproj在项目上右键选择 “Restore Packages” 还原依赖。第 4 步修改连接字符串定位项目中的 appsettings.json将连接字符串改为指向你的数据库。默认值指向本机 SQLEXPRESS 实例的 ProductCatalog 数据库并使用集成安全Windows 身份验证{ ConnectionStrings: { BelgradeDemo: Server.\\SQLEXPRESS;DatabaseProductCatalog;Integrated Securitytrue;Application NameBelgrade } }修改后重新构建CtrlShiftB、右键项目 Build或命令行dotnet build。第 5 步运行应用Visual Studio 中按F5或CtrlF5或在项目根目录命令行执行dotnet run打开/index.html即可体验完整功能查看全部产品、增删改产品点击绿色 () 按钮查看某行数据的所有历史变更通过滑块JQuery UI Slider向左拖动回溯到过去某个时间点从历史中恢复某个版本编辑任意产品并验证公司信息已被脱敏以不同公司身份登录验证只能看到当前公司的产品数据。数据库层实现详解源码级JSON 功能从建表、导入到存储过程1 setup.sql 定义了两个包含 JSON 列的核心表。Product表把Data、Tags两列声明为nvarchar用于存放半结构化 JSONCREATE TABLE Product ( ProductID int DEFAULT (NEXT VALUE FOR ProductId) PRIMARY KEY, Name nvarchar(50) NOT NULL, Color nvarchar(15) NULL, Size nvarchar(5) NULL, Price money NOT NULL, Quantity int NULL, CompanyID int, Data nvarchar(4000), Tags nvarchar(4000), DateModified datetime2(0) NOT NULL DEFAULT (GETUTCDATE()) )关键点SQL Server 不要求 JSON 列使用专用类型普通nvarchar即可存放 JSON 文本ProductID通过序列ProductId起始值 29自动生成。随后用OPENJSON把一组 JSON 数组批量导入表INSERT INTO Product (ProductID, Name, Color, Size, Price, Quantity, CompanyID, Data, Tags, DateModified) SELECT ProductID, Name, Color, Size, Price, Quantity, CompanyID, Data, Tags, DateModified FROM OPENJSON (products) WITH( ProductID int, Name nvarchar(50), Color nvarchar(15), Size nvarchar(5), Price money, Quantity int, CompanyID int, Data nvarchar(MAX) AS JSON, Tags nvarchar(MAX) AS JSON, DateModified datetime2(0) )注意AS JSON关键字它告诉OPENJSON该列本身是 JSON 对象/数组而非标量从而保留嵌套结构Data中的ManufacturingCost、MadeInTags数组等。Company表同样通过OPENJSON导入三家示例公司A Datum Corporation、Contoso, Ltd.、Consolidated Messenger。同脚本还创建了两个面向 JSON 输入的存储过程Web API 正是通过它们完成产品的新增与更新新增产品InsertProductFromJson接收完整 JSON 对象用OPENJSON ... WITH提取字段后插入OUTPUT INSERTED.ProductID返回新行 ID。其中Name、Price使用了Nstrict $.Name/Nstrict $.Price路径表达式——strict表示 JSON 中缺少该属性时报错保证关键字段必填CREATE PROCEDURE [dbo].InsertProductFromJson) AS BEGIN INSERT INTO dbo.Product(Name, Color, Size, Price, Quantity, CompanyID, Data, Tags) OUTPUT INSERTED.ProductID SELECT Name, Color, Size, Price, Quantity, CompanyID, Data, Tags FROM OPENJSON(ProductJson) WITH ( Name nvarchar(100) Nstrict $.Name, Color nvarchar(30), Size nvarchar(10), Price money Nstrict $.Price, Quantity int, CompanyID int, Data nvarchar(max) AS JSON, Tags nvarchar(max) AS JSON) as json END更新产品UpdateProductFromJson接收 ProductID 与 JSON更新标量列并用ISNULL(json.Data, dbo.Product.Data)保持“未提交字段保留原值”的语义即 JSON 中未携带的Data/Tags不会被清空CREATE PROCEDURE dbo.UpdateProductFromJson(ProductID int, ProductJson NVARCHAR(MAX)) AS BEGIN UPDATE dbo.Product SET Name json.Name, Color json.Color, Size json.Size, Price json.Price, Quantity json.Quantity, Data ISNULL(json.Data, dbo.Product.Data), Tags ISNULL(json.Tags, dbo.Product.Tags) FROM OPENJSON(ProductJson) WITH ( Name nvarchar(100) Nstrict $.Name, Color nvarchar(30), Size nvarchar(10), Price money Nstrict $.Price, Quantity int, Data nvarchar(max) AS JSON, Tags nvarchar(max) AS JSON) as json WHERE dbo.Product.ProductID ProductID ENDJSON 查询与操作演示2 json.sql 系统演示了 SQL Server 2016 的 JSON 全家族函数1. 用FOR JSON PATH把查询结果直接格式化为 JSON 响应Web API 的数据库侧输出select ProductID, Name, Color, Size, Price, Quantity, JSON_VALUE(Data, $.MadeIn) as MadeIn, JSON_QUERY(Tags) as Tags from Product FOR JSON PATHJSON_VALUE提取 JSON 中的标量值如$.MadeInJSON_QUERY原样返回 JSON 对象/数组片段如Tags数组避免被转义成字符串单行输出用FOR JSON PATH, WITHOUT_ARRAY_WRAPPER去掉外层数组方括号。2. 在查询任意位置使用 JSON 数据——筛选、分组、聚合、排序均可select JSON_VALUE(Data, $.Type) as Type, Color, AVG( cast(JSON_VALUE(Data, $.ManufacturingCost) as float) ) as Cost from Product group by JSON_VALUE(Data, $.Type), Color having JSON_VALUE(Data, $.Type) is not null order by JSON_VALUE(Data, $.Type)3. 用JSON_MODIFY就地更新 JSON 字段——修改标量值或向数组追加元素update Product set Data JSON_MODIFY(Data, $.ManufacturingCost, 10), Tags JSON_MODIFY(Tags, append $, new) where ProductID 164.OPENJSON把 JSON 转换为关系行集单对象转一行、数组转多行、嵌套路径$.Data.Type提取深层字段甚至可以像查表一样对 JSON 数组做GROUP BY/HAVING聚合分析。5.CROSS APPLY OPENJSON展开 JSON 数组做存在性检索——找出所有带promo标签的产品select ProductID, Name, Color, Size, Price, Quantity, Tags from Product CROSS APPLY OPENJSON(Tags) where value promo6. 规避FOR JSON PATH对嵌套 JSON 的转义问题直接select ... Data, Tags时JSON 列会被当作字符串转义改用JSON_QUERY(Data) as Data, JSON_QUERY(Tags) as Tags后嵌套对象才会作为真正的 JSON 结构输出。时态表历史追踪、回溯与恢复3 setup-temporal.sql 将Product表升级为系统版本控制时态表并预置了 2015-05 至 2016-02 期间的多版本历史数据通过OPENJSON导入History.Product。开启时态表的三个关键动作-- 1. 添加有效时间列默认最大日期表示“至今仍有效” ALTER TABLE Product ADD ValidTo datetime2(0) NOT NULL CONSTRAINT Product_ValidTo_EndTime DEFAULT (9999-12-31 23:59:59) -- 2. 指定时间段Period ALTER TABLE Product ADD PERIOD FOR SYSTEM_TIME (DateModified, ValidTo) -- 3. 开启系统版本控制并绑定历史表 ALTER TABLE Product SET (SYSTEM_VERSIONING ON (HISTORY_TABLE History.Product))时态表要求两个datetime2列分别代表版本生效起始时间这里复用已有的DateModified与失效时间ValidTo系统会在每次 UPDATE/DELETE 时自动把旧版本写入History.Product。脚本还封装了三个应用直接调用的数据库对象版本差异函数dbo.diff_Product(id, date)把当前版本与过去某时间点版本分别转成 JSON再对OPENJSON展开的键值集合做 JOIN返回所有发生变化的列新值 v1、旧值 v2create function dbo.diff_Product (id int, date datetime2(0)) returns table as return ( select v1.[key] as [Column], v1.value as v1, v2.value as v2 from OPENJSON( (select * from Product where ProductID id FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) v1 join OPENJSON( (select * from Product for system_time as of date where ProductID id FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) v2 on v1.[key] v2.[key] where v1.value v2.value )dbo.GetProducts/dbo.GetProductsAsOf分别返回“当前产品 全部历史”和“某时间点的产品 相对当前版本的差异”并用FOR JSON AUTO, ROOT(data)直接产出前端可用的 JSONcreate procedure dbo.GetProductsAsOf (date datetime2) as begin select Product.ProductID, Product.Name, Product.Color, Product.Price, Product.Quantity, JSON_VALUE(Product.Data, $.MadeIn) as MadeIn, JSON_QUERY(Product.Tags) as Tags, Product.DateModified, ProductDifferences.* from Product FOR SYSTEM_TIME AS OF date outer apply dbo.diff_Product(Product.ProductID, date) as ProductDifferences order by Product.ProductID asc FOR JSON AUTO, ROOT(data) enddbo.RestoreProduct(productid, date)用MERGE把指定时间点的历史版本覆盖写回当前表命中则更新、未命中则插入实现“一键恢复旧版本”create procedure dbo.RestoreProduct (productid int, date datetime2) as begin MERGE Product USING ( select ProductID, Name, Color, Size, Price, Quantity, CompanyID, Tags, Data from Product FOR SYSTEM_TIME AS OF date where ProductID productid) AS Restored (ProductID, Name, Color, Size, Price, Quantity, CompanyID, Tags, Data) ON (Product.ProductID Restored.ProductID) WHEN MATCHED THEN update set Name restored.Name, Color restored.Color, Size restored.Size, Price restored.Price, Quantity restored.Quantity, CompanyId restored.CompanyID, Data restored.Data, Tags restored.Tags WHEN NOT MATCHED THEN insert (ProductID, Name, Color, Size, Price, Quantity, CompanyID, Data, Tags) values (ProductID, Name, Color, Size, Price, Quantity, CompanyID, Data, Tags); end4 temporal.sql 提供完整的验证脚本核心用法一览-- 回溯查看 2015-07-28 13:20:00 时刻的产品列表 select ProductID, Name, Color, Size, Price, Quantity from Product FOR SYSTEM_TIME AS OF 2015-07-28 13:20:00; -- 差异产品 17 当前版本与历史版本的差异列 select * from dbo.diff_Product(17, 2015-07-28 13:20:00); -- 全量历史产品 17 的完整变更轨迹 select ProductID, Name, Color, Size, Price, Quantity, DateModified, ValidTo from Product FOR SYSTEM_TIME ALL where productid 17 order by DateModified desc; -- 恢复用 2015-11-07 03:40:09 的版本覆盖当前版本 exec RestoreProduct 17, 2015-11-07 03:40:09;脚本最后还演示了配合窗口函数LAG/LEAD的进阶分析——找出价格突变的“尖峰”记录上一价格与下一价格相同、且与当前价格偏差 ≥10%可用于价格异常监控。动态数据脱敏Email、Phone、Postcode 打码5>ALTER TABLE Company ALTER COLUMN Email ADD MASKED WITH (FUNCTION email()) ALTER TABLE Company ALTER COLUMN Phone ADD MASKED WITH (FUNCTION partial(5,XXXXXXX,2)) ALTER TABLE Company ALTER COLUMN Postcode ADD MASKED WITH (FUNCTION default())三种脱敏函数的效果函数说明效果示例email()邮箱脱敏仅保留首字母与域名msavicdatum.com→mXXXdatum.compartial(5,XXXXXXX,2)保留前 5 个字符与后 2 个字符中间用掩码替换(381) 555-7639→(381) XXXXXXX39default()按类型默认掩码字符串列显示XXXX46077→XXXX脱敏是“声明式”的未授权用户无UNMASK权限执行SELECT * FROM Company时SSMS/驱动层自动返回掩码值而授权用户看到明文。脚本注释中给出了验证方式——用只具备基本权限的WebUser身份执行查询EXECUTE AS USER WebUser; SELECT * FROM Company; REVERT;行级安全按公司隔离产品数据6 rls.sql 实现多租户隔离每个会话通过SESSION_CONTEXT携带当前公司 ID安全策略据此过滤可见行。第 1 步创建安全谓词函数必须SCHEMABINDING。CompanyID -1视为管理员可访问全部公司数据CREATE FUNCTION dbo.pUserCanAccessCompanyData(CompanyID int) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT 1 as canAccess WHERE SESSION_CONTEXT(NCompanyID) -1 OR CAST(SESSION_CONTEXT(NCompanyID) as int) CompanyID)第 2 步创建安全策略并添加过滤谓词作用在Product上CREATE SECURITY POLICY dbo.ClientAccessPolicy ADD FILTER PREDICATE dbo.pUserCanAccessCompanyData(CompanyID) ON dbo.Product WITH (StateON)第 3 步策略扩展——对历史表加过滤谓词防止通过时态回溯跨公司查看、对 Product 加阻止谓词BLOCK PREDICATE禁止写入他公司数据、对 Company 加过滤谓词公司列表也只显示自己-- 历史数据同样隔离 ALTER SECURITY POLICY dbo.ClientAccessPolicy ADD FILTER PREDICATE dbo.pUserCanAccessCompanyData(CompanyID) ON History.Product -- 阻止跨公司写入 ALTER SECURITY POLICY dbo.ClientAccessPolicy ADD BLOCK PREDICATE dbo.pUserCanAccessCompanyData(CompanyID) ON dbo.Product -- 公司表仅可见当前公司 ALTER SECURITY POLICY dbo.ClientAccessPolicy ADD FILTER PREDICATE dbo.pUserCanAccessCompanyData(CompanyID) ON dbo.Company验证方式脚本注释中给出设置会话上下文后SELECT * FROM Product会随 CompanyID 变化返回不同子集777等不存在的公司则看不到任何数据EXEC sp_set_session_context CompanyID, 1 SELECT * FROM Product服务端实现ASP.NET Core Web API 如何衔接数据库数据访问与依赖注入Startup.cs 展示了两种数据访问方式并存Entity Framework Coreservices.AddDbContextProductCatalogContext(...)用于 ProductCatalogController.cs 中的常规 CRUDAdd、GetAll、GetByIdBelgrade SqlClient 的IQueryPipe/ICommand直接执行存储过程并以流式方式把FOR JSON结果写到响应配合AddRls(CompanyID, () GetCompanyIdFromSession(sp))可从 HTTP Session 读取 CompanyID 写入SESSION_CONTEXT与服务端 RLS 策略联动代码中该调用被注释作为接入示例保留。会话与中间件管线app.UseSession()启用 SessionUseStaticFiles()提供 wwwroot 下的静态页面UseMvc默认路由指向ProductCatalog控制器的Index动作。日志落库Serilog NLogStartup构造函数中配置了 Serilog 的 MSSqlServer sink把应用日志写入数据库Logs表由 1 setup.sql 创建的Logs表结构——Message、Level、EventTime、LogEvent四列并通过ColumnOptions移除默认的 Id/Properties/MessageTemplate/Exception 列、仅保留 JSON 格式的LogEvent日志表与配置保持一致var columnOptions new ColumnOptions(); columnOptions.Store.Remove(StandardColumn.Id); columnOptions.Store.Remove(StandardColumn.Properties); columnOptions.Store.Remove(StandardColumn.MessageTemplate); columnOptions.Store.Remove(StandardColumn.Exception); columnOptions.TimeStamp.ColumnName EventTime; columnOptions.Store.Add(StandardColumn.LogEvent); Log.Logger new LoggerConfiguration() .WriteTo.MSSqlServer(Configuration[ConnectionStrings:BelgradeDemo], Logs, columnOptions: columnOptions, autoCreateSqlTable: false) .CreateLogger();同时nlog.config与NLog.Extensions.Logging也被引入作为备选日志通道构造函数中保留了相关注释说明。日志写入的是同一BelgradeDemo连接字符串指向的数据库。控制器与前端页面ProductCatalogController.cs 提供Index返回产品列表视图、AddPOST 新增并重定向、GetAllJSON 列表、GetById按 ID 查询未找到返回 404等动作Models.cs 中ProductCatalogContext映射 EF 实体。前端页面位于 wwwrootindex.html产品列表 CRUD 历史差异展示temporal.html时态回溯滑块与版本恢复界面dashboard.html、report-multibar.html、report-pie.html基于 NVD3 的报表页对应 JS 逻辑分布在 products.js、products-crud.js、products-temporal.js、rls.js、dashboard.js 中。进阶脚本同库内的扩展演示除四大主题外sql-scripts 还附带了一批编号脚本可在此产品目录场景上继续探索 SQL Server 更多能力xtp.sql在xtp架构下创建内存优化表MEMORY_OPTIMIZEDON与原生编译存储过程NATIVE_COMPILATIONATOMIC WITH (TRANSACTION ISOLATION LEVEL SNAPSHOT, LANGUAGE Nus_english)演示 JSON 存取过程在高吞吐场景的版本bcp.sql创建 Orders/OrderLines 表并演示BULK INSERT配合EXTERNAL DATA SOURCEAzure Blob Storage从云端批量加载数据支持指定FORMATFILE与TABLOCKstring-agg.sql用STRING_AGG拼接每家公司的产品名称列表结合STRING_ESCAPE(...,json)手工构造合法 JSON 数组以及基于订单关联的“买了也买”Customers Also Buy推荐查询A1 fraud-detection.sql、A2 compare-products.sql、A3 setup-temporal-3-part-name.sql欺诈检测、产品对比与时态表三段式命名相关扩展bcp 目录下提供 bcp 导出/导入订单数据的批处理脚本含 README 与 bcp.tt 模板test/ProductListComparison.jmxJMeter 压测脚本可用于对比不同数据访问路径的产品列表查询性能。设计说明与限制README 明确声明本示例的目标是演示 SQL Server/Azure SQL Database 功能的最小可运行代码并非通用 Web 开发架构最佳实践模板。它刻意保持了精简——只包含构建 REST API 所需的最少代码没有使用 Repository 等分层模式应用通过 ASP.NET Core 内置的依赖注入DI机制组织服务但这并非必需。你可以轻松修改代码以适配自有应用架构。相关资源本示例依赖的组件官方资料与仓库内关联内容前端组件JQuery DataTables 行展开row_details 示例、JQuery UI Slider仓库内相关示例JSON 功能合集见 samples/features/json时态表产品目录见 samples/features/temporal/product-catalogSQL 图Graph与更多演示见 samples/demos。常见问题排查要点兼容级别未设置为 130JSON 函数、时态表等语法会报错或不生效务必先执行ALTER DATABASE ... SET COMPATIBILITY_LEVEL 130连接字符串指向错误实例默认是.\SQLEXPRESS Windows 集成安全Azure SQL Database 需要改用Servertcp:...database.windows.net,1433;User ID...;Password...形式的 SQL 身份验证RLS 下看不到数据确认已EXEC sp_set_session_context CompanyID, id设置会话上下文且该 ID 与Product.CompanyID匹配管理员使用-1脱敏未生效确认当前登录用户没有UNMASK权限脱敏仅对无权限用户生效时态表恢复RestoreProduct通过MERGE写回当前表恢复动作本身也会作为新版本写入历史表这是时态表的正常行为。赞分享示例工程数据库教程后端【免费下载链接】sql-server-samplesAzure Data SQL Samples - Official Microsoft GitHub Repository containing code samples for SQL Server, Azure SQL, Azure Synapse, and Azure SQL Edge项目地址https://gitcode.com/gh_mirrors/sq/sql-server-samples点击查看免费下载相关推荐WideWorldImporters 行级安全性Row-Level Security实战指南基于 SQL Server 2016 与 Azure SQL Database 的 T-SQL 演示WideWorldImporters 行级安全性Row Level Security实战指南基于 SQL Server 2016 与 Azure SQL示例工程数据库教程后端使用 ASP.NET Core 与 SQL Server FOR JSON 构建 ReactJS 评论应用sql-server-samples 中的 JSON 前后端集成实战使用 ASP.NET Core 与 SQL Server FOR JSON 构建 ReactJS 评论应用sql server samples 中的 JSON示例工程数据库教程后端SQL Server JSON 与 Node.js 实战构建基于 Express 4 jQuery/Bootstrap 的产品目录应用SQL Server JSON 与 Node.js 实战构建基于 Express 4 jQuery/Bootstrap 的产品目录应用 导读 本文基于 A示例工程数据库教程后端创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考