Inserting Binary Objects Into A SQL Server Table Using A Stored Procedure And PowerShell



Our team was tasked with putting scanned expense report receipts (.PDF) in a SQL Server table for review.  As the DBA I thought my part was pretty straight forward.  I knocked out the table and associated objects including the stored procedures to service the table and was enjoying a cup of coffee when I realized I had not tested my insert procedure.  Then it dawned on me – I had no way to test it in SSMS.  How can you use a text based tool to manipulate binary files into the database?  If you’ve figured that one out I’d love to see it.  If you haven’t then perhaps my PowerShell approach will be of some use to you.

First let’s create some basic SQL objects, a table and a stored procedure:

CREATE TABLE TAPTEST (Col1 VARBINARY(MAX) NULL)

GO

CREATE PROCEDURE usp_TAPTEST @Col1 VARBINARY (MAX) AS

      INSERT INTO dbo.TAPTEST (Col1) VALUES (@Col1)

GO

Here is the PowerShell code if you are in a hurry but it’s worth going through line by line as we will do below.

#Get the file

      [Byte[]]$file = get-content -Encoding Byte c:\Temp\TAPTEST.pdf 

#Connect to DB   

      $DB = “server=MyInstanceName;integrated security=sspi;database=Bob”

      $conn = new-object System.Data.SqlClient.SqlConnection($DB)

#Build the command and parameters

      $cmd = new-object System.Data.SqlClient.SqlCommand(usp_TAPTEST”,$conn)

      $cmd.CommandType = [System.Data.CommandType]StoredProcedure

      $cmd.Parameters.Add(“@Col1”, [System.Data.SqlDbType]VarBinary)

      $cmd.Parameters[“@Col1”].Size = 1

      $cmd.Parameters[“@Col1”].Value = $file

#Execute the command

      $conn.Open()

      $cmd.ExecuteNonQuery()

 Taking a closer look.

#Get the file

      [Byte[]]$file = get-content -Encoding Byte c:\Temp\TAPTEST.pdf

 The square brackets force the data type for the $file variable.  Additionally, since Get-Content normally reads the file as text the –Encoding parameter is used to tell it to read it as binary.

Connecting to the database is just as you would do for any other SMO connection, nothing special here.

#Connect to DB   

      $DB = “server=MyInstanceName;integrated security=sspi;database=Bob”

      $conn = new-object System.Data.SqlClient.SqlConnection($DB)

Building the command and parameter objects was a little more interesting.

 

#Build the command and parameters

      $cmd = new-object System.Data.SqlClient.SqlCommand(usp_TAPTEST”,$conn)

 

Note the command text above:  usp_TAPTEST which will be executed at the server.  Any valid DML should execute, including the following. usp_TAPTEST @Col1 = 1″ which works great as long as you have a printable data type.  If it weren’t for reading the file into a Byte data type in the first line we’d be stuck again.   So how DO you tell it this is a binary object?  Read on.

In the first line below we tell PowerShell to treat the CommandText as a stored procedure.   Then we set the first parameter’s data type to varbinary.  Setting the size parameter to -1 tells it that the size is MAX  Finally, set the value of the parameter to the file you read in initially.

 

      $cmd.CommandType = [System.Data.CommandType]StoredProcedure

      $cmd.Parameters.Add(“@Col1”, [System.Data.SqlDbType]VarBinary)

      $cmd.Parameters[“@Col1”].Size = 1

      $cmd.Parameters[“@Col1”].Value = $file

 At this point you are ready to go, just open the connection and execute.

#Execute the command

      $conn.Open()

      $cmd.ExecuteNonQuery()

 

And voila!

Notice the French lingo – this is a high class blog!

 

 

 

 

 

As you can see, the code is pretty simple with the only tricky part having to do with fitting the binary nature of the document with the parameter used by the SQL Command object.

Hopefully you will find this useful both as stand-alone code and as another step in the drive for PowerShell mastery.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s