Private/Powershell/PsDbModel.ps1
|
<############################################################################ # Given a connection string to a SQL Server database, # check the metadata, and for each table in the database, # fill a custom TableInfo object with metadata about those tables and columns. # # This is not specific to C# or angular, it's just TypeScript objects # storing metadata about database tables. ############################################################################> Function New-PsModel([DbInfo]$dbInfo) { Write-Host "### Store metadata of database tables" $tableInfos = @{}; $conn = Get-DbConnection $dbInfo.connStr $ds = Invoke-DbQuery $conn "select TABLE_NAME, iif(TABLE_TYPE = 'BASE TABLE', 0, 1) as IS_VIEW from INFORMATION_SCHEMA.TABLES where TABLE_TYPE in ('BASE TABLE', 'VIEW') " @{} $dt = $ds.Tables[0] foreach ($Row in $dt) { [string]$tableName = $Row.TABLE_NAME [bool]$isView = $Row.IS_VIEW [TableInfo]$tableInfo = New-PsTable $dbInfo $tableName $isView $tableInfos.Add($tableName, $tableInfo) } Close-DbConnection $conn return $tableInfos } <############################################################################ # Given a tablename and a connection string to a database, # make a POTO class where the POTO properties match metadata # # This is not specific to C# or angular, it's just TypeScript objects # storing metadata. ############################################################################> Function New-PsTable([DbInfo]$dbInfo, [string]$tableName, [bool]$isView) { Write-Host "### Generate internal model for $($tableName)" $conn = Get-DbConnection $dbInfo.connStr # Get a list of all columns in this table $ds = Invoke-DbQuery $conn "select C.COLUMN_NAME, C.IS_NULLABLE, C.DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, iif( ( SELECT ku.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ku ON tc.CONSTRAINT_TYPE = 'PRIMARY KEY' AND tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME where c.TABLE_CATALOG = tc.TABLE_CATALOG AND c.TABLE_SCHEMA = tc.TABLE_SCHEMA AND c.TABLE_NAME = tc.TABLE_NAME AND c.COLUMN_NAME = ku.COLUMN_NAME ) is null, 'NO', 'YES') as PRIMARY_KEY from INFORMATION_SCHEMA.COLUMNS as C where TABLE_NAME = @TableName" @{ TableName = $tableName } $dt = $ds.Tables[0] # Generate a TypeScript POTO file that represents this table [ColumnInfo[]] $columnInfos = @(); # Loop through all fields foreach ($Row in $dt) { [string]$fieldName = $row.COLUMN_NAME [string]$isNullable = $row.IS_NULLABLE [string]$dataType = $row.DATA_TYPE [int]$charMaxLen = $null; if($row.CHARACTER_MAXIMUM_LENGTH -match '^\d+$') { $charMaxLen = $row.CHARACTER_MAXIMUM_LENGTH } [string]$isPrimaryKey = $row.PRIMARY_KEY [ColumnInfo] $columnInfo = [ColumnInfo]::new($fieldName, $dataType, ($isNullable -eq "yes"), $charMaxLen, ($isPrimaryKey -eq "yes")) $columnInfos = $columnInfos += $columnInfo } Close-DbConnection $conn [TableInfo] $tableInfo = [TableInfo]::new($tableName, $columnInfos, $isView) return $tableInfo } |