2,187
edits
Difference between revisions of "Power Shell"
Jump to navigation
Jump to search
→MySQL: Reworked
m (Minor re-org) |
(→MySQL: Reworked) |
||
| Line 165: | Line 165: | ||
== MySQL == | == MySQL == | ||
=== Connect === | |||
<pre> | <pre> | ||
function ConnectMySQL([string]$user,[string]$pass,[string]$MySQLHost,[string]$database) { | function ConnectMySQL([string]$user,[string]$pass,[string]$MySQLHost,[string]$database) { | ||
# Load MySQL .NET Connector Objects | # Load MySQL .NET Connector Objects | ||
[void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data") | [void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data") | ||
| Line 175: | Line 176: | ||
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr) | $conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr) | ||
$conn.Open() | $conn.Open() | ||
return $conn | return $conn | ||
} | } | ||
function | function DisconnectMySQL($conn) { | ||
$conn.Close() | |||
} | |||
# So, for example... | |||
# | # Connection Variables | ||
$user = 'myuser' | $user = 'myuser' | ||
$pass = 'mypass' | $pass = 'mypass' | ||
| Line 200: | Line 194: | ||
$conn = ConnectMySQL $user $pass $MySQLHost $database | $conn = ConnectMySQL $user $pass $MySQLHost $database | ||
# | </pre> | ||
$query = | |||
=== Commands === | |||
All database operations are done through methods of the <code> MySqlCommand </code> object, the two methods of main interest are... | |||
* '''ExecuteNonQuery''' - Used for queries that don't return any real information, such as an INSERT, UPDATE, or DELETE. | |||
* '''ExecuteReader''' - Used for normal queries that return multiple values. Results need to be received into <code> MySqlDataReader </code> object. | |||
* '''ExecuteScalar''' - Used for normal queries that return a single. The result needs to be received into a variable. | |||
'''Non-Query''' | |||
<pre> | |||
function ExecuteMySQLNonQuery($conn, [string]$query) { | |||
$command = $conn.CreateCommand() # Create command object | |||
$command.CommandText = $query # Load query into object | |||
$RowsInserted = $command.ExecuteNonQuery() # Execute command | |||
$command.Dispose() # Dispose of command object | |||
if ($RowsInserted) { | |||
return $RowInserted | |||
} else { | |||
return $false | |||
} | |||
} | |||
# So, to insert records into a table | |||
$query = "INSERT INTO test (id, name, age) VALUES (1, 'Joe', 33)" | |||
$Rows = WriteMySQLQuery $conn $query | $Rows = WriteMySQLQuery $conn $query | ||
Write-Host $Rows " inserted into database" | Write-Host $Rows " inserted into database" | ||
</pre> | </pre> | ||
'''Reader Query''' | |||
'''Scalar Query''' | |||
'''Other''' | |||
<pre> | |||
$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand("USE $database", $conn) | |||
</pre> | |||
== Exceptions and Error Handling == | == Exceptions and Error Handling == | ||