Programming4us
         
 
 
SQL Server

Administering SQL Server 2008 with PowerShell : PowerShell Scripting Basics (part 2)

7/15/2011 5:32:05 PM

Conditional Statements

Often in scripting, some kind of decision must be made by comparing values before a script or set of commands continues.

Operators can provide a simple example of how conditional statements work, as shown here:

PS>if("userA" -eq "userA")
>> {
>> Write-Host "Equal"
>> }
>> else
>> {
>> Write-Host "Not equal"
>> }
>>Equal
PS>$user="userB"
PS>if("userA" -eq "$user")
>> {
>> Write-Host "Equal"
>> }
>> else
>> {
>> Write-Host "Not equal"
>> }
>>Not equal
PS>

The preceding code provides a simple example and shows how interactive the PowerShell console can be. The >> character is simply PowerShell informing the user that the commands entered are basically not complete, and more input is required.

A later example using the AdventureWorks2008R2 database uses a conditional statement to make a particular decision based on the results from evaluating a particular expression.

Functions

Quite often, as the usage of SQL-PowerShell increases, some efficiencies are gained by using functions. Functions are especially useful when you are creating things that are done on a regular basis either directly in the console or in a script.

For example, a long script may have been developed that contains several checks for the existence of a file, such as a long database filename, as in the following:

PS>Function test {
>> param($user1,$user2)
>> if("$user1" -eq "$user2")
>> {
>> Write-Host "Equals"
>> }
>> else
>> {
>> Write-Host "Not equal"
>> }
>> }
>>PS>test "userA" "userB"
Not equal
PS>test "userA" "userA"
Equals
PS>

Using the earlier example of comparing two strings, this example writes a function named test, so if future comparisons are required, the typing requirements will be greatly reduced.

Note

Execute the Get-Help about_function command in PowerShell for more information and examples.


In a later example, a function is used to create a quick reference for sending out an email via PowerShell.

Looping Statements

Often a script needs to loop through items and act on each. PowerShell supports several looping constructs. Examples of the for and foreach constructs are demonstrated here.

PS>for($i=0;$i -lt 5;$i+=2){
>> $i
>> }
>>0
2
4
PS>

The preceding example shows a for loop. The method to jump or way to use a step is shown. A jump or step is indicated by the last part of the preceding for loop, where $i+=2 is used. If this example had used $i++ instead, the output would be each number from 0 to 5.

Here’s an example of using foreach:

PS C:\book>dir
Directory: Microsoft.PowerShell.Core\FileSystem::C:\book
Mode LastWriteTime Length Name
---- ------------- ------ ----
d---- 8/4/2008 11:29 PM directory
-a--- 8/5/2008 12:01 AM 53 database.csv
-a--- 8/4/2008 11:27 PM 0 file.ps1
-a--- 8/4/2008 11:27 PM 0 file.txt
-a--- 8/4/2008 11:47 PM 1813 list.csv
PS C:\book>$contents=dir
PS C:\book>$contents
Directory: Microsoft.PowerShell.Core\FileSystem::C:\book
Mode LastWriteTime Length Name
---- ------------- ------ ----
d---- 8/4/2008 11:29 PM directory
-a--- 8/5/2008 12:01 AM 53 database.csv
-a--- 8/4/2008 11:27 PM 0 file.ps1
-a--- 8/4/2008 11:27 PM 0 file.txt
-a--- 8/4/2008 11:47 PM 1813 list.csv
PS C:\book>foreach($each in $contents){
>> $each.name
>> }
>>directory
database.csv
file.ps1
file.txt
list.csv
PS C:\book>


In this example, within the foreach scriptblock, any number of commands could have been added, and they would have acted on each object.

Note

Use the Get-Help about_for, Get-Help about_foreach, and Get-Help about_while commands for more information and examples.


Note

Another feature that can also be useful in scripting is keywords, such as break, continue, and return. They can be used in various circumstances to basically end the execution of conditional statements and also looping statements. See Get-Help about_break and Get-Help about_continue for more information and examples.


Filtering Cmdlets

