Advertisment

Query MySQL with C#

author-image
CIOL Bureau
Updated On
New Update

Shekhar Govindarajan

Advertisment

Install the MySQL database for Windows as explained in the article “MySQL for Windows’. For this article the following steps are required:

  • Install .Net SDK

  • Make C# (part of .Net SDK) understand ODBC (refer to the box ‘ODBC’)

  • Install ODBC drivers called MyODBC for MySQL 

We have used the final release of the .Net SDK. You can download it from http://msdn.microsoft.com/downloads/. Once downloaded, run ‘setup.exe’ to install it. 

Advertisment

The SDK may need MDAC (Microsoft Data Access Components) 2.7, which can be downloaded from http://www.microsoft.com/ data/download_270RTM.htm. 

For step 2, you must download ‘ODBC .Net Provider’ from http://msdn.microsoft.com/downloads/. You can find it under the section ‘.Net Framework>ODBC .Net Data Provider’, in the left HTML frame. Run the downloaded file named odbc_net.msi. ODBC .Net Provider is a set of .Net classes, which provide methods (functions) to connect and query an ODBC compliant database. 

For step 3, unzip the MyODBC zip file found in Developer section on this month CD. Subsequently run setup.exe. 

Advertisment

An example
As an example database, we create a database called ‘mp3collection’, which stores information—title, artist, album, and filename—about MP3 files. A table within ‘mp3collection’ named ‘mp3files’ will be used to store all this information in its corresponding columns. We also have a column named ‘sno’ (serial number) in ‘mp3files’. We have deliberately used this column to explain the concept of ‘auto_ increment’ facility in MySQL. See the box ‘AUTO_INCREMENT’ later in this article. 

Issue the following at the Command Prompt of Windows 2000:

c:\mysql\bin\mysql -u root –p 

When prompted, enter the password corresponding to the ‘root’—that you had set before. Now enter the following command at the mysql prompt:

create database mp3collection;
use mp3collection;
create table mp3files (sno smallint unsigned auto_increment not null,title char(50),artist char(20),album char(50),filename char(50), primary key (sno));
grant all on mp3col lection.* to csharp@127.0.0.1 identified by ‘pc quest’;

Advertisment

The last statement creates a new MySQL user named ‘csharp’ and assigns him the password ‘pcquest’. It also gives this user ‘all’ access privileges on the database ‘mp3collection’ when he connects from the same machine—the one that runs MySQL. Refer to the article Access Priviliges in MySQL page 102, November 2001 for more on the MySQL ‘grant’ statements.

Set up a DSN 
To connect to the MySQL database through C#, we need to setup what is called a DSN (Data Source Name) on the Windows machine. Navigate to Control Panel>Administrative Tools>Data Sources (ODBC). Click on the tab User DSN and then click on Add. Select MySQL from the list of presented drivers and click on Finish. The MySQL Driver configuration window will pop up. Fill in the following:

Windows DSN name: mysql_csharp
MySQL host: 127.0.01
MySQL database name: mp3collection
user : csharp
password : pcquest

Leave the remaining text fields blank. 

Advertisment

The C# application
Copy the files – insert.cs and display.cs – found in the cdrom\sorc_cod directory on the CD to your hard disk. They contain C# code to insert data and display data in the MySQL database respectively. Now compile the two programs as:

csc /r:Microsoft.odbc.dll insert.cs
csc /r:Microsoft.odbc.dll display.cs

We will explain the /r:Microsoft. odbc.dll option of the C# compiler a little later. After compilation, execute insert.exe. The program prompts for the MP3 title, artist, album and filename. Once you provide these details, it displays the SQL INSERT query, which will be submitted to the MySQL server. Note that in the INSERT query, no value for ‘sno’ column is specified. Refer to the box ‘AUTO_INCREMENT’ for the reason. Run insert.exe a couple of more times to add data for more MP3 files. Then execute display.exe, which will use the SQL SELECT query to retrieve, and display the information on MP3s stored in the database. 

Understanding the code
Open insert.cs and display.cs in Notepad to follow us better. As said before we installed ODBC .Net provider for methods, which allows connecting and querying ODBC databases. When installed, it provides a Namespace (collection of classes) called Microsoft.Data. Odbc. The classes within this Namespace provide the required methods. Hence with the line:

Advertisment

using Microsoft.Data.Odbc 

We declare that we will be using classes in this Namespace. So where is the actual code for these classes located? It’s all bundled in a dll file called Microsoft.odbc.dll. We know this but the C# compiler doesn’t. Hence when we compiled the programs above, we specified this using the ‘/r’ option. Such DLL files are called assemblies in C# lingo. The next couple of steps in the Main method perform the following:

  • Specify the Data Source Name, which is required to connect to the ODBC database.

  • Connect to the database

  • Construct an SQL Query.

  • Submit the query to the database

  • In case of display.cs, we get the result of the SELECT Query. We iterate through this result and display it. 

  • Close the connection to the database

Now we explain how we perform each of the above steps in C#.

Advertisment
  • For the first step, we need to create an object of the class OdbcConnection by giving the DSN name as a parameter to its constructor. The syntax is:

OdbcConnection_object = new OdbcConnection(“DSN=”);

2. We connect to the database server as:

connection.Open( );

where connection is the OdbcConnection object. 

3. We construct an SQL query by creating an object of OdbcCommand class. We give the SQL query itself and the OdbcConnection object as a parameter to its constructor. The syntax is:

OdbcCommand_object = new OdbcCommand(,);

4. In case of SQL queries that don’t return any data like INSERT, DELETE, UPDATE etc. we call ExecuteNonQuery( ) method. In case of SQL queries which return data like the SELECT query, we call ExecuteReader( ) method. Both these methods belong to the OdbcCommand class. The latter method returns an OdbcDataReader object, which contains the returned data rows. 

5. When we call the method Read( ) of the OdbcDataReader class, it reads a data row and returns true. It will return false when it has read all the data rows. Hence we use a while loop as follows:

while(reader.Read( ))
{
code to display columns’ data in the data row
}

Here ‘reader’ is the OdbcDataReader object. Within this loop we can use the GetString( ) method of the OdbcDataReader class to get data in a particular column in the returned data rows. This method accepts the column number as a parameter and returns the data in that column. The column numbers start from zero (0). For example if we provide ‘2’ as parameter, GetString( ) will return the artist name. However, since we know the name of the columns, it would make more sense to retrieve the data using the column name. Another method of the OdbcData Reader class named GetOrdinal( ), returns the column number when given the column name as a parameter. Hence we use this method as a parameter to the GetString( ) method as you can see in code of display.cs. The syntax of GetString( ) and GetOrdinal( ) methods are as follows:

GetString(
); (note that the column number starts from zero)
GetOrdinal(“
);

6. Finally we close the connection using connection.Close( ).
You can also connect to a MySQL database server running on a remote Linux machine or a remote Windows machine. You only need to substitute the IP address 127.0.0.1 with the IP address of the machine on which you would be running the C# programs in the GRANT statement. Secondly you must specify the IP address of the remote machine for the ‘MySQL host’ in place of 127.0.0.1 while setting up the DSN. 

Source: PCQuest

tech-news