PowerShell Scripting: Using and Querying Datasets Directly in PowerShell

Introduction

Running direct queries on SQL can be a performance drag especially where you have thousands of records. Better to bring the data to a local Dataset and run your queries. So say you read data from a SQL table from PowerShell into a Dataset. Then read data from a comma delimited file and use one of the fields in each record to find the corresponding record in the data from the SQL table. You can scan through the dataset to find the record you want and then pull the data from the second file. One way is to use the ForEach statement, which means go through every record serially to find the correct one. When you are talking about thousands of record, it becomes a performance drag. The script below script can improve performance greatly.

PowerShell is very similar to C#.Dotnet. You can use the same query style on datasets in C# in a PowerShell script. Here is what you do:

In the sql table we have:

EmployeeID, Firstname, lastname, Initials, Country, City

There are 10,000 records, read into dataset.

In the comma delimited file File2 we have

EmployeeID, field1, field2

1000 records

For each EmployeeID in File2 find the corresponding employeeID in dataset and get the country.

PowerShell Code

# SQL Query get Active users into dataset

#

$SQLServer = ‘Server1’

$SQLDBName = ‘Database1’

$SqlQuery = “select EmployeeID, EmployeeID, Firstname, lastname, Initials, Country, City

from EmployeeInfo

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlConnection.ConnectionString = “Server = $SQLServer; Database = $SQLDBName; Integrated Security = True”

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlCmd.CommandText = $SqlQuery

$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

$SqlAdapter.SelectCommand = $SqlCmd$DataSet = New-Object System.Data.DataSet

$SqlAdapter.Fill($DataSet)

$SqlConnection.Close()

#

#Read in the File2 data file

$file = (Get-Content “C:\Temp\File2.txt”)

for($i=1;$i -lt $file.count;$i++){

$csvobj = ($file[$i] -split “,”)

 

#EmployeeID is in $csvobj[0]

# Add the Country from the dataset using query

#

$emplyID = @()

$emplyID = $DataSet.tables[0].select(“EmployeeID='”+$csvobj[0]+”‘”)

If ($emplyID.length -ne 0)

#we have a match

{

Foreach ($Row in $emplyID)

{

[string]$country = $Row[“Country”]

}

}

}

Advertisements