get-datatable.ps1

  • Article

get-datatable.ps1 is an improvement on the get-dataset.ps1 from an earlier post. Instead of returning a dataset it returns a DataTable; and a UpdateSql method is added to the object returned, so you don't need a separate script to send the changes back to sql.

This allows updates to a small table to look like:

$t = datatable "select * from stuff" -db foo
$t | %{ .... update the data rows ... }
$t.UpdateSql()

Get-DataTable.ps1:

 param ($sql,$server=".",$db) 

$connectionstring= "Server=$server;database=$db;trusted_connection=yes;" 
$dt = new-object data.DataTable 
$da = New-Object system.data.sqlclient.sqldataadapter $sql, $connectionstring 
$null = $da.Fill($dt) 
$dt.ExtendedProperties["sql"]= $sql 
$dt.ExtendedProperties["connectionstring"]= $connectionstring 

$dt = add-member ScriptMethod UpdateSql { 
$da = New-Object system.data.sqlclient.sqldataadapter $this.ExtendedProperties["sql"], $this.ExtendedProperties["connectionstring"] 

$cb = new-object system.data.sqlclient.sqlcommandbuilder $da 
$da.UpdateCommand = $cb.GetUpdateCommand() 
$da.InsertCommand = $cb.GetInsertCommand() 
$da.DeleteCommand = $cb.GetDeleteCommand() 
$null = $da.Update($this) 
} -in $dt -pass 

#return data table in array so table doesn't get decomposed into an array of data rows. 
,$dt