PowerShell Scripting: Using and Querying Datasets Directly in PowerShell


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
 #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"]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s