| external help file | Module Name | online version | schema |
|---|---|---|---|
Rnwood.Dataverse.Data.PowerShell.FrameworkSpecific.dll-Help.xml |
Rnwood.Dataverse.Data.PowerShell |
2.0.0 |
Invokes a Dataverse SQL query using Sql4Cds and writes any resulting rows to the pipeline.
Invoke-DataverseSql -Connection <ServiceClient> -Sql <String> [-UseTdsEndpoint] [-Timeout <Int32>]
[-Parameters <PSObject>] [-BatchSize <Int32>] [-MaxDegreeOfParallelism <Int32>] [-BypassCustomPluginExecution]
[-ProgressAction <ActionPreference>] [-WhatIf] [-Confirm] [<CommonParameters>]
Sql4Cds is a powerfull engine which can translate many SELECT, INSERT, UPDATE and DELETE Sql queries and execute them against Dataverse. This Cmdlet uses Sql4Cds to execute such queries.
If the query returns a result set, it will output to the pipeline with an object per row having a property per column in the result set. If applicable (e.g. for UPDATE), the affected row count is written to verbose output.
@parameters in the query will have their values taken from the Parameters property. This can be from the pipeline to allow the query to be executed multiple times.
PS C:\> Invoke-DataverseSql -connection $connection -sql "SELECT TOP 1 createdon FROM Contact WHERE lastname=@lastname" -parameters @{lastname="Wood"}
createdon
---------
28/11/2024 16:28:12Returns the rows from the SELECT query matching the @lastname parameter which is supplied.
PS C:\> @{lastname="Wood"}, @{lastname="Cat2"} | Invoke-DataverseSql -connection $c -sql "SELECT TOP 1 lastname, createdon FROM Contact WHERE lastname=@lastname"
lastname createdon
-------- ---------
Wood 28/11/2024 16:28:12
Cat2 28/11/2024 16:42:30Returns the rows from the SELECT query matching the @lastname parameters which are supplied via the pipeline. The query is executed once for each of the pipeline objects.
Controls the batch size used by Sql4Cds.
Type: Int32
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseBypasses custom plugins. See Sql4Cds docs.
Type: SwitchParameter
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalsePrompts you for confirmation before running the cmdlet.
Type: SwitchParameter
Parameter Sets: (All)
Aliases: cf
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseDataverseConnection instance obtained from Get-DataverseConnnection cmdlet
Type: ServiceClient
Parameter Sets: (All)
Aliases:
Required: True
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseMaximum number of threads to use. See Sql4Cds docs.
Type: Int32
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseSpecifies values for the @parameters used in the Sql. This can be a Hashtable or any PSObject with properties.
This can be read from the pipeline to allow the query to be executed once per input object using different values.
Type: PSObject
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: None
Accept pipeline input: True (ByValue)
Accept wildcard characters: FalseSQL to execute. See Sql4Cds docs for supported queries. Can contain @parameters.
Type: String
Parameter Sets: (All)
Aliases:
Required: True
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseTimeout for query to execute. See Sql4Cds docs.
Type: Int32
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseLet Sql4Cds use the TDS endpoint or not for compatible queries. The default is to not use this.
Type: SwitchParameter
Parameter Sets: (All)
Aliases:
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseShows what would happen if the cmdlet runs. The cmdlet is not run. Does not apply to read only queries.
Type: SwitchParameter
Parameter Sets: (All)
Aliases: wi
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseType: ActionPreference
Parameter Sets: (All)
Aliases: proga
Required: False
Position: Named
Default value: None
Accept pipeline input: False
Accept wildcard characters: FalseThis cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable, -InformationAction, -InformationVariable, -OutVariable, -OutBuffer, -PipelineVariable, -Verbose, -WarningAction, and -WarningVariable. For more information, see about_CommonParameters.
A special thanks to Mark Carrington for his amazing open-source project that has enabled this.