/// <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();
}
}