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

Hey there, I just noted that I was using the wrong connection method to connect to a MySQL database. However, I posted another question below asking how I go about using the .NET connection tool to connect to a local MySQL database. — user958267
– user958267, Commented Sep 23, 2011 at 4:36
Please edit your question to provide more information. Answers should be answers to the problem, they aren't intended for discussion. — user50049
– user50049, Commented Sep 23, 2011 at 6:22

CB. · Accepted Answer · 2011-09-22 07:13:12Z

6

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

answered Sep 22, 2011 at 7:13

CB.

61.3k9 gold badges170 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating a PowerShell Connection String

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related