Share via

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

  • Article

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()

Comments

  • Anonymous
    July 26, 2010
    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;            con.Open();            cmd.ExecuteNonQuery();

  • Anonymous
    May 23, 2013
    The PowerShell code in this example is vulnerable to SQL Injection attacks because it does not use parameters.