/// <summary>
/// Executes a SQL Script and retrieves all data obteined
/// </summary>
/// <remarks>
/// You must explicity close the returned DataReader once it's not used,
/// otherwise the assigned connection would stay opened and cause
/// a bad use of resources
/// </remarks>
/// <param name="command">
/// Sql Sentence to exectute for retrieve data
/// </param>
/// <returns>
/// A System.Data.DbDataReader with all data obtained
/// </returns>
public override IDataReader GetDataReader(Command command)
{
//Local Vars
MySqlPCL.MySqlDataReader reader = null;
CommandEventArgs e = new CommandEventArgs(command);
lock (Locker)
{
try
{
//raising events
OnBeforeGetDataReader(e);
//running the script only if e.Cancel is false
if (!e.Cancel)
{
//Initializing command and connection
MySqlPCL.MySqlCommand dbCommand = new MySqlPCL.MySqlCommand();
dbCommand.Connection = Connection;
dbCommand.CommandText = command.Script;
dbCommand.Transaction = Transaction;
foreach (CommandParameter param in command.Parameters)
{
dbCommand.Parameters.Add(Parse(param));
}
OpenConnection();
//Validating if there is a current transaction
if (Transaction == null)
{
//Loading the data reader indicating that the close of the reader
//must close the connection too
reader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
else
{
//Loading the data reader
reader = dbCommand.ExecuteReader();
}
}
//raising events
e.Result = reader;
OnAfterGetDataReader(e);
}
catch (System.Exception ex)
{
//Closing the reader if apply
CloseConnection();
//Re - throw the excepción to the caller
throw new SqlException(command, "Error on creating DataReader (" + ex.Message + ")", ex);
}
}
//Returning the DataReader
return new DataReader(this, (MySqlPCL.MySqlDataReader) e.Result);
}
/// <summary>
/// Executes a SQL Script and retrieves all data obteined
/// </summary>
/// <param name="command">
/// Sql Sentence to execute for retrieve data
/// </param>
/// <returns>
/// A System.Data.DataTable with all data obtained
/// </returns>
public override IDataTable GetDataTable(Command command)
{
//Local Vars
System.Data.DataTable tableToReturn = null;
MySqlPCL.MySqlDataAdapter adapter;
lock (Locker)
{
try
{
//raising events
CommandEventArgs e = new CommandEventArgs(command);
OnBeforeGetDataTable(e);
//running the script only if e.Cancel is false
if (!e.Cancel)
{
//Initializing command and connection
MySqlPCL.MySqlCommand dbCommand = new MySqlPCL.MySqlCommand();
dbCommand.Connection = Connection;
dbCommand.CommandText = command.Script;
dbCommand.Transaction = Transaction;
foreach (CommandParameter param in command.Parameters)
{
dbCommand.Parameters.Add(Parse(param));
}
OpenConnection();
//Configuring the adapter
adapter = new MySqlPCL.MySqlDataAdapter();
adapter.SelectCommand = dbCommand;
//Loading the data
tableToReturn = new System.Data.DataTable();
adapter.Fill(tableToReturn);
}
//raising events
e.Result = tableToReturn;
OnAfterGetDataTable(e);
//Returning the data to the caller
return new DataTable(this, tableToReturn);
}
catch (System.Exception ex)
{
//Re - throwing the exception to the caller
throw new SqlException(command, "Error on creating DataTable (" + ex.Message + ")", ex);
}
finally
{
//Closing the connection
CloseConnection();
}
}
}
/// <summary>
/// Executes a scalar function SQL Script and retrieves a single value
/// </summary>
/// <remarks>
/// Usefull to execute aggregated functions that returns only one row and one value
/// </remarks>
/// <param name="command">
/// Sql Sentence to exectute for retrieve data
/// </param>
/// <returns>
/// Value returned in the first row and firstn collumn
/// </returns>
public override object GetScalar(Command command)
{
//Local Vars
object value = null;
CommandEventArgs e = new CommandEventArgs(command);
lock (Locker)
{
try
{
//raising events
OnBeforeGetDataReader(e);
//running the script only if e.Cancel is false
if (!e.Cancel)
{
//Initializing command and connection
MySqlPCL.MySqlCommand dbCommand = new MySqlPCL.MySqlCommand();
dbCommand.Connection = Connection;
dbCommand.CommandText = command.Script;
dbCommand.Transaction = Transaction;
foreach (CommandParameter param in command.Parameters)
{
dbCommand.Parameters.Add(Parse(param));
}
OpenConnection();
//Loading the data reader
value = dbCommand.ExecuteScalar();
}
//raising events
e.Result = value;
OnAfterGetDataReader(e);
}
catch (System.Exception ex)
{
//Closing the reader if apply
CloseConnection();
//Re - throw the excepción to the caller
throw new SqlException(command, "Error on creating DataReader (" + ex.Message + ")", ex);
}
finally
{
//Closing the connection
CloseConnection();
}
}
//Returning the DataReader
return e.Result;
}
/// <summary>
/// Executes a SQL Script that doesn't retun rows
/// </summary>
/// <param name="command">
/// A list of SQL scripts to be executed
/// </param>
/// <returns>
/// An int indicating the number of affected rows
/// </returns>
public override IEnumerable<int> Execute(IEnumerable<Command> commands)
{
//Local Vars
int rowsAffected = 0;
Command current = new Command();
//Validating that exists at least one script
if (commands == null) throw new ArgumentNullException("commands");
lock (Locker)
{
CommandEventArgs e = null;
try
{
//Initializing command and connection
MySqlPCL.MySqlCommand dbCommand = new MySqlPCL.MySqlCommand();
dbCommand.Connection = Connection;
dbCommand.Transaction = Transaction;
OpenConnection();
//Crossing the array of scripts
foreach (Command command in commands)
{
//raising events
e = new CommandEventArgs(command);
OnBeforeExecute(e);
current = command;
//Executing the code only if have authorization
if (!e.Cancel)
{
//Setting the command text
dbCommand.CommandText = command.Script;
foreach (CommandParameter param in command.Parameters)
{
dbCommand.Parameters.Add(Parse(param));
}
//Executing command and getting the number of affected rows
rowsAffected = dbCommand.ExecuteNonQuery();
}
//clear parameters
dbCommand.Parameters.Clear();
//calling events
e.Result = rowsAffected;
OnAfterExecute(e);
}
}
catch (System.Exception ex)
{
//Re throwing exception to the caller
throw new SqlException(current, "Error on executing command against the database (" + ex.Message + ")", ex);
}
finally
{
//Closing the connection
CloseConnection();
}
//Returning value
if (e != null)
{
yield return (int) e.Result;
}
}
}