A record is one row in a database table: a grouped set of values organized under the table’s columns. A table resembles an Excel sheet, while a database resembles an Excel workbook. section 26.1
A disk-inventory record can hold:
- Computer name and disk identifier. section 26.3
- Disk size and free space as large integer values. section 26.3
- Drive type as a small integer and the date the record was added. section 26.3
From PowerShell object to table record
Get-DiskInfo produces objects containing computer, size, drive, free-space, and drive-type properties. A SQL table defines matching columns, plus DateAdded, so repeated inventory runs can store records for later free-space trend reporting. section 26.3
Listing 26.2 Start with a command that produces objects as output
function Get-DiskInfo {
[CmdletBinding()]
Param(
[Parameter(Mandatory=$True,
ValueFromPipeline=$True)]
[string[]]$ComputerName
)
BEGIN {
Set-StrictMode -Version 2.0
}
PROCESS {
ForEach ($comp in $ComputerName) {
$params = @{'ComputerName' = $comp
'ClassName' = 'Win32_LogicalDisk'}
$disks = Get-CimInstance @params
ForEach ($disk in $disks) {
$props = @{'ComputerName' = $comp
'Size' = $disk.size
'Drive' = $disk.deviceid
'FreeSpace' = $disk.freespace
'DriveType' = $disk.drivetype}
New-Object -TypeName PSObject -Property $props
} #foreach disk
} #foreach computer
} #PROCESS
END {}
}
Inspect the command that constructs disk-information objects, the source shape for database records. section 26.3
Listing 26.3 Adding code for table creation
function New-DiskInfoSQLTable {
[CmdletBinding()]
param()
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = $DiskInfoSqlConnection
$conn.Open()
$sql = @"
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='diskinfo' AND
xtype='U')
CREATE TABLE diskinfo (
ComputerName VARCHAR(64),
DiskSize BIGINT,
DriveType TINYINT,
FreeSpace BIGINT,
DriveID CHAR(2),
DateAdded DATETIME2
)
"@
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $conn
$cmd.CommandText = $sql
$cmd.ExecuteNonQuery() | Out-Null
$conn.Close()
}
$DiskInfoSqlConnection =
"Server=localhost\SQLEXPRESS;Database=Scripting;Trusted_Connection=True;"
Export-ModuleMember -Function Get-DiskInfo
Export-ModuleMember -Variable DiskInfoSqlConnection
Inspect the SQL definition that maps record values to typed table columns. section 26.3
