public override DBResult Update(DatabaseConnection conn, QueryString sql, OdbcParameterList parms)
{
if (ReadOnly)
{
throw new DataSourceException("{0} is read-only", this);
}
DBResult ret = new DBResult(DBResult.Result.Invalid);
try
{
if (conn == null)
{
throw new Exception("No connection to Database. Credentials must be specified and valid.");
}
// Retrofit SQL to take Odbc style parameter markers
int i = 0;
int loc = 0;
if (parms != null)
{
String sqlString = sql.ToString();
foreach (OdbcParameter parm in parms)
{
loc = sqlString.IndexOf('?', loc);
if (loc >= 0)
{
loc++;
sqlString = sqlString.Substring(0, loc) + i + sqlString.Substring(loc);
i++;
}
}
sql = sql.Trusted ? FormatTrusted(sqlString) : FormatUntrusted(sqlString);
}
MySqlCommand cmd = new MySqlCommand(sql.ToString(), (MySqlConnection)conn.DbConnection);
if (parms != null)
{
i = 0;
foreach (OdbcParameter parm in parms)
{
cmd.Parameters.Add(new MySqlParameter("" + i, parm.Value));
i++;
}
}
cmd.CommandTimeout = DefaultTimeout;
ret.RowsAffected = cmd.ExecuteNonQuery();
ret.ResultCode = DBResult.Result.Success;
ret.Message = "SUCCESS";
cmd.Dispose();
}
catch (Exception e)
{
ret = HandleException(sql, e);
}
LastResult = ret;
return(ret);
}
public override DBResult Query(DatabaseConnection conn, QueryString sql, OdbcParameterList parms, out DatabaseDataReader reader, int timeout = 30)
{
DBResult ret = new DBResult(DBResult.Result.Success);
reader = null;
try
{
if (conn == null)
{
throw new Exception("No connection to Database. Credentials must be specified and valid.");
}
OdbcCommand cmd = new OdbcCommand(sql.ToString(), (OdbcConnection)conn.DbConnection);
reader = new DatabaseDataReader(cmd.ExecuteReader());
if (reader.HasRows == false)
{
ret.ResultCode = DBResult.Result.NoData;
}
}
catch (Exception e)
{
ret = HandleException(sql, e);
}
return(ret);
}
public override DBResult QueryScalar(DatabaseConnection conn, QueryString sql, out Object result)
{
DBResult ret = new DBResult(DBResult.Result.NoData);
result = null;
try
{
if (conn == null)
{
throw new Exception("No connection to Database. Credentials must be specified and valid.");
}
MySqlCommand cmd = new MySqlCommand(sql.ToString(), (MySqlConnection)conn.DbConnection);
result = cmd.ExecuteScalar();
if (result != null)
{
ret.ResultCode = DBResult.Result.Success;
}
cmd.Dispose();
}
catch (Exception e)
{
ret = HandleException(sql, e);
}
LastResult = ret;
return(ret);
}
public DBResult QueryScalar(QueryString sql, out Object retObject)
{
DatabaseConnection connection;
retObject = null;
DBResult retVal;
if ((retVal = GetConnection(out connection)).ResultCode == DBResult.Result.Success)
{
#if INCLUDE_PERFORMANCE_LOGGING
if (m_bPerformanceLoggingEnabled)
{
connection.SelectTimer.Start(sql.ToString());
}
#endif
retVal = QueryScalar(connection, sql, out retObject);
#if INCLUDE_PERFORMANCE_LOGGING
if (m_bPerformanceLoggingEnabled)
{
connection.SelectTimer.Stop();
}
#endif
if (retVal.ResultCode == DBResult.Result.ConnectionDropped)
{
int retriesLeft = connection.ConnectionRetries;
do
{
Log.LogText(LogLevel.WARNING, "The connection was found to be dropped - will retry {0} of {1}", retriesLeft, connection.ConnectionRetries);
if (connection != null)
{
_connectionPool.DropConnection(connection);
}
if ((retVal = GetConnection(out connection)).ResultCode == DBResult.Result.Success)
{
retVal = QueryScalar(connection, sql, out retObject);
}
if (connection.ConnectionRetryInterval != TimeSpan.Zero)
{
Thread.Sleep(connection.ConnectionRetryInterval);
}
} while (retVal.ResultCode == DBResult.Result.ConnectionDropped && --retriesLeft > 0);
}
ReleaseConnection(connection);
}
LastResult = retVal;
return(retVal);
}
public override DBResult Update(DatabaseConnection conn, QueryString sql, OdbcParameterList parms)
{
if (ReadOnly)
{
throw new DataSourceException("{0} is read-only", this);
}
DBResult ret = new DBResult(DBResult.Result.Invalid);
OdbcCommand cmd = null;
try
{
if (conn == null)
{
throw new Exception("No connection to Database. Credentials must be specified and valid.");
}
cmd = new OdbcCommand(sql.ToString(), (OdbcConnection)conn.DbConnection);
if (parms != null)
{
cmd.Parameters.AddRange(parms.ToArray());
}
ret.RowsAffected = cmd.ExecuteNonQuery();
cmd.Parameters.Clear(); // Bug Fix #360
ret.ResultCode = DBResult.Result.Success;
ret.Message = "SUCCESS";
}
catch (Exception e)
{
ret = HandleException(sql, e);
if (cmd != null && parms != null)
{
cmd.Parameters.Clear();
}
}
return(ret);
}
public override DBResult Query(DatabaseConnection conn, QueryString sql, OdbcParameterList parms, out DatabaseDataReader reader, int timeout = 30)
{
DBResult ret = new DBResult(DBResult.Result.Success);
reader = null;
MySqlCommand cmd = null;
try
{
if (conn == null)
{
throw new Exception("No connection to Database. Credentials must be specified and valid.");
}
// Retrofit SQL to take Odbc style parameter markers
int i = 0;
int loc = 0;
if (parms != null)
{
String sqlString = sql.ToString();
foreach (OdbcParameter parm in parms)
{
loc = sqlString.IndexOf('?', loc);
if (loc >= 0)
{
loc++;
sqlString = sqlString.Substring(0, loc) + i + sqlString.Substring(loc);
i++;
}
}
sql = sql.Trusted ? FormatTrusted(sqlString) : FormatUntrusted(sqlString);
}
cmd = new MySqlCommand(sql.ToString(), (MySqlConnection)conn.DbConnection);
cmd.CommandTimeout = timeout;
if (parms != null)
{
i = 0;
foreach (OdbcParameter parm in parms)
{
cmd.Parameters.Add(new MySqlParameter("" + i, parm.Value));
i++;
}
}
reader = new DatabaseDataReader(cmd.ExecuteReader());
if (reader.HasRows == false)
{
ret.ResultCode = DBResult.Result.NoData;
reader.Close();
}
}
catch (Exception e)
{
ret = HandleException(sql, e);
}
finally
{
if (cmd != null)
{
// cmd.Dispose();
}
}
LastResult = ret;
return(ret);
}
public DBResult Update(QueryString sql, OdbcParameterList parameterDictionary)
{
if (ReadOnly)
{
throw new DataSourceException("{0} is read-only", this);
}
DatabaseConnection connection;
DBResult result = GetConnection(out connection);
result = Update(connection, sql, parameterDictionary);
if (result.ResultCode == DBResult.Result.ConnectionDropped)
{
/** If the connection was lost during the query, we will perform retry logic */
int retriesLeft = connection.ConnectionRetries;
do
{
Log.LogText(LogLevel.WARNING, "The connection was found to be dropped - will retry {0} of {1}", retriesLeft, connection.ConnectionRetries);
/** lose the old connection and obtain a new one */
if (connection != null)
{
_connectionPool.DropConnection(connection);
}
if ((result = GetConnection(out connection)).ResultCode == DBResult.Result.Success)
{
#if INCLUDE_PERFORMANCE_LOGGING
if (m_bPerformanceLoggingEnabled)
{
connection.UpdateTimer.Start(sql.ToString());
}
#endif
result = Update(connection, sql, parameterDictionary);
#if INCLUDE_PERFORMANCE_LOGGING
if (m_bPerformanceLoggingEnabled)
{
connection.UpdateTimer.Stop();
}
#endif
}
else
{
connection = null;
}
if (connection != null && connection.ConnectionRetryInterval != TimeSpan.Zero)
{
Thread.Sleep(connection.ConnectionRetryInterval);
}
} while (result.ResultCode == DBResult.Result.ConnectionDropped && --retriesLeft > 0);
}
/** connection will be null in the case that we couldn't get one on a retry */
if (connection != null)
{
ReleaseConnection(connection);
}
LastResult = result;
return(result);
}
public virtual DBResult Query(QueryString sql, OdbcParameterList parms, out DatabaseDataReader reader, int timeout = 30)
{
reader = null;
DBResult ret;
/** we must be able to get a connection */
DatabaseConnection connection;
if ((ret = GetConnection(out connection)).ResultCode == DBResult.Result.Success)
{
#if INCLUDE_PERFORMANCE_LOGGING
if (m_bPerformanceLoggingEnabled)
{
connection.SelectTimer.Start(sql.ToString());
}
#endif
/** execute the qurey */
ret = Query(connection, sql, parms, out reader, timeout);
#if INCLUDE_PERFORMANCE_LOGGING
if (m_bPerformanceLoggingEnabled)
{
connection.SelectTimer.Stop();
}
#endif
if (ret.ResultCode == DBResult.Result.ConnectionDropped)
{
/** If the connection was lost during the query, we will perform retry logic */
int retriesLeft = connection.ConnectionRetries;
do
{
Log.LogText(LogLevel.WARNING, "The connection was found to be dropped - will retry {0} of {1}", retriesLeft, connection.ConnectionRetries);
if (connection != null)
{
_connectionPool.DropConnection(connection);
}
if ((ret = GetConnection(out connection)).ResultCode == DBResult.Result.Success)
{
#if INCLUDE_PERFORMANCE_LOGGING
if (m_bPerformanceLoggingEnabled)
{
connection.SelectTimer.Start(sql.ToString());
}
#endif
ret = Query(connection, sql, out reader);
#if INCLUDE_PERFORMANCE_LOGGING
if (m_bPerformanceLoggingEnabled)
{
connection.SelectTimer.Stop();
}
#endif
}
else
{
/** no connection could be obtained */
connection = null;
}
if (connection != null && connection.ConnectionRetryInterval != TimeSpan.Zero)
{
Thread.Sleep(connection.ConnectionRetryInterval);
}
} while (ret.ResultCode == DBResult.Result.ConnectionDropped && --retriesLeft > 0);
if (retriesLeft == 0)
{
connection = null;
}
}
/** if we successfully got a connection, clean up */
if (connection != null)
{
/** if we have something to return, create a result set */
if (ret.ResultCode == DBResult.Result.Success)
{
reader.ConnectionPool = _connectionPool;
reader.Connection = connection;
}
else
{
ReleaseConnection(connection);
reader = new DatabaseDataReader();
}
}
}
LastResult = ret;
return(ret);
}