Using PowerShell to View Images in Database
Accessing database using PowerShell is pretty simple as all you need to do is to use the .NET classes. You can also retrieve the images from the database and view them in the windows form. Let's see how this can be done.
First let's write some PowerShell script to access the Northwind database and display the results on the screen.
Here is the script to retrieve the data and display on the screen.
$conn = new-object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "server=localhost;database=Northwind;integrated security=true"
$cmd = new-object System.Data.SqlClient.SqlCommand
$cmd.CommandText = "SELECT CategoryID, CategoryName, Description,Picture FROm Categories"
$cmd.Connection = $conn
$adapter = new-object System.Data.SqlClient.SqlDataAdapter
$adapter.SelectCommand = $cmd
$ds = new-object System.Data.DataSet
$adapter.Fill($ds)
$conn.close()
$ds.Tables[0]
If you are familiar with .NET then it will be very simple for you to understand. The result is shown in the screen shot below:
Pretty simple right!
Now, I want that when I select the category it will diplay me the image of the category which is stored in the Picture column of the Categories table.
Here is the script:
while(1)
{
$val = read-host "Select a category or press q to quit"
if($val -eq "q") { return }
$val = [int] $val
$bytes = $ds.Tables[0].Rows[$val][3]
[void][reflection.assembly]::LoadWithPartialName("System.IO")
$memoryStream = new-object System.IO.MemoryStream
$memoryStream.write($bytes,78,$bytes.Length - 78)
[void][reflection.assembly]::LoadWithPartialName("System.Windows.Forms")
$form = new-object Windows.Forms.Form
$form.Text = "Image Viewer"
$pictureBox = new-object Windows.Forms.PictureBox
$pictureBox.Width = 300
$pictureBox.Height = 300
$pictureBox.Image = [System.Drawing.Image]::FromStream($memoryStream)
$form.controls.add($pictureBox)
$form.Add_Shown( { $form.Activate() } )
$form.ShowDialog()
}
Now, when I type the categoryID the respective image is displayed in the windows form.
Here is a different selection.
and so on.
I hope you enjoyed this post!
All these images of food is making me hungry so I am going to grab something to eat :)
Ohh before I forget here is the complete script:
$conn = new-object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "server=localhost;database=Northwind;integrated security=true"
$cmd = new-object System.Data.SqlClient.SqlCommand
$cmd.CommandText = "SELECT CategoryID, CategoryName, Description,Picture FROm Categories"
$cmd.Connection = $conn
$adapter = new-object System.Data.SqlClient.SqlDataAdapter
$adapter.SelectCommand = $cmd
$ds = new-object System.Data.DataSet
$adapter.Fill($ds)
$conn.close()
$ds.Tables[0]
while(1)
{
$val = read-host "Select a category or press q to quit"
if($val -eq "q") { return }
$val = [int] $val
$bytes = $ds.Tables[0].Rows[$val][3]
[void][reflection.assembly]::LoadWithPartialName("System.IO")
$memoryStream = new-object System.IO.MemoryStream
$memoryStream.write($bytes,78,$bytes.Length - 78)
[void][reflection.assembly]::LoadWithPartialName("System.Windows.Forms")
$form = new-object Windows.Forms.Form
$form.Text = "Image Viewer"
$pictureBox = new-object Windows.Forms.PictureBox
$pictureBox.Width = 300
$pictureBox.Height = 300
$pictureBox.Image = [System.Drawing.Image]::FromStream($memoryStream)
$form.controls.add($pictureBox)
$form.Add_Shown( { $form.Activate() } )
$form.ShowDialog()
}
UPDATE: You can view my article Accessing Database And Displaying Images Using PowerShell.