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"



# 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



# 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



Comments (2)

  1. Igor says:

    can yuo please convert this C# into powershell?

    con = new SqlConnection("Data Source=testserver1; Initial Catalog=Igors_Test; Integrated Security=SSPI");

               cmd = new SqlCommand();

               cmd.Connection = con;

               cmd.CommandType = CommandType.StoredProcedure;

               cmd.CommandText = "iz_sp_InsertStudent";

               cmd.Parameters.Add(new SqlParameter("@status", SqlDbType.VarChar, 50)).Value = txtFN.Text;

               cmd.Parameters.Add(new SqlParameter("@Message", SqlDbType.VarChar, 50)).Value = txtLN.Text;



  2. Steve Og says:

    The PowerShell code in this example is vulnerable to SQL Injection attacks because it does not use parameters.

Skip to main content