Add Access Record Pt III – parameter sets
Last time we added the option of inputting the table and values to our function but we needed a way to discriminate between that and using a full SQL statement. We can achieve this by dividing the parameters into parameter sets NOTE – This is a PowerShell v2 capability.
| 001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
| function Add-AccessRecord {
[CmdletBinding()]
param (
[parameter(ParameterSetName="sql")]
[string]$sql,
[System.Data.OleDb.OleDbConnection]$connection,
[parameter(ParameterSetName="value")]
[string]$table,
[parameter(ParameterSetName="value")]
[string]$values
)
if($psCmdlet.ParameterSetName -eq "value"){
$sql = "INSERT INTO $table VALUES ($values)"
}
$cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
$cmd.ExecuteNonQuery()
} |
We add the [CmdletBinding()] parameter and on the sql,varaible we add a parameter set name of “sql”. The table and values parameters are put into a parameter set of “value”. The connection parameter is not explicitly placed into either parameter set which means it is in both!
We can then check the ParameterSetName property and create the $sql variable if required. The function can be used as follows
Import-Module accessfunctions
$db = Open-AccessDatabase -name test03.mdb -path c:\test
Add-AccessRecord -sql 'insert into test1 values("James","Jones","18/04/1981")' -connection $db
Add-AccessRecord -connection $db -table test1 -values '"Alex","Green","17/05/1981"'
In these two examples we have restricted ourselves to one or the other of the parameter sets. if we try to mix parameters across the parameter sets
PS> Add-AccessRecord -sql 'insert into test1 values("Rab","Burns","08/06/1981")' -connection $db -table test1
Add-AccessRecord : Parameter set cannot be resolved using the specified named parameters.
At line:1 char:17
+ Add-AccessRecord <<<< -sql 'insert into test1 values("Rab","Burns","08/06/1981")' -connection $db -table test1
+ CategoryInfo : InvalidArgument: (:) [Add-AccessRecord], ParameterBindingException
+ FullyQualifiedErrorId : AmbiguousParameterSet,Add-AccessRecord
we get an error message.
We now have a function that allows us to work in the most efficient way at a particular moment. Next we’ll see how we can feed the values from a csv file.