public User getLastUser()
{
var user = new User();
try
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["museum.Properties.Settings.museumConnectionString"].ConnectionString))
{
connection.Open();
string sqlQuery = "SELECT TOP 1 * FROM users ORDER BY id DESC";
SqlCommand command = new SqlCommand(sqlQuery, connection);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
user.id = (int)reader["id"];
user.username = (string)reader["username"];
user.name = (string)reader["name"];
user.last = (string)reader["last"];
user.password = (string)reader["password"];
user.email = (string)reader["email"];
}
}
}
}
catch (SqlException sql)
{
MessageBox.Show("The user could not be retrieved" + sql);
}
return user;
}
public List<User> getAllUserData()
{
List<User> userList = new List<User>();
try
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["museum.Properties.Settings.museumConnectionString"].ConnectionString))
{
connection.Open();
string sqlQuery = "SELECT * FROM users";
SqlCommand command = new SqlCommand(sqlQuery, connection);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var user = new User();
user.id = (int)reader["id"];
user.username = (string)reader["username"];
user.name = (string)reader["name"];
user.last = (string)reader["last"];
user.password = (string)reader["password"];
user.email = (string)reader["email"];
userList.Add(user);
}
}
}
}
catch (SqlException sql)
{
MessageBox.Show("The user data could not be retrieved!" + sql);
return null;
}
return userList;
}
private SignUpForm(int userId)
{
this.userId = userId;
InitializeComponent();
/*if userId is not 0 use the form as edit form and load the user data*/
if (userId != 0)
{
user = userDao.getSingleUser(userId);
SignUpEmail.Text = user.email;
SignUpLast.Text = user.last;
SignUpPassword.Text = user.password;
SignUpUserName.Text = user.username;
SignUpName.Text = user.name;
}
}
public bool addUser(User user)
{
try{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["museum.Properties.Settings.museumConnectionString"].ConnectionString))
{
connection.Open();
string sqlQuery = "INSERT INTO users (username,name,last,email,password) VALUES (@username,@name,@last,@email,@password)";
SqlCommand command = new SqlCommand(sqlQuery, connection);
command.Parameters.AddWithValue("@username", user.username);
command.Parameters.AddWithValue("@name", user.name);
command.Parameters.AddWithValue("@last", user.last);
command.Parameters.AddWithValue("@email", user.email);
command.Parameters.AddWithValue("@password", user.password);
int oe = command.ExecuteNonQuery();
}
}catch(SqlException sql){
MessageBox.Show("The user could not be inserted" + sql);
}
return true;
}
public bool updateUser(User user)
{
try
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["museum.Properties.Settings.museumConnectionString"].ConnectionString))
{
connection.Open();
string sqlQuery = "UPDATE users SET username = @username,name = @name,last = @last,email = @email,password =@password WHERE id = " + user.id;
SqlCommand command = new SqlCommand(sqlQuery, connection);
command.Parameters.AddWithValue("@username", user.username);
command.Parameters.AddWithValue("@name", user.name);
command.Parameters.AddWithValue("@last", user.last);
command.Parameters.AddWithValue("@email", user.email);
command.Parameters.AddWithValue("@password", user.password);
command.ExecuteNonQuery();
}
}
catch (SqlException sql)
{
MessageBox.Show("The user could not be updated" + sql);
return false;
}
return true;
}
private void button1_Click(object sender, EventArgs e)
{
/*use the form as an sign up form*/
if (userId == 0)
{
/*here goes the validation*/
var username = SignUpUserName.Text;
var password = SignUpPassword.Text;
var name = SignUpName.Text;
var last = SignUpLast.Text;
var email = SignUpEmail.Text;
if (username == "" || password == "" || name == "" || last == "" || email == "")
{
MessageBox.Show("Please fill in all fields!");
return;
}
User newUser = new User();
newUser.name = name;
newUser.username = username;
newUser.last = last;
newUser.password = password;
newUser.email = email;
if (userDao.addUser(newUser))
{
MessageBox.Show("User entered!");
newUser.id = userDao.getLastUser().id;
Globals.currentUser = newUser;
/*Fire the UserSignedUp event in order to fill in the combo box in main form*/
OnSignUp();
this.Close();
}
}
/*use the form as an editform*/
else
{
/*here goes the validation*/
var username = SignUpUserName.Text;
var password = SignUpPassword.Text;
var name = SignUpName.Text;
var last = SignUpLast.Text;
var email = SignUpEmail.Text;
if (username == "" || password == "" || name == "" || last == "" || email == "")
{
MessageBox.Show("Please fill in all fields!");
return;
}
User newUser = new User();
newUser.id = userId;
newUser.name = name;
newUser.username = username;
newUser.last = last;
newUser.password = password;
newUser.email = email;
if(userDao.updateUser(newUser))
{
MessageBox.Show("User updated!");
Globals.currentUser = newUser;
/*Fire the UserSignedUp event in order to fill in the combo box in main form*/
OnSignUp();
this.Close();
}
}
}
