en-us/Simplified_SQL.walkthru.help.txt
|
<#
PowerShell Pipeworks contains several functions to help you simplify interaction with SQL. The core functions are: * Add-SQLTable * Get-SQLTable * New-SQLDatabase * Remove-SQL * Select-SQL * Update-SQL The two important ones for you to remember are: * Select-SQL, which runs SQL and unrolls each result * Update-SQL, which takes a pipeline of objects and updates a SQL database To show you how these work, let's create and update a simple table. First, we need to create a new database. This example requires that you run it on a computer with SQL server #> # Create a random DB name $dbName = "Db" + (Get-Random) # Create the database on the local computer New-SQLDatabase -DatabaseName $dbName # Craft a connection string to the database $connectionString = "Data Source=$env:ComputerName;Initial Catalog=$dbName;Integrated Security=SSPI;" <# Now we need to create some input for our table. We'll do this by creating a pair of random input objects, and then piping them into Update-SQL. If you provide -Force to Update-SQL, it will create the table and modify it to fit your input object. #> # Create the list of input objects $inputObjs = @() # Add a couple of items to the list $inputObjs += New-Object PSObject -Property @{ "a" = Get-Random "B" = Get-Random } $inputObjs += New-Object PSObject -Property @{ "a" = Get-Random "B" = Get-Random } $inputObjs | Update-Sql -TableName "TestTable" -Force -connectionStringOrSetting $connectionString <# Now let's query the table to check that the items were inserted. Since we've already supplied a connection string, we don't need to do it again #> Select-SQL -FromTable TestTable <# Great! Now let's query again, and this time let's modify the values for B #> Select-SQL -FromTable TestTable | Add-Member NoteProperty B (Get-Random) -Force -PassThru | Update-Sql -TableName "TestTable" <# And let's check our table: #> Select-SQL -FromTable TestTable <# Now this time, let's modify only the first item's value for B #> Select-SQL -FromTable TestTable -Top 1 | Add-Member NoteProperty B (Get-Random) -Force -PassThru | Update-Sql -TableName "TestTable" <# Let's take one last look: #> Select-SQL -FromTable TestTable <# It's great that Pipeworks simplifies SQL, but it also gives you easy access to full SQL. Select-SQL has an alias, "sql", and it's first positional parameter is a raw SQL statement. This means you can write as complicated SQL as you'd like in PowerShell with very simple syntax like this: #> SQL "SELECT COUNT(*) as Count FROM TestTable" <# Let's remove our table to clean up: #> Remove-SQL -TableName TestTable -ConnectionStringOrSetting $connectionString -Confirm:$false |