/// <summary>
/// Controla la existencia de una entidad en base a su Id y nombre de Tabla.
/// </summary>
/// <param name="entityId">El valor del campo clave.</param>
/// <param name="tableName">El nombre de la tabla.</param>
/// <param name="fieldName">El nombre del campo.</param>
/// <param name="connection">La conexión a la base de datos.</param>
/// <param name="transaction">La transacción, si existe.</param>
/// <returns>True si tiene éxito.</returns>
public static bool ExistsEntity(int entityId, string tableName, string fieldName, IDbConnection connection, IDbTransaction transaction)
{
bool result = false;
DataAccessConnection dataAccess = DataAccessConnection.Instance;
string cmdText = "SELECT " + fieldName + " FROM [" + tableName + "] WHERE " + fieldName + " = @identity";
IDbCommand command = dataAccess.GetNewCommand(cmdText, connection);
command.Transaction = transaction;
IDbDataParameter param = dataAccess.GetNewDataParameter();
param.ParameterName = "@identity";
param.DbType = DbType.Int32;
param.Value = entityId;
command.Parameters.Add(param);
IDataReader dataReader = command.ExecuteReader();
if (dataReader.Read())
{
result = true;
}
dataReader.Close();
return(result);
}
/// <summary>
/// Controla que el procedimiento exista en la base actual.
/// </summary>
/// <param name="procedureName">Nombre del procedimiento.</param>
/// <returns>True si tiene éxito.</returns>
public static bool DBCheckedStoredProcedure(string procedureName)
{
bool result = false;
// Conectarse a la base de datos
DataAccessConnection dataAccess = DataAccessConnection.Instance;
IDbConnection dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
// Construir el comando para controlar la existencia del procedimiento
string cmdText = "SELECT name FROM sysObjects WHERE name = @procedureName AND type = 'P'";
IDbCommand command = dataAccess.GetNewCommand(cmdText, dbConnection);
IDbDataParameter param = dataAccess.GetNewDataParameter();
param.ParameterName = "@procedureName";
param.DbType = DbType.AnsiString;
param.Size = procedureName.Length;
param.Value = procedureName;
command.Parameters.Add(param);
// Controlar la existencia de la tabla y cerrar la conexión
IDataReader reader = command.ExecuteReader();
if (reader.Read())
{
result = true;
}
reader.Close();
dbConnection.Close();
return(result);
}
/// <summary>
/// Retorna el proximo Id para una tabla.
/// </summary>
/// <param name="nameId">Nombre del campo clave de la tabla.</param>
/// <param name="tableName">Nombre de la tabla.</param>
/// <param name="connection">La conexión con la base de datos.</param>
/// <param name="transaction">La transacción a usar.</param>
/// <returns>Un entero indicando el próximo Id a usar.</returns>
public static int GetNextId(string nameId, string tableName, IDbConnection connection, IDbTransaction transaction)
{
DataAccessConnection dataAccess = DataAccessConnection.Instance;
string sqlCommand = "NextID" + tableName;
IDbCommand command = dataAccess.GetNewCommand(sqlCommand, connection, transaction);
command.CommandType = CommandType.StoredProcedure;
IDbDataParameter parameterId = dataAccess.GetNewDataParameter("@" + nameId, DbType.Int32);
parameterId.Direction = ParameterDirection.Output;
command.Parameters.Add(parameterId);
command.ExecuteScalar();
int nextId = (int)parameterId.Value;
return(nextId);
}
/// <summary>
/// Function to load a CampaignEntity from database.
/// </summary>
/// <param name="id">The ID of the record to load</param>
/// <param name="loadRelation">if is true load the relation</param>
/// <param name="scope">Internal structure used to avoid circular reference locks, must be provided if calling from other data access object</param>
/// <returns>The entity instance</returns>
/// <exception cref="UtnEmallDataAccessException">
/// If a DbException occurs while accessing the database.
/// </exception>
public CampaignEntity Load(int id, bool loadRelation, Dictionary <string, IEntity> scope)
{
// Build a key for internal scope object
string scopeKey = id.ToString(NumberFormatInfo.InvariantInfo) + "Campaign";
if (scope != null)
{
// If scope contains the object it was already loaded,
// return it to avoid circular references
if (scope.ContainsKey(scopeKey))
{
return((CampaignEntity)scope[scopeKey]);
}
}
else
{
// If there isn't a current scope create one
scope = new Dictionary <string, IEntity>();
}
CampaignEntity campaign = null;
// Check if the entity was already loaded by current data access object
// and return it if that is the case
if (inMemoryEntities.ContainsKey(id))
{
campaign = inMemoryEntities[id];
// Add current object to current load scope
scope.Add(scopeKey, campaign);
}
else
{
bool closeConnection = false;
try
{
// Open a new connection if it isn't on a transaction
if (dbConnection == null || dbConnection.State.CompareTo(ConnectionState.Closed) == 0)
{
closeConnection = true;
dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
}
string cmdText = "SELECT idCampaign, description, name, startDate, stopDate, idUser, timestamp FROM [Campaign] WHERE idCampaign = @idCampaign";
// Create the command
IDbCommand sqlCommand = dataAccess.GetNewCommand(cmdText, dbConnection, dbTransaction);
// Create the Id parameter for the query
IDbDataParameter parameter = dataAccess.GetNewDataParameter("@idCampaign", DbType.Int32);
parameter.Value = id;
sqlCommand.Parameters.Add(parameter);
// Use a DataReader to get data from db
IDataReader reader = sqlCommand.ExecuteReader();
campaign = new CampaignEntity();
if (reader.Read())
{
// Load fields of entity
campaign.Id = reader.GetInt32(0);
if (!reader.IsDBNull(1))
{
campaign.Description = reader.GetString(1);
}
if (!reader.IsDBNull(2))
{
campaign.Name = reader.GetString(2);
}
campaign.StartDate = reader.GetDateTime(3);
campaign.StopDate = reader.GetDateTime(4);
campaign.IdUser = reader.GetInt32(5);
// Add current object to the scope
scope.Add(scopeKey, campaign);
// Add current object to cache of loaded entities
inMemoryEntities.Add(campaign.Id, campaign);
// Read the timestamp and set new and changed properties
campaign.Timestamp = reader.GetDateTime(6);
campaign.IsNew = false;
campaign.Changed = false;
// Close the reader
reader.Close();
// Load related objects if required
if (loadRelation)
{
LoadRelationUser(campaign, scope);
}
}
else
{
reader.Close();
}
}
catch (DbException dbException)
{
// Catch DBException and rethrow as custom exception
throw new UtnEmallDataAccessException(dbException.Message, dbException);
}
finally
{
// Close connection if it was opened by ourself
if (closeConnection)
{
dbConnection.Close();
}
}
}
// Return the loaded entity
return(campaign);
}
/// <summary>
/// Function to load a StoreEntity from database.
/// </summary>
/// <param name="id">The ID of the record to load</param>
/// <param name="loadRelation">if is true load the relation</param>
/// <param name="scope">Internal structure used to avoid circular reference locks, must be provided if calling from other data access object</param>
/// <returns>The entity instance</returns>
/// <exception cref="UtnEmallDataAccessException">
/// If a DbException occurs while accessing the database.
/// </exception>
public StoreEntity Load(int id, bool loadRelation, Dictionary <string, IEntity> scope)
{
// Build a key for internal scope object
string scopeKey = id.ToString(NumberFormatInfo.InvariantInfo) + "Store";
if (scope != null)
{
// If scope contains the object it was already loaded,
// return it to avoid circular references
if (scope.ContainsKey(scopeKey))
{
return((StoreEntity)scope[scopeKey]);
}
}
else
{
// If there isn't a current scope create one
scope = new Dictionary <string, IEntity>();
}
StoreEntity store = null;
// Check if the entity was already loaded by current data access object
// and return it if that is the case
if (inMemoryEntities.ContainsKey(id))
{
store = inMemoryEntities[id];
// Add current object to current load scope
scope.Add(scopeKey, store);
}
else
{
bool closeConnection = false;
try
{
// Open a new connection if it isn't on a transaction
if (dbConnection == null || dbConnection.State.CompareTo(ConnectionState.Closed) == 0)
{
closeConnection = true;
dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
}
string cmdText = "SELECT idStore, name, telephoneNumber, internalPhoneNumber, contactName, ownerName, email, webAddress, localNumber, idMall, idDataModel, timestamp FROM [Store] WHERE idStore = @idStore";
// Create the command
IDbCommand sqlCommand = dataAccess.GetNewCommand(cmdText, dbConnection, dbTransaction);
// Create the Id parameter for the query
IDbDataParameter parameter = dataAccess.GetNewDataParameter("@idStore", DbType.Int32);
parameter.Value = id;
sqlCommand.Parameters.Add(parameter);
// Use a DataReader to get data from db
IDataReader reader = sqlCommand.ExecuteReader();
store = new StoreEntity();
if (reader.Read())
{
// Load fields of entity
store.Id = reader.GetInt32(0);
if (!reader.IsDBNull(1))
{
store.Name = reader.GetString(1);
}
if (!reader.IsDBNull(2))
{
store.TelephoneNumber = reader.GetString(2);
}
if (!reader.IsDBNull(3))
{
store.InternalPhoneNumber = reader.GetString(3);
}
if (!reader.IsDBNull(4))
{
store.ContactName = reader.GetString(4);
}
if (!reader.IsDBNull(5))
{
store.OwnerName = reader.GetString(5);
}
if (!reader.IsDBNull(6))
{
store.Email = reader.GetString(6);
}
if (!reader.IsDBNull(7))
{
store.WebAddress = reader.GetString(7);
}
if (!reader.IsDBNull(8))
{
store.LocalNumber = reader.GetString(8);
}
store.IdMall = reader.GetInt32(9);
store.IdDataModel = reader.GetInt32(10);
// Add current object to the scope
scope.Add(scopeKey, store);
// Add current object to cache of loaded entities
inMemoryEntities.Add(store.Id, store);
// Read the timestamp and set new and changed properties
store.Timestamp = reader.GetDateTime(11);
store.IsNew = false;
store.Changed = false;
// Close the reader
reader.Close();
// Load related objects if required
if (loadRelation)
{
LoadRelationStoreCategory(store, scope);
}
}
else
{
reader.Close();
}
}
catch (DbException dbException)
{
// Catch DBException and rethrow as custom exception
throw new UtnEmallDataAccessException(dbException.Message, dbException);
}
finally
{
// Close connection if it was opened by ourself
if (closeConnection)
{
dbConnection.Close();
}
}
}
// Return the loaded entity
return(store);
}