MySQL OdbcCommand commands sometimes hangs?

Question

I am running a loop in C# that reads a file and make updates to the MySQL database with MySQL ODBC 5.1 driver in a Windows 8 64-bit environment.

The operations is simple

1. Count +1
2. See if file exists
3. Load XML file(XDocument)
4. Fetch data from XDocument
5. Open ODBCConnection
6. Run a couple of Stored Procedures against the MySQL database to store data
7. Close ODBCConnection

The problem is that after a while it will hang on for example a OdbcCommand.ExecuteNonQuery. It is not always the same SP that it will hang on?

This is a real problem, I need to loop 60 000 files but it only last around 1000 at a time.

Edit 1: The problem seemse to accure here hever time :

public bool addPublisherToGame(int inPublisherId, int inGameId)
        {
            string sqlStr;
            OdbcCommand commandObj;
            try
            {
                sqlStr = "INSERT INTO games_publisher_binder (gameId, publisherId) VALUE(?,?)";

                commandObj = new OdbcCommand(sqlStr, mainConnection);
                commandObj.Parameters.Add("@gameId", OdbcType.Int).Value = inGameId;
                commandObj.Parameters.Add("@publisherId", OdbcType.Int).Value = inPublisherId;

                if (Convert.ToInt32(executeNonQuery(commandObj)) > 0)
                    return true;
                else
                    return false;

            }
            catch (Exception ex)
            {
                throw (loggErrorMessage(this.ToString(), "addPublisherToGame", ex, -1, "", ""));
            }
            finally
            {

            }
        }


protected object executeNonQuery(OdbcCommand inCommandObj)
        {
            try
            {
                //FileStream file = new FileStream("d:\\test.txt", FileMode.Append, FileAccess.Write);

                //System.IO.StreamWriter stream = new System.IO.StreamWriter(file);
                //stream.WriteLine(DateTime.Now.ToString() + " - " + inCommandObj.CommandText);
                //stream.Close();
                //file.Close();

                //mainConnection.Open();
                return inCommandObj.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

I can see that the in parameters is correct

The open and close of the connection is done in a top method for ever loop (with finally).

Edit 2:

This is the method that will extract the information and save to database :

public Boolean addBoardgameToDatabase(XElement boardgame, GameFactory gameFactory)
        {
            int incomingGameId = -1;
            XElement tmpElement;
            string primaryName = string.Empty;
            List<string> names = new List<string>();
            GameStorage externalGameStorage;

            int retry = 3;
            try
            {
                if (boardgame.FirstAttribute != null &&
                    boardgame.FirstAttribute.Value != null)
                {
                    while (retry > -1)
                    {
                        try
                        {
                            incomingGameId = int.Parse(boardgame.FirstAttribute.Value);

                            #region Find primary name
                            tmpElement = boardgame.Elements("name").Where(c => c.Attribute("primary") != null).FirstOrDefault(a => a.Attribute("primary").Value.Equals("true"));

                            if (tmpElement != null)
                                primaryName = tmpElement.Value;
                            else
                                return false;
                            #endregion

                            externalGameStorage = new GameStorage(incomingGameId,
                                                                primaryName,
                                                                string.Empty,
                                                                getDateTime("1/1/" + boardgame.Element("yearpublished").Value),
                                                                getInteger(boardgame.Element("minplayers").Value),
                                                                getInteger(boardgame.Element("maxplayers").Value),
                                                                boardgame.Element("playingtime").Value,
                                                                0, 0, false);

                            gameFactory.updateGame(externalGameStorage);
                            gameFactory.updateGameGrade(incomingGameId);

                            gameFactory.removeDesignersFromGame(externalGameStorage.id);
                            foreach (XElement designer in boardgame.Elements("boardgamedesigner"))
                            {
                                gameFactory.updateDesigner(int.Parse(designer.FirstAttribute.Value), designer.Value);
                                gameFactory.addDesignerToGame(int.Parse(designer.FirstAttribute.Value), externalGameStorage.id);
                            }

                            gameFactory.removePublishersFromGame(externalGameStorage.id);
                            foreach (XElement publisher in boardgame.Elements("boardgamepublisher"))
                            {
                                gameFactory.updatePublisher(int.Parse(publisher.FirstAttribute.Value), publisher.Value, string.Empty);
                                gameFactory.addPublisherToGame(int.Parse(publisher.FirstAttribute.Value), externalGameStorage.id);
                            }

                            foreach (XElement element in boardgame.Elements("name").Where(c => c.Attribute("primary") == null))
                                names.Add(element.Value);

                            gameFactory.removeGameNames(incomingGameId);

                            foreach (string name in names)
                                if (name != null && name.Length > 0)
                                    gameFactory.addGameName(incomingGameId, name);

                            return true;
                        }
                        catch (Exception)
                        {

                            retry--;
                            if (retry < 0)
                                return false;
                        }
                    }

                }

                return false;
            }
            catch (Exception ex)
            {
                throw (new Exception(this.ToString() + ".addBoardgameToDatabase : " + ex.Message, ex));
            }
        }

And then we got one step higher, the method that will trigger addBoardgameToDatabase :

private void StartThreadToHandleXmlFile(int gameId)
        {
            FileInfo fileInfo;
            XDocument xmlDoc;
            Boolean gameAdded = false;
            GameFactory gameFactory = new GameFactory();

            try
            {
                fileInfo = new FileInfo(_directory + "\\" + gameId.ToString() + ".xml");

                if (fileInfo.Exists)
                {
                    xmlDoc = XDocument.Load(fileInfo.FullName);

                    if (addBoardgameToDatabase(xmlDoc.Element("boardgames").Element("boardgame"), gameFactory))
                    {
                        gameAdded = true;
                        fileInfo.Delete();
                    }
                    else
                        return;
                }

                if (!gameAdded)
                {
                    gameFactory.InactivateGame(gameId);
                    fileInfo.Delete();
                }
            }
            catch (Exception)
            { throw; }
            finally
            {
                if(gameFactory != null)
                    gameFactory.CloseConnection();
            }
        }

And then finally the top level :

public void UpdateGames(string directory)
{
    DirectoryInfo dirInfo;
    FileInfo fileInfo;
    Thread thread;

    int gameIdToStartOn = 1;

    dirInfo = new DirectoryInfo(directory);
    if(dirInfo.Exists)
    {
        _directory = directory;
        fileInfo = dirInfo.GetFiles("*.xml").OrderBy(c=> int.Parse(c.Name.Replace(".xml",""))).FirstOrDefault();

        gameIdToStartOn = int.Parse(fileInfo.Name.Replace(".xml", ""));

        for (int gameId = gameIdToStartOn; gameId < 500000; gameId++) 
        {
            try
            { StartThreadToHandleXmlFile(gameId); }
            catch(Exception){}
        }
    }
}

Answer 1

1. Use SQL connection pooling by adding "Pooling=true" to your connectionstring.
2. Make sure you properly close the connection AND the file.
3. You can create one large query and execute it only once, I think it is a lot faster then 60.000 loose queries!

Can you show a bit of your code?