Reimers.dk

I have previously written a post about how to read overlay information from an MS SQL Server database. To start off the new series on how to use databases in your map application I am going to supplement that post with a post about how to do the same thing with marker data stored in a MySQL database. MySQL is often praised because it is open source, but how many people really want to tinker with the code? A better argument is that it is free, and more importantly is that many web hosts give you MySQL capacity when hosting your website - MS SQL databases are frequently limited in size, which is not the case for MySQL databases.

To set up the sample database download and run the MySQL script to create the database. Make sure you have the latest MySQL .NET Connector for your site and import the MySql.Data.MySqlClient namespace.

You should now be ready to get on with the coding. When using the MySQL .NET Connector the code will be practically identical to reading information from an MS SQL Server.

C#

MySqlConnection conn = new MySqlConnection("Your connection
        string");

MySqlCommand cmd = 

    new MySqlCommand(
        "SELECT ID, Latitude, Longitude, MarkerText FROM vue_marker", 
        conn);

conn.Open();

System.Data.SqlClient.SqlDataReader dtr = cmd.ExecuteReader();

while (dtr.Read())

{

    //Create your overlays.

    GoogleMarker marker = new GoogleMarker();

    marker.ID = dtr["ID"].ToString();

    marker.Latitude = Convert.ToDouble(dtr["Lat"]);

    marker.Longitude = Convert.ToDouble(dtr["Lng"]);

    marker.MarkerText = dtr["Text"].ToString();

    GMap.Overlays.Add(marker);
    //In the free version use:
    //GMap.Markers.Add(marker);

}

dtr.Close();

conn.Close();

VB.NET

Dim conn As New MySqlConnection("Your connection
        string")

Dim cmd As New MySqlCommand( _
"SELECT ID, Latitude, Longitude, MarkerText FROM vue_marker", _
conn)

conn.Open()

Dim dtr As MySqlDataReader = cmd.ExecuteReader

While dtr.Read

    Dim marker As New GoogleMarker

    marker.ID = dtr("ID").ToString

    marker.Latitude = CType(dtr("Latitude"), Double)

    marker.Longitude = CType(dtr("Longitude"), Double)

    marker.MarkerText = dtr("MarkerText").ToString

    GMap.Overlays.Add(marker)
'In the free version use the following:
    'GMap.Markers.Add(marker)

End While

dtr.Close()

conn.Close()

If you compare to the post I wrote on reading from an SQL Server then you will notice how close it is.

There are many other code samples in the previous post on reading and they all follow the same pattern. I'm not going to repeast them all here, but leave it to you to convert polygon analysis using MySQL data.