Connecting to MySQL from PowerShell

Using PowerShell with the MySQL .NET Connector

May 15, 2009
Mark Alexander Bain

PowerShell and the MySQL .NET Connector - Mark Alexander Bain

PowerShell is Microsoft's .NET scripting language. If the programmer combines this with the MySQL .NET connector then they can build a very powerful database application

Anyone who has ever created an MS-DOS batch file might (quite rightly) think that obtaining and using information stored in a MySQL database is quite difficult. But that's only because they're not using Microsoft Window's newest shell scripting language - PowerScript. Suddenly a database is just another .NET framework object and that's exactly what PowerScript and MySQL's .NET Connector are based on.

Getting Started with PowerScript and the MySQL .NET Connector

PowerScript is not yet installed by default (although it is expected to be included in Windows 7). Any XP or Vista users will, therefore, need to download it (as explained in An Introduction to the Windows PowerShell). Similarly the MySQL .NET Connector will also need to be downloaded and installed (and that's covered in How to Access MySQL with C#).

Loading the MySQL .NET Connector

Most programmers using an IDE (Integrated Design Environment) such as Microsoft Visual Studio or SharpDevelop will know that they must add an object as a reference and then declare it in the code. In PowerShell this is all done one a single line:

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")

The objects supplied by the MySQL .NET Connector will now be available to the PowerShell script.

Connecting to MySQL from PowerShell

A connection must be made to the database before its contents can be accessed, and the first step in this process is to define a connection string:

$connectionString = "server=localhost;uid=aec_user;pwd=aec;database=aec;"

The connection string needs to include:

the server (localhost is the pc on which PowerShell is running)
the uid (or user id)
the pwd (user password)
and, of course, the database

This connection string is used when the connection is opened:

$connection = New-Object MySql.Data.MySqlClient.MySqlConnection

$connection.ConnectionString = $connectionString

$connection.Open()

The database is now ready to accept SQL queries.

Sending SQL Queries to a MySQL Database

With the connection open a SQL query can be sent to the database. It's actually done in two steps:

create a MySQL command object
create a MySQL data adapter object

The command object is created by using a SQL statement and the connection:

$sql = "select * from samples"

$command = New-Object MySql.Data.MySqlClient.MySqlCommand($sql, $connection)

And then that is used to create a data adapter object:

$dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($command)

The next step is to populate a dataset and use the results of the query.

Populating a Dataset

The dataset must be created before it can be used in the script:

$dataSet = New-Object System.Data.DataSet

The script uses the data adapter to fill the dataset (at which time the dataset can be assigned a name):

$recordCount = $dataAdapter.Fill($dataSet, "sample_data")

The function outputs the number of records returned by the query and the contents of the dataset can be sent directly to the screen:

$dataSet.Tables["sample_data"]

Or it can be formatted:

$dataSet.Tables["sample_data"] | Format-Table

Or even formatted for viewing in a web browser:

$dataSet.Tables["sample_data"] | Format-Table > C:\inetpub\wwwroot\samples.html

The final step is to close the connection.

Closing the Database Connection

In theory the database connection will be closed when PowerShell finishes. However, it is always good pratice to ensure that this is done correctly:

$connection.Close()

This will enure that any memory used by the process is freed up.

Summary

Microsoft Windows PowerShell is a very powerful scripting language. This power derives from the fact that PowerShell is based on the .NET framework. Therefore PowerShell can access all of the .NET objects that a programmer might only expect to see in a full blown IDE. Access to a database, such as MySQL, becomes a matter of obtaining the correct classes and using them to work with the contents of the database.

Mark Alexander Bain - Mark Alexander Bain is a writer, Mo Bro and consultant for all aspects of software development at dsquared. He has also written regularly ...