PowerShell Dynamic Parameters and SQL Server

Have you ever been in a situation that you want to call a cmdlet or a function with a parameter that depends on a conditional criteria that is available as a list? In this article I will show a technique where you can use PowerShell Dynamic Parameters to assist the user with parameter values.

In the documentation of Dynamic Parameters found at about_Functions_Advanced_Parameters in get-help it is defined as “parameters of a cmdlet, function, or script that are available only under certain conditions.” And can be created so that appears “only when another parameter is used in the function command or when another parameter has a certain value.” So, we can say that PowerShell Dynamic Parameters are used when the result of a parameter depends on the previous parameter.

In the example I will present,

I will create a function called Get-SQLdatabase that outputs database information. I will give the user the ability to use two parameters :

• Name of the SQL Server instance
• Optionally, choose a databases from the server specified in the first parameter.

The functions starts by loading the assemblies and declaring the first parameter.

#load the assemblies
import-module sqlserver 
 
function Get-SQLdatabase{
    [CmdletBinding()]
    Param(
        [Parameter(
            Mandatory=$true,
            Position=1,
            HelpMessage=”Please provide the SQL Server name”
        )]
        [string]$SQLserver
    )

Parameter 1 is mandatory and corresponds to the SQL Server instance’s name. This example currently only works for trusted connections, but adding a parameter for the user and password would be mostly trivial.

Setting up the Dynamic Parameter

Now we want that the second parameter to not be mandatory because you can set a specific database or all of them. If you don’t include a value, then the function will show the properties of all databases on the instance.

However, if you want to choose a single database, instead of the user needing to know all of the databases and typing one in. The following code will let you simply choose the database you want to use from a dropdown list.

To set this up, there are a few extra configuration points we will need. We start with the parameter name, dictionary, and the collection of the attributes :

# Set the dynamic parameters' name
            $ParameterName = 'Database'
 
            # Create the dictionary
            $RuntimeParameterDictionary = New-Object 
System.Management.Automation.RuntimeDefinedParameterDictionary
 
            # Create the collection of attributes
            $AttributeCollection = New-Object 
System.Collections.ObjectModel.Collection[System.Attribute]

Second we need to create and set the parameters attributes, add the attributes to the attributes collection

# Create and set the parameters attributes
            $ParameterAttribute = New-Object 
System.Management.Automation.ParameterAttribute
            $ParameterAttribute.Mandatory = $False
            $ParameterAttribute.Position = 2
 
            # Add the attributes to the attributes collection
            $AttributeCollection.Add($ParameterAttribute)

 

Third we generate the Validate set in the System.Management.Automation.ValidateSetAttribute class . It is in this step that we will add the database names of the SQL Server instance specified in the first parameter. Here we connect to the SQL Server instance passed in the first parameter, get the databases names and add the result in the attributes collection :

# Generate and set the ValidateSet
            $server = new-object
 ("Microsoft.SqlServer.Management.Smo.Server") $SQLserver
            
            $arrset = $server.databases.name
            $ValidateSetAttribute = New-Object 
System.Management.Automation.ValidateSetAttribute($arrSet)
            # Add the ValidateSet to the attributes collection
            $AttributeCollection.Add($ValidateSetAttribute)

Finally we create and return the dynamic parameter:

# Create and return the dynamic parameter
            $RuntimeParameter = New-Object 
     System.Management.Automation.RuntimeDefinedParameter( 
     $ParameterName, [string], $AttributeCollection)
            $RuntimeParameterDictionary.Add
                         ( $ParameterName, $RuntimeParameter)
            return $RuntimeParameterDictionary

The rest of the function is simply there to use the database name that was passed in. All it will do is show the properties of one or all databases on your server:

begin {
            $server = new-object 
      (“Microsoft.SqlServer.Management.Smo.Server”) $SQLserver
    }
         
    process {
        #if the database parameter was passed shows only the 
        #properties of the database otherwise show 
        #the properties of all databases
        if ($psboundparameters.Keys.Contains('database'))  {
           $server.Databases[$psboundparameters.database]
        } else {
           $server.Databases
        }
    }

 

The complete function is here (or you can grab the entire code from our website in a .zip file here.):

#load the assemblies
import-module sqlserver 
 
function Get-SQLdatabase{
    [CmdletBinding()]
    Param(
        [Parameter(
            Mandatory=$true,
            Position=1,
            HelpMessage="Please provide the SQL Server name"
        )]
        [string]$SQLserver
    )
    DynamicParam {
 
            # Set the dynamic parameters' name
            $ParameterName = 'Database'
 
            # Create the dictionary
            $RuntimeParameterDictionary = New-Object 
System.Management.Automation.RuntimeDefinedParameterDictionary
 
            # Create the collection of attributes
            $AttributeCollection = New-Object 
System.Collections.ObjectModel.Collection[System.Attribute]
 
            # Create and set the parameters attributes
            $ParameterAttribute = New-Object 
System.Management.Automation.ParameterAttribute
            $ParameterAttribut.Mandatory = $False
            $ParameterAttribute.Posietion = 2
 
            # Add the attributes to the attributes collection
            $AttributeCollection.Add($ParameterAttribute)
 
            # Generate and set the ValidateSet
            $server = New-Object 
("Microsoft.SqlServer.Management.Smo.Server") $SQLserver
            
            $arrset = $server.databases.name
            $ValidateSetAttribute = New-Object 
System.Management.Automation.ValidateSetAttribute($arrSet)
 
            # Add the ValidateSet to the attributes collection
            $AttributeCollection.Add($ValidateSetAttribute)
 
            # Create and return the dynamic parameter
            $RuntimeParameter = New-Object 
System.Management.Automation.RuntimeDefinedParameter( 
$ParameterName, [string], $AttributeCollection)
            $RuntimeParameterDictionary.Add( $ParameterName, 
$RuntimeParameter)
            return $RuntimeParameterDictionary
    }
    begin {
            $server = new-object 
(“Microsoft.SqlServer.Management.Smo.Server”) $SQLserver
    }
         
    process {
        #if the database parameter was passed shows only the 
        #properties of the database
 otherwise show the 
        #properties of all databases
        if ($psboundparameters.Keys.Contains('database'))  {
           $server.Databases[$psboundparameters.database]
        } else {
           $server.Databases
        }
    }
}
#Test code to try out the function
#Get-SQLdatabase -SQLserver . -Database AdventureWorks2022

Testing the Dynamic Parameter

What this extra parameter work does for you can be seen when we start to execute the function. You can see that we have the databases listed using Intellisense when we go to get the second parameter :

As you can see in the picture all the databases from the SQL Server are listed for the second parameter.

Obviously, the point of the code is to show how the parameters work, but If you execute the code, you will see a bunch of statistics about your database and the code. For example, for the AdventureWorks2022 database on my server, (using the PowerShell Gridview to format the output):

Get-SQLdatabase -SQLserver . -Database AdventureWorks2022 
| Out-GridView

The output is something like:

With quite a few more columns. Remove the database parameter and the output will include all of the databases on your server.

Summary

In this article we covered how the Dynamic Parameters is come in handy in particular situations we need more from PowerShell and how to use in SQL Server. One of the most typical uses is a function that you use in an Ad-hoc fashion to fetch some details from a SQL Server regularly.

References

PowerShell Magazine

FoxDeploy

 

The post PowerShell Dynamic Parameters and SQL Server appeared first on Simple Talk.

from Simple Talk https://ift.tt/EydQBgC
via