SQL Query into Dataset then split the results back into rows

hi,

i have dataset created calling sql stored procedure.  from results selecting , creating variable ($45, $28, $14) based on number of row returned.  if more 1 record set has searched number powershell returns both of them appears 1 record.  code follows:

 

$connstring = "xxxx;"

#first sp exec dbo.xxxxx_generate_alerts #################################################################
$querytext = "exec dbo.suppliers_generate_alerts"
$sqlconnection = new-object system.data.sqlclient.sqlconnection
$sqlconnection.connectionstring = $connstring
$sqlcommand = $sqlconnection.createcommand()

$sqlcommand.commandtext = $querytext


$dataadapter = new-object system.data.sqlclient.sqldataadapter $sqlcommand
$dataset = new-object system.data.dataset

$dataadapter.fill($dataset)

$data = $dataset.tables[0]

$connection.close()

#these search through returned $dataset.  the alertcountdown has been set in sql stored proc

#variables containing searched records within results returned sp

[string]$45 = $dataset.tables[0].select("alertcountdown='1'") | out-string
[string]$28 = $dataset.tables[0].select("alertcountdown='2'") | out-string
[string]$14 = $dataset.tables[0].select("alertcountdown='3'") | out-string

the results if write contents of $45 screen this:

nondiscloseid  : 1074
creation_date     : 01/01/1900 00:00:00
expiry_date       : 22/06/2016 00:00:00
supplier_signator : supplier_signator
position          : supplier_position
supplier_email    : supplier_email
company_name      : company_name
company_address   : company_address
company_address 1 : company_address 1
company_address 2 : company_address 2
company_address 3 : company_address 3
company_address 4 : company_address 4
signator      : xxx_signator
position      : xxx_position
email         : email@address.com
renewed           : no
alerts_enabled    : yes
alert expire  : yes
alertcountdown    : 3

nondiscloseid  : 1075
creation_date     : 01/01/1900 00:00:00
expiry_date       : 22/06/2016 00:00:00
supplier_signator : supplier_signator
position          : supplier_position
supplier_email    : supplier_email
company_name      : company_name
company_address   : company_address
company_address 1 : company_address 1
company_address 2 : company_address 2
company_address 3 : company_address 3
company_address 4 : company_address 4
signator      : xxx_signator
position      : xxx_position
email         : email@address.com
renewed           : no
alerts_enabled    : yes
alert expire  : yes
alertcountdown    : 3

an example of treating 1 record happens if type be:

foreach ($row in $45.rows{$row.email}

returns 2 email addresses....

email@address.comemail@address.com

is there way can treat each row powershell array or xml perhaps.  my plan email each record email address within each record , why need them separate.  i hope makes sense

thanks

---

alter de ruine

try not jamming on 1 line.  serves no purpose.

$mail=@{ 	subject="$subject14$ndatypeqavtosup" 	smtpserver=$smtpserver 	from=$fromaddress }  foreach ($row in $data) { 	if ($row.alertcountdown -eq '3') { 		$row1 = $row | out-string 		send-mailmessage -to $row.qav_email, $cc -body $ndaurl$row1  @mail 	} }

now can read it.

---

\_(ツ)_/

Windows Server  >  Windows PowerShell