PowerShell Scripting: Using and Querying Datasets Directly in PowerShell

  • Article

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 records, 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 a dataset.

In the comma delimited file, File2, we have:

EmployeeID, field1, field2

1000 records

For each EmployeeID in File2 find the corresponding employeeID in the 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"]
 
}
 
}
 
}