/// <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); }