Performing an INSERT from a PowerShell script

This code demonstrates how to do an INSERT into SQL Server from a PowerShell script using an ADO.NET command object with strongly-typed parameters.  This script issues a DIR *.* command and inserts the results to the database.  While this example is not particularly useful, it is hopefully very simple and understandable to explain the concepts.

In general, parameterized SQL queries (like the below) perform better and are less vulnerable to SQL injection attacks than SQL queries created using string concatenation*.

The script code displayed in the blog post performs several INSERT statements using a row-by-row method.  As a bonus, I have uploaded a version of this script to SkyDrive that uses two flavors of Table-Valued parameters which should give you better performance for big datasets.

 

Download Scripts:

SkyDrive link to Row by Row only Script (has less commentary than this blog post, but code is identical)

SkyDrive link to Row by Row and Table Valued Parameter Script

Note: You have to edit the above files in a text editor to set the SQL Server name and DB name.  Once you edit the file it is no longer considered "remote" so you can run the file as long as the PowerShell ExecutionPolicy is set to RemoteSigned.

 

To try this script, first create a table in a junk database on a test SQL server:

CREATE TABLE FilesInFolder (

       ID INT IDENTITY(1,1) NOT NULL,

       TheFileName NVARCHAR(260) NOT NULL,

       FileLength BIGINT NOT NULL,

       LastModified DATETIME2 NOT NULL,

       PRIMARY KEY (ID)

);

 

Then paste the following code into a .PS1 file (you have to edit the $DBServer and $DBName values first) and run it using Powershell:

 

function Do-FilesInsertRowByRow ([Data.SqlClient.SqlConnection] $OpenSQLConnection) {

 

    $sqlCommand = New-Object System.Data.SqlClient.SqlCommand

    $sqlCommand.Connection = $sqlConnection

 

    # This SQL query will insert 1 row based on the parameters, and then will return the ID

    # field of the row that was inserted.

    $sqlCommand.CommandText = "SET NOCOUNT ON; " +

        "INSERT INTO dbo.FilesInFolder (TheFileName,FileLength,LastModified) " +

        "VALUES (@TheFileName,@FileLength,@LastModified); " +

        "SELECT SCOPE_IDENTITY() as [InsertedID]; "

   

    # I am adding the parameters without values outside the loop.  This means that inside the

    #  loop all I have to do is assign the values and say "run" - much less work than setting

    #  up everything from scratch in each iteration.

    # Also notice the doubled-up (()) - this is how you create a new object and then

    #  immediately pass it as a function parameter in PowerShell.

    # Next, the class names in square brackets are .NET types.  Powershell assumes System.,

    #  so you can omit it.  The double-colon is how you reference enumeration members.

    # Finally, I am piping the output of these calls to Out-Null since otherwise PowerShell

    #  would output the properties of the command object to the console on each call since

    #  there is no assignment made to a variable or another pipeline destination.

    $sqlCommand.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@TheFileName",[Data.SQLDBType]::NVarChar, 260))) | Out-Null

    $sqlCommand.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@FileLength",[Data.SQLDBType]::BigInt))) | Out-Null

    $sqlCommand.Parameters.Add((New-Object Data.SqlClient.SqlParameter("@LastModified",[Data.SQLDBType]::DateTime2))) | Out-Null

   

    # I love how I can foreach over a call like "dir *.*" in PowerShell!!

    foreach ($file in dir *.*) {

        # Here we set the values of the pre-existing parameters based on the $file iterator

        $sqlCommand.Parameters[0].Value = $file.FullName

        $sqlCommand.Parameters[1].Value = $file.Length

        $sqlCommand.Parameters[2].Value = $file.LastWriteTime

 

        # Run the query and get the scope ID back into $InsertedID

        $InsertedID = $sqlCommand.ExecuteScalar()

        # Write to the console.

        "Inserted row ID $InsertedID for file " + $file.Name

    }

 

}

 

 

# Open SQL connection (you have to change these variables)

$DBServer = "MySQLServerName\MyInstanceName"

$DBName = "MyJunkDBName"

$sqlConnection = New-Object System.Data.SqlClient.SqlConnection

$sqlConnection.ConnectionString = "Server=$DBServer;Database=$DBName;Integrated Security=True;"

$sqlConnection.Open()

 

# Quit if the SQL connection didn't open properly.

if ($sqlConnection.State -ne [Data.ConnectionState]::Open) {

    "Connection to DB is not open."

    Exit

}

 

# Call the function that does the inserts.

Do-FilesInsertRowByRow ($sqlConnection)

 

# Close the connection.

if ($sqlConnection.State -eq [Data.ConnectionState]::Open) {

    $sqlConnection.Close()

}

 

 

 

*By "in general", I mean that this is true most of the time, and in the few times when it is not true, the use of parameters is unlikely to leave you worse off than not using parameters.  Plainly, if you are using string concatenation to write SQL statements in any language, you are doing it very wrong and potentially leaving your database open for attack and exploitation.

 

I'm a member of the SkyDrive Insiders program; you can learn more about this program here.  I published a quick-start guide to using SkyDrive at work here.  If you have any questions about SkyDrive, please ask me!

PowerShell: Redirecting Console Output and Error Output to a Variable and E-mailing it

I recently had the need to script the execution of a command-line utility and thought it would be a good learning experience to try doing it with PowerShell.  The utility would sometimes output to the STDOUT, but would also send its output to STDERR if there was a problem.  This seemed to involve many different problems all at once including:

• Running an EXE with a space in the path and multiple command-line parameters
• Capture both STDERR and STDOUT to a variable
• Escaping special characters/using special characters
• Doing string concatenation
• Doing explicit type conversion
• Sending an email
• Joining a string array to a single string variable
• Doing a "FOR" loop (optional but fun).

Anyway, here's the script I came up with that runs a command-line app and takes all the output (including the error stream) and joins it into a string and then sends it in an email to me.  It's a bit ugly because I am a PowerShell novice, but it does work.  The only trouble I've noticed is that sometimes the error lines will be a bit out of sequence from the corresponding stdout lines.  If someone knows how to fix that, please let me know in the comments!!!

 

#Setup the command string - notice the `" to escape the double-quotes inside the string (deals with spaces in path)

# and the old batch file trick of doing 2>&1 to ensure STDERR is piped to STDOUT.

$command = "& `"C:\Users\myprofile\Documents\visual studio 2010\Projects\TestOutput\TestOutput\bin\Debug\testoutput.exe`" someparameter -xyz someotherparameter -abc someotherthing -rfz -a somethinghere 2>&1"
#Execute the command and put the output in an array.

$console_output_array = invoke-expression $command

#loop through the array and print out the results to the command line (optional)

for ($i=0; $i -lt $console_output_array.length; $i++)
{
    [string]$i + "=<" + $console_output_array[$i] + ">"
}

#create a single string by joining together the array

$console_output_string = [string]::join("`r`n",$console_output_array)

#send an email with the results

$emailFrom = "fromemail@example.com"
$emailTo = "toemail@example.com" 
$subject = "Email subject goes here"
$smtp = new-object Net.Mail.SmtpClient("yoursmtpserver.example.com", 25)
$smtp.Send($emailFrom, $emailTo, $subject, $console_output_string)