Exemplo n.º 1
0
        public IList<ItemStatusEntity> GetAllItemStatuss()
        {
            List<ItemStatusEntity> itemStatusList = new List<ItemStatusEntity>();
            try
            {
                _conn = new SqlConnection(ConnString);
                _conn.Open();

                string sql = "SELECT itemStatusUd, status, updateTimestamp, updatePersonFK FROM DBO.itemStatus;";
                var cmd = new SqlCommand(sql, _conn);

                var rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    var itemStatus = new ItemStatusEntity()
                    {
                        itemStatusId = rdr.IsDBNull(rdr.GetOrdinal("itemStatusId")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("itemStatusId")),
                        status = rdr.IsDBNull(rdr.GetOrdinal("status")) ? null : rdr.GetString(rdr.GetOrdinal("status")),
                        updateTimestamp = rdr.IsDBNull(rdr.GetOrdinal("updateTimestamp")) ? new DateTime() : rdr.GetDateTime(rdr.GetOrdinal("updateTimestamp")),
                        updatePersonFK = rdr.IsDBNull(rdr.GetOrdinal("updatePersonFK")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("updatePersonFK"))
                    };
                    itemStatusList.Add(itemStatus);
                }
            }
            finally
            {
                _conn?.Close();
            }
            return itemStatusList;
        }
Exemplo n.º 2
0
        internal static ItemStatusEntity ItemStatus(ItemStatus ent)
        {
            ItemStatusEntity data = new ItemStatusEntity();

            data.itemStatusId = ent.Id;
            data.status = ent.Status;
            data.updateTimestamp = ent.UpdateTimestamp;
            data.updatePersonFK = ent.UpdatePersonFK;

            return data;
        }
Exemplo n.º 3
0
        internal static ItemStatus ItemStatus(ItemStatusEntity data)
        {
            ItemStatus ent = new ItemStatus();

            ent.Id = data.itemStatusId;
            ent.Status = data.status;
            ent.UpdateTimestamp = data.updateTimestamp;
            ent.UpdatePersonFK = data.updatePersonFK;

            return ent;
        }
Exemplo n.º 4
0
        public ItemStatusEntity GetItemStatusById(int id)
        {
            List<ItemStatusEntity> itemStatusList = new List<ItemStatusEntity>();
            try
            {
                _conn = new SqlConnection(ConnString);
                _conn.Open();

                string sql = "SELECT itemStatusUd, status, updateTimestamp, updatePersonFK FROM DBO.itemStatus WHERE itemStatusId = @id;";
                var cmd = new SqlCommand(sql, _conn);

                var paramQuery = new SqlParameter
                {
                    ParameterName = "@id",
                    Value = "%" + id + "%"
                };
                cmd.Parameters.Add(paramQuery);

                var rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    var itemStatus = new ItemStatusEntity()
                    {
                        itemStatusId = rdr.IsDBNull(rdr.GetOrdinal("itemStatusId")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("itemStatusId")),
                        status = rdr.IsDBNull(rdr.GetOrdinal("status")) ? null : rdr.GetString(rdr.GetOrdinal("status")),
                        updateTimestamp = rdr.IsDBNull(rdr.GetOrdinal("updateTimestamp")) ? new DateTime() : rdr.GetDateTime(rdr.GetOrdinal("updateTimestamp")),
                        updatePersonFK = rdr.IsDBNull(rdr.GetOrdinal("updatePersonFK")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("updatePersonFK"))
                    };
                    itemStatusList.Add(itemStatus);
                }
            }
            finally
            {
                _conn?.Close();
            }
            return itemStatusList.FirstOrDefault();
        }
Exemplo n.º 5
0
        private void CheckItemStatusForRequiredValues(ItemStatusEntity status, RepositoryUtils.RepositoryAction action)
        {
            List<string> missingFields = new List<string>();

            //if (String.IsNullOrWhiteSpace(p.userName)) missingFields.Add("User Name");
            //if (String.IsNullOrWhiteSpace(p.emailAddress)) missingFields.Add("Email Address");
            //if (String.IsNullOrWhiteSpace(p.firstName)) missingFields.Add("First Name");
            //if (String.IsNullOrWhiteSpace(p.lastName)) missingFields.Add("Last Name");
            //if (String.IsNullOrWhiteSpace(p.passwordHash)) missingFields.Add("Password");

            if (missingFields.Count > 0)
            {
                throw new Exception(String.Format("Cannot {0} ItemStatus: Missing Fields {1}", action.ToString(), String.Join(", ", missingFields.ToArray())));
            }
        }
Exemplo n.º 6
0
        public void Update(int id, ItemStatusEntity itemStatus)
        {
            CheckItemStatusForRequiredValues(itemStatus, RepositoryUtils.RepositoryAction.Update);

            var contactToUpdate = GetItemStatusById(id);
            if (contactToUpdate == null)
            {
                throw new Exception("ItemStatus does not exist in database");
            }

            try
            {
                _conn = new SqlConnection(ConnString);

                var cmd = _conn.CreateCommand();
                cmd.CommandText = @"UPDATE person SET [status]=@status,
                                                      [updateTimestamp]=getdate(),
                                                      [updatePersonFK]=@updatePersonFK
                                                      WHERE personId=@Id";

                cmd.Parameters.Add("@Id", SqlDbType.Int);
                cmd.Parameters["@Id"].Value = id;

                cmd.Parameters.Add("@status", SqlDbType.VarChar);
                cmd.Parameters["@status"].Value = itemStatus.status;

                cmd.Parameters.Add("@updatePersonFK", SqlDbType.Int);
                cmd.Parameters["@updatePersonFK"].Value = itemStatus.updatePersonFK;

                _conn.Open();

                var number = cmd.ExecuteNonQuery();

                if (number != 1)
                {
                    throw new Exception($"No Contacts were updated with Id: {id}");
                }
            }
            finally
            {
                _conn?.Close();
            }
        }
Exemplo n.º 7
0
        public long Insert(ItemStatusEntity itemStatus)
        {
            CheckItemStatusForRequiredValues(itemStatus, RepositoryUtils.RepositoryAction.Insert);
            try
            {
                var contactExists = GetItemStatusByStatusName(itemStatus.status);
                if (contactExists != null)
                {
                    throw new Exception($"Entity {itemStatus.status} already exists in database!");
                }
                _conn = new SqlConnection(ConnString);

                var cmd = _conn.CreateCommand();

                cmd.CommandText =
                    @"INSERT INTO[dbo].[itemStatus] (status, updateTimestamp, updatePersonFK)
                    VALUES(@status, getdate(), @updatePersonFK);SELECT CAST(scope_identity() AS int)";

                cmd.Parameters.Add("@status", SqlDbType.VarChar);
                cmd.Parameters["@status"].Value = itemStatus.status;

                cmd.Parameters.Add("@updatePersonFK", SqlDbType.Int);
                cmd.Parameters["@updatePersonFK"].Value = itemStatus.updatePersonFK;

                _conn.Open();

                try
                {
                    return int.Parse(cmd.ExecuteScalar().ToString());
                }
                catch (Exception)
                {
                    throw new Exception($"ItemStatus {itemStatus.status} not inserted in database!");
                }

            }
            finally
            {
                _conn?.Close();
            }
        }