C# MySQL ConnectionTimeout

[KB1IBH]

i've created a small program which is meant to pull data (addresses) from a Mysql Database, Geocode (convert from address to lat/lon) those addresses using the Microsoft MapPoint API, then write the latitude and longitude to the appropriate entry in the database. when running this code, it executes the query to pull the data from the table (+-59,000 entries, ~18MB) the console shows an exception (screenshot below) which appears to show that the request is timing out. there is no doubt that data is being pulled, because after the error appears, it begins writing the data that it was able to pull (~200-400 entries). it appears as though after 30 seconds, it just quits, and im not certain how i would change this... it appears as though conn.ConnectionTimeout might be the way to go, but its read-only... any ideas?

everything is done in C# using Visual Studio 2008, under Windows 7 Ultimate.
the code uses MySql.Data.MySqlClient available from the Mysql website
as well as a reference to the Microsoft MapPoint API, which isnt freely available.

the screenshot of the error can be seen here: http://dl.dropbox.com/u/2502210/screeny.jpg

and the Code is below, minus connection string details...

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using MySql.Data.MySqlClient;

namespace Geocode
{
    class Program
    {
        static void Main(string[] args)
        {
            MapPoint.Application objmap = new MapPoint.Application();
            MapPoint.Map mymap = new MapPoint.Map();
            MapPoint.Location Loc;
            MapPoint.FindResults objFindResults = null;
            object key = 1;
            objmap.Visible = false;
            objmap.UserControl = false;

            MySqlConnection conn = new MySqlConnection("Userid=************;database=************;server=************;password=************;");
            MySqlConnection connwriter = new MySqlConnection("Userid=************;database=************;server=************;password=************"); 
            
            conn.Open();
            connwriter.Open();

            MySqlCommand command = conn.CreateCommand();
            command.CommandText = "Select * from FFL WHERE latitude IS NULL";
            command.CommandType = CommandType.Text;
            command.CommandTimeout = 0;    

            MySqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                objFindResults = mymap.FindAddressResults(reader["premise_street"].ToString(), reader["premise_city"].ToString(), "", reader["premise_state"].ToString(), reader["premise_zip_code"].ToString(), "USA");
               
                Loc = (MapPoint.Location)objFindResults.get_Item(ref key);
                double dblLat = Loc.Latitude;
                double dblLon = Loc.Longitude;

                string nonquery = "UPDATE FFL SET latitude='"
                    + dblLat.ToString()
                    + "', longitude='"
                    + dblLon.ToString()
                    + "' WHERE UID="
                    + reader["UID"].ToString();

                MySqlCommand edit = new MySqlCommand(nonquery, connwriter);
                int maxIterations = 100;
                for (int i = 0; i < maxIterations; i++) 
                {
                    try
                    {
                        edit.ExecuteNonQuery();
                        break;
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(e);
                    }
                }
                Console.WriteLine("updated data for " + reader["business_name"].ToString());
                //MapPoint.Pushpin pin = objmap.ActiveMap.AddPushpin(Loc, reader["ffl_number"].ToString());
            }

            //objmap.ActiveMap.SaveAs("FFL",MapPoint.GeoSaveFormat.geoFormatMap,true);

            reader.Close();
            conn.Close();
            connwriter.Close();
              
        }
    }
}

There are no compilation errors, or warnings

[sotoasty]

I have seen this happen many times. Essentially, it is the reader that is causing the issue. You have the reader open while you are finding the lat/long and updating the DB. The best way to do this is to use a data table instead of a reader. Or if you really want to use a reader, create a class with the properties of the FFL table, and with a function to save the data back to the MySQL DB. create a list of this new class, and in your while loop, add copies of the class to the list. Close your reader, then loop through the list of classes with a foreach, get the lat/long then save to the DB.

[Arjay]

I agree with post #2.

Btw, don't worry about have to keep a single MySql connection open because the providers do a pretty good job of caching connection info so just open a connection, grab the records and close the connection. When you are done processing the records, open a connection and write the data.

Lastly, check out using the 'using' block if your version of MySql connector supports it.

using( MySqlConnection conn = new MySqlConnection(...) )
{
  using(  MySqlCommand command = conn.CreateCommand( ) )
  {
    // use the cmd here
  }
}

With the using block, the MySqlConnection and MySqlCommand objects will be closed as soon as the block scope is left. Many folks run into resource/connections issues because they don't close the sql connection and command objects down properly - this is one way to avoid the problem.

[TechGnome]

"it appears as though conn.ConnectionTimeout "

DO NOT confuse ConnectionTimeout with CommandTimeout.... ConnectionTimeout is the length of time to wait WHEN ESTABLISHING the connection. Let me repeat that... CONNECTIONTimeout is for establishing the CONNECTION.

CommandTimeout is the length of time to wait for a COMMAND to execute before timing out. One has nothing to do with the other. What you need to do is revert the ConnectionTimeout back to it's default value (30 seconds) and start playing with the COMMANDTIMEOUT property of your Command Object. I usually start by setting it to 300 - or 5minutes. And work from there. DO NOT UNDER ANY CIRCUMSTANCES set it to 0. Ever. That's bad juju. Setting it to 0 will cause it to wait indefinitely. should you set it to 0, and then accidentally start a command that gets lose from you (it happens)... or run a really super intensive query, it can lock up your app, and could pose a load threat to the server.

-tg

[TechGnome]

I'd also like to point out that this is something I see a lot of... confusing ConnectionTimeout and CommandTimeout

-tg