How to copy Datareader and create DataTable in Powershell

Question

First of all, let me thank those people who 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 from it. In this table, I will use to load excel using the new Powershell OpenXML cmdlets. When I try to build a DataTable, the code will work. I do not think that I am correctly encoding the new "System.Object []" object. 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 

Answer 1

PowerShell's Mladen answer translation 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, you do not need to call ExecuteReader on the command, you can create a DataAdapter and use it to populate 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 

Answer 2

I don't know how to do this in powershell, but in .net you do it like this:

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