/// <summary>
/// Función para cargar un CategoryEntity desde la base de datos
/// </summary>
/// <param name="propertyName">Un string con el nombre del campo o una constante de la clase que representa ese campo</param>
/// <param name="expValue">El valor que será insertado en la clausula where</param>
/// <param name="loadRelation">Si es true carga la relacion</param>
/// <returns>Una lista que contiene todas las entidades que concuerdan con la clausula where</returns>
/// <exception cref="ArgumentNullException">
/// Si <paramref name="propertyName"/> es null or vacio.
/// Si <paramref name="propertyName"/> no es una propiedad de la clase CategoryEntity.
/// Si <paramref name="expValue"/> es null.
/// </exception>
/// <exception cref="UtnEmallDataAccessException">
/// Si una DbException ocurre cuando se accede a la base de datos
/// </exception>
public Collection <CategoryEntity> LoadWhere(string propertyName, object expValue, bool loadRelation, OperatorType operatorType)
{
if (String.IsNullOrEmpty(propertyName) || expValue == null)
{
throw new ArgumentException("The argument can not be null or be empty", "propertyName");
}
if (!properties.ContainsKey(propertyName))
{
throw new ArgumentException("The property " + propertyName + " is not a property of this entity", "propertyName");
}
Collection <CategoryEntity> categoryList;
bool closeConnection = false;
try
{
// Abrir una nueva conexión con la base de datos si es necesario
if (dbConnection == null || dbConnection.State.CompareTo(ConnectionState.Closed) == 0)
{
closeConnection = true;
dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
}
string op = DataAccessConnection.GetOperatorString(operatorType);
// Construir la consulta
string cmdText = "SELECT idCategory, description, name, idParentCategory, timestamp FROM [Category] WHERE " + propertyName + " " + op + " @expValue";
// Crea el command
SqlCeCommand sqlCommand = dataAccess.GetNewCommand(cmdText, dbConnection, dbTransaction);
// Agrega los parametros al command
SqlCeParameter parameter = dataAccess.GetNewDataParameter();
parameter.ParameterName = "@expValue";
Type parameterType = properties[propertyName];
parameter.DbType = DataAccessConnection.GetParameterDBType(parameterType);
parameter.Value = expValue;
sqlCommand.Parameters.Add(parameter);
// Crea un datareader
IDataReader reader = sqlCommand.ExecuteReader();
categoryList = new Collection <CategoryEntity>();
CategoryEntity category;
List <int> listId = new List <int>();
// Agrega los id a una lista de ids
while (reader.Read())
{
listId.Add(reader.GetInt32(0));
}
// Cerrar el Reader
reader.Close();
// Carga las entidades
foreach (int id in listId)
{
category = Load(id, loadRelation, null);
categoryList.Add(category);
}
}
catch (DbException dbException)
{
// Relanza la excepcion como una excepcion personalizada
throw new UtnEmallDataAccessException(dbException.Message, dbException);
}
finally
{
// Cierra la conexión si fue abierta dentro de la Función
if (closeConnection)
{
dbConnection.Close();
}
}
return(categoryList);
}
/// <summary>
/// Function to Load a CustomerEntity from database.
/// </summary>
/// <param name="propertyName">A string with the name of the field or a
/// constant from the class that represent that field</param>
/// <param name="expValue">The value that will be inserted on the where
/// clause of the sql query</param>
/// <param name="loadRelation">If is true load the relations</param>
/// <returns>A list containing all the entities that match the where clause</returns>
/// <exception cref="ArgumentNullException">
/// If <paramref name="propertyName"/> is null or empty.
/// If <paramref name="propertyName"/> is not a property of CustomerEntity class.
/// If <paramref name="expValue"/> is null.
/// </exception>
/// <exception cref="UtnEmallDataAccessException">
/// If an DbException occurs in the try block while accessing the database.
/// </exception>
public Collection <CustomerEntity> LoadWhere(string propertyName, object expValue, bool loadRelation, OperatorType operatorType)
{
if (String.IsNullOrEmpty(propertyName) || expValue == null)
{
throw new ArgumentException("The argument can not be null or be empty", "propertyName");
}
if (!properties.ContainsKey(propertyName))
{
throw new ArgumentException("The property " + propertyName + " is not a property of this entity", "propertyName");
}
Collection <CustomerEntity> customerList;
bool closeConnection = false;
try
{
// Open a new connection with a database if necessary
if (dbConnection == null || dbConnection.State.CompareTo(ConnectionState.Closed) == 0)
{
closeConnection = true;
dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
}
string op = DataAccessConnection.GetOperatorString(operatorType);
// Build the query string
string cmdText = "SELECT idCustomer, name, surname, address, phoneNumber, userName, password, birthday, howManyChildren, gender, civilState, timestamp FROM [Customer] WHERE " + propertyName + " " + op + " @expValue";
// Create the command
SqlCeCommand sqlCommand = dataAccess.GetNewCommand(cmdText, dbConnection, dbTransaction);
// Add parameters values to the command
SqlCeParameter parameter = dataAccess.GetNewDataParameter();
parameter.ParameterName = "@expValue";
Type parameterType = properties[propertyName];
parameter.DbType = DataAccessConnection.GetParameterDBType(parameterType);
parameter.Value = expValue;
sqlCommand.Parameters.Add(parameter);
// Create a DataReader
IDataReader reader = sqlCommand.ExecuteReader();
customerList = new Collection <CustomerEntity>();
CustomerEntity customer;
List <int> listId = new List <int>();
// Add list of Ids to a list
while (reader.Read())
{
listId.Add(reader.GetInt32(0));
}
// Close the reader
reader.Close();
// Load the entities
foreach (int id in listId)
{
customer = Load(id, loadRelation, null);
customerList.Add(customer);
}
}
catch (DbException dbException)
{
// Catch DbException and rethrow as custom exception
throw new UtnEmallDataAccessException(dbException.Message, dbException);
}
finally
{
// Close connection if it was opened by myself
if (closeConnection)
{
dbConnection.Close();
}
}
return(customerList);
}