Software Dev't Navigation

Latest news

11 Feb 2008: MySQL Connector/NET version 5.1.5 released with a number of important bug fixes including support for TinyInt as Boolean.

Using MySQL with .NET Application

If you want to use MySQL in a .NET application then I have found the most straightforward method is to download the ADO.Net Driver for MySQL (Connector/NET) from the MySQL website.

If you choose not to use the more modern ADO.Net Connectors then it is also possible to use the ODBC (Open DataBase Connectivity) Connectors supplied by Microsoft. Both methods have their pros and cons, so you should look at these carefully before deciding which way to go. This web page only deals with using the ADO.NET connectors supplied by MySQL AB.

What is ADO NET

This is the latest technology from Microsoft for providing data access methods; previously C++ programmers had a choice of using ODBC and DAO classes in the Microsoft MFC (Microsoft Foundation Classes) to gain access to databases. ADO has a big advantage in that all of the data access classes are encompassed in the NET framework, so no extra code or libraries are required, unless of course you want to connect to MySQL (or other non Microsoft databases) which requires a separate NET connector. I find that the other big advantage of ADO NET is that you can use a language such as C# as opposed to C++ and also take advantage of the extremely rich NET class library. The following is all about how to go about taking advantage of ADO NET whilst using MySQL (as opposed to sat SQLServer) as your database.

Downloading the Connector

On the MySQL site navigate through to Drivers/Downloads and you will find a choice of connector depending on the version of NET you are using. If you are using NET Framework 1.1 then you should download Connector/Net 1.0 (latest stable version 1.07). On the other hand if you are using NET framework 2.0 then there doesn't seem anything to lose by downloading Connector/Net 5.1 (latest stable version 5.1.5). It appears Connector/Net 5.0 is being replaced as the MySQL Server 5.1 version becomes the main release.

Installing the Connector

If you run the installer you will be asked if you want it to be installed in the GAC (Global Assembly Cache). Since this Net assembly will be used quite often I answered yes, as the GAC is used to store NET assemblies designed to be shared by several applications. That is basically all there is to installation.

Connecting to a MySQL Application

Once you know how this is not too tedious. The following is based on my experiences using SharpDevelop and the C# language. Within your SharpDevelop project go to the Project/Add Reference tab. Under the GAC tab you should find a MySQL.Data reference which you need to select. Once selected you should now see in the project tree a new reference to MySQL.Data which is required to connect to the database. I prefer to set up a class for Connection and Disconnection, but the following sample code will give you some idea of how to proceed.

Sample Connection Class

using System;
using System.IO;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace DataManager
{
class DataDB
{
/*
* Default Constructor for the class.
* It will be passed 4 items of data. 4 strings with
* connection information, and a windows forms label
* to show the connection status
*/
#region Default Constructor
public DataDB(string myServer, string myUsername, string myPassword, string myDatabase) <br />
{
try
{
this.Server = myServer;
this.Username = myUsername;
this.Password = myPassword;
this.Database = myDatabase;

// Check to make sure that our class was passed information
// from the caller before we attempt to connect
if (String.IsNullOrEmpty(Server))
throw new InvalidDataException("No Server Specified");
if (String.IsNullOrEmpty(Username))
throw new InvalidDataException("No Username Specified");
if (String.IsNullOrEmpty(Password))
throw new InvalidDataException("No Password Specified");
if (String.IsNullOrEmpty(Database))
throw new InvalidDataException("No Database Specified");

}
catch (Exception Ex)
{
MessageBox.Show(Ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
#endregion
/*
* The connection sub
* This function accepts a label element to allow status messages
* to be relayed to the user.
*
*/
#region Connect
public void Connect(System.Windows.Forms.Label lblStatus)
{
try
{
// Our connection string
string ConnString = String.Format("server={0};user id={1}; password={2}; database={3}; pooling=false",
this.Server ,this.Username, this.Password, this.Database );
if (conn != null)
conn.Close();

// Initialise the connection
conn = new MySqlConnection(ConnString);
// Open the connection

conn.Open();
lblStatus.Text = String.Format("Connected to {0} as user: {1} at {2}", this.Server , this.Username , DateTime.Now.ToString());

}
catch (Exception Ex)
{
MessageBox.Show(Ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

}

You would then need to create a new instance of the class DataDB - say MyDB and create a connection as follows :-

MyDB = new DataDB("myservername", "myusername", "mypassword", "mydatabase");
MyDB.Connect(lblStatus);}

One very important point to note if you are using ADO NET 2.0 is the need to use open connections very sparingly. If you have come from a Delphi background you may be used to keeping the database connection open whenever the application is in use. However, because ADO NET uses a dataset approach which involves dragging the data from the database into the dataset, it is much better to open the database run some SQL and then close the database connection. So instead of having a connection open for the life of the application running, you will tend to have many connections, each of which stay alive for the life of a transaction.

ePlaice / For the Best Software on the Net

Mainly Free and Open Source Software