From e1b5c612472b9d1acf47383c0bf75b555daff2e6 Mon Sep 17 00:00:00 2001 From: Master ScienceSim Date: Thu, 4 Feb 2010 13:19:30 -0800 Subject: Updated MySQL connection management to use the MySQL connection pooling. This should accommodate various timeout problems that exist with the current connection pool code in a more general and standard way. --- OpenSim/Data/MySQL/MySQLGenericTableHandler.cs | 215 +++++++++++++------------ 1 file changed, 116 insertions(+), 99 deletions(-) (limited to 'OpenSim/Data/MySQL/MySQLGenericTableHandler.cs') diff --git a/OpenSim/Data/MySQL/MySQLGenericTableHandler.cs b/OpenSim/Data/MySQL/MySQLGenericTableHandler.cs index fdb98eb..698bf52 100644 --- a/OpenSim/Data/MySQL/MySQLGenericTableHandler.cs +++ b/OpenSim/Data/MySQL/MySQLGenericTableHandler.cs @@ -54,12 +54,16 @@ namespace OpenSim.Data.MySQL string realm, string storeName) : base(connectionString) { m_Realm = realm; + m_connectionString = connectionString; + if (storeName != String.Empty) { - Assembly assem = GetType().Assembly; - - Migration m = new Migration(m_Connection, assem, storeName); - m.Update(); + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) + { + dbcon.Open(); + Migration m = new Migration(dbcon, GetType().Assembly, storeName); + m.Update(); + } } Type t = typeof(T); @@ -107,147 +111,160 @@ namespace OpenSim.Data.MySQL List terms = new List(); - MySqlCommand cmd = new MySqlCommand(); - - for (int i = 0 ; i < fields.Length ; i++) + using (MySqlCommand cmd = new MySqlCommand()) { - cmd.Parameters.AddWithValue(fields[i], keys[i]); - terms.Add("`" + fields[i] + "` = ?" + fields[i]); - } - - string where = String.Join(" and ", terms.ToArray()); + for (int i = 0 ; i < fields.Length ; i++) + { + cmd.Parameters.AddWithValue(fields[i], keys[i]); + terms.Add("`" + fields[i] + "` = ?" + fields[i]); + } - string query = String.Format("select * from {0} where {1}", - m_Realm, where); + string where = String.Join(" and ", terms.ToArray()); - cmd.CommandText = query; + string query = String.Format("select * from {0} where {1}", + m_Realm, where); - return DoQuery(cmd); + cmd.CommandText = query; + + return DoQuery(cmd); + } } protected T[] DoQuery(MySqlCommand cmd) { - IDataReader reader = ExecuteReader(cmd); - if (reader == null) - return new T[0]; - - CheckColumnNames(reader); - List result = new List(); - while (reader.Read()) + using (MySqlConnection dbcon = new MySqlConnection(m_connectionString)) { - T row = new T(); + dbcon.Open(); + cmd.Connection = dbcon; - foreach (string name in m_Fields.Keys) + using (IDataReader reader = cmd.ExecuteReader()) { - if (m_Fields[name].GetValue(row) is bool) - { - int v = Convert.ToInt32(reader[name]); - m_Fields[name].SetValue(row, v != 0 ? true : false); - } - else if (m_Fields[name].GetValue(row) is UUID) - { - UUID uuid = UUID.Zero; + if (reader == null) + return new T[0]; - UUID.TryParse(reader[name].ToString(), out uuid); - m_Fields[name].SetValue(row, uuid); - } - else if (m_Fields[name].GetValue(row) is int) - { - int v = Convert.ToInt32(reader[name]); - m_Fields[name].SetValue(row, v); - } - else - { - m_Fields[name].SetValue(row, reader[name]); - } - } - - if (m_DataField != null) - { - Dictionary data = - new Dictionary(); + CheckColumnNames(reader); - foreach (string col in m_ColumnNames) + while (reader.Read()) { - data[col] = reader[col].ToString(); - if (data[col] == null) - data[col] = String.Empty; + T row = new T(); + + foreach (string name in m_Fields.Keys) + { + if (m_Fields[name].GetValue(row) is bool) + { + int v = Convert.ToInt32(reader[name]); + m_Fields[name].SetValue(row, v != 0 ? true : false); + } + else if (m_Fields[name].GetValue(row) is UUID) + { + UUID uuid = UUID.Zero; + + UUID.TryParse(reader[name].ToString(), out uuid); + m_Fields[name].SetValue(row, uuid); + } + else if (m_Fields[name].GetValue(row) is int) + { + int v = Convert.ToInt32(reader[name]); + m_Fields[name].SetValue(row, v); + } + else + { + m_Fields[name].SetValue(row, reader[name]); + } + } + + if (m_DataField != null) + { + Dictionary data = + new Dictionary(); + + foreach (string col in m_ColumnNames) + { + data[col] = reader[col].ToString(); + if (data[col] == null) + data[col] = String.Empty; + } + + m_DataField.SetValue(row, data); + } + + result.Add(row); } - - m_DataField.SetValue(row, data); } - - result.Add(row); } - CloseReaderCommand(cmd); - return result.ToArray(); } public T[] Get(string where) { - MySqlCommand cmd = new MySqlCommand(); - - string query = String.Format("select * from {0} where {1}", - m_Realm, where); - - cmd.CommandText = query; - - return DoQuery(cmd); + using (MySqlCommand cmd = new MySqlCommand()) + { + + string query = String.Format("select * from {0} where {1}", + m_Realm, where); + + cmd.CommandText = query; + + return DoQuery(cmd); + } } public bool Store(T row) { - MySqlCommand cmd = new MySqlCommand(); + using (MySqlCommand cmd = new MySqlCommand()) + { - string query = ""; - List names = new List(); - List values = new List(); + string query = ""; + List names = new List(); + List values = new List(); - foreach (FieldInfo fi in m_Fields.Values) - { - names.Add(fi.Name); - values.Add("?" + fi.Name); - cmd.Parameters.AddWithValue(fi.Name, fi.GetValue(row).ToString()); - } + foreach (FieldInfo fi in m_Fields.Values) + { + names.Add(fi.Name); + values.Add("?" + fi.Name); + cmd.Parameters.AddWithValue(fi.Name, fi.GetValue(row).ToString()); + } - if (m_DataField != null) - { - Dictionary data = + if (m_DataField != null) + { + Dictionary data = (Dictionary)m_DataField.GetValue(row); - foreach (KeyValuePair kvp in data) - { - names.Add(kvp.Key); - values.Add("?" + kvp.Key); - cmd.Parameters.AddWithValue("?" + kvp.Key, kvp.Value); + foreach (KeyValuePair kvp in data) + { + names.Add(kvp.Key); + values.Add("?" + kvp.Key); + cmd.Parameters.AddWithValue("?" + kvp.Key, kvp.Value); + } } - } - query = String.Format("replace into {0} (`", m_Realm) + String.Join("`,`", names.ToArray()) + "`) values (" + String.Join(",", values.ToArray()) + ")"; + query = String.Format("replace into {0} (`", m_Realm) + String.Join("`,`", names.ToArray()) + "`) values (" + String.Join(",", values.ToArray()) + ")"; - cmd.CommandText = query; + cmd.CommandText = query; - if (ExecuteNonQuery(cmd) > 0) - return true; + if (ExecuteNonQuery(cmd) > 0) + return true; - return false; + return false; + } } public bool Delete(string field, string val) { - MySqlCommand cmd = new MySqlCommand(); + using (MySqlCommand cmd = new MySqlCommand()) + { - cmd.CommandText = String.Format("delete from {0} where `{1}` = ?{1}", m_Realm, field); - cmd.Parameters.AddWithValue(field, val); + cmd.CommandText = String.Format("delete from {0} where `{1}` = ?{1}", m_Realm, field); + cmd.Parameters.AddWithValue(field, val); - if (ExecuteNonQuery(cmd) > 0) - return true; + if (ExecuteNonQuery(cmd) > 0) + return true; - return false; + return false; + } } } } -- cgit v1.1