Extracting SQL values into powershell variables?

Question

I currently have the following script:

$SQLServer = "sqldev1" 
$SQLDBName = "SPDEV_Printing" 
$SqlQuery = "select * from PcBeheerPrinter WHERE PRT_name = 'bwpsc006'" 

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

clear 

$DataSet.Tables[0]

which returns some console output like this:

    PRT_PrintServerName     : pcounter
PRT_Name                : bwpsc006
PRT_PortNameFull        : PCOUNT_bwpsc006
PRT_CAL_SerialNumber    :
PRT_ACTIVE              : 1
PRT_CAL_RespondToPing   : 1
PRT_CAL_NamePinged      : bwpsc006
PRT_CAL_FirstSeendate   : 8/02/2017 20:55:13
PRT_CAL_LastSeendate    : 4/12/2017 11:36:19
PRT_CAL_SNMPPossible    : 1
PRT_CAL_Brand           :
PRT_ShareName           : bwpsc006
PRT_Comment             :
PRT_Datatype            : RAW
PRT_DriverName          : Canon iR-ADV C5045/5051 PCL6
PRT_Location            :
PRT_PrintProcessor      :
PRT_Published           : 0
PRT_Shared              : 0
PRT_NumberOfMissedPings : 0
PRT_LastResponsedate    : 4/12/2017 11:36:19
PRT_RenderingMode       : CSR

My question is: How do I transfer some of these values into PS variables? I need the Name, SerialNumber and some others (these will do as example) to eventually transfer them to a SharePoint list. I'm still a beginner and can't figure it out through googling.

Thanks!

$Name = $DataSet.Tables[0].PRT_Name; $SerialNo = $DataSet.Tables[0].PRT_CAL_SerialNumber and so on! — Vivek Kumar Singh
– Vivek Kumar Singh, Commented Apr 4, 2018 at 11:27
Thank you! Feel free to reply this as an answer so I can accept it. — Lightninstu
– Lightninstu, Commented Apr 4, 2018 at 11:37

Vivek Kumar Singh · Accepted Answer · 2018-04-04 11:44:44Z

2

When using the System.Data.SqlClient.SqlCommand class, most of the objects returned will be of type datatable. Since, $DataSet will be of type datatable, you can directly use all the returned properties like this -

$Name = $DataSet.Tables[0].PRT_Name; $SerialNo = $DataSet.Tables[0].PRT_CAL_SerialNumber and so on.

You can then use the variables $Name and $Serialno as input to your SharePoint List.

answered Apr 4, 2018 at 11:44

Vivek Kumar Singh

3,3601 gold badge19 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ranadip Dutta · Accepted Answer · 2018-04-04 11:37:05Z

1

If the data type is object, then you can directly use the select-object like:

$DataSet.Tables[0] | Select-object  PRT_Name,PRT_CAL_SerialNumber

answered Apr 4, 2018 at 11:37

Ranadip Dutta

9,5493 gold badges33 silver badges48 bronze badges

Collectives™ on Stack Overflow

Extracting SQL values into powershell variables?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related