public bool addUser(User user)
{
String sql;
if (user.getId() > 0)
{
sql = "UPDATE users SET username = @username, email = @email, firstname = @firstname, lastname = @surname, password = @password, universityId = @universityId WHERE userId = @userId";
}
else
{
sql = "INSERT INTO users (username, email, firstname, lastname, password,universityId)" +
"VALUES (@username, @email, @firstname, @surname, @password,@universityId)";
}
SqlCommand query = new SqlCommand(sql,con);
SqlParameter parUsername = new SqlParameter("@username", SqlDbType.VarChar, 50);
SqlParameter parEmail = new SqlParameter("@email", SqlDbType.VarChar, 50);
SqlParameter parFirstname = new SqlParameter("@firstname", SqlDbType.VarChar, 50);
SqlParameter parSurname = new SqlParameter("@surname", SqlDbType.VarChar, 50);
SqlParameter parPassword = new SqlParameter("@password", SqlDbType.VarChar, 50);
SqlParameter parUniversity = new SqlParameter("@universityId", SqlDbType.Int);
SqlParameter parUserId = new SqlParameter("@userId", SqlDbType.Int);
parUsername.Value = user.getUsername();
parEmail.Value = user.getEmail();
parFirstname.Value = user.getFirstname();
parSurname.Value = user.getLastname();
parPassword.Value = user.getPassword();
parUniversity.Value = user.getUniversityId();
parUserId.Value = user.getId();
query.Parameters.Add(parUsername);
query.Parameters.Add(parEmail);
query.Parameters.Add(parFirstname);
query.Parameters.Add(parSurname);
query.Parameters.Add(parPassword);
query.Parameters.Add(parUniversity);
query.Parameters.Add(parUserId);
Console.WriteLine(query.CommandText);
try
{
this.openCon();
query.ExecuteScalar();
this.closeCon();
return true;
}
catch (Exception e)
{
Console.WriteLine("Adding user failed");
Console.WriteLine(e.Message);
this.closeCon();
return false;
}
}
public WriteNote(User currentUser)
{
InitializeComponent();
this.currentUser = currentUser;
this.currentNote = new Note(0, this.noteText.Rtf, this.tagsBox.Text, this.titleBox.Text, 0, currentUser.getId());
data = new SQLconnect();
loadNotebooks();
}
public MainWindow(User currentUser)
{
InitializeComponent();
this.currentUser = currentUser;
data = new SQLconnect();
loadNotebooks();
loadFriends();
loadNotes(currentUser.getId(), "userId");
}
public bool insertFriend(User user1, User user2)
{
String sql;
String sql2;
sql = "INSERT INTO friends (user1, user2)" +
"VALUES (@user1, @user2)";
sql2 = "INSERT INTO friends (user1, user2)" +
"VALUES (@user2, @user1)";
SqlCommand query = new SqlCommand(sql, con);
SqlCommand query2 = new SqlCommand(sql2, con);
SqlParameter parUser1 = new SqlParameter("@user1", SqlDbType.Int);
SqlParameter parUser2 = new SqlParameter("@user2", SqlDbType.Int);
SqlParameter parUser3 = new SqlParameter("@user1", SqlDbType.Int);
SqlParameter parUser4 = new SqlParameter("@user2", SqlDbType.Int);
parUser1.Value = user1.getId();
parUser2.Value = user2.getId();
parUser3.Value = user1.getId();
parUser4.Value = user2.getId();
query.Parameters.Add(parUser1);
query.Parameters.Add(parUser2);
query2.Parameters.Add(parUser3);
query2.Parameters.Add(parUser4);
try
{
this.openCon();
query.ExecuteNonQuery();
query2.ExecuteNonQuery();
this.closeCon();
return true;
}
catch (Exception e)
{
Console.WriteLine("Adding friend failed");
Console.WriteLine(e.Message);
this.closeCon();
return false;
}
}
public List<User> getFriends(User user)
{
SqlDataReader reader;
List<User> userlist = new List<User>();
SqlCommand query = new SqlCommand("SELECT user2 FROM friends WHERE user1 = @userId", con);
SqlParameter parUserId = new SqlParameter("userId", SqlDbType.Int);
parUserId.Value = user.getId();
query.Parameters.Add(parUserId);
this.openCon();
try
{
reader = query.ExecuteReader();
List<String> ids = new List<String>();
while (reader.Read())
{
ids.Add(reader["user2"].ToString());
}
reader.Close();
foreach(String s in ids)
{
List<User> users = findUser("userId",s);
userlist.Add(users.ElementAt(0));
}
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
}
this.closeCon();
return userlist;
}
public bool checkIfFriends(User user1, User user2)
{
SqlCommand query = new SqlCommand("SELECT * FROM friends WHERE user1 = @user1 AND user2 = @user2", con);
SqlParameter parUser1 = new SqlParameter("user1", SqlDbType.Int);
SqlParameter parUser2 = new SqlParameter("user2", SqlDbType.Int);
parUser1.Value = user1.getId();
parUser2.Value = user2.getId();
query.Parameters.Add(parUser1);
query.Parameters.Add(parUser2);
try
{
this.openCon();
SqlDataReader reader;
reader = query.ExecuteReader();
if (reader.HasRows)
{
return true;
}
this.closeCon();
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
}
this.closeCon();
return false;
}
public NotebookWindow(User user)
{
InitializeComponent();
this.currentUser = user;
this.currentNotebook = new Notebook(0, currentUser.getId(), "", "");
}
