#91 : How to export SQL Server table to JSON?

JSON stands for Javascript Object Natation. It has become a standard format to exchange and store data since when Big data revolution has begun. It is now used in several languages to exchange data. In Powershell, we have cmdlets: ConvertTo-JSON that can be utilized for converting a .NET object to a JSON format output.

Below is the complete demonstration:

Creating a table for the example: 

Before we export the data, let's create a table with some data into it. Below example creates a table named Employee and adds two rows only. 

create table Employee ( EmpID int, Name varchar(200)) 
go 

insert into Employee values ( 1 , 'Som Tripathi' ) 
insert into Employee values ( 2 , 'Ostwald' ) 
go 

select * from Employee 
go

Output: 

Now we can continue with the script that exports the columns in JSON format.

Reading from SQL Server instance and convert to JSON: 

To be able to read data from SQL Server table, you must have a table name and running SQL Server instance. I assume you have a database called db123 and server named SQL123\TST1.

Script: 

#--Exporting SQL Server table to JSON --#

cls 

#--Establishing connection to SQL Server --# 
$InstanceName = "SQLTEST1\ENG_TST1"
$connectionString = "Server=$InstanceName;Database=dbadb;Integrated Security=True;"

#--Main Query --# 
$query = "SELECT * FROM Employee"

$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = $query

$result = $command.ExecuteReader()

$table = new-object "System.Data.DataTable"

$table.Load($result)

#--Exporting data to the screen --# 
$table | select $table.Columns.ColumnName | ConvertTo-Json


$connection.Close()

Output: 

The example is written in a simple manner, you may enhance and create function out of it.

While you are about to export a very large table (with size more than 1 GB), try to perform the task in batches. This will help reducing the memory consumption. You can create a view or modify the query to perform this task and if you have a unique column, your task will be more simpler.

Thanks for reading this article. Please do add some comments if you find any difficulty understanding the script. 

#90 : How to display balloon popup with Powershell?

With Powershell, you can leverage nearly all features available for .NET applications. Sometimes you must have noticed a popup in the right corner that pops up and it contains icons associated with it. Such notification can be useful if your script is performing some task with your account logged in to the host.

You can see the video here with live demonstration:


Script:

#--Declaring the function that takes care of balloon popup --#
function Show-BaloonPopup([String]$Title, [String]$Message , [int]$Delay=5   )
{

    Add-Type -AssemblyName System.Windows.Forms
    $script:balloon = New-Object System.Windows.Forms.NotifyIcon

    $Icon = 'Info' 
    $balloon.Icon=[System.Drawing.Icon]::ExtractAssociatedIcon("C:\bin\Apps\Powershell\ico.ico")

    $balloon.BalloonTipIcon  = $Icon

    $balloon.BalloonTipText  = $Message
    $balloon.BalloonTipTitle = $Title
    $balloon.Visible         = $true
    $balloon.ShowBalloonTip($Delay*1000)


    Start-Sleep -s $Delay 
    $balloon.Dispose()
} 

#--Calling the function with appropriate parameters --# 
Show-BaloonPopup "My Title" "This is the message. You operation has been completed." 5 

Output: 
You may notice a popup something like below. It appears for 5 seconds and disappear. We have added this logic due to reason that we don't have further need to have icon. If you remove the part of script that dispose the variable, the icon will appear forever and it will only go unless the system is rebooted or re-logged in.

Hope, it was east to implement. Please post comments if you have any difficulty implementing it. 

Thanks for reading this article. 

#89 : Powershell on Linux

You can install Powershell on your Linux box as well. This is a major change and it makes Powershell not just unlimited but limitless.

Below is a GitHub link for Microsoft page that describes all the latest progresses in this field:
https://github.com/PowerShell/PowerShell/blob/master/docs/installation/linux.md

#88: Difference between two dates

Based on previous posts, I am writing this article. Dates are interesting elements and in most of the scripts, we need to display reports that reflects difference of dates such as the time consumed in a copy operation or any other task. Such statistical information can only be produced if we know how to manipulate date as data type.

DateTime
DateTime is a data-type which is a .NET Class. This class can store date as data-type and it has several mothods that can help us.

You can declare it by casting.

Example: 

[DateTime]$Date1="01/29/2017"  
[DateTime]$Date2="01/29/2019"  
    
( $Date2 - $Date1 ).Days.ToString().padleft(3,"0") + ":" + ` 
( $Date2 - $Date1 ).Hours.ToString().padleft(2,"0")  + ":" + ` 
( $Date2 - $Date1 ).Minutes.ToString().padleft(2,"0")  + ":" +  ` 
( $Date2 - $Date1 ).Seconds.ToString().padleft(2,"0")

Output: 
730:00:00:00

Explanation: 
The first line declares variable by casting it with DateTime data-type and same is repeated with another variable in second line.

Next four lines would get the difference in days, hours, minutes and seconds. With each line we have using padding so that 1 is shown as 01 and 2 is shown as 02 and so on so forth.

A concatenation is done with colons (:) and days are shown with three padding of zeros while hour, minute and seconds are padded with two zeros. 

Hope you enjoyed this tip!

Thanks for reading the article. Please send your comments if you find any difficulty understanding this tip.