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 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 DBResult Delete(QueryString sql, OdbcParameterList parameterDictionary) { if (ReadOnly) { throw new DataSourceException("{0} is read-only", this); } DBResult ret = Update(sql, parameterDictionary); if (ret.ResultCode == DBResult.Result.UpdateFailure) { ret.ResultCode = DBResult.Result.DeleteFailure; } return(ret); }
public virtual DBResult Insert(DatabaseConnection conn, QueryString sql, OdbcParameterList parameterDictionary) { if (ReadOnly) { throw new DataSourceException("{0} is read-only", this); } DBResult ret = Update(conn, sql, parameterDictionary); if (ret.ResultCode == DBResult.Result.Success) { ret.ItemID = (UInt32)GetLastInsertID(conn); } else if (ret.ResultCode == DBResult.Result.UpdateFailure) { ret.ResultCode = DBResult.Result.InsertionFailure; } return(ret); }
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 abstract DBResult Update(DatabaseConnection conn, QueryString sql, OdbcParameterList parms);
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 abstract DBResult Query(DatabaseConnection conn, QueryString sql, OdbcParameterList parms, 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); }