Difference between revisions of "Power Shell"

== MySQL ==

=== Connect ===

function ConnectMySQL([string]$user,[string]$pass,[string]$MySQLHost,[string]$database) {  

   # Load MySQL .NET Connector Objects  

# Connect to MySQL Database  

$conn = ConnectMySQL $user $pass $MySQLHost $database  

=== Commands ===

'''Non-Query'''

function ExecuteMySQLNonQuery($conn, [string]$query) {  

   $command = $conn.CreateCommand()                  # Create command object

$Rows = ExecuteMySQLNonQuery $conn $query  

Write-Host $Rows " inserted into database"  

'''Reader Query'''

In theory, this should work, but it doesn't seem to for me.  There's something wrong with the <code>while ($results.Read())</code>, in that you end up displaying the last row returned by the SQL query multiple times.  Suspect its due to the way that a Reader object only seems to hold a result temporarily.

$query = "SELECT * FROM subnets;"

$cmd = $connMySQL.CreateCommand()  

   }

}

Instead, this approach seems to work more reliably.  By loading the data into a dataset, it becomes available for offline manipulation and isn't reliant on the database once the data is loaded in.

function ExecuteMySQLQuery([string]$query) {  

   # NonQuery - Insert/Update/Delete query where no return data is required

   $dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($cmd)      # Create data adapter from query command

   $dataSet = New-Object System.Data.DataSet                                    # Create dataset

   $cmd.Dispose()

}

# So, to produce a table of results from a query...

$query = "SELECT * FROM subnets;"

'''Scalar Query'''

'''Other'''

$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand("USE $database", $conn)

== Exceptions and Error Handling ==