Well my English is too bad to explain what i want but i will try
There are some very big multiline string - part of sql query
$q =
"Values
(
'$($line.'common.uuid')',
'$($line.'user.user_id')',
'$($line.'user.ldap.user_attributes.displayName')',
'$($line.'user.email_address')',
'$($line.'common.owner')',
'$($line.'common.current_phone_number')',
'$($line.'common.current_country_name')',
)"
That string is very hard to edit Are there any way to do like that in simple way? Or only something like foreach $field in $fields?
$fields = '
common.uuid
user.user_id
user.ldap.user_attributes.displayName
user.email_address
.....'
$q = '(
$line.($fields somehow here)
)'
There can be about 100 lines of values and its much easier to edit that list in $fields instead of $q
Thanks!
CodePudding user response:
As Mathias R. Jessen points out, it is generally preferable to use a parameterized SQL query than to use string interpolation to bake values into a query string, not least for better security, but also to avoid round-trip conversion to and from strings for non-string values.
If you do want to stick with your string-interpolation approach, here's a solution based on the .psobject
intrinsic member, which is a rich source of reflection about any object and allows enumerating its properties via the .Properties
collection:
# Sample input object.
$line = [pscustomobject] @{
'common.uuid' = '1-2-3';
'user.user_id' = 'jdoe';
}
# Use string interpolation to embed all of the object's property values.
$q = @"
Values (
$($line.psobject.Properties.Value.ForEach({ "'" $_ "'" }) -join ",`n ")
)
"@
This solution makes two assumptions:
You're interested in the values of all properties of the input object.
You do not need nested property access (in the code in your question, a property access such as
.'user.user_id'
is a single property access targeting a property named verbatimuser.user_id
, even though the name itself suggests a property path (nested access)).
If these assumptions don't hold, see Daniel's helpful answer.
Note that, as in your question, all values are represented as strings ('...'
) in the resulting query fragment.
If you choose a parameterized-query approach instead, you can similarly use $line.psobject.Properties.Value
to obtain an array of all property values. (Note that even though .Value
is applied to a collection of elements, PowerShell conveniently collects its elements' .Value
values, which is a feature known as member enumeration).
The above stores the following in variable $q
:
Values (
'1-2-3',
'jdoe'
)
See this answer for an overview of PowerShell's string interpolation via expandable strings ("..."
).
CodePudding user response:
As usual, mklement0 offers a great approach looping through the object's properties directly, though you may have to rearrange the sql statement to accommodate the order of the properties and/or add some additional filtering if you are not interested in all of the properties on the object.
I will go ahead and post my solution as it may offer some additional insight to how this can be done.
# Example object data
$lines = @(
@{
common = @{
uuid = 'some uuid'
owner = 'some owner'
current_phone_number = '222-555-1212'
current_country_name = 'USA'
}
user = @{
user_id = 'some userid'
email_address = '[email protected]'
ldap = @{user_attributes = @{displayName = 'Some User' } }
}
}
@{
common = @{
uuid = 'some other uuid'
owner = 'some other owner'
current_phone_number = '222-555-1212'
current_country_name = 'UK'
}
user = @{
user_id = 'some other userid'
email_address = '[email protected]'
ldap = @{user_attributes = @{displayName = 'Some other User' } }
}
}
)
# Create an array of the fields we are interested in,
# in the order we would like them to appear in the sql statement
$fields = @(
'common.uuid',
'user.user_id',
'user.ldap.user_attributes.displayName',
'user.email_address',
'common.owner',
'common.current_phone_number',
'common.current_country_name'
)
# loop through the fields forming the line
# and then join them together using -join
$values = ($fields | ForEach-Object {
"`t'`$(`$line.$_)'"
}) -join ',`n'
# add in the rest of the statement
$q = "Values (`n $values `n)"
foreach ($line in $lines) {
# Use ExpandString to expand the variables inside
# our $q string
$ExecutionContext.InvokeCommand.ExpandString($q)
}
Output
Values (
'some uuid',
'some userid',
'Some User',
'[email protected]',
'some owner',
'222-555-1212',
'USA'
)
Values (
'some other uuid',
'some other userid',
'Some other User',
'[email protected]',
'some other owner',
'222-555-1212',
'UK'
)