WK Hyper-V Script

$Global:SCCMSQLSERVER = "  (local)\LAB5"
$Global: DBNAME = "IAAS_61054700"
$Global:ID = "C-01"
Try
{
$SQLConnection = New-Object System.Data.SQLClient.SQLConnection
$SQLConnection.ConnectionString ="server=$SCCMSQLSERVER;database=$DBNAME;Integrated Security=True;Trusted_Connection=false;User ID=sa;Password=1234"
$SQLConnection.Open()
}
catch
{
    [System.Windows.Forms.MessageBox]::Show("Failed to connect SQL Server:") 
}
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand
$SQLCommand.CommandText = "SELECT [ID],[Firstname],[Lastname],[Byear],[Age], vmCPUCore, vmRam FROM $Global : DBNAME.[dbo].[Customer] where [ID] = '$ID'"
$SQLCommand.Connection = $SQLConnection
$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SQLCommand                 
$SQLDataset = New-Object System.Data.DataSet
$SqlAdapter.fill($SQLDataset) | out-null
$tablevalue = @()
Write-Host "Connect to Server: $SCCMSQLSERVER"
Write-Host "Database Name: $DBNAME"
Write-Host "Customer is $ID"
foreach ($data in $SQLDataset.tables[0])
{
$tablevalue = $data
$userID = $tablevalue.ID
$userName = $tablevalue.Firstname
$Ram= [int]:: Parse($tablevalue.vmRam) * 1024 * 1024
$Path1 = "D:\WK\VM\$userID"  
                del "D:\WK\VM\$userID"  
                 mkdir "D:\WK\VM\$userID"  
                $Ram2 = "1024MB"
                 New-VM -Name Testmachine -Path $Path1 -MemoryStartupBytes $Ram
  
              #RAM    New-VHD -Path "D:\WK\VM\$userID\Testmachine.vhdx" -SizeBytes 10GB -Dynamic
               #RAM   Add-VMHardDiskDrive -VMName Testmachine -Path "D:\WK\VM\$userID\Testmachine.vhdx"
              #RAM   Set-VMDvdDrive -VMName Testmachine -ControllerNumber 1 -Path "D:\Software\WS2012R2X64.ISO"
               #RAM  Start-VM -Name Testmachine
              #RAM   Get-VM Testmachine
               #RAM  Set-VMMemory -VMName Testmachine -DynamicMemoryEnabled $true -StartupBytes 1348MB -MinimumBytes 512MB
               #RAM  Stop-VM -Name Testmachine
               #RAM Remove-VM Testmachine

}
$SQLConnection.close()

------------------------------

CREATE TABLE [dbo].[Customer](
 [ID] [nchar](10) NOT NULL,
 [Firstname] [nvarchar](100) NULL,
 [Lastname] [nvarchar](100) NULL,
 [Byear] [int] NULL,
 [Age]  AS (datepart(year,getdate())-[Byear]),
 [Remark] [nvarchar](max) NULL,
 [vmCPUCore] [int] NULL,
 [vmRam] [int] NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]