It is often helpful to perform ad hoc queries and commands against a data source such as a SQL server, Access database, or even an Excel spreadsheet. This is especially true when you want to take data from one system and put it in another, or when you want to bring the data into your PowerShell environment for detailed interactive manipulation or processing.
Although you can directly access each of these data sources in PowerShell (through its support of the .NET Framework), each data source requires a unique and hard to remember syntax. Example 155 makes working with these SQLbased data sources both consistent and powerful.
Example 155. InvokeSqlCommand.ps1
############################################################################## ## ## InvokeSqlCommand.ps1 ## ## Return the results of a SQL query or operation ## ## ie:
##
##
## Use Windows authentication
##
InvokeSqlCommand.ps1 Sql "SELECT TOP 10 * FROM Orders"
##
##
## Use SQL Authentication
##
$cred = GetCredential
##
InvokeSqlCommand.ps1 Sql "SELECT TOP 10 * FROM Orders" Cred $cred
##
##
## Perform an update
##
$server = "MYSERVER"
##
$database = "Master"
##
$sql = "UPDATE Orders SET EmployeeID = 6 WHERE OrderID = 10248"
##
InvokeSqlCommand $server $database $sql
##
##
$sql = "EXEC SalesByCategory 'Beverages'"
##
InvokeSqlCommand Sql $sql
##
##
## Access an access database
##
InvokeSqlCommand (ResolvePath access_test.mdb) Sql "SELECT * from Users"
##
##
## Access an excel file
##
InvokeSqlCommand (ResolvePath xls_test.xls) Sql 'SELECT * from [Sheet1$]'
##
##############################################################################
param( [string] $dataSource = ".\SQLEXPRESS", [string] $database = "Northwind", [string] $sqlCommand = $(throw "Please specify a query."), [System.Management.Automation.PsCredential] $credential
)
## Prepare the authentication information. By default, we pick ## Windows authentication $authentication = "Integrated Security=SSPI;"
## If the user supplies a credential, then they want SQL ## authentication if($credential) {
$plainCred = $credential.GetNetworkCredential() $authentication = ("uid={0};pwd={1};" f $plainCred.Username,$plainCred.Password) }
Example 155. InvokeSqlCommand.ps1 (continued)
## Prepare the connection string out of the information they ## provide $connectionString = "Provider=sqloledb; " +
"Data Source=$dataSource; " + "Initial Catalog=$database; " + "$authentication; "
## If they specify an Access database or Excel file as the connection ## source, modify the connection string to connect to that data source if($dataSource match '\.xls$|\.mdb$') {
$connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$dataSource; "
if($dataSource match '\.xls$') { $connectionString += 'Extended Properties="Excel 8.0;"; '
## Generate an error if they didn't specify the sheet name properly if($sqlCommand notmatch '\[.+\$\]') {
$error = 'Sheet names should be surrounded by square brackets, and ' +
'have a dollar sign at the end: [Sheet1$]' WriteError $error return
} } }
## Connect to the data source and open it $connection = NewObject System.Data.OleDb.OleDbConnection $connectionString $command = NewObject System.Data.OleDb.OleDbCommand $sqlCommand,$connection $connection.Open()
## Fetch the results, and close the connection $adapter = NewObject System.Data.OleDb.OleDbDataAdapter $command $dataset = NewObject System.Data.DataSet [void] $adapter.Fill($dataSet) $connection.Close()
## Return all of the rows from their query $dataSet.Tables | SelectObject Expand Rows
