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


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;

               con.Open();

               cmd.ExecuteNonQuery();

  2. Steve Og says:

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