/// <summary> /// Author: Ibrahim Abuzaid /// Data Transfer Object to represent a User from the /// Database /// /// Added 3/4 By Ibarahim /// Updated Stored Procedure and Method Name 4/14/16 Emily /// /// Updated method - SP calls for active 4/21/16 Steve Hoover /// </summary> public static List <User> RetrieveUserList(Active active) { var user = new List <User>(); int _active = 0; var conn = DBConnection.GetDBConnection(); var query = @"Admin.spSelectUsers"; var cmd = new SqlCommand(query, conn); cmd.CommandType = CommandType.StoredProcedure; if (active == Active.active) { _active = 1; } cmd.Parameters.AddWithValue("@Active", _active); try { conn.Open(); var reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { User currentUser = new User() { UserID = reader.GetInt32(0), FirstName = reader.GetString(1), LastName = reader.GetString(2), Zip = reader.GetString(3), EmailAddress = reader.GetString(4), UserName = reader.GetString(5), Password = reader.GetString(6), Active = reader.GetBoolean(7) }; if (reader.IsDBNull(3)) { currentUser.Zip = null; } else { currentUser.Zip = reader.GetString(3); } if (reader.IsDBNull(4)) { currentUser.EmailAddress = null; } else { currentUser.EmailAddress = reader.GetString(4); } if (reader.IsDBNull(8)) { currentUser.RegionId = null; } else { currentUser.RegionId = reader.GetInt32(8); } user.Add(currentUser); } } else { throw new ApplicationException("Data not found"); } } catch (Exception) { throw; } finally { conn.Close(); } return(user); }
/// <summary> /// Rhett Allen /// Created: 2016/02/26 /// /// Get a single user based on the id in the database /// </summary> /// <param name="userID">The UserID in the database</param> /// <returns>The specified plant object</returns> public static User RetrieveUserByID(int userID) { User user = new User(); var conn = DBConnection.GetDBConnection(); var query = @"Admin.spSelectUser"; var cmd = new SqlCommand(query, conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@UserID", userID); try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { user = new User() { UserID = reader.GetInt32(0), FirstName = reader.GetString(1), LastName = reader.GetString(2), UserName = reader.GetString(5), Password = reader.GetString(6), Active = reader.GetBoolean(7) }; // Rhett Allen 3/6/16 - changed ExecuteReader to accept null values if (reader.IsDBNull(3)) { user.Zip = null; } else { user.Zip = reader.GetString(3); } if (reader.IsDBNull(4)) { user.EmailAddress = null; } else { user.EmailAddress = reader.GetString(4); } if (reader.IsDBNull(8)) { user.RegionId = null; } else { user.RegionId = reader.GetInt32(8); } } } catch (Exception) { throw; } finally { conn.Close(); } return(user); }
//Updated class name 4/14/16 Emily public static User RetrieveUserByUsername(string username) { User user; var conn = DBConnection.GetDBConnection(); var query = @"Admin.spSelectUserByUsername"; var cmd = new SqlCommand(query, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@username", username); try { conn.Open(); var reader = cmd.ExecuteReader(); if (reader.HasRows) { reader.Read(); int? regionID = 0; string zip = ""; if (!reader.IsDBNull(4)) { zip = reader.GetString(4); } if (!reader.IsDBNull(6)) { // Rhett Allen 3/24/16 - regionID is now being assigned regionID = reader.GetInt32(6); } else { // Rhett Allen 3/24/16 - added else. Made region default null instead of zero since region is nullable regionID = null; } user = new User() { UserID = reader.GetInt32(0), UserName = reader.GetString(1), FirstName = reader.GetString(2), LastName = reader.GetString(3), Zip = zip, EmailAddress = reader.GetString(5), RegionId = regionID, Active = reader.GetBoolean(7) }; } else { throw new ApplicationException("Data not found"); } } catch (Exception) { throw; } finally { conn.Close(); } return(user); }
/// <summary> /// Author: Chris Schwebch /// Updated: Nick King /// Date: 04/6/16 /// Gets gardens the user belongs to returns garen list /// </summary> public static List <Group> RetrieveGardenInfo(int userID) { //var gardenInfo = new List<Garden>(); string groupName = null; Group _Group = new Group(); List <Group> groupList = new List <Group>(); var conn = DBConnection.GetDBConnection(); string query = "Gardens.spSelectUserGardens"; var cmd = new SqlCommand(query, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@UserID", userID); //cmd.Parameters.AddWithValue("@Active", 1); try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { if (groupName == null || !groupName.Equals(reader.GetString(6))) { _Group = new Group(); _Group.GardenList = new List <Garden>(); groupName = reader.GetString(6); _Group.Name = reader.GetString(6); //_Group.GroupLeader = reader.String(7); _Group.GroupID = reader.GetInt32(1); groupList.Add(_Group); } if (groupName.Equals(reader.GetString(6))) { Garden _Garden = new Garden(); _Garden.GardenID = reader.GetInt32(0); _Garden.GroupID = reader.GetInt32(1); _Garden.GardenName = reader.GetString(2); _Garden.UserID = reader.GetInt32(3); _Garden.GardenDescription = reader.GetString(4); _Garden.GardenRegion = reader.GetString(5); _Group.GardenList.Add(_Garden); } } } } catch (Exception) { throw; } finally { conn.Close(); } return(groupList); }
/// <summary> /// Retrieve ExpertRequests to be processed by an administrator. /// /// Created By: Trent Cullinan 03/15/2016 /// </summary> /// <param name="accessToken">To confirm access as administrator.</param> /// <returns>Collection of ExpertRequests that need processing.</returns> public List <ExpertRequest> RetrieveExpertRequests(AccessToken accessToken) { List <ExpertRequest> requests = null; //if (RetrieveAdminRoleStatus(accessToken)) //{ requests = new List <ExpertRequest>(); var conn = DBConnection.GetDBConnection(); var cmd = new SqlCommand("Admin.spSelectExpertRequests", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@Active", true); try { conn.Open(); var reader = cmd.ExecuteReader(); while (reader.Read()) { requests.Add(new ExpertRequest() { RequestID = reader.GetInt32(0), User = new User() { UserID = reader.GetInt32(1), UserName = reader.GetString(2), FirstName = reader.GetString(3), LastName = reader.GetString(4), EmailAddress = reader.GetString(5) }, RequestDate = reader.GetDateTime(6), RequestTitle = reader.GetString(7), RequestContent = reader.GetString(8) }); } } catch (SqlException) { throw; } finally { conn.Close(); } //} //else //{ // throw new Exception("User must be an admin to access this method."); //} return(requests); }
/// <summary> /// Rhett Allen /// Created Date: 4/7/16 /// Gets a list of nutrients /// </summary> /// <returns>List of nutrients</returns> public static List <Nutrient> RetrieveNutrient() { List <Nutrient> nutrients = new List <Nutrient>(); var conn = DBConnection.GetDBConnection(); var query = "Expert.spSelectNutrients"; var cmd = new SqlCommand(query, conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; try { conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { Nutrient nutrient = new Nutrient() { NutrientID = reader.GetInt32(0), Name = reader.GetString(1), Description = reader.GetString(2), CreatedBy = reader.GetInt32(3), CreatedDate = reader.GetDateTime(4) }; if (reader.IsDBNull(5)) { nutrient.ModifiedBy = null; } else { nutrient.ModifiedBy = reader.GetInt32(5); } if (reader.IsDBNull(6)) { nutrient.ModifiedDate = null; } else { nutrient.ModifiedDate = reader.GetDateTime(6); } nutrients.Add(nutrient); } } } catch (Exception) { throw; } finally { conn.Close(); } return(nutrients); }