/// <summary>
/// Initializes a new instance of a
/// <c>PreferenceDataAccess</c> type.
/// It checks if the table and stored procedure
/// are already on the database, if not, it creates
/// them.
/// Sets the properties that allows to make queries
/// by calling the LoadWhere method.
/// </summary>
public PreferenceDataAccess()
{
dataAccess = DataAccessConnection.Instance;
if (!dbChecked)
{
DbChecked();
}
if (properties == null)
{
SetProperties();
}
inMemoryEntities = new Dictionary <int, PreferenceEntity>();
}
/// <summary>
/// Initializes a new instance of a
/// <c>ServiceCategoryDataAccess</c> type.
/// It checks if the table and stored procedure
/// are already on the database, if not, it creates
/// them.
/// Sets the properties that allows to make queries
/// by calling the LoadWhere method.
/// </summary>
public ServiceCategoryDataAccess()
{
dataAccess = DataAccessConnection.Instance;
if (!dbChecked)
{
DbChecked();
}
if (properties == null)
{
SetProperties();
}
inMemoryEntities = new Dictionary <int, ServiceCategoryEntity>();
}
/// <summary>
/// Initializes a new instance of a
/// <c>RegisterAssociationCategoriesDataAccess</c> type.
/// It checks if the table and stored procedure
/// are already on the database, if not, it creates
/// them.
/// Sets the properties that allows to make queries
/// by calling the LoadWhere method.
/// </summary>
public RegisterAssociationCategoriesDataAccess()
{
dataAccess = DataAccessConnection.Instance;
if (!dbChecked)
{
DbChecked();
}
if (properties == null)
{
SetProperties();
}
inMemoryEntities = new Dictionary <int, RegisterAssociationCategoriesEntity>();
}
/// <summary>
/// Crea un procedimiento de eliminación para una tabla determinada
/// </summary>
/// <param name="tableName">Nombre de la tabla.</param>
/// <param name="idColumnName">Nombre de la columna clave.</param>
/// <returns>True si tiene éxito.</returns>
public static bool CreateDeleteStoredProcedure(string tableName, string idColumnName)
{
// Conectarse a la base de datos
DataAccessConnection dataAccess = DataAccessConnection.Instance;
IDbConnection dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
// Crear el comando para crear el procedimiento
string procedure = "CREATE PROCEDURE Delete" + tableName + "( @" + idColumnName + " int) AS DELETE FROM [" + tableName + "] WHERE " + idColumnName + " = @" + idColumnName;
IDbCommand command = dataAccess.GetNewCommand(procedure, dbConnection);
command.ExecuteNonQuery();
dbConnection.Close();
return(true);
}
/// <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 check and create table and stored procedures for this class.
/// </summary>
private static void DbChecked()
{
if (dbChecked)
{
return;
}
string[] fieldsName = new string[] { "idPreference", "active", "level", "idCustomer", "idCategory" };
Type[] fieldsType = new Type[] { typeof(int), typeof(bool), typeof(double), typeof(int), typeof(int) };
bool existsTable = DataAccessConnection.DBCheckedTable("Preference");
if (!existsTable)
{
DataAccessConnection.CreateTable("Preference", fieldsName, true, fieldsType);
}
bool existsProcedureDelete = DataAccessConnection.DBCheckedStoredProcedure("DeletePreference");
bool existsProcedureSave = DataAccessConnection.DBCheckedStoredProcedure("SavePreference");
bool existsProcedureUpdate = DataAccessConnection.DBCheckedStoredProcedure("UpdatePreference");
if (!existsProcedureDelete)
{
DataAccessConnection.CreateDeleteStoredProcedure("Preference", "idPreference");
}
if (!existsProcedureSave)
{
DataAccessConnection.CreateSaveStoredProcedure("Preference", fieldsName, fieldsType);
}
if (!existsProcedureUpdate)
{
DataAccessConnection.CreateUpdateStoredProcedure("Preference", "idPreference", fieldsName, fieldsType);
}
dbChecked = true;
}
/// <summary>
/// Function to check and create table and stored procedures for this class.
/// </summary>
private static void DbChecked()
{
if (dbChecked)
{
return;
}
string[] fieldsName = new string[] { "idStore", "name", "telephoneNumber", "internalPhoneNumber", "contactName", "ownerName", "email", "webAddress", "localNumber", "idMall", "idDataModel" };
Type[] fieldsType = new Type[] { typeof(int), typeof(string), typeof(string), typeof(string), typeof(string), typeof(string), typeof(string), typeof(string), typeof(string), typeof(int), typeof(int) };
bool existsTable = DataAccessConnection.DBCheckedTable("Store");
if (!existsTable)
{
DataAccessConnection.CreateTable("Store", fieldsName, true, fieldsType);
}
bool existsProcedureDelete = DataAccessConnection.DBCheckedStoredProcedure("DeleteStore");
bool existsProcedureSave = DataAccessConnection.DBCheckedStoredProcedure("SaveStore");
bool existsProcedureUpdate = DataAccessConnection.DBCheckedStoredProcedure("UpdateStore");
if (!existsProcedureDelete)
{
DataAccessConnection.CreateDeleteStoredProcedure("Store", "idStore");
}
if (!existsProcedureSave)
{
DataAccessConnection.CreateSaveStoredProcedure("Store", fieldsName, fieldsType);
}
if (!existsProcedureUpdate)
{
DataAccessConnection.CreateUpdateStoredProcedure("Store", "idStore", fieldsName, fieldsType);
}
dbChecked = true;
}
/// <summary>
/// Function to check and create table and stored procedures for this class.
/// </summary>
private static void DbChecked()
{
if (dbChecked)
{
return;
}
string[] fieldsName = new string[] { "idTable", "name", "isStorage", "idDataModel", "idComponent" };
Type[] fieldsType = new Type[] { typeof(int), typeof(string), typeof(bool), typeof(int), typeof(int) };
bool existsTable = DataAccessConnection.DBCheckedTable("Table");
if (!existsTable)
{
DataAccessConnection.CreateTable("Table", fieldsName, true, fieldsType);
}
bool existsProcedureDelete = DataAccessConnection.DBCheckedStoredProcedure("DeleteTable");
bool existsProcedureSave = DataAccessConnection.DBCheckedStoredProcedure("SaveTable");
bool existsProcedureUpdate = DataAccessConnection.DBCheckedStoredProcedure("UpdateTable");
if (!existsProcedureDelete)
{
DataAccessConnection.CreateDeleteStoredProcedure("Table", "idTable");
}
if (!existsProcedureSave)
{
DataAccessConnection.CreateSaveStoredProcedure("Table", fieldsName, fieldsType);
}
if (!existsProcedureUpdate)
{
DataAccessConnection.CreateUpdateStoredProcedure("Table", "idTable", fieldsName, fieldsType);
}
dbChecked = true;
}
/// <summary>
/// Function to check and create table and stored procedures for this class.
/// </summary>
private static void DbChecked()
{
if (dbChecked)
{
return;
}
string[] fieldsName = new string[] { "idConnectionWidget", "idTarget", "idSource", "idCustomerServiceData" };
Type[] fieldsType = new Type[] { typeof(int), typeof(int), typeof(int), typeof(int) };
bool existsTable = DataAccessConnection.DBCheckedTable("ConnectionWidget");
if (!existsTable)
{
DataAccessConnection.CreateTable("ConnectionWidget", fieldsName, true, fieldsType);
}
bool existsProcedureDelete = DataAccessConnection.DBCheckedStoredProcedure("DeleteConnectionWidget");
bool existsProcedureSave = DataAccessConnection.DBCheckedStoredProcedure("SaveConnectionWidget");
bool existsProcedureUpdate = DataAccessConnection.DBCheckedStoredProcedure("UpdateConnectionWidget");
if (!existsProcedureDelete)
{
DataAccessConnection.CreateDeleteStoredProcedure("ConnectionWidget", "idConnectionWidget");
}
if (!existsProcedureSave)
{
DataAccessConnection.CreateSaveStoredProcedure("ConnectionWidget", fieldsName, fieldsType);
}
if (!existsProcedureUpdate)
{
DataAccessConnection.CreateUpdateStoredProcedure("ConnectionWidget", "idConnectionWidget", fieldsName, fieldsType);
}
dbChecked = true;
}
/// <summary>
/// Function to check and create table and stored procedures for this class.
/// </summary>
private static void DbChecked()
{
if (dbChecked)
{
return;
}
string[] fieldsName = new string[] { "idPermission", "allowRead", "allowUpdate", "allowNew", "allowDelete", "businessClassName", "idGroup" };
Type[] fieldsType = new Type[] { typeof(int), typeof(bool), typeof(bool), typeof(bool), typeof(bool), typeof(string), typeof(int) };
bool existsTable = DataAccessConnection.DBCheckedTable("Permission");
if (!existsTable)
{
DataAccessConnection.CreateTable("Permission", fieldsName, true, fieldsType);
}
bool existsProcedureDelete = DataAccessConnection.DBCheckedStoredProcedure("DeletePermission");
bool existsProcedureSave = DataAccessConnection.DBCheckedStoredProcedure("SavePermission");
bool existsProcedureUpdate = DataAccessConnection.DBCheckedStoredProcedure("UpdatePermission");
if (!existsProcedureDelete)
{
DataAccessConnection.CreateDeleteStoredProcedure("Permission", "idPermission");
}
if (!existsProcedureSave)
{
DataAccessConnection.CreateSaveStoredProcedure("Permission", fieldsName, fieldsType);
}
if (!existsProcedureUpdate)
{
DataAccessConnection.CreateUpdateStoredProcedure("Permission", "idPermission", fieldsName, fieldsType);
}
dbChecked = true;
}
/// <summary>
/// Function to check and create table and stored procedures for this class.
/// </summary>
private static void DbChecked()
{
if (dbChecked)
{
return;
}
string[] fieldsName = new string[] { "idGroup", "name", "description", "isGroupActive" };
Type[] fieldsType = new Type[] { typeof(int), typeof(string), typeof(string), typeof(bool) };
bool existsTable = DataAccessConnection.DBCheckedTable("Group");
if (!existsTable)
{
DataAccessConnection.CreateTable("Group", fieldsName, true, fieldsType);
}
bool existsProcedureDelete = DataAccessConnection.DBCheckedStoredProcedure("DeleteGroup");
bool existsProcedureSave = DataAccessConnection.DBCheckedStoredProcedure("SaveGroup");
bool existsProcedureUpdate = DataAccessConnection.DBCheckedStoredProcedure("UpdateGroup");
if (!existsProcedureDelete)
{
DataAccessConnection.CreateDeleteStoredProcedure("Group", "idGroup");
}
if (!existsProcedureSave)
{
DataAccessConnection.CreateSaveStoredProcedure("Group", fieldsName, fieldsType);
}
if (!existsProcedureUpdate)
{
DataAccessConnection.CreateUpdateStoredProcedure("Group", "idGroup", fieldsName, fieldsType);
}
dbChecked = true;
}
/// <summary>
/// Function to check and create table and stored procedures for this class.
/// </summary>
private static void DbChecked()
{
if (dbChecked)
{
return;
}
string[] fieldsName = new string[] { "idCampaign", "description", "name", "startDate", "stopDate", "idUser" };
Type[] fieldsType = new Type[] { typeof(int), typeof(string), typeof(string), typeof(System.DateTime), typeof(System.DateTime), typeof(int) };
bool existsTable = DataAccessConnection.DBCheckedTable("Campaign");
if (!existsTable)
{
DataAccessConnection.CreateTable("Campaign", fieldsName, true, fieldsType);
}
bool existsProcedureDelete = DataAccessConnection.DBCheckedStoredProcedure("DeleteCampaign");
bool existsProcedureSave = DataAccessConnection.DBCheckedStoredProcedure("SaveCampaign");
bool existsProcedureUpdate = DataAccessConnection.DBCheckedStoredProcedure("UpdateCampaign");
if (!existsProcedureDelete)
{
DataAccessConnection.CreateDeleteStoredProcedure("Campaign", "idCampaign");
}
if (!existsProcedureSave)
{
DataAccessConnection.CreateSaveStoredProcedure("Campaign", fieldsName, fieldsType);
}
if (!existsProcedureUpdate)
{
DataAccessConnection.CreateUpdateStoredProcedure("Campaign", "idCampaign", fieldsName, fieldsType);
}
dbChecked = true;
}
/// <summary>
/// Function to check and create table and stored procedures for this class.
/// </summary>
private static void DbChecked()
{
if (dbChecked)
{
return;
}
string[] fieldsName = new string[] { "idRegisterAssociation", "idRegister", "idTable" };
Type[] fieldsType = new Type[] { typeof(int), typeof(int), typeof(int) };
bool existsTable = DataAccessConnection.DBCheckedTable("RegisterAssociation");
if (!existsTable)
{
DataAccessConnection.CreateTable("RegisterAssociation", fieldsName, true, fieldsType);
}
bool existsProcedureDelete = DataAccessConnection.DBCheckedStoredProcedure("DeleteRegisterAssociation");
bool existsProcedureSave = DataAccessConnection.DBCheckedStoredProcedure("SaveRegisterAssociation");
bool existsProcedureUpdate = DataAccessConnection.DBCheckedStoredProcedure("UpdateRegisterAssociation");
if (!existsProcedureDelete)
{
DataAccessConnection.CreateDeleteStoredProcedure("RegisterAssociation", "idRegisterAssociation");
}
if (!existsProcedureSave)
{
DataAccessConnection.CreateSaveStoredProcedure("RegisterAssociation", fieldsName, fieldsType);
}
if (!existsProcedureUpdate)
{
DataAccessConnection.CreateUpdateStoredProcedure("RegisterAssociation", "idRegisterAssociation", fieldsName, fieldsType);
}
dbChecked = true;
}
/// <summary>
/// Function to check and create table and stored procedures for this class.
/// </summary>
private static void DbChecked()
{
if (dbChecked)
{
return;
}
string[] fieldsName = new string[] { "idMall", "serverName", "mallName" };
Type[] fieldsType = new Type[] { typeof(int), typeof(string), typeof(string) };
bool existsTable = DataAccessConnection.DBCheckedTable("Mall");
if (!existsTable)
{
DataAccessConnection.CreateTable("Mall", fieldsName, true, fieldsType);
}
bool existsProcedureDelete = DataAccessConnection.DBCheckedStoredProcedure("DeleteMall");
bool existsProcedureSave = DataAccessConnection.DBCheckedStoredProcedure("SaveMall");
bool existsProcedureUpdate = DataAccessConnection.DBCheckedStoredProcedure("UpdateMall");
if (!existsProcedureDelete)
{
DataAccessConnection.CreateDeleteStoredProcedure("Mall", "idMall");
}
if (!existsProcedureSave)
{
DataAccessConnection.CreateSaveStoredProcedure("Mall", fieldsName, fieldsType);
}
if (!existsProcedureUpdate)
{
DataAccessConnection.CreateUpdateStoredProcedure("Mall", "idMall", fieldsName, fieldsType);
}
dbChecked = true;
}
/// <summary>
/// Function to check and create table and stored procedures for this class.
/// </summary>
private static void DbChecked()
{
if (dbChecked)
{
return;
}
string[] fieldsName = new string[] { "idConnectionPoint", "connectionType", "xCoordinateRelativeToParent", "yCoordinateRelativeToParent", "idParentComponent", "idComponent", "idConnectionWidget" };
Type[] fieldsType = new Type[] { typeof(int), typeof(int), typeof(double), typeof(double), typeof(int), typeof(int), typeof(int) };
bool existsTable = DataAccessConnection.DBCheckedTable("ConnectionPoint");
if (!existsTable)
{
DataAccessConnection.CreateTable("ConnectionPoint", fieldsName, true, fieldsType);
}
bool existsProcedureDelete = DataAccessConnection.DBCheckedStoredProcedure("DeleteConnectionPoint");
bool existsProcedureSave = DataAccessConnection.DBCheckedStoredProcedure("SaveConnectionPoint");
bool existsProcedureUpdate = DataAccessConnection.DBCheckedStoredProcedure("UpdateConnectionPoint");
if (!existsProcedureDelete)
{
DataAccessConnection.CreateDeleteStoredProcedure("ConnectionPoint", "idConnectionPoint");
}
if (!existsProcedureSave)
{
DataAccessConnection.CreateSaveStoredProcedure("ConnectionPoint", fieldsName, fieldsType);
}
if (!existsProcedureUpdate)
{
DataAccessConnection.CreateUpdateStoredProcedure("ConnectionPoint", "idConnectionPoint", fieldsName, fieldsType);
}
dbChecked = true;
}
/// <summary>
/// Function to check and create table and stored procedures for this class.
/// </summary>
private static void DbChecked()
{
if (dbChecked)
{
return;
}
string[] fieldsName = new string[] { "idDeviceProfile", "deviceType", "deviceModel", "macAddress", "windowsMobileVersion", "idCustomer" };
Type[] fieldsType = new Type[] { typeof(int), typeof(string), typeof(string), typeof(string), typeof(string), typeof(int) };
bool existsTable = DataAccessConnection.DBCheckedTable("DeviceProfile");
if (!existsTable)
{
DataAccessConnection.CreateTable("DeviceProfile", fieldsName, true, fieldsType);
}
bool existsProcedureDelete = DataAccessConnection.DBCheckedStoredProcedure("DeleteDeviceProfile");
bool existsProcedureSave = DataAccessConnection.DBCheckedStoredProcedure("SaveDeviceProfile");
bool existsProcedureUpdate = DataAccessConnection.DBCheckedStoredProcedure("UpdateDeviceProfile");
if (!existsProcedureDelete)
{
DataAccessConnection.CreateDeleteStoredProcedure("DeviceProfile", "idDeviceProfile");
}
if (!existsProcedureSave)
{
DataAccessConnection.CreateSaveStoredProcedure("DeviceProfile", fieldsName, fieldsType);
}
if (!existsProcedureUpdate)
{
DataAccessConnection.CreateUpdateStoredProcedure("DeviceProfile", "idDeviceProfile", fieldsName, fieldsType);
}
dbChecked = true;
}
/// <summary>
/// Function to check and create table and stored procedures for this class.
/// </summary>
private static void DbChecked()
{
if (dbChecked)
{
return;
}
string[] fieldsName = new string[] { "idUserActionClientData", "actionType", "start", "stop", "idTable", "idRegister", "idComponent", "idService" };
Type[] fieldsType = new Type[] { typeof(int), typeof(int), typeof(System.DateTime), typeof(System.DateTime), typeof(int), typeof(int), typeof(int), typeof(int) };
bool existsTable = DataAccessConnection.DBCheckedTable("UserActionClientData");
if (!existsTable)
{
DataAccessConnection.CreateTable("UserActionClientData", fieldsName, true, fieldsType);
}
bool existsProcedureDelete = DataAccessConnection.DBCheckedStoredProcedure("DeleteUserActionClientData");
bool existsProcedureSave = DataAccessConnection.DBCheckedStoredProcedure("SaveUserActionClientData");
bool existsProcedureUpdate = DataAccessConnection.DBCheckedStoredProcedure("UpdateUserActionClientData");
if (!existsProcedureDelete)
{
DataAccessConnection.CreateDeleteStoredProcedure("UserActionClientData", "idUserActionClientData");
}
if (!existsProcedureSave)
{
DataAccessConnection.CreateSaveStoredProcedure("UserActionClientData", fieldsName, fieldsType);
}
if (!existsProcedureUpdate)
{
DataAccessConnection.CreateUpdateStoredProcedure("UserActionClientData", "idUserActionClientData", fieldsName, fieldsType);
}
dbChecked = true;
}
/// <summary>
/// Function to check and create table and stored procedures for this class.
/// </summary>
private static void DbChecked()
{
if (dbChecked)
{
return;
}
string[] fieldsName = new string[] { "idUser", "userName", "password", "name", "surname", "phoneNumber", "isUserActive", "charge", "idStore" };
Type[] fieldsType = new Type[] { typeof(int), typeof(string), typeof(string), typeof(string), typeof(string), typeof(string), typeof(bool), typeof(string), typeof(int) };
bool existsTable = DataAccessConnection.DBCheckedTable("User");
if (!existsTable)
{
DataAccessConnection.CreateTable("User", fieldsName, true, fieldsType);
}
bool existsProcedureDelete = DataAccessConnection.DBCheckedStoredProcedure("DeleteUser");
bool existsProcedureSave = DataAccessConnection.DBCheckedStoredProcedure("SaveUser");
bool existsProcedureUpdate = DataAccessConnection.DBCheckedStoredProcedure("UpdateUser");
if (!existsProcedureDelete)
{
DataAccessConnection.CreateDeleteStoredProcedure("User", "idUser");
}
if (!existsProcedureSave)
{
DataAccessConnection.CreateSaveStoredProcedure("User", fieldsName, fieldsType);
}
if (!existsProcedureUpdate)
{
DataAccessConnection.CreateUpdateStoredProcedure("User", "idUser", fieldsName, fieldsType);
}
dbChecked = true;
}
/// <summary>
/// Function to Load a CampaignEntity 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 CampaignEntity 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 <CampaignEntity> 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 <CampaignEntity> campaignList;
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 idCampaign, description, name, startDate, stopDate, idUser, timestamp FROM [Campaign] WHERE " + propertyName + " " + op + " @expValue";
// Create the command
IDbCommand sqlCommand = dataAccess.GetNewCommand(cmdText, dbConnection, dbTransaction);
// Add parameters values to the command
IDbDataParameter 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();
campaignList = new Collection <CampaignEntity>();
CampaignEntity campaign;
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)
{
campaign = Load(id, loadRelation, null);
campaignList.Add(campaign);
}
}
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(campaignList);
}
/// <summary>
/// Function to Save a CampaignEntity in the database.
/// </summary>
/// <param name="campaign">CampaignEntity to save</param>
/// <param name="scope">Interna structure to avoid circular reference locks. Provide an instance when calling from other data access object.</param>
/// <exception cref="ArgumentNullException">
/// If <paramref name="campaign"/> is not a <c>CampaignEntity</c>.
/// </exception>
/// <exception cref="UtnEmallDataAccessException">
/// If an DbException occurs in the try block while accessing the database.
/// </exception>
public void Save(CampaignEntity campaign, Dictionary <string, IEntity> scope)
{
if (campaign == null)
{
throw new ArgumentException("The argument can't be null");
}
// Create a unique key to identify the object in the internal scope
string scopeKey = campaign.Id.ToString(NumberFormatInfo.InvariantInfo) + "Campaign";
if (scope != null)
{
// If it's on the scope return it, don't save again
if (scope.ContainsKey(scopeKey))
{
return;
}
}
else
{
// Create a new scope if it's not provided
scope = new Dictionary <string, IEntity>();
}
try
{
// Open a DbConnection and a new transaction if it isn't on a higher level one
if (!isGlobalTransaction)
{
dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
dbTransaction = dbConnection.BeginTransaction();
}
string commandName = "";
bool isUpdate = false;
// Check if it is an insert or update command
if (campaign.IsNew || !DataAccessConnection.ExistsEntity(campaign.Id, "Campaign", "idCampaign", dbConnection, dbTransaction))
{
commandName = "SaveCampaign";
}
else
{
isUpdate = true;
commandName = "UpdateCampaign";
}
// Create a db command
IDbCommand sqlCommand = dataAccess.GetNewCommand(commandName, dbConnection, dbTransaction);
sqlCommand.CommandType = CommandType.StoredProcedure;
// Add parameters values to current command
IDbDataParameter parameter;
if (isUpdate)
{
parameter = dataAccess.GetNewDataParameter("@idCampaign", DbType.Int32);
parameter.Value = campaign.Id;
sqlCommand.Parameters.Add(parameter);
}
FillSaveParameters(campaign, sqlCommand);
// Execute the command
if (isUpdate)
{
sqlCommand.ExecuteNonQuery();
}
else
{
IDbDataParameter parameterIdOutput = dataAccess.GetNewDataParameter("@idCampaign", DbType.Int32);
parameterIdOutput.Direction = ParameterDirection.ReturnValue;
sqlCommand.Parameters.Add(parameterIdOutput);
sqlCommand.ExecuteNonQuery();
campaign.Id = Convert.ToInt32(parameterIdOutput.Value, NumberFormatInfo.InvariantInfo);
}
scopeKey = campaign.Id.ToString(NumberFormatInfo.InvariantInfo) + "Campaign";
// Add entity to current internal scope
scope.Add(scopeKey, campaign);
// Save collections of related objects to current entity
// Save objects related to current entity
// Update
// Close transaction if initiated by me
if (!isGlobalTransaction)
{
dbTransaction.Commit();
}
// Update new and changed flags
campaign.IsNew = false;
campaign.Changed = false;
}
catch (DbException dbException)
{
// Rollback transaction
if (!isGlobalTransaction)
{
dbTransaction.Rollback();
}
// Rethrow as custom exception
throw new UtnEmallDataAccessException(dbException.Message, dbException);
}
finally
{
// Close connection if initiated by me
if (!isGlobalTransaction)
{
dbConnection.Close();
dbConnection = null;
dbTransaction = null;
}
}
}
/// <summary>
/// Crea una nueva tabla con indices de clave foranea
/// </summary>
/// <param name="table">El nombre de la tabla a crear.</param>
/// <param name="fieldsName">Los nombres de los campos.</param>
/// <param name="fieldsType">Los tipos de los campos.</param>
/// <param name="indexColumns">Los nombres de los campos a indizar.</param>
/// <returns>True si tiene éxito.</returns>
public static bool CreateTable(string table, string[] fieldsName, bool isIdIdentity, Type[] fieldsType, string[] indexColumns)
{
if (String.IsNullOrEmpty(table))
{
throw new ArgumentException("Must provide a name for the table.", "table");
}
if (fieldsName.Length == 0)
{
throw new ArgumentException("Must provide at least one field name.", "table");
}
if (fieldsType.Length == 0)
{
throw new ArgumentException("Must provide at least one field type.", "fieldsType");
}
if (fieldsName.Length != fieldsType.Length)
{
throw new ArgumentException("Quantity oif fields names and types must be equal.", "fieldsType");
}
// Agrega el campo timestamp
string[] fields = new string[fieldsName.Length + 1];
fieldsName.CopyTo(fields, 0);
fields[fields.Length - 1] = "timestamp";
fieldsName = fields;
// Agrega el tipo del campo timestamp
Type[] fieldsNewTypes = new Type[fieldsType.Length + 1];
fieldsType.CopyTo(fieldsNewTypes, 0);
fieldsNewTypes[fieldsNewTypes.Length - 1] = Type.GetType("System.DateTime");
fieldsType = fieldsNewTypes;
// Crear el comando SQL para la creación de la tabla
string cmdText = "CREATE TABLE [" + table + "] (";
cmdText += "" + fieldsName[0] + " " + GetSQLTypeName(fieldsType[0]) + " PRIMARY KEY";
if (isIdIdentity)
{
cmdText += " IDENTITY";
}
for (int i = 1; i < fieldsName.Length; i++)
{
cmdText += ",[" + fieldsName[i] + "] " + GetSQLTypeName(fieldsType[i]);
}
cmdText += ")";
// Conectarse a la base de datos
DataAccessConnection dataAccess = DataAccessConnection.Instance;
IDbConnection dbConnection = dataAccess.GetNewConnection();
dbConnection.Open();
// Ejecutar el comando
IDbCommand command = dataAccess.GetNewCommand(cmdText, dbConnection);
command.ExecuteNonQuery();
// Crear los índices
if (indexColumns != null && indexColumns.Length > 0)
{
string cmdIndex;
for (int i = 0; i < indexColumns.Length; i++)
{
cmdIndex = "CREATE INDEX IDX_" + table + "_" + indexColumns[i] +
" ON [" + table + "] (" + indexColumns[i] + ");";
command.CommandText = cmdIndex;
command.ExecuteNonQuery();
}
}
// Cerrar la conexión
dbConnection.Close();
return(true);
}
