Working with System.ComponentModel.MarshalByValueComponent

Question

I'm pulling data from SQL using System.Data.SqlClient and eventaully end up with a dataset stored into a variable:

$SqlQuery = "select * from my_Table"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
$SqlConnection.Open()

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$amManagedFolders = New-Object System.Data.DataSet
$SqlAdapter.Fill($dataset)

I then access the table via $dataset.Tables[0] which is of type: System.ComponentModel.MarshalByValueComponent

I need to be able to use ForEach in order to loop through that table and store the results into a CustomPSObject for later use, however, I am not sure of a method to use in order to achieve this.

Possible duplicate of PowerShell - ForEach using SQL data input — user4317867
– user4317867, Commented Jan 17, 2016 at 16:27
I need to work straight from the SQL Table in $dataset and ForEach it straight into a CustomObject. This works, what then doesn't work, is querying the custom object. — PnP
– PnP, Commented Jan 17, 2016 at 16:33
This looks very close to a problem I had. Trying to query a CustomObject. I ended up importing data as CSV, then piping it to Where-Object and matching my criteria. — user4317867
– user4317867, Commented Jan 17, 2016 at 16:43

Community · Accepted Answer · 2017-05-23 11:59:17Z

0

Using this page I found the following code helpful. I'm taking a SQL query and producing an Excel workbook with the results.

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSetTable = $DataSet.Tables["Table"]
## - Build the Excel column heading:
[Array] $getColumnNames = $DataSetTable.Columns | Select ColumnName
## - Build column header:
[Int] $RowHeader = 1
foreach ($ColH in $getColumnNames)
....
foreach ($rec in $DataSetTable.Rows)
{
foreach ($Coln in $getColumnNames)
{
## - Next line convert cell to be text only:
$xlsSh.Cells.NumberFormat = "@"
## - Populating columns:
$xlsSh.Cells.Item($rowData, $colData) = $rec.$($Coln.ColumnName).ToString()
$ColData++
}
$rowData++; $ColData = 1
}

-Edit, saw your comment. You might be able to adapt the answer to this question

edited May 23, 2017 at 11:59

CommunityBot

11 silver badge

answered Jan 17, 2016 at 16:37

user4317867

2,4465 gold badges34 silver badges59 bronze badges

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

Collectives™ on Stack Overflow

Working with System.ComponentModel.MarshalByValueComponent

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related