A column is a table field that defines one attribute of each record and stores values of a corresponding type. A table contains rows and columns, much like an Excel sheet; a database contains tables, much like a workbook. section 26.1
Design columns by matching them to the objects your PowerShell command produces. A disk-inventory table uses columns for computer name, disk size, drive type, free space, drive ID, and the date each row was added. Choosing suitable SQL types makes each field fit its data: strings for names and identifiers, large integers for sizes, a small integer for drive type, and a date-time value for inventory history. section 26.3
Disk inventory columns
The table definition declares ComputerName VARCHAR(64), DiskSize BIGINT, DriveType TINYINT, FreeSpace BIGINT, DriveID CHAR(2), and DateAdded DATETIME2. Together, these columns preserve the output of Get-DiskInfo and support later free-space trend reporting. 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 complete PowerShell and SQL example that creates the diskinfo table, including its column definitions and SQL connection handling. section 26.3
