sql-server - 使用 SqlDataAdapter 和 DataTable 通过 PowerShell 在 SQL Server 上获取和更新

Question

我正在尝试使用 PowerShell 获取 System.Data.DataTable从 SQL 服务器，编辑数据，并将其更新回 SQL 服务器，但我无法让它工作。下面的代码运行/执行，但数据没有改变。

$sqlConnection = new-object System.Data.SqlClient.SqlConnection("Server=server,1234; Database=dingo; Trusted_Connection=True;")
$sqlConnection.open()

$sqlCommand = $sqlConnection.CreateCommand()
$sqlCommand.CommandText = "SELECT * FROM dbo.test"

$dt = new-object System.Data.DataTable
$adapter = new-object System.Data.SqlClient.SqlDataAdapter($sqlCommand)

$adapter.Fill($dt)

# edit the rows
$dt.Rows[0].BeginEdit()
$dt.Rows[0]["a"] = "nacho"
$dt.Rows[0].AcceptChanges()

# command builder
$cb = new-object system.data.sqlclient.sqlcommandbuilder($adapter)

$adapter.UpdateCommand = $cb.GetUpdateCommand()


$adapter.Update($dt)

$sqlConnection.Close()

Answer 1

您不应该调用 AcceptChange 相反，您需要调用 EndEdit .

调用AcceptChanges时，它结束编辑，但将该行标记为 Unchanged , 所以它不会被 DataAdapter 处理因为它被标记为未更改。

When invoking AcceptChanges, the EndEdit method is implicitly called to end any edits. If the RowState of the row was Added or Modified, the RowState becomes Unchanged. If the RowState was Deleted, the row is removed.