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);
}
private static T ReadValue <T>(DatabaseDataReader reader, string columnName, Func <object, T> callback)
{
try
{
int ordinal = reader.GetOrdinal(columnName);
if (!Convert.IsDBNull(reader[ordinal]))
{
return(callback(reader[ordinal]));
}
}
catch (IndexOutOfRangeException rangeEx)
{
Log.SysLogText(LogLevel.ERROR, "DBUtil: column=" + columnName + " is not a valid column name in the returned results.");
Log.SysLogText(LogLevel.ERROR, rangeEx.Message);
Log.SysLogText(LogLevel.ERROR, rangeEx.StackTrace);
}
catch (Exception ex)
{
Log.SysLogText(LogLevel.ERROR, ex.Message);
Log.SysLogText(LogLevel.ERROR, ex.StackTrace);
}
return(default(T));
}
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 override DBResult Query(DatabaseConnection conn, QueryString sql, out DatabaseDataReader reader, int timeout = 30)
{
return(Query(conn, sql, null, out reader, timeout));
}
public abstract DBResult Query(DatabaseConnection conn, QueryString sql, OdbcParameterList parms, out DatabaseDataReader reader, int timeout = 30);
public abstract DBResult Query(DatabaseConnection conn, QueryString sql, out DatabaseDataReader reader, int timeout = 30);
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);
}
public virtual DBResult Query(QueryString sql, out DatabaseDataReader reader, int timeout = 30)
{
return(Query(sql, null, out reader, timeout));
}
public static Int32 GetInt32(DatabaseDataReader reader, string columnName)
{
return(ReadValue <int>(reader, columnName, obj => Convert.ToInt32(obj)));
}
public static decimal GetDecimal(DatabaseDataReader reader, string columnName)
{
return(ReadValue <decimal>(reader, columnName, obj => Convert.ToDecimal(obj)));
}