How to Use the MySQL Data Reader

Running Select Queries on a MySQL Database with C# and PowerShell

May 15, 2009

One real advantange to using the Microsoft .NET framework is that the same objects (such as a data reader) can be used in multiple applications using different languages.

One good reason for programmers to use Microsoft Windows these days is that it really doesn't matter which programming language they use, provided that it supports the Microsoft .NET framework. A good example is database access.

If a programmer has installed the MySQL .NET Connector then any of their .NET based applications will be able to access a MySQL database with the minimum of effort. For example, a very easy way of obtaining information from a database is to use a data reader - an object that can be used as easily in C# as it can in PowerShell.

What is a Data Reader?

A data reader is the simplest .NET method of accessing the contents of a database. It runs any valid SQL select statement on the database and returns the results of the query to the client application. It cannot, therefore, be used to insert, update or delete records held in database tables but that, of course, it not what it is designed to do.

Loading the MySQL .NET Connector

The MySQL .NET connector must be loaded before its objects can be used in an application. The particular methods of the programming language being used will vary, for example, the PowerShell code to do this would be:

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

And in C# it would be:

using MySQL.Data.MySQLClient;

With that in place a connection can be made to the database.

Creating a Database Connection

The application must define the database connection as a new object, so the PowerShell code would be:

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

And C# would be:

private MySqlConnection connection = new MySqlConnection ();

However, once the connection object has been created then the code used becomes virtually identical. To start with every database connection requires a database connection string:

$connection.ConnectionString = "server=localhost;uid=aec_user;pwd=aec;database=aec;"

And the then the connection can be opened:

$connection.Open()

The difference between C# and PowerShell is obvious:

PowerShell variables (such as object names) start with the $ sign, C# variables do not
C# commands must end in a semi-colon, PowerShell commands do not

Apart from that the code is the same.

Creating a Data Reader

The data reader is created in two steps:

a SQL command is created using the database connection
the data reader is created using the SQL command

So, the PowerShell code to create the SQL command is:

$command = $connection.CreateCommand()

$command.CommandText = "select * from samples";

Here all of the contents of the “samples” table would be returned but this also works with more generic statements such as “show tables”. The final step (regardless of the SQL query) is to create the data reader itself:

$reader = $command.ExecuteReader()

The data reader will now contain the results from the database query.

Processing the Contents of a Data Reader

The contents of a data reader is processes row by row:

while ($reader.Read()) {

And then field by field:

for ($i= 0; $i -lt $reader.FieldCount; $i++) {

write-output $reader.GetValue($i).ToString()

}

In this example the contents of the table will be displayed directly to the screen.

Ending the Session

The final step (as in all database operations) is to close the database connection:

$connection.Close()

Any resources used by the connection will now be released.

Summary

It doesn’t matter which .NET framework language a programmer use using when it comes to using a data reader, the process is just the same:

create a database connection object
set the connection string
open the connection
use the connection to create a SQL command
use the SQL command to create a data reader
process the contents of the data reader

And, although the exact language syntax may change, the end result will be the same – a database that’s easy to access from a Windows application.

The copyright of the article How to Use the MySQL Data Reader in Windows Programming is owned by Mark Alexander Bain. Permission to republish How to Use the MySQL Data Reader in print or online must be granted by the author in writing.

Using the MySQL Data Reader

A PowerShell Script Using the MySQL Data Reader

Using a Data Reader

Print Article

View All Articles

How to subscribe to feeds