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)