/// <summary>
/// Executes a query to the database using the provided parameters and returns the result as a DataRow.
/// </summary>
public DataRow ExecuteDataRow(string commandText, CommandType commandType, CommandParameterCollection parameters)
{
DataRow row = null;
DataSet set = ExecuteDataSet(commandText, commandType, parameters);
if (set != null && set.Tables.Count > 0)
{
DataTable table = set.Tables[0];
if (table.Rows.Count > 0)
{
row = table.Rows[0];
}
}
return(row);
}
/// <summary>
/// Utility method for processing output parameters.
/// </summary>
private static void ProcessOutputParameters(DbCommand command, CommandParameterCollection commandParameters)
{
if (commandParameters != null && command.Parameters.Count > 0)
{
var outputValues = new Dictionary <string, object>();
for (int i = 0; i < command.Parameters.Count; i++)
{
DbParameter parameter = command.Parameters[i];
if (parameter.Direction != ParameterDirection.Input)
{
outputValues.Add(parameter.ParameterName, parameter.Value);
}
}
commandParameters.OutputValues = outputValues;
}
}
/// <summary>
/// Executes a query to the database using the provided parameters and returns the result as a DataSet.
/// </summary>
public DataSet ExecuteDataSet(string commandText, CommandType commandType, CommandParameterCollection parameters)
{
DataSet set = null;
DbCommand command = null;
DbDataAdapter adapter = null;
try
{
CheckConnection();
command = SetupDbCommand(this, this.DatabaseContext, this._connection, this._transaction, commandText, commandType, parameters);
adapter = this.DatabaseContext.Provider.CreateDataAdapter();
adapter.SelectCommand = command;
set = new DataSet();
adapter.Fill(set);
//CloseConnection();
ProcessOutputParameters(command, parameters);
}
catch (Exception ex)
{
if (set != null)
{
set.Dispose();
}
HandleException(ex);
throw;
}
finally
{
DisposeUtility.Dispose(adapter, command);
}
return(set);
}
public static bool ExecuteDelete(QueryContext query, TEntity entity, string procedureName)
{
if (query == null)
{
throw new ArgumentNullException("query cannot be null.");
}
if (entity == null)
{
throw new ArgumentNullException("Entity(" + typeof(TEntity).Name + ") cannot be null.");
}
if (string.IsNullOrWhiteSpace(procedureName))
{
throw new ArgumentException("procedureName cannot be null, empty or whitespace.");
}
CommandParameterCollection parameters = new CommandParameterCollection();
entity.InternalDelete(parameters);
return(ExecuteQueryDeleteModel(query, entity, procedureName, parameters));
}
/// <summary>
/// Saves changed to the Database for the provided entity.
/// </summary>
public static ErrorCollection ExecuteCreate(QueryContext query, TEntity entity, string procedureName)
{
if (query == null)
{
throw new ArgumentNullException("query cannot be null.");
}
if (entity == null)
{
throw new ArgumentNullException("Entity(" + typeof(TEntity).Name + ") cannot be null.");
}
if (string.IsNullOrWhiteSpace(procedureName))
{
throw new ArgumentException("procedureName cannot be null, empty or whitespace.");
}
if (!entity.IsNew)
{
throw new ArgumentException("Entity must be new.");
}
ErrorCollection errors = new ErrorCollection();
entity.InternalValidate(errors);
if (errors.Count == 0)
{
SaveType saveType = SaveType.Create;
CommandParameterCollection parameters = new CommandParameterCollection();
entity.InternalSave(query.DatabaseContext, parameters, saveType);
ExecuteQueryUpdateModel(query, entity, procedureName, parameters);
}
return(errors);
}
/// <summary>
/// Executes a query to the database using the provided parameters and returns the result as collection of Entity objects.
/// </summary>
public IList <TEntity> ExecuteList <TEntity>(string commandText, CommandType commandType, CommandParameterCollection parameters) where TEntity : Entity <TEntity>, new()
{
IList <TEntity> entities;
DbDataReader reader = null;
//HACK: Issue with Npgsql DbReader implementation that if no results are returned
//it'll still return true when invoking reader.Read() which you would have thought
//it'll return false...
try
{
reader = ExecuteReader(commandText, commandType, parameters);
entities = EntityFactory <TEntity> .CreateListFromReader(reader);
}
catch (Exception ex)
{
entities = new List <TEntity>();
HandleException(ex);
}
finally
{
DisposeUtility.Dispose(reader);
}
return(entities);
}
public IDictionary <TKey, TValue> ExecuteDictionary <TKey, TValue>(string commandText, CommandType commandType, CommandParameterCollection parameters)
{
IDictionary <TKey, TValue> results = new Dictionary <TKey, TValue>();
DbDataReader reader = null;
//HACK: Issue with Npgsql DbReader implementation that if no results are returned
//it'll still return true when invoking reader.Read() which you would have thought
//it'd return false...
try
{
reader = ExecuteReader(commandText, commandType, parameters);
while (reader.Read())
{
results.Add(reader.GetValue <TKey>(0), reader.GetValue <TValue>(1));
}
}
catch (Exception ex)
{
HandleException(ex);
}
finally
{
DisposeUtility.Dispose(reader);
}
return(results);
}
/// <summary>
/// Executes a query to the database using the provided parameters and returns the result as a typed object.
/// </summary>
public TObject ExecuteScalar <TObject>(string commandText, CommandType commandType, CommandParameterCollection parameters)
{
TObject outcome;
object value = ExecuteScalar(commandText, commandType, parameters);
if (value != DBNull.Value)
{
//outcome = (TObject)value;
outcome = TryCast.AsGeneric <TObject>(value, null, default(TObject));
}
else
{
outcome = default(TObject);
}
return(outcome);
}
/// <summary>
/// Saves changes to the Database for the provided entity using the given parameters.
/// </summary>
public static ErrorCollection ExecuteCreate(QueryContext query, TEntity entity, string procedureName, CommandParameterCollection parameters)
{
if (query == null)
{
throw new ArgumentNullException("query");
}
if (entity == null)
{
throw new ArgumentNullException("entity");
}
if (string.IsNullOrWhiteSpace(procedureName))
{
throw new ArgumentException("procedureName");
}
if (parameters == null || parameters.Count == 0)
{
throw new ArgumentException("At least 1 parameter must be specified.");
}
if (!entity.IsNew)
{
throw new ArgumentException("Entity must be new.");
}
ErrorCollection errors = new ErrorCollection();
entity.InternalValidate(errors);
if (errors.Count == 0)
{
ExecuteQueryUpdateModel(query, entity, procedureName, parameters);
}
return(errors);
}
/// <summary>
/// Executes a query to the database using the provided parameters and returns the result as a DataTable.
/// </summary>
public DataTable ExecuteDataTable(string commandText, CommandType commandType, CommandParameterCollection parameters)
{
DataTable table = null;
DataSet set = ExecuteDataSet(commandText, commandType, parameters);
if (set != null && set.Tables.Count > 0)
{
table = set.Tables[0];
}
return(table);
}
public static IList <TEntity> ExecuteList(DatabaseContext context, string procedureName, CommandParameterCollection parameters)
{
return(BaseEntityLayer <TEntity> .ExecuteList(context, procedureName, parameters));
}
private static void ExecuteQueryUpdateModel(QueryContext query, TEntity entity, string procedureName, CommandParameterCollection parameters)
{
if (query == null)
{
throw new ArgumentNullException("query");
}
if (parameters == null)
{
throw new ArgumentNullException("parameters");
}
if (parameters.Count == 0)
{
throw new InvalidOperationException("Unable to execute '" + procedureName + "' on Entity(" + typeof(TEntity).Name + ") if no parameters are provided...");
}
using (DbDataReader reader = query.ExecuteReader(procedureName, CommandType.StoredProcedure, parameters))
{
if (reader.Read())
{
entity.InternalLoad(reader);
}
}
}
internal static TEntity ExecuteSingle(QueryContext query, CommandParameterCollection parameters)
{
return(ExecuteSingle(query, parameters, typeof(TEntity).Name + "_Find"));
}
internal virtual void InternalSave(DatabaseContext context, CommandParameterCollection parameters, SaveType saveType)
{
OnSaving(context, parameters, saveType);
}
protected virtual void OnDeleting(CommandParameterCollection parameters)
{
//Do nothing.
}
protected abstract void OnSaving(DatabaseContext context, CommandParameterCollection parameters, SaveType saveType);
private static bool ExecuteQueryDeleteModel(QueryContext query, TEntity entity, string procedureName, CommandParameterCollection parameters)
{
if (query == null)
{
throw new ArgumentNullException("query");
}
if (parameters == null)
{
throw new ArgumentNullException("parameters");
}
if (parameters.Count == 0)
{
throw new InvalidOperationException("Unable to execute '" + procedureName + "' on Entity(" + typeof(TEntity).Name + ") if no parameters are provided...");
}
return(query.ExecuteScalar <bool>(procedureName, CommandType.StoredProcedure, parameters));
}
/// <summary>
/// Utility method for creating a DbCommand instance.
/// </summary>
private static DbCommand SetupDbCommand(QueryContext query, DatabaseContext context, DbConnection connection, DbTransaction transaction, string commandText, CommandType commandType, CommandParameterCollection parameters)
{
DbCommand command = context.Provider.CreateCommand(commandText, commandType, connection);
command.CommandTimeout = query.CommandTimeout;
command.Transaction = transaction;
if (parameters != null)
{
if (parameters.Count > 0)
{
command.Parameters.AddRange(parameters.ToDbParameters(context));
}
else
{
throw new InvalidOperationException("A CommandParameterCollection instance was passed in but contained no values, provide a NULL reference instead or provide a collection with at least one value.");
}
}
return(command);
}
internal virtual void InternalDelete(CommandParameterCollection parameters)
{
OnDeleting(parameters);
}
internal static IList <TEntity> ExecuteList(DatabaseContext context, CommandParameterCollection parameters)
{
return(ExecuteList(context, typeof(TEntity).Name + "_List", parameters));
}
internal static TEntity ExecuteSingle(QueryContext query, CommandParameterCollection parameters, string procedureName)
{
TEntity entity = query.ExecuteSingle <TEntity>(procedureName, CommandType.StoredProcedure, parameters);
return(entity);
}
/// <summary>
/// Executes a query to the database using the provided parameters and returns the result as a DbDataReader.
/// </summary>
public DbDataReader ExecuteReader(string commandText, CommandType commandType, CommandParameterCollection parameters)
{
DbDataReader reader = null;
DbCommand command = null;
try
{
CheckConnection();
command = SetupDbCommand(this, this.DatabaseContext, this._connection, this._transaction, commandText, commandType, parameters);
//if(this._transaction != null)
//{
// reader = command.ExecuteReader();
//}
//else
//{
// reader = command.ExecuteReader(CommandBehavior.CloseConnection);
//}
reader = command.ExecuteReader();
ProcessOutputParameters(command, parameters);
}
catch (Exception ex)
{
HandleException(ex);
throw;
}
finally
{
DisposeUtility.Dispose(command);
}
return(reader);
}
internal static IList <TEntity> ExecuteList(DatabaseContext context, string procedureName, CommandParameterCollection parameters)
{
IList <TEntity> entities;
using (QueryContext query = new QueryContext(context))
{
entities = query.ExecuteList <TEntity>(procedureName, CommandType.StoredProcedure, parameters);
}
return(entities);
}
public static TEntity ExecuteSingle(QueryContext query, CommandParameterCollection parameters, string procedureName)
{
return(BaseEntityLayer <TEntity> .ExecuteSingle(query, parameters, procedureName));
}
