A fake attribute named [MagicDBProp()] must be added to each parameter that corresponds to a column in the SELECT statement:
DbReaderCommand Get-TestData {
[MagicDbInfo(FromClause='Test')]
param(
[MagicDbProp()] # Adding this wires up the command to the SELECT statement
$TableColumn
)
}
An empty attribute is allowed, but it is recommended that all instances contain at least a ‘ColumnName’ property since column names can become ambiguous if there are any table joins.
The attribute has several valid properties that alter the behavior of each of the parameters or the properties on the returned objects:
This is what the PowerShell parameter is named in the SELECT query, including the table name or alias. While this paramter isn’t required for very simple queries where there are no ambiguous columns, it is highly recommended that it always be specified. If not specified, the name of the parameter is used in its place.
Example:
DbReaderCommand Get-Customer {
[MagicDbInfo(FromClause = 'FROM Customers')]
param(
[MagicDbProp(ColumnName='Customers.CustomerId')]
[string] $CustomerId
)
}
In this example, the $CustomerId parameter corresponds to the Customers.CustomerId table column. Since the value provided as the ‘ColumnName’ will be directly substituted in the SQL command generated by the function, the table name wasn’t required in this instance (even though it is a good idea to provide it anyway).
Here’s what a sample SQL query generated by this command would look like:
PS> Get-Customer -CustomerId 123 -ReturnSqlQuery
SELECT
Customers.CustomerId AS CustomerId
FROM
Customers
WHERE
(Customers.CustomerId LIKE '123')
Normally, any parameters defined in the param() block are valid parameters for the PowerShell command that is generated from the DbReaderCommand definition, and parameters with the [MagicDBProp()] attribute get added to the list of columns to return in the SQL results. Parameters that have an associated column in the SQL results act as filters.
Sometimes it may be desirable to include a column in the SQL results, but not have a filtering parameter.
For example, imagine wanting to return a CustomerId, for any returned customer objects, but not wanting the querying command to have a -CustomerId parameter. To do that, you could define a DbReaderCommand like this:
DbReaderCommand Get-Customer {
[MagicDbInfo(FromClause = 'FROM Customers')]
param(
[MagicDbProp(ColumnName='Customers.CustomerId', NoParameter)]
[int] $CustomerId,
[MagicDbProp(ColumnName='Customers.FirstName', NoParameter=$false)]
[string] $FirstName,
[MagicDbProp(ColumnName='Customers.LastName', ComparisonOperator='ILIKE')]
[string] $LastName,
[MagicDbProp(ColumnName='Customers.Title')]
[string] $Title
)
}
Note that $CustomerId has the ‘NoParameter’ property specified (if a value isn’t assigned, e.g., $true or $false, then $true is assumed). The $FirstName parameter also has the property, but it is set to $false, which causes the same behavior as if ‘NoParameter’ wasn’t specified at all.
This means that the ‘Get-Customer’ command doesn’t have a -CustomerId parameter:
PS> Get-Command Get-Customer -Syntax
Get-Customer [[-FirstName] <string[]>] [[-LastName] <string[]>] [[-Title] <string[]>]
[[-Negate] <string[]>] [[-GroupBy] <string[]>] [[-OrderBy] <string[]>] [-ReturnSqlQuery]
[<CommonParameters>]
But the underlying SQL queries will have CustomerId as a column:
PS> Get-Customer -ReturnSqlQuery
SELECT
Customers.CustomerId AS CustomerId,
Customers.FirstName AS FirstName,
Customers.LastName AS LastName,
Customers.Title AS Title
FROM
Customers
The default comparison operator depends on the type of the property/parameter: * Strings default to the LIKE operator * Everything else defaults to the equals (=) operator
To override this behavior, the ComparisonOperator property can be used. An example of when you may want to do this is for DB engines where LIKE is case sensitive, and you have to provide a separate ILIKE operator to get case insensitivity.
Note that there is no validation performed on the value provided to this property, so any value provided here will be inserted directly into the SQL query in the WHERE clause.
Example:
DbReaderCommand Get-Customer {
[MagicDbInfo(FromClause = 'FROM Customers')]
param(
[MagicDbProp(ColumnName='Customers.FirstName', ComparisonOperator='ILIKE')]
[string] $FirstName,
[MagicDbProp(ColumnName='Customers.LastName')]
[string] $LastName
)
}
In this example, any values provided to the -FirstName parameter will be used with the ILIKE operator in the SQL statement WHERE clause, and any values provided to the -LastName parameter will be used with the [string] default’s LIKE operator. Here’s what a sample SQL query would look like:
PS> Get-Customer -FirstName a* -ReturnSqlQuery
SELECT
Customers.FirstName AS FirstName,
Customers.LastName AS LastName
FROM
Customers
WHERE
(Customers.FirstName ILIKE 'a%')
By default, WHERE conditions for each parameter are combined with the OR conditional operator. This behavior can be changed, however, with the ‘ConditionalOperator’ attribute. There are only two valid values for this attribute: OR and AND.
Note that this modifies the behavior for mutiple values provided to a single parameter, NOT to the conditions when each group of parameter values are joined together. See the example for more information.
Example:
DbReaderCommand Get-Customer {
[MagicDbInfo(FromClause = 'FROM Customers')]
param(
[MagicDbProp(ColumnName='Customers.FirstName', ConditionalOperator='AND')]
[string] $FirstName,
[MagicDbProp(ColumnName='Customers.LastName')]
[string] $LastName
)
}
In this example, multiple values provided to the -FirstName parameter will all need to evaluate to TRUE since they will be ANDed together. The -LastName parameter will still behave as normal since OR is the default. Here’s what a SQL statement would look like:
PS> Get-Customer -FirstName a*, *e -LastName a*, e* -ReturnSqlQuery
SELECT
Customers.FirstName AS FirstName,
Customers.LastName AS LastName
FROM
Customers
WHERE
(Customers.FirstName LIKE 'a%' AND Customers.FirstName LIKE 'e%') AND
(Customers.LastName LIKE 'a%' OR Customers.LastName LIKE 'e%')
If user input needs to be transformed somehow, the TransformArgument attribute can be used. This attribute takes a scriptblock (any other type of object will be coerced into a scriptblock, and an error will be thrown when the command is being built if the object can’t be coerced). Any data a user passes to a parameter that has this attribute is run through the scriptblock as if it was piped to ForEach-Object.
You can use this to transform the input data in any number of ways. Note that this only changes the data before it is placed in the the SQL query. This doesn’t change the data returned from the query in any way.
Example:
DbReaderCommand Get-Customer {
[MagicDbInfo(FromClause = 'FROM Customers')]
param(
[MagicDbProp(ColumnName='Customers.CustomerId')]
[int] $CustomerId,
[MagicDbProp(ColumnName='Customers.FirstName', TransformArgument={ "${_}".ToLower() })]
[string] $FirstName,
[MagicDbProp(ColumnName='Customers.LastName')]
[string] $LastName,
[MagicDbProp(ColumnName='Customers.StartDate', TransformArgument={ $_.ToString('yyyy-MM-dd') })]
[datetime] $StartDate
)
}
This should take any strings passed to -FirstName and convert them to lowercase. A sample SQL query returned would look like the following:
PS> Get-Customer -FirstName FRED, GEORGE -LastName SMITH -StartDate 1/1 -ReturnSqlQuery
SELECT
Customers.CustomerId AS CustomerId,
Customers.FirstName AS FirstName,
Customers.LastName AS LastName,
Customers.StartDate AS StartDate
FROM
Customers
WHERE
(Customers.FirstName LIKE 'fred' OR Customers.FirstName LIKE 'george') AND
(Customers.LastName LIKE 'SMITH') AND
(Customers.StartDate = '2017-01-01')
By default, string property types allow the question mark (?) and asterisk (*) to be used as wildcards. Those are not SQL wildcards, though, so they are automatically replaced with their corresponding SQL wildcards characters.
To prevent this replacement from happening, the AllowWildcards attribute can be set to $false.
Example:
DbReaderCommand Get-Customer {
[MagicDbInfo(FromClause = 'FROM Customers')]
param(
[MagicDbProp(ColumnName='Customers.CustomerId')]
[int] $CustomerId,
[MagicDbProp(ColumnName='Customers.FirstName', AllowWildcards=$false)]
[string] $FirstName,
[MagicDbProp(ColumnName='Customers.LastName')]
[string] $LastName
)
}
PS> Get-Customer -FirstName First?ame* -LastName Last?ame* -ReturnSqlQuery
SELECT
Customers.CustomerId AS CustomerId,
Customers.FirstName AS FirstName,
Customers.LastName AS LastName
FROM
Customers
WHERE
(Customers.FirstName = 'First?ame*') AND
(Customers.LastName LIKE 'Last_ame%')
The PropertyName attribute allows a SQL column name to be overriden. By default, the column name is determined by the parameter name in the param() block.
Example:
DbReaderCommand Get-Customer {
[MagicDbInfo(FromClause = 'FROM Customers')]
param(
[MagicDbProp(ColumnName='Customers.CustomerId')]
[int] $CustomerId,
[MagicDbProp(ColumnName='Customers.FirstName', PropertyName='RenamedFirstName')]
[string] $FirstName,
[MagicDbProp(ColumnName='Customers.LastName')]
[string] $LastName
)
}
PS> Get-Customer -FirstName First -LastName Name -ReturnSqlQuery
SELECT
Customers.CustomerId AS CustomerId,
Customers.FirstName AS RenamedFirstName,
Customers.LastName AS LastName
FROM
Customers
WHERE
(Customers.FirstName LIKE 'First') AND
(Customers.LastName LIKE 'Name')