/// <summary> /// Crea el procedimiento "CreateNextId" para la tabla especificada. /// </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 CreateNextIdStoredProcedure(string tableName, string idColumnName) { if (String.IsNullOrEmpty(tableName)) { throw new ArgumentException("Must provide a name for the table.", "tableName"); } // Crear el comando para crear el procedimiento string cmdText = "CREATE PROCEDURE NextID" + tableName + "( @" + idColumnName + " int OUT)" + "AS IF (SELECT COUNT(*) FROM [" + tableName + "]) = 0 " + "BEGIN " + " SET @" + idColumnName + " = 1 " + "END " + "ELSE " + "BEGIN " + " SET @" + idColumnName + " = (SELECT (MAX(" + idColumnName + ")+1) FROM [" + tableName + "]) " + "END"; // Conectarse a la base de datos DataAccessConnection dataAccess = DataAccessConnection.Instance; IDbConnection dbConnection = dataAccess.GetNewConnection(); dbConnection.Open(); // Ejecutar el procedimiento y cerrar la conexión IDbCommand command = dataAccess.GetNewCommand(cmdText, dbConnection); command.ExecuteNonQuery(); dbConnection.Close(); return(true); }
/// <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> /// Crea un procedimiento de grabación para la tabla indicada usando clave autonumerica. /// </summary> /// <param name="tableName">Nombre de la tabla.</param> /// <param name="fieldsName">Nombres de los campos de la tabla.</param> /// <param name="fieldsType">Tipos .NET de los campos en el mismo orden que los nombres.</param> /// <returns>True si tiene éxito.</returns> public static bool CreateSaveStoredProcedure(string tableName, string[] fieldsName, Type[] fieldsType) { if (fieldsName.Length == 0) { throw new ArgumentException("Must provide the table's field names.", "fieldsName"); } if (fieldsType.Length == 0) { throw new ArgumentException("Must provide the table's field types.", "fieldsType"); } // Crea el comando SQL para insertar un registro en la tabla string cmdText = "INSERT INTO [" + tableName + "] VALUES("; for (int i = 1; i < fieldsName.Length; i++) { if (i == 1) { cmdText += "@" + fieldsName[i]; } else { cmdText += ",@" + fieldsName[i]; } } cmdText += ", GETDATE())"; // Crear el comando para crear el procedimiento de inserción string procedure = "CREATE PROCEDURE Save" + tableName + "("; for (int i = 1; i < fieldsName.Length; i++) { if (i == 1) { procedure += "@" + fieldsName[i] + " " + GetSQLTypeName(fieldsType[i]); } else { procedure += ",@" + fieldsName[i] + " " + GetSQLTypeName(fieldsType[i]); } } procedure += ") AS " + cmdText; procedure += " RETURN SCOPE_IDENTITY()"; // Conectarse a la base de datos DataAccessConnection dataAccess = DataAccessConnection.Instance; IDbConnection dbConnection = dataAccess.GetNewConnection(); dbConnection.Open(); // Ejecutar el procedimiento y cerrar la conexión IDbCommand command = dataAccess.GetNewCommand(procedure, dbConnection); command.ExecuteNonQuery(); dbConnection.Close(); return(true); }
/// <summary> /// Crea un procedimiento de actualización para la tabla indicada. /// </summary> /// <param name="tableName">Nombre de la tabla.</param> /// <param name="idColumnName">The name of the id column</param> /// <param name="fieldsName">Nombres de los campos de la tabla.</param> /// <param name="fieldsType">Tipos .NET de los campos en el mismo orden que los nombres.</param> /// <returns>True if successful</returns> public static bool CreateUpdateStoredProcedure(string tableName, string idColumnName, string[] fieldsName, Type[] fieldsType) { if (fieldsName.Length == 0) { throw new ArgumentException("Must provide the table's field names.", "fieldsName"); } if (fieldsType.Length == 0) { throw new ArgumentException("Must provide the table's field types.", "fieldsType"); } // Crea el comando para actualizar un registro string cmdText = "UPDATE [" + tableName + "] SET "; for (int i = 1; i < fieldsName.Length; i++) { if (i == 1) { cmdText += fieldsName[i] + " = @" + fieldsName[i]; } else { cmdText += ", [" + fieldsName[i] + "] = @" + fieldsName[i]; } } cmdText += " , timestamp=GETDATE() WHERE [" + idColumnName + "] = @" + idColumnName; // Crear el comando para crear el procedimiento de actualización string procedure = "CREATE PROCEDURE Update" + tableName + "("; for (int i = 0; i < fieldsName.Length; i++) { if (i == 0) { procedure += "@" + fieldsName[i] + " " + GetSQLTypeName(fieldsType[i]); } else { procedure += ",@" + fieldsName[i] + " " + GetSQLTypeName(fieldsType[i]); } } procedure += ")AS " + cmdText; // Conectarse a la base de datos DataAccessConnection dataAccess = DataAccessConnection.Instance; IDbConnection dbConnection = dataAccess.GetNewConnection(); dbConnection.Open(); // Ejecutar el procedimiento y cerrar la conexión IDbCommand command = dataAccess.GetNewCommand(procedure, dbConnection); command.ExecuteNonQuery(); dbConnection.Close(); return(true); }
/// <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 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> /// 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); }
/// <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); }