So far we have seen how to add data to a table in an access database – now we want to delete some records. This is an action that can cause problems especially if we get the wrong records – ideally we want to a mechanism to check what we are doing. The PowerShell cmdlets that change system state have –whatif and –confirm parameters to enable us to test our actions. The Advanced Function capability in PowerShell v2 enables us to duplicate this functionality very simply.
|
001
002 003 004 005 006 007 008 009 010 011 012 |
function Remove-AccessData {
[CmdletBinding(SupportsShouldProcess=$true)] param ( [string]$table, [string]$filter, [System.Data.OleDb.OleDbConnection]$connection ) $sql = "DELETE FROM $table WHERE $filter" $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection) if ($psCmdlet.ShouldProcess("$($connection.DataSource)", "$sql")){$cmd.ExecuteNonQuery()} } |
Our function uses the CmdletBinding parameter we saw in our last version of Adding a record. This time we have added the SupportsShouldProcess attribute.
Our parameters are the table and the connection which we have seen before. The filter parameter is the stuff in the WHERE clause of the DELETE statement without the WHERE.
As an example
Remove-AccessData -table test1 -filter "LastName = ‘Bloggs’" -connection $db
Translates to a SQL statement of
DELETE FROM test1 WHERE LastName = ‘Bloggs’
inside the function.
We create the SQL by string substitution and create the command. Instead of directly executing the command we use the $psCmdlet.ShouldProcess() method to test if –whatif or –confirm have been used before executing the command.
These examples should show how it works.
First we’ll test that the data we want to delete actually exists
PS> Get-AccessData -sql "select * from test1 where LastName = ‘Bloggs’" -connection $db
FirstName LastName DOB
——— ——– —
Jo … Bloggs … 24/12/1980 00:00:00
Now we use the –whatif parameter to show us what would happen if we ran the command
PS> Remove-AccessData -table test1 -filter "LastName = ‘Bloggs’" -connection $db -whatif
What if: Performing operation "DELETE FROM test1 WHERE LastName = ‘Bloggs’" on Target "c:\test\test03.mdb".
and we can confirm that the data is still there
PS> Get-AccessData -sql "select * from test1 where LastName = ‘Bloggs’" -connection $db
FirstName LastName DOB
——— ——– —
Jo … Bloggs … 24/12/1980 00:00:00
Now we can ask for confirmation on the delete
PS> Remove-AccessData -table test1 -filter "LastName = ‘Bloggs’" -connection $db -confirm
Confirm
Are you sure you want to perform this action?
Performing operation "DELETE FROM test1 WHERE LastName = ‘Bloggs’" on Target "c:\test\test03.mdb".
(Y) Yes (A) Yes to All (N) No (L) No to All (S) Suspend [?] Help (default is "Y"): n
note – all () in above line should be []
We have not confirmed so the data is still present.
PS> Get-AccessData -sql "select * from test1 where LastName = ‘Bloggs’" -connection $db
FirstName LastName DOB
——— ——– —
Jo … Bloggs … 24/12/1980 00:00:00
This time we do confirm
PS> Remove-AccessData -table test1 -filter "LastName = ‘Bloggs’" -connection $db -confirm
Confirm
Are you sure you want to perform this action?
Performing operation "DELETE FROM test1 WHERE LastName = ‘Bloggs’" on Target "c:\test\test03.mdb".
(Y) Yes (A) Yes to All (N) No (L) No to All (S) Suspend [?] Help (default is "Y"): y
1
note – all () in above line should be []
And the data is gone
PS> Get-AccessData -sql "select * from test1 where LastName = ‘Bloggs’" -connection $db
PS> Get-AccessData -sql "select * from test1 " -connection $db
FirstName LastName DOB
——— ——– —
Fred … Smith … 01/09/1979 00:00:00
John … Smith … 01/07/1981 00:00:00
James … Jones … 18/04/1981 00:00:00
Alex … Green … 17/05/1981 00:00:00
If you are 150% sure of what you are deleting then just run the function without –whatif or –confirm otherwise use them as a final test.
The ability to add a very powerful check to our functions with minimal coding is a great bonus from PowerShell v2. We will see this technique in more functions as we develop our access module.