如何更新DataTable并将修改提交回SQL Server源数据库

解决方案 #

你遇到的报错是因为SqlDataAdapter默认没有配置对应的更新命令，无法识别DataTable的变更如何同步到原SQL表。你可以通过SqlCommandBuilder自动生成适配的更新命令，实现成本最低。

具体修改步骤 #

1. 添加命令生成代码，放置位置在$dataAdapter.Fill($dataTable) | Out-Null之后，遍历DataTable行的循环之前即可，代码如下：

# 自动生成适配的增删改命令
$sqlCmdBuilder = New-Object System.Data.SqlClient.SqlCommandBuilder($dataAdapter)
# 可选：显式指定自动生成的更新命令（非必须，CommandBuilder会自动关联到dataAdapter）
$dataAdapter.UpdateCommand = $sqlCmdBuilder.GetUpdateCommand()

注意：该方法要求你的SQL表设置了主键，SqlCommandBuilder需要依靠主键定位需要更新的行，从你提供的场景来看UserList表必然存在主键字段，无需额外配置。

手动编写UpdateCommand的方案（适合复杂场景） #

如果不想用自动生成的命令，也可以手动构造更新命令，放在同样的位置即可：

$updateCmd = New-Object System.Data.SqlClient.SqlCommand
$updateCmd.Connection = $sqlConn
# 注意WHERE条件后的Id替换为你表实际的主键字段名
$updateCmd.CommandText = "UPDATE $SQLTableName SET shouldSendEmail=@shouldSendEmail, emailSentCount=@emailSentCount WHERE Id=@Id"
# 定义参数映射，将DataTable的字段对应到SQL参数
$updateCmd.Parameters.Add("@shouldSendEmail", [System.Data.SqlDbType]::Bit, 1, "shouldSendEmail")
$updateCmd.Parameters.Add("@emailSentCount", [System.Data.SqlDbType]::Int, 4, "emailSentCount")
$updateCmd.Parameters.Add("@Id", [System.Data.SqlDbType]::Int, 4, "Id").SourceVersion = [System.Data.DataRowVersion]::Original
$dataAdapter.UpdateCommand = $updateCmd

额外的代码bug修复 #

你当前的脚本里还有几个会导致执行报错的问题可以同步修复：

• 数据库连接字符串里使用了中文双引号，需要替换为英文双引号，否则会识别错误
• Send-MailMessage的参数换行时，每行末尾需要加反引号`，否则PowerShell会识别为独立命令报错
• switch分支里给$MessageBody赋值的字符串没有加引号，会被识别为命令报错

修改后的SendEmail函数参考：

Function SendEmail
{
    Param(
        [string]$ToMailAddress,
        [int]$MessageTemplate
        
    )

    [string]$MessageBody=$null

    switch ($MessageTemplate)
    {
        1 {$MessageBody = "Test new certificate issued. Please ignore!"}
        2 {$MessageBody = "Test existing certificate renewed. Please ignore!"}
    }


    $from = "EmailApplicationAccount@example.com"
    $to = $ToMailAddress
    $smtp = "smtp.example.net" 
    Send-MailMessage `
        -From $from `
        -To $to `
        -Subject $MessageBody `
        -SmtpServer $smtp 

}

内容的提问来源于stack exchange，提问作者gtrivedi