考虑以下TSql代码:
CREATE TABLE Employee
(
[EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED
, [Name] nvarchar(100) NOT NULL
, [Position] varchar(100) NOT NULL
, [Department] varchar(100) NOT NULL
, [Address] nvarchar(1024) NOT NULL
, [AnnualSalary] decimal (10,2) NOT NULL
, [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START
, [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END
, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));
以及使用DACFx:的以下C#代码
TSqlObject table;
TSqlObject nameColumn;
// picture some code instantiating the two variables above
var primaryKey = table.GetChildren().Single(x => x.ObjectType == ModelSchema.PrimaryKeyConstraint);
var isMax = nameColumn.Object.GetProperty<bool?>(Column.IsMax);
因此,在这里我们看到,我查询了DACFx API,以了解列实例是否是MAX类型的列,并获取有关表主键的信息。
现在我想了解一下表格的临时设置。我需要知道SYSTEM_VERSIONING
是否打开,如果打开,作为历史记录表的表是什么。我还想知道哪些字段用作行的开始和结束。
我如何使用DACFx API知道这一点?
我知道这是一个老问题,但我只是花了一整天的时间,终于找到了如何提取SYSTEM_VERSIONING设置。希望这对其他人有帮助:
TSqlObject table;
// Code initializing the variable
// Get the type of retention. This can be either -1 (if Infinite)
// or one of the value in the TemporalRetentionPeriodUnit enum
var retentionUnit = table.GetProperty<int>(Table.RetentionUnit);
// Get the retention value. If retention is Infinite this will be -1
var retentionValue = table.GetProperty<int>(Table.RetentionValue);
// Get a reference to the history table.
var historyTable = table.GetReferenced(Table.TemporalSystemVersioningHistoryTable);