3 files changed, 70 insertions, 4 deletions
diff --git a/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLGridData.cs b/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLGridData.cs
index 6ac8cc3..5006aaf 100644
--- a/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLGridData.cs
+++ b/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLGridData.cs
@@ -75,6 +75,7 @@ namespace OpenGrid.Framework.Data.MySQL
            }
            catch (Exception e)
            {
+                database.Reconnect();
                Console.WriteLine(e.ToString());
                return null;
            }
@@ -106,6 +107,7 @@ namespace OpenGrid.Framework.Data.MySQL
            }
            catch (Exception e)
            {
+                database.Reconnect();
                Console.WriteLine(e.ToString());
                return null;
            }
@@ -137,6 +139,7 @@ namespace OpenGrid.Framework.Data.MySQL
            }
            catch (Exception e)
            {
+                database.Reconnect();
                Console.WriteLine(e.ToString());
                return null;
            }
diff --git a/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLManager.cs b/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLManager.cs
index aee18b9..2eaa158 100644
--- a/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLManager.cs
+++ b/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLManager.cs
@@ -16,6 +16,7 @@ namespace OpenGrid.Framework.Data.MySQL
    class MySQLManager
    {
        IDbConnection dbcon;
+        string connectionString;
        /// <summary>
        /// Initialises and creates a new MySQL connection and maintains it.
@@ -29,7 +30,7 @@ namespace OpenGrid.Framework.Data.MySQL
        {
            try
            {
-                string connectionString = "Server=" + hostname + ";Port=" + port + ";Database=" + database + ";User ID=" + username + ";Password=" + password + ";Pooling=" + cpooling + ";";
+                connectionString = "Server=" + hostname + ";Port=" + port + ";Database=" + database + ";User ID=" + username + ";Password=" + password + ";Pooling=" + cpooling + ";";
                dbcon = new MySqlConnection(connectionString);
                dbcon.Open();
@@ -52,6 +53,28 @@ namespace OpenGrid.Framework.Data.MySQL
        }
        /// <summary>
+        /// Reconnects to the database
+        /// </summary>
+        public void Reconnect()
+        {
+            lock (dbcon)
+            {
+                try
+                {
+                    // Close the DB connection
+                    dbcon.Close();
+                    // Try reopen it
+                    dbcon = new MySqlConnection(connectionString);
+                    dbcon.Open();
+                }
+                catch (Exception e)
+                {
+                    Console.WriteLine("Unable to reconnect to database " + e.ToString());
+                }
+            }
+        }
+        /// <summary>
        /// Runs a query with protection against SQL Injection by using parameterised input.
        /// </summary>
        /// <param name="sql">The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y</param>
@@ -70,10 +93,47 @@ namespace OpenGrid.Framework.Data.MySQL
                return (IDbCommand)dbcommand;
            }
-            catch (Exception e)
+            catch
            {
-                Console.WriteLine("Failed during Query generation: " + e.ToString());
+                lock (dbcon)
-                return null;
+                {
+                    // Close the DB connection
+                    try
+                    {
+                        dbcon.Close();
+                    }
+                    catch { }
+                    // Try reopen it
+                    try
+                    {
+                        dbcon = new MySqlConnection(connectionString);
+                        dbcon.Open();
+                    }
+                    catch (Exception e)
+                    {
+                        Console.WriteLine("Unable to reconnect to database " + e.ToString());
+                    }
+                    // Run the query again
+                    try
+                    {
+                        MySqlCommand dbcommand = (MySqlCommand)dbcon.CreateCommand();
+                        dbcommand.CommandText = sql;
+                        foreach (KeyValuePair<string, string> param in parameters)
+                        {
+                            dbcommand.Parameters.Add(param.Key, param.Value);
+                        }
+                        return (IDbCommand)dbcommand;
+                    }
+                    catch (Exception e)
+                    {
+                        // Return null if it fails.
+                        Console.WriteLine("Failed during Query generation: " + e.ToString());
+                        return null;
+                    }
+                }
            }
        }
diff --git a/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLUserData.cs b/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLUserData.cs
index 57dbfc6..6f3cad6 100644
--- a/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLUserData.cs
+++ b/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLUserData.cs
@@ -51,6 +51,7 @@ namespace OpenGrid.Framework.Data.MySQL
            }
            catch (Exception e)
            {
+                database.Reconnect();
                Console.WriteLine(e.ToString());
                return null;
            }
@@ -78,6 +79,7 @@ namespace OpenGrid.Framework.Data.MySQL
            }
            catch (Exception e)
            {
+                database.Reconnect();
                Console.WriteLine(e.ToString());
                return null;
            }
@@ -116,6 +118,7 @@ namespace OpenGrid.Framework.Data.MySQL
            }
            catch (Exception e)
            {
+                database.Reconnect();
                Console.WriteLine(e.ToString());
                return null;
            }

diff --git a/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLGridData.cs b/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLGridData.cs index 6ac8cc3..5006aaf 100644 --- a/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLGridData.cs +++ b/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLGridData.cs
@@ -75,6 +75,7 @@ namespace OpenGrid.Framework.Data.MySQL
75	}	75	}
76	catch (Exception e)	76	catch (Exception e)
77	{	77	{
		78	database.Reconnect();
78	Console.WriteLine(e.ToString());	79	Console.WriteLine(e.ToString());
79	return null;	80	return null;
80	}	81	}
@@ -106,6 +107,7 @@ namespace OpenGrid.Framework.Data.MySQL
106	}	107	}
107	catch (Exception e)	108	catch (Exception e)
108	{	109	{
		110	database.Reconnect();
109	Console.WriteLine(e.ToString());	111	Console.WriteLine(e.ToString());
110	return null;	112	return null;
111	}	113	}
@@ -137,6 +139,7 @@ namespace OpenGrid.Framework.Data.MySQL
137	}	139	}
138	catch (Exception e)	140	catch (Exception e)
139	{	141	{
		142	database.Reconnect();
140	Console.WriteLine(e.ToString());	143	Console.WriteLine(e.ToString());
141	return null;	144	return null;
142	}	145	}


