How to update datatable and commit back to sql server source

Question

Alright so I have a table with some data in it. Here is a screenshot of the table def in SQL server:

And there is an app which populates some of the data in the table. Here is a screenshot of the sample data. All columns except for [emailSentCount] are populated by an external app.

Now my question is with with a Powershell script that I'm trying to build to consume this data and send email Notifications. I read all the content of the table in a DataTable. I go through each Row and decide if I have to send an email for that row. If so, then I send the email and I update the [emailSentCount] column by adding + 1 to it.

At the end of the script I'm trying to send these changes I made to the DataTable back to the table on SQL server. However I get an error:

Here is the script I'm working with.

param(
    [string]$SQLServerName="SQLServerName\InstanceName"
    ,[string]$SQLDatabaseName="DBName"
    ,[string]$SQLTableName = "UserList"
    ,[string]$SQLSelectQuery="SELECT * FROM $SQLTableName"    
)

cls


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 = "[email protected]"
    $to = $ToMailAddress
    $smtp = "smtp.example.net" 
    Send-MailMessage 
        -From $from 
        -To $to 
        -Subject $MessageBody 
        -SmtpServer $smtp 

}


$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = “Server=$SQLServerName;Integrated Security=true;Initial Catalog=$SQLDatabaseName”
$sqlConn.Open()

$sqlCommand = $sqlConn.CreateCommand()
$sqlCommand.CommandText = $SQLSelectQuery


$dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $sqlCommand
$dataTable = New-Object System.Data.DataTable
$dataAdapter.Fill($dataTable) | Out-Null



foreach($dataRow in $dataTable.Rows)
{
    write-host $dataRow["shouldSendEmail"]
    if($dataRow["shouldSendEmail"] -eq $true)
    {
        # First send email depending on whether its a first time new cert or a cert renewal.
        if($dataRow["certRenewal"] -eq $true)
        {
            SendEmail -ToMailAddress $dataRow["email"] -MessageTemplate 2
        }
        else
        {
            SendEmail -ToMailAddress $dataRow["email"] -MessageTemplate 1
        }
        
        # After you have sent the email, increase the emailSentCount value in the datatable.
        $dataRow["emailSentCount"] = $dataRow["emailSentCount"] + 1

        #Also reset the shouldSendEmail column to $false/0
        $dataRow["shouldSendEmail"] = $false
  
    }
}

$dataAdapter.Update($dataTable)


$sqlConn.Close()

It seems I need to include some Update command. But what will it look like in this context and wwhere does it need to be included?

Answer 1

Actually, I found the solution. We have to build an update command before we start messing with the data in the rows. Here is the bit I added just before the Foreach loop.

# command builder
$commandBuilder = new-object system.data.sqlclient.sqlcommandbuilder($dataAdapter)
$dataAdapter.UpdateCommand = $commandBuilder.GetUpdateCommand()

And that was it! No errors and I can see the data on columns [shouldSendEmail] and [emailSentCount] change on the source table in SQL server as intended in the script.