PowerShell also has a few useful filtering cmdlets:

  • Where-Object (alias where and ?)— Participates in a pipeline by helping to narrow down the objects passed along the pipeline based on some specific criteria.

  • ForEach-Object (alias foreach and %)— Participates in a pipeline by applying a scriptblock to every object passed along the pipeline.

By looking at a few files and a directory contained within a test directly, we can easily demonstrate the use of both cmdlets:

PS C:\book> dir
Directory: Microsoft.PowerShell.Core\FileSystem::C:\book

Mode LastWriteTime Length Name
---- -------------- ------ ----
d---- 8/4/2008 11:29 PM directory
-a--- 8/4/2008 11:27 PM 0 file.ps1
-a--- 8/4/2008 11:27 PM 0 file.txt

PS C:\book> dir|ForEach-Object{$_.Name.ToUpper()}
DIRECTORY
FILE.PS1
FILE.TXT
PS C:\book> dir|Where-Object{$_.PsIsContainer}

Directory: Microsoft.PowerShell.Core\FileSystem::C:\book

Mode LastWriteTime Length Name
---- ------------- ------ ----
d---- 8/4/2008 11:29 PM directory

PS C:\book>

In this example, first the ForEach-Object cmdlet is demonstrated, where each object passed along from the dir command is acted on, and the name of the object (filename or directory name) is changed to uppercase.

Next, the Where-Object cmdlet is demonstrated, where each object passed along is evaluated this time to determine whether it is a file or directory. If it is a directory (the scriptblock {$_.PsIsContainer} returns as True), the object continues along the pipeline, but in this case, the pipeline has ended.

Note

There is a ForEach-Object cmdlet and a foreach keyword, and they are not the same. Something useful to remember is that ForEach-Object would be used as part of a pipeline.


Formatting Cmdlets

Several formatting cmdlets are very useful:

  • Format-Table (alias ft)— Cmdlet that prints out properties in a table-based format.

  • Format-List (alias fl)— Cmdlet that prints out properties in a list-style format.

Some simple examples of Format-Table can be easily demonstrated, as you can see here:

PS C:\book\test> Get-Process powershell
Handles NPM(K) PM(K) WS(K) VM(M) CPU(s)
Id ProcessName
------- ------ ----- ----- ----- ------
-- ----------
548 13 54316 13192 164 25.55
2600 powershell