diff --git a/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLManager.cs b/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLManager.cs index aee18b9..2eaa158 100644 --- a/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLManager.cs +++ b/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLManager.cs
@@ -16,6 +16,7 @@ namespace OpenGrid.Framework.Data.MySQL
16	class MySQLManager	16	class MySQLManager
17	{	17	{
18	IDbConnection dbcon;	18	IDbConnection dbcon;
		19	string connectionString;
19		20
20	/// <summary>	21	/// <summary>
21	/// Initialises and creates a new MySQL connection and maintains it.	22	/// Initialises and creates a new MySQL connection and maintains it.
@@ -29,7 +30,7 @@ namespace OpenGrid.Framework.Data.MySQL
29	{	30	{
30	try	31	try
31	{	32	{
32	string connectionString = "Server=" + hostname + ";Port=" + port + ";Database=" + database + ";User ID=" + username + ";Password=" + password + ";Pooling=" + cpooling + ";";	33	connectionString = "Server=" + hostname + ";Port=" + port + ";Database=" + database + ";User ID=" + username + ";Password=" + password + ";Pooling=" + cpooling + ";";
33	dbcon = new MySqlConnection(connectionString);	34	dbcon = new MySqlConnection(connectionString);
34		35
35	dbcon.Open();	36	dbcon.Open();
@@ -52,6 +53,28 @@ namespace OpenGrid.Framework.Data.MySQL
52	}	53	}
53		54
54	/// <summary>	55	/// <summary>
		56	/// Reconnects to the database
		57	/// </summary>
		58	public void Reconnect()
		59	{
		60	lock (dbcon)
		61	{
		62	try
		63	{
		64	// Close the DB connection
		65	dbcon.Close();
		66	// Try reopen it
		67	dbcon = new MySqlConnection(connectionString);
		68	dbcon.Open();
		69	}
		70	catch (Exception e)
		71	{
		72	Console.WriteLine("Unable to reconnect to database " + e.ToString());
		73	}
		74	}
		75	}
		76
		77	/// <summary>
55	/// Runs a query with protection against SQL Injection by using parameterised input.	78	/// Runs a query with protection against SQL Injection by using parameterised input.
56	/// </summary>	79	/// </summary>
57	/// <param name="sql">The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y</param>	80	/// <param name="sql">The SQL string - replace any variables such as WHERE x = "y" with WHERE x = @y</param>
@@ -70,10 +93,47 @@ namespace OpenGrid.Framework.Data.MySQL
70		93
71	return (IDbCommand)dbcommand;	94	return (IDbCommand)dbcommand;
72	}	95	}
73	catch (Exception e)	96	catch
74	{	97	{
75	Console.WriteLine("Failed during Query generation: " + e.ToString());	98	lock (dbcon)
76	return null;	99	{
		100	// Close the DB connection
		101	try
		102	{
		103	dbcon.Close();
		104	}
		105	catch { }
		106
		107	// Try reopen it
		108	try
		109	{
		110	dbcon = new MySqlConnection(connectionString);
		111	dbcon.Open();
		112	}
		113	catch (Exception e)
		114	{
		115	Console.WriteLine("Unable to reconnect to database " + e.ToString());
		116	}
		117
		118	// Run the query again
		119	try
		120	{
		121	MySqlCommand dbcommand = (MySqlCommand)dbcon.CreateCommand();
		122	dbcommand.CommandText = sql;
		123	foreach (KeyValuePair<string, string> param in parameters)
		124	{
		125	dbcommand.Parameters.Add(param.Key, param.Value);
		126	}
		127
		128	return (IDbCommand)dbcommand;
		129	}
		130	catch (Exception e)
		131	{
		132	// Return null if it fails.
		133	Console.WriteLine("Failed during Query generation: " + e.ToString());
		134	return null;
		135	}
		136	}
77	}	137	}
78	}	138	}
79		139


diff --git a/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLUserData.cs b/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLUserData.cs index 57dbfc6..6f3cad6 100644 --- a/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLUserData.cs +++ b/OpenGridServices/OpenGrid.Framework.Data.MySQL/MySQLUserData.cs
@@ -51,6 +51,7 @@ namespace OpenGrid.Framework.Data.MySQL
51	}	51	}
52	catch (Exception e)	52	catch (Exception e)
53	{	53	{
		54	database.Reconnect();
54	Console.WriteLine(e.ToString());	55	Console.WriteLine(e.ToString());
55	return null;	56	return null;
56	}	57	}
@@ -78,6 +79,7 @@ namespace OpenGrid.Framework.Data.MySQL
78	}	79	}
79	catch (Exception e)	80	catch (Exception e)
80	{	81	{
		82	database.Reconnect();
81	Console.WriteLine(e.ToString());	83	Console.WriteLine(e.ToString());
82	return null;	84	return null;
83	}	85	}
@@ -116,6 +118,7 @@ namespace OpenGrid.Framework.Data.MySQL
116	}	118	}
117	catch (Exception e)	119	catch (Exception e)
118	{	120	{
		121	database.Reconnect();
119	Console.WriteLine(e.ToString());	122	Console.WriteLine(e.ToString());
120	return null;	123	return null;
121	}	124	}