PowerShell is the new shell introduced by Microsoft. PowerShell allows you to write scripts that can perform various tasks. These tasks include moving data between servers, deploying applications, displaying data etc. In this article I will demonstrate how to fetch data from the database using PowerShell. This article will allow cover how to display images in the windows form using PowerShell.

Introduction:

 

PowerShell is the new shell introduced by Microsoft. PowerShell allows you to write scripts that can perform various tasks. These tasks include moving data between servers, deploying applications, displaying data etc. In this article I will demonstrate how to fetch data from the database using PowerShell. This article will allow cover how to display images in the windows form using PowerShell.

 

Downloading and Installing PowerShell:

 

The first task you need to perform is to download and install Windows PowerShell. You can download PowerShell using this link. After downloading the PowerShell you simply need to install it using the installer provided.

 

Running PowerShell:

 

Now, run the PowerShell using the Windows PowerShell icon. You will see a blue command prompt. You can type dir and view your current location in the hard drive. Now let’s get to our main task which is to display data from the database.

 

Accessing Database Using PowerShell:

 

I know you might be thinking that I am rushing to the advance stuff without covering the basics. Just hang on and you will see how closely it relates with  the .NET framework.

 

Let’s create a simple SqlConnection object.

 

$conn = new-object System.Data.SqlClient.SqlConnection

 

Now, let’s assign the connection string to the SqlConnection object.

 

$conn.ConnectionString = "server=localhost;database=Northwind;integrated security=true"

 

You can see it is almost you are coding in C#. But don’t be carried away since PowerShell is quite different from the C# language. Now, let’s view the complete code which is used to load the data from the Categories table located in the Northwind database to the DataSet object.

 

 

$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]

 

Simply, copy and paste the code in PowerShell and you will see the following output.

 

 

 

As, you can see in the image above the data from the Categories table is displayed in the PowerShell window. If you want to export the data to a file then you can do the following:

 

$ds.Tables[0] > c:\Categories.txt

 

or even to an excel file

 

$ds.Tables[0] > c:\Categories.xls

 

Pretty cool right!

 

Displaying Images in the Form:

 

If you notice in the image above the Picture column is displayed as bytes. Let’s see how we can display the picture in the windows form using a PictureBox control. Check out the code below:  

 

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()

 

}

 

We are running a while loop which ask the user to enter the categoryID of the picture to be displayed. Then we retrieve the byte array from the DataSet and write it into the memory stream. Before using the memory stream object we need to load the System.IO assembly which is performed by [void][reflection.assembly]::LoadWithPartialName("System.IO").

 

The next part creates a windows form by first loading the System.Windows.Forms assembly. A new PictureBox control is created. Then we assign the Image property of the PictureBox control to the Image returned by the Image.FromStream method. Since, the FromStream is a static method of the Image class the syntax for calling it is a little different. Later the PictureBox control is added to the form and the form is displayed.

 

Check out the images below which shows the windows form created and the image being displayed.

 

 

 

Conclusion:

 

The new Windows PowerShell is bundled with many new features making administrative tasks fast and easy to perform.

 

I hope you liked the article, happy coding!