This is a script that performs an ExecuteNonQuery commands against a SQL database. This script has a modular function that performs the commands necessary to establish, open, close and execute the database connection and commands. To perform queries, simply pass the connection string and command to execute to the ExecNonQuery function and it does the rest. The script also performs write-Host commands to notify the console what it's doing so you're not left wondering. There is also exception handling to take care of any issues that might occur while working with the database.

PowerShell
Edit|Remove
######################################################### 
# Script:      SqlExecNonQuery.ps1                      # 
# Created By:  Nathon Dalton                            # 
# Created On:  08/16/2011                               # 
# Description: Execute a command against a SQL database # 
#              without returning a result (NonQuery).   # 
######################################################### 
 
############# 
# Functions # 
############# 
 
# Performs an ExecuteNonQuery command against the database connection. 
function ExecNonQuery 
{ 
    param ($conStr$cmdText) 
 
    # Determine if parameters were correctly populated. 
    if (!$conStr -or !$cmdText) 
    { 
        # One or more parameters didn't contain values. 
        write-Host "ExecNonQuery function called with no connection string and/or command text." 
    } 
    else 
    { 
        write-Host "Creating SQL Connection..." 
        # Instantiate new SqlConnection object. 
        $Connection = New-Object System.Data.SQLClient.SQLConnection 
         
        # Set the SqlConnection object's connection string to the passed value. 
        $Connection.ConnectionString = $conStr 
         
        # Perform database operations in try-catch-finally block since database operations often fail. 
        try 
        { 
            write-Host "Opening SQL Connection..." 
            # Open the connection to the database. 
            $Connection.Open() 
             
            write-Host "Creating SQL Command..." 
            # Instantiate a SqlCommand object. 
            $Command = New-Object System.Data.SQLClient.SQLCommand 
            # Set the SqlCommand's connection to the SqlConnection object above. 
            $Command.Connection = $Connection 
            # Set the SqlCommand's command text to the query value passed in. 
            $Command.CommandText = $cmdText 
             
            write-Host "Executing SQL Command..." 
            # Execute the command against the database without returning results (NonQuery). 
            $Command.ExecuteNonQuery() 
        } 
        catch [System.Data.SqlClient.SqlException] 
        { 
            # A SqlException occurred. According to documentation, this happens when a command is executed against a locked row. 
            write-Host "One or more of the rows being affected were locked. Please check your query and data then try again." 
        } 
        catch 
        { 
            # An generic error occurred somewhere in the try area. 
            write-Host "An error occurred while attempting to open the database connection and execute a command." 
        } 
        finally { 
            # Determine if the connection was opened. 
            if ($Connection.State -eq "Open") 
            { 
                write-Host "Closing Connection..." 
                # Close the currently open connection. 
                $Connection.Close() 
            } 
        } 
    } 
} 
 
################ 
# Script Start # 
################ 
 
# Specify SQL Connection String. 
$conStr = "server=(local);database=MyDB;trusted_connection=true;" 
 
# Execute SQL Command Against Database. 
ExecNonQuery -conStr $conStr -cmdText "exec sp_myStoredProc" 
 
write-Host "Query Complete!"
Created By: Nathon Dalton
Created On: 8/17/2011