Below is the sample PowerShell script that executes a basic query against a MySql database. This example database has a single table (Test) and a single column (A), but you can easily manipulate the script to perform all sorts of database administration tasks including creating/dropping tables/indexes, importing data, exporting data, e-mailing data, managing processes/users, etc...
Like ADO.Net, MySql connector/Net has the idea of a connected and disconnected mode. This example assumes that the data manipulation will happen while the client is connected, rather than using DataSets and TableAdapters to read/manipulate the data.
# Mike Burr
# April 2013
# Demonstrates how to perform a basic query on MySql using PowerShell
[System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
[System.Reflection.Assembly]::LoadWithPartialName("MySql.Data.MySqlClient")
#Create the connection
$connstr = "server=localhost;user=root;database=my_test_db;port=3306;password=some_pwd;"
$conn = New-Object "MySql.Data.MySqlClient.MySqlConnection" $connstr
Try {
write-host "Connection Opened"
#Open the connection
$conn.Open()
$query = "SELECT * FROM Test WHERE A = @A"
$cmd = New-Object "MySql.Data.MySqlClient.MySqlCommand" -ArgumentList $query,$conn
$cmd.Parameters.AddWithValue("@A", "test")
$reader = $cmd.ExecuteReader()
while ($reader.Read()) {
write-host ("A is " + ($reader["A"]))
}
} Catch {
#Perform exception handling logic here
} Finally {
#Close the connection
write-host "Connection Closed"
$conn.Close()
}
-->
See Also:
How to Query a Microsoft SQL Server Database with Windows PowerShell
No comments:
Post a Comment