PowerShell Script for Export to MS SQL Database - data and file upload

This post explains how to upload data from PowerShell script to MS SQL Database table. Also it will uploads “Test.xml” data to the database table in binary format along with table data.


Function ExportToDB
{
#Get the file
[Byte[]]$file = get-content -Encoding Byte "E:\data\test.xml"

#Connect to DB
$ConnectionString ="Server=XX-XXXX\XXX,10000; Database=TESTDB;Integrated Security=true"
$conn = new-object System.Data.SqlClient.SqlConnection($ConnectionString)

Try
{
$conn.Open()

#Build the command and parameters
$sqlInsert = "INSERT INTO [SITSCD].[dbo].[ContentClassificationReports] (DateCreated,UserCreated,DateModified,UserModified,BinaryContent,Enviroment) VALUES (@DC,@UC,@DM,@UM,@File,@Env)"
$cmd = new-object System.Data.SqlClient.SqlCommand($sqlInsert,$conn)
$cmd.CommandType = [System.Data.CommandType]'Text'
$cmd.Parameters.Add(“@File”, [System.Data.SqlDbType]'VarBinary')
$cmd.Parameters.Add(“@DC”, [System.Data.SqlDbType]'DateTime')
$cmd.Parameters.Add(“@DM”, [System.Data.SqlDbType]'DateTime')
$cmd.Parameters.Add(“@UC”, [System.Data.SqlDbType]'nvarchar')
$cmd.Parameters.Add(“@UM”, [System.Data.SqlDbType]'nvarchar')
$cmd.Parameters.Add(“@Env”, [System.Data.SqlDbType]'nvarchar')

$cmd.Parameters[“@File”].Size = –1
$cmd.Parameters[“@File”].Value = $file
$cmd.Parameters[“@DC”].Value = date
$cmd.Parameters[“@DM”].Value = date
$cmd.Parameters[“@UC”].Value = $UserName
$cmd.Parameters[“@UM”].Value = $UserName
$cmd.Parameters[“@Env”].Value = $Location


#Execute the command

$cmd.ExecuteNonQuery()
}
Catch
{
Write-Verbose "[Catch] Error While uploading document in  to database."
}
Finally
{
Write-Verbose "[FINALLY] Performing cleanup actions." -Verbose
$conn.Close()
}    

}

ExportToDB