コード例 #1
0
        /// <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);
        }
コード例 #2
0
        /// <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);
        }
コード例 #3
0
        /// <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);
        }
コード例 #4
0
        /// <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);
        }
コード例 #5
0
        /// <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);
        }