Updating a DataSet with Powershell and saving changes back to SQL


This example uses the AdventureWorks sample database.


# get the dataset
PS C:\demo> $ds = .\get-dataset.ps1 “select * from Production.ProductModel” -db adventureworks
# write out xml from the instructions column into seperate files
PS C:\demo> $ds.Tables[0] | ?{$_[“instructions”] -ne [dbnull]::value } | %{$_[“instructions”]| sc “productmodel-$($_[‘productmodelid’]).xml”}
# verify output
PS C:\demo> ls productmodel-*

Directory: Microsoft.PowerShell.Core\FileSystem::C:\demo

Mode LastWriteTime Length Name
—- ————- —— —-
-a— 2/25/2007 8:47 PM 5567 productmodel-10.xml
-a— 2/25/2007 8:47 PM 2142 productmodel-43.xml
-a— 2/25/2007 8:47 PM 1967 productmodel-44.xml
-a— 2/25/2007 8:47 PM 4051 productmodel-47.xml
-a— 2/25/2007 8:47 PM 4078 productmodel-48.xml
-a— 2/25/2007 8:47 PM 1927 productmodel-53.xml
-a— 2/25/2007 8:47 PM 1565 productmodel-66.xml
-a— 2/25/2007 8:47 PM 1572 productmodel-67.xml
-a— 2/25/2007 8:47 PM 5340 productmodel-7.xml

# now lets make some xml files for product model 1-5
PS C:\demo> 1..5 | %{cp productmodel-10.xml productmodel-$_.xml}
# Update the dataset with the new xml content from the files
PS C:\demo> $ds.Tables[0] | %{$file = “productmodel-$($_[‘productmodelid’]).xml”;if (test-path $file) {$_[“instructions”] = [string]::join(“`r`n”,(gc $file))}}
# save changes back to SQL
PS C:\demo> .\save-datasetchanges.ps1 $ds


Get-DataSet.ps1 stores the sql command and connection string used to create the dataset as extended properties on the dataset. This is used by Save-DataSetChanges.ps1 to construct a SqlCommandBuilder object; which is then used to create the update/insert/delete command objects and update the SQL Server using a SqlDataAdapter.


get-dataset.ps1:

param ($sql,$server=“.”,$db) 

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


save-datasetchanges.ps1:

param ([system.data.dataset]$dataset)  

$ds = $dataset
$da = New-Object system.data.sqlclient.sqldataadapter $ds.ExtendedProperties[“sql”], $ds.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($ds)


Comments (1)

  1. get-datatable.ps1 is an improvement on the get-dataset.ps1 from an earlier post . Instead of returning