Creating a PowerShell Connection String

Question

I have been trying to create a ConnnectionString that will allow me to connect to my local database using PowerShell. Below is my code:

$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=localhost;Database=test;Uid=<username here>;Pwd=<password here>;"
$conn.Open()
$sql = "SELECT EMP_STATUS FROM test_table"
$cmd = New-Object System.Data.SqlClient.SqlCommand($sql,$conn)
$rdr = $cmd.ExecuteReader()
while($rdr.Read())
{
    $test = $rdr["EMP_STATUS"].ToString()
}
Write-Output $test

However, I have NO CLUE what I am doing wrong and have been pulling my hair out for quite some time. Can anyone help me figure out what I am doing wrong in the ConnectionString?

Thanks everyone!!

---

I realized that my first problem was that I have MySQL database, not SQL database. As a result, I will have to connect using a different method. This is exactly where I need your help!! So far I have modified my code as follows:

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection

$connString = "server=localhost;port=3306;uid=<username here>;pwd=<password here> ;database=test;"
$conn.ConnectionString = $connString
$conn.Open()
$sql = "SELECT EMP_STATUS FROM test_table"
$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand($sql,$conn)
$rdr = $cmd.ExecuteReader()
$test = @()
while($rdr.Read())
{
    $test += ($rdr["EMP_STATUS"].ToString())
}
Write-Output $test

However, here are a few more questions: 1) How do you use the MySQL .NET connection tool to connect to a local MySQL database? 2) Where should this PowerShell script be saved? 3) Are there any additional changes I should make?

Thanks so much

Solution

try this:

$conn.ConnectionString = "Server=localhost;Database=test;User ID=<username here>;Password=<password here>;"

then $test give you only the last value found in the select! To have $test containing all value from select change your code like this:

$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=localhost;Database=test;User ID=<username here>;Password=<password here>;"
$conn.Open()
$sql = "SELECT EMP_STATUS FROM test_table"
$cmd = New-Object System.Data.SqlClient.SqlCommand($sql,$conn)
$rdr = $cmd.ExecuteReader()
$test = @()
while($rdr.Read())
{
    $test += ($rdr["EMP_STATUS"].ToString())
}
Write-Output $test