/// <summary> /// execute the SQL SELECT statement and return the data set. /// Fills the resulting data set and creates a DataTable named "Table". /// </summary> /// <param name="statement">Sql statement</param> /// <returns>Dataset containing the result of sql statement</returns> /// <exception cref="System.Exception">On error system.Exception is thrown</exception> public DataSet select(string statement) { statement = statement.ToLower(); if ((statement.IndexOf("select") == -1) && (statement.IndexOf("exec") == -1)) { throw new Error("Wrong SQL query"); } DataSet result = new DataSet(); TRANSACTION_STATE tx_state = TRANSACTION_STATE.COMMIT; try { this.open_connection(true, OPERATION_KIND.USING_DATA); // in transactional mode OleDbCommand sqlcmd = this.select_command(statement); this.adapter_.SelectCommand = sqlcmd; // fills the data set and creates a DataTable named "Table". this.adapter_.Fill(result); } catch (System.Exception se) // anything else { se.Source = "AdoNet.select()"; tx_state = TRANSACTION_STATE.ROLLBACK; throw new Error("select()", se); } finally { this.close_connection(tx_state); } return(result); }
//-------------------------------------------------------------- // Execute any non-query through here... /// <summary> /// Executes a SQL statement against the Connection and returns the number of rows affected. /// </summary> /// <param name="statement">sql statement</param> /// <returns>Number of rows affected</returns> /// <exception cref=" System.Exception">on error throws System.Exception</exception> int ExecuteNonQuery(string statement) { TRANSACTION_STATE tx_state = TRANSACTION_STATE.COMMIT; System.Exception ex = null; int result = 0; try { if (this.connection_.State == ConnectionState.Open) { this.close_connection(TRANSACTION_STATE.COMMIT); } this.open_connection(true, OPERATION_KIND.CHANGING_DATA); OleDbCommand sqlcmd = new OleDbCommand(statement, this.connection_); sqlcmd.Transaction = this.transaction_; result = sqlcmd.ExecuteNonQuery(); } /* * catch ( OleDbException sex ) * { * return sex.Number ; * } */ catch (System.Exception se) { tx_state = TRANSACTION_STATE.ROLLBACK; if (se != null) { ex = se; } } finally { this.close_connection(tx_state); if (ex != null) { ex.Source = "AdoNet.executeNonQuery()"; throw ex; } } return(result); }
/// <summary> /// close the current connection, taking care of the possible transaction /// and its outcome /// </summary> /// <param name="TxSTATE">commit or rollback, enum value</param> private void close_connection(TRANSACTION_STATE TxSTATE) { if (this.connection_.State != ConnectionState.Closed) { if (this.transaction_ != null) { if (this.transaction_.Connection != null) { if (TxSTATE == AdoNet.TRANSACTION_STATE.COMMIT) { this.transaction_.Commit(); } else { this.transaction_.Rollback(); } this.transaction_ = null; } } this.connection_.Close(); } }