Updating a DataSet with Powershell and saving changes back to SQL

  • Article

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)