ADO.NET in PowerShell...Update SQL Data Example/Sample

We have a database that stores all our configuration key/value pairs in various tables (1 per component). This code updates the table based on the specifed table, key, and value.

 

# Open SQL connection

$sqlConnection = New-Object System.Data.SqlClient.SqlConnection

$sqlConnection.ConnectionString = "Server=$computer;Database=ConfigurationStore;Integrated Security=True"

$sqlConnection.Open()

# Get DataTable to modify

$sqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

$dataTable = New-Object System.Data.DataTable

$sqlCommandSelect = New-Object System.Data.SqlClient.SqlCommand

$sqlCommandSelect.CommandText = "SELECT * FROM $table WHERE Name='$key'"

$sqlCommandSelect.Connection = $sqlConnection

$sqlDataAdapter.SelectCommand = $sqlCommandSelect

$sqlDataAdapter.Fill($dataTable)

# Setup UpdateCommand

$sqlCommandUpdate = New-Object System.Data.SqlClient.SqlCommand

#$sqlCommandUpdate.CommandText = "UPDATE dbo.$table SET $table.Value = N'$value' WHERE Name = N'$key'"

$sqlCommandUpdate.CommandText = "UPDATE dbo.$table SET $table.Value = @Value WHERE Name = @Name"

$sqlCommandUpdate.Connection = $sqlConnection

$sqlDataAdapter.UpdateCommand = $sqlCommandUpdate

$sqlDataAdapter.UpdateCommand.Parameters.Add("@Value", [System.Data.SqlDbType]::NVarChar, 500, "Value")

$sqlParameter = New-Object System.Data.SqlClient.SqlParameter

$sqlParameter = $sqlDataAdapter.UpdateCommand.Parameters.Add("@Name", [System.Data.SqlDbType]::NVarChar)

$sqlParameter.SourceColumn = "Name"

$sqlParameter.SourceVersion = [System.Data.DataRowVersion]::Original

# Update DataTable

$dataTable.Rows[0].Value = $value

$sqlDataAdapter.Update($dataTable)

$sqlConnection.Close()