PS C:\book\test> Get-Process powershell| `
>> Format-Table -autosize handles,id
>>
Handles Id
------- --
561 2600

PS C:\book\test>

In this example, you use the Get-Process cmdlet to list the properties of the powershell.exe process. By default, the PowerShell formatting subsystem determines what properties to display. Using Format-Table, you modify the properties displayed. The –autosize parameter is used to shorten and align all the columns neatly.

Note

There are also Format-Custom and Format-Wide cmdlets. See the built-in help for each cmdlet for more information and examples.


Dealing with CSV Files

PowerShell provides two cmdlets that help greatly when you are dealing with files in the comma-separated value (CSV) format:

  • Import-Csv— This cmdlet reads in a CSV file and creates objects from its contents.

  • Export-Csv— This cmdlet takes an object (or objects) as input and create sa CSV file, as shown here:

PS>dir | Export-Csv c:\temp\file.csv

This simple example shows how to output the contents of the current directory to a CSV-formatted file. Looking at the contents of this file displays information about the objects, though, instead of just plain strings such as filenames.

The following example creates a simple CSV-formatted file and then is read in using the Import-Csv cmdlet. The Select-Object cmdlet is used to display only the database column:

PS C:\> cd c:\temp
PS C:\temp> Add-Content database.csv "server,database"
PS C:\temp> Add-Content database.csv "server1,database1"
PS C:\temp> Add-Content database.csv "server2,database2"
PS C:\temp> Get-Content database.csv
server,database
server1,database1
server2,database2

PS C:\temp> Import-Csv database.csv|Format-Table -AutoSize
server database
------ --------
server1 database1
server2 database2

PS C:\temp> Import-Csv database.csv|Select-Object database
database
--------
database1
database2

PS C:\temp>

Note

The Format-Table cmdlet is used in the preceding example simply to format the data in a more appropriate format for this book.


Dealing with Dates and Times

Being able to do date/time calculations is very useful. Fortunately PowerShell provides all kinds of quick date/time calculations. Some of the more common tricks are shown in the following example:

PS>[DateTime]::Now
Tuesday, August 05, 2008 2:01:22 PM

PS>([DateTime]::Now).AddDays(-1)
Monday, August 04, 2008 2:01:44 PM

PS>

Here, a .NET method is used to get a new value from the original object. This is done in a “single step,” in contrast to saving the object to a variable and then using the method on the variable. The use of a minus sign indicates that a value is being requested from the past.

Other common date/time methods include

  • AddHours— Add/subtract based on a number of hours.

  • AddMilliseconds— Add/subtract based on a number of milliseconds.

  • AddMinutes— Add/subtract based on a number of minutes.

  • AddMonths— Add/subtract based on a number of months.

  • AddSeconds— Add/subtract based on a number of seconds.

  • AddYears— Add/subtract based on a number of years.

-WhatIf/-Confirm Parameters

Several of the core PowerShell cmdlets support –whatif and/or –confirm parameters. The cmdlets that support these parameters could actually make system changes that cannot be reserved, such as deleting a file.

Consider the following example using these parameters:

PS>New-Item -Type File -Path file.tmp

Directory: Microsoft.PowerShell.Core\FileSystem::C:\book

Mode LastWriteTime Length Name
---- -------------- ------ ----
-a--- 8/4/2008 10:33 PM 0 file.tmp

PS>Remove-Item -Path file.tmp -WhatIf
What if: Performing operation "Remove File" on Target
"C:\book\file.tmp".
PS>

Two new cmdlets are demonstrated in the preceding example: New-Item, used to create things such as files, and Remove-Item, used to delete or remove things such as files.

Other -----------------
- Administering SQL Server 2008 with PowerShell : Overview of PowerShell
- SQL Server 2008 Scheduling and Notification : Scripting Jobs and Alerts, Multiserver Job Management & Event Forwarding
- SQL Server 2008 Scheduling and Notification : Managing Alerts
- SQL Injection Attacks and Defense : Accessing the File System (part 2) - Writing Files
- SQL Injection Attacks and Defense : Accessing the File System (part 1) - Reading Files
- SQL Server 2008 Scheduling and Notification : Managing Jobs
- SQL Server 2008 Scheduling and Notification : Managing Operators
- SQL Server 2008 Scheduling and Notification : Configuring the SQL Server Agent
- SQL Server 2008 : Database Mail - Related Views and Procedures
- SQL Server 2008 : Database Mail - Using SQL Server Agent Mail
- SQL Server 2008 : Sending and Receiving with Database Mail
- SQL Server 2008 : Setting Up Database Mail
- SQL Server 2008 : Security and Compliance - Setting Up Auditing via T-SQL & SQL Injection Is Easy to Do
- SQL Server 2008 : Security and Compliance - SQL Server Auditing
- SQL Server 2008 : Security and Compliance
- SQL Server 2008 : Transparent Data Encryption
- SQL Server 2008 : Data Encryption - Column-Level Encryption
- SQL Server 2008 : Data Encryption - SQL Server Key Management
- SQL Server 2008 : Data Encryption
- SQL Server 2008 : Client Data Access Technologies
 
 
Most View
- Sharepoint 2010 : Backup and Restore (part 1) - Recycle Bin settings in Central Administration
- Encryption basics for SQL Server : Cryptographic Keys
- Working with Search Page Layouts : Adding Navigation to the Search Center (part 1) - Adding Home and Back Buttons to the Search Result Page
- Programming Windows Azure : Table Operations - Understanding Pagination
- Windows 7 : Scripting Windows with PowerShell - Getting Started with PowerShell
- Exchange Server 2010 Mailbox Services Configuration (part 4) - Client Configuration
- Windows Phone 7 : Using the Touch Screen (part 3) - Sprite Hit Testing - Rectangular Hit Tests
- Microsoft ASP.NET 4 : Ajax - Extender Controls (part 2) - A Modal Pop-up Dialog-Style Component
- Relevant IAM Standards and Protocols for Cloud Services (part 1)
- Windows 7 : Accessing a Shared Printer
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS