Richard Siddaway's Blog

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.

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.