How to Loop Datareader and create DataTable in Powershell

Question

First of all let me thank those people that answered my previous questions. You guys are Awesome!!!

Here is my question: I like to query my sql server Stored procedure and return a datareader. However I want to create a Table out of it. That table I will use to load excel using the new Powershell OpenXML commandlets. The code fails when I try to build the DataTable. I don't think I am loding the new-object "System.Object[]" correctly. Here is what I got so far:

$sqlConnection = new-object System.Data.SqlClient.SqlConnection "server=localhost;database=Demo;Integrated Security=sspi"
$sqlConnection.Open()

#Create a command object
$sqlCommand = $sqlConnection.CreateCommand()
$sqlCommand.CommandText = "EXEC Demo.usp_GetTableValueParameter_Data"

#Execute the Command
$sqlReader = $sqlCommand.ExecuteReader()

#Parse the records

$sqlReader | &{ begin{$values = new-object "System.Object[]" $sqlReader["Name"], $sqlReader["Level_Desc"], $sqlReader["Level"]} process {$_.GetValues($values); $datatable.Rows.Add($values)}}

##$datatable | format-table -autosize

# Close the database connection
$sqlConnection.Close()

#STARTING OPENXML PROCESS
#----------------------------
$xlsFile = "C:\Temp\Data.xlsx"
$datatable | Export-OpenXmlSpreadSheet -OutputPath $xlsFile  -InitialRow 3

Community · Accepted Answer · 2017-05-23 12:16:19Z

Translating Mladen's answer into PowerShell is pretty straight forward:

$sqlConnection = new-object System.Data.SqlClient.SqlConnection "server=localhost;database=Demo;Integrated Security=sspi"
$sqlConnection.Open()

#Create a command object
$sqlCommand = $sqlConnection.CreateCommand()
$sqlCommand.CommandText = "EXEC Demo.usp_GetTableValueParameter_Data"

#Execute the Command
$sqlReader = $sqlCommand.ExecuteReader()

$Datatable = New-Object System.Data.DataTable
$DataTable.Load($SqlReader)

# Close the database connection
$sqlConnection.Close()

#STARTING OPENXML PROCESS
#----------------------------
$xlsFile = "C:\Temp\Data.xlsx"
$datatable | Export-OpenXmlSpreadSheet -OutputPath $xlsFile  -InitialRow 3

However, if you just need a DataTable back, you don't need to call ExecuteReader on the command, you could create a DataAdapter and use that to fill the DataTable:

$sqlConnection = new-object System.Data.SqlClient.SqlConnection "server=localhost;database=Demo;Integrated Security=sspi"
$sqlConnection.Open()

#Create a command object
$sqlCommand = $sqlConnection.CreateCommand()
$sqlCommand.CommandText = "EXEC Demo.usp_GetTableValueParameter_Data"

$adapter = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcommand
$dataset = New-Object System.Data.DataSet

$adapter.Fill($dataSet) | out-null

# Close the database connection
$sqlConnection.Close()

$datatable = $dataset.Tables[0]  

#STARTING OPENXML PROCESS
#----------------------------
$xlsFile = "C:\Temp\Data.xlsx"
$datatable | Export-OpenXmlSpreadSheet -OutputPath $xlsFile  -InitialRow 3

Mladen Prajdic · Accepted Answer · 2009-07-26 16:34:20Z

3

i have no clue how to do this in powershell but in .net you do it like this:

DataTable dt = new DataTable();
dt.Load(yourSqlReader);

answered Jul 26, 2009 at 16:34

Mladen Prajdic

15.7k2 gold badges46 silver badges51 bronze badges

Collectives™ on Stack Overflow

How to Loop Datareader and create DataTable in Powershell

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