// ------------------------------------------------------------------------
// Method: RemoveRelationship()
// Purpose: Remove existing relationship
// ------------------------------------------------------------------------
public bool RemoveRelationship(int childUserId, int otherUserId, relationshipTypeId RelationType)
{
bool isChanged = false;
SqlConnection conn = new SqlConnection(_connectionString);
string sql = "uspRemoveRelationship";
SqlCommand command = new SqlCommand(sql, conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
// add parameters
command.Parameters.AddWithValue("ChildUserID", childUserId);
command.Parameters.AddWithValue("OtherUserID", otherUserId);
command.Parameters.AddWithValue("RelationshipTypeID", Convert.ToInt32(RelationType));
try
{
conn.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
isChanged = true;
}
}
catch (Exception ex)
{
throw;
}
finally
{
conn.Close();
}
return(isChanged);
}
// ------------------------------------------------------------------------
// Method: AddNewRelationship()
// Purpose: Adds new Relationship record to DB
// ------------------------------------------------------------------------
public bool AddNewRelationship(int childUserId, int otherUserId, relationshipTypeId RelationshipTypeId)
{
bool IsAdded = false;
SqlConnection conn = new SqlConnection(_connectionString);
string sql = "uspAddNewRelationship";
SqlCommand command = new SqlCommand(sql, conn);
command.CommandType = System.Data.CommandType.StoredProcedure;
// add parameters
command.Parameters.AddWithValue("ChildUserID", childUserId);
command.Parameters.AddWithValue("OtherUserID", otherUserId);
command.Parameters.AddWithValue("RelationshipTypeID", Convert.ToInt32(RelationshipTypeId));
try
{
conn.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
if (Convert.ToInt32(reader[0]) > 0)
{
IsAdded = true;
}
}
}
catch (Exception ex)
{
throw;
}
finally
{
conn.Close();
}
return(IsAdded);
}
// -----------------------------------------------------------------------------------
// Method: GetRelatedAcounts()
// Purpose: Calls DB for all related user accounts based on their relationship type
// and returns a list of user objects for each record found
// -----------------------------------------------------------------------------------
public List <User> GetRelatedAccounts(int userId, relationshipTypeId RelationshipTypeId)
{
List <User> childUsers = new List <User>();
List <int> userIDs = new List <int>();
string query = GetQueryStringForRelationshipType(userId);
string sql = string.Format(query, userId, Convert.ToInt32(RelationshipTypeId));
SqlConnection conn = new SqlConnection(_connectionString);
SqlCommand command = new SqlCommand(sql, conn);
try
{
// Grab the associated IDs
conn.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
userIDs.Add(Convert.ToInt32(reader["UserID"]));
}
conn.Close();
// Turn IDs into list of Users
foreach (int targetUserID in userIDs)
{
childUsers.Add(GetUser(targetUserID));
}
}
catch (Exception ex)
{
// todo
}
finally
{
conn.Close();
}
return(childUsers);
}
