by kpuckett at 2012-11-21 07:38:03
$sqlconn = new-object system.data.sqlclient.sqlconnectionby DonJ at 2012-11-21 13:47:24
$sqlconn.connectionstring = "Server=servername;database=dbname;integrated security=true"
$sqlconn.open()
$sqlcmd = new-object system.data.sqlclient.sqlcommand("select * from tablename", $sqlconn)
$dataset = new-object system.data.dataset
$sqldataadapter = new-object system.data.sqlclient.sqldataadapter($sqlcmd)
$sqlcmdbuilder = new-object system.data.sqlclient.sqlcommandbuilder($sqldataadapter)
$numrows = $sqldataadapter.fill($dataset)
# do stuff here to add or modify the dataset
# then, to write back the changes to SQL Server:
$sqldataadapter.updatecommand = $sqlcmdbuilder.getupdatecommand() # <---------- Necessary???
$sqldataadapter.insertcommand = $sqlcmdbuilder.getinsertcommand() # <---------- Necessary???
$sqldataadapter.deletecommand = $sqlcmdbuilder.getdeletecommand() # <---------- Necessary???
$changedrows = $sqldataadapter.update($dataset)
$sqlconn.close()
I see examples of similar scripts at various sites that include the update/insert/deletecommands, and some that don’t, so I’m just trying to determine if the calls to the method are necessary for the Update call to work, or if the commands will get automagically created by the command builder based on what changes were made to the dataset. Haven’t had chance to try it against a SQL Server yet so I thought I’d post here first. Thanks for any info!
This isn’t really a PowerShell question - you’d probably get a more authoritative answer on StackOverflow or someplace more dev-focused. I get that you’re using PowerShell here, but you’re actually directly using the .NET Framework classes in the SQLClient. The code would look the same in C# and similar in VB, for example. Me… I dunno the answer to your question. Haven’t used the stuff that intensely. Sorry!