由于PowerShell在X64的操作系统上有X86 & X64两种,对于安装的SQL Server同样有两种版本X64 & X86,如果安装的是X86的SQL Server只能通过X86的PowerShell去执行Invoke-sqlcmd cmdlet,同理只能在安装X64的SQL Server只能通过X64的PowerShell去执行Invoke-sqlcmd cmdlet,所以为了防止这种隐患的存在,我们选择了System.Data.SqlClient命名空间去实现,其中$SQLCommand为SQL查询语句,实现如下:
functionGet-DBObject
{
[OutputType([System.Array])]
param(
[Parameter(Position=0, Mandatory=$true)]
[ValidateNotNullOrEmpty()]
[System.String]
$SQLCommand
)
process
{
Write-Verbose"Get DataBase Object and returned."
$SQLCommand+=" ORDER BY CONVERT(Varchar(50),[id]) "
$connStr="Data Source=$DefaultServer;Initial Catalog=$DataBaseName;User ID=$DBUserName;Password=$DBPassword"
[System.Data.SqlClient.SqlConnection]::ClearAllPools()
[System.Data.SqlClient.SqlConnection]$sqlconn=New-ObjectSystem.Data.SqlClient.SqlConnection($connStr)
[System.Data.SqlClient.SqlCommand]$sqlcmd=New-ObjectSystem.Data.SqlClient.SqlCommand($SQLCommand,$sqlconn)
[System.Data.SqlClient.SqlDataAdapter]$sqladp=New-ObjectSystem.Data.SqlClient.SqlDataAdapter($sqlcmd)
[System.Data.DataSet]$ds=New-ObjectSystem.Data.DataSet
$output=$sqladp.Fill($ds,"Table0")
[Object[]]$dbObject=$ds.Tables["Table0"]
return$dbObject
}
}