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

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

  • Comments 2
  • Likes

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

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

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment