Creating a PowerShell Connection String

Question

I am 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 I stretch my hair for quite some time. Can someone help me figure out what I'm doing wrong in ConnectionString?

Thanks everyone!

---

I realized that my first problem was that I have a MySQL database, not an 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 changed 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 the local MySQL database? 2) Where to save this PowerShell script? 3) Are there any additional changes I have to make?

Thank you very much

Answer 1

try the following:

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

then $ test gives you only the last value found in select! To have $ test containing all the values ​​from select, change your code as follows:

 $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