使用SMO对象写数据到SQL Server 1

CLS
$TargetPath = "C:\Users\kivin\Documents\PowerShell\UploadBitlockerKey\Test\*.*"
$ImportedFolder = "C:\Users\kivin\Documents\PowerShell\UploadBitlockerKey\Test\Imported\"
$Files =  ls $TargetPath

		#Region Connect To DB
		CLS
		$Results = $null
		[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 
		$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') "192.168.40.123"  

		#This sets the connection to mixed-mode authentication 
		$srv.ConnectionContext.LoginSecure=$false; 

		#This sets the login name 
		$srv.ConnectionContext.set_Login("sa"); 

		#This sets the password 
		$srv.ConnectionContext.set_Password('YourPassword')
		##$srv.Databases |select Logins
		$db = $srv.Databases['BitlockerKey']
		#$db

		#endregion Connect To DB
		
foreach($File in $Files)
{
 $BitlockerKey = $null
 $KeyProtector = $null
 $Contents = gc $File
  #####################################Find the Bitlocker Key############################
 foreach($Content in $Contents)
 {
  if($Content -like '*??????-??????-??????-??????-??????-??????-??????-??????*')
  {
    $BitlockerKey = $Content.ToString().Trim()
	
  }
  
 }
 #####################################Find the Bitlocker Key############################

 #####################################Find the Key Protector############################
  foreach($Content in $Contents)
 {
  
  if($Content -like '*????????-????-????-????-????????????*')
  {
    $KeyProtector = $Content.ToString().Trim()
	$KeyProtector = $KeyProtector -replace('ID: {','') -replace('}','')
	
	write-host "Upload KeyProtector : $KeyProtector / Bitlocker: $BitlockerKey to Database " -ForegroundColor blue
	###Write Data To DB
	$sqlcommand =@"
					          Use BitlockerKey
							  
							  BEGIN
		   					    IF NOT EXISTS (SELECT * FROM SHDRBitlockerKey WHERE KeyProtector = '$KeyProtector'  AND  BitlockerKeyString = '$BitlockerKey')
									   BEGIN
									       INSERT INTO SHDRBitlockerKey
									       VALUES (N'$KeyProtector',N'$BitlockerKey')
									   END
							  END
							  
							  
					          
"@      #The @ must at the very begin of the line
		                  ################################SQL Command to Insert the record###############
		                  
		                  $db.ExecuteWithResults($sqlcommand).Tables.Rows
						  
	write-host "Upload KeyProtector : $KeyProtector / Bitlocker: $BitlockerKey to Database done !!!! " -ForegroundColor Magenta
	
	
	
  }
 }
  #####################################Find the Key Protector############################
 #Hanld the File move it to imported folder
 $CurrentFileFullName = ($File |select FullName).FullName.ToString().Trim()
 Write-Host "Move the file $CurrentFileFullName to imported folder " -ForegroundColor Blue
 CMD /C COPY /B /Y $CurrentFileFullName $ImportedFolder
 CMD /C DEL /F /Q $CurrentFileFullName
  Write-Host "Move the file $CurrentFileFullName to imported folder " -ForegroundColor Magenta
}