public int RemoveUser(string username)
{
RetrieveTables(); // get a fresh copy of the database tables before every query to prevent errors
try
{
// check if the user is already gone
List <string> fetchedUsernames = (from Column in UserTable.AsEnumerable() // pulls existing usernames
select Column.Field <string>("username")).ToList();
if (fetchedUsernames.Contains(username))
{
string query = "DELETE FROM [User] WHERE username = '******';";
ExecuteSQLCommand(query);
return(0);
}
else
{
return(0);
}
}
catch (SqlException e)
{
return(-1);
}
}
public int AddUser(User newUser)
{
RetrieveTables(); // get a fresh copy of the database tables before every query to prevent errors
try
{
List <int> fetchedUserIDs = (from Column in UserTable.AsEnumerable() // pulls existing IDs
select Column.Field <int>("id")).ToList();
newUser.id = fetchedUserIDs.Max() + 1; // adds new ID
string query = "INSERT INTO [User] VALUES('" + newUser.username + "', '"
+ newUser.email + "', '"
+ newUser.passWord + "', "
+ newUser.id + ");";
ExecuteSQLCommand(query);
return(0);
}
catch (SqlException e)
{
return(-1);
}
}
public User GetUser(string username, string password)
{
RetrieveTables(); // get a fresh copy of the database tables before every query to prevent errors
User fetchedUser = new User();
try
{
var fetchedUserRow = from Row in UserTable.AsEnumerable()
where Row.Field <string>("username") == username
where Row.Field <string>("password") == password
select Row;
if (fetchedUserRow.AsEnumerable().Count() == 0) // User not found
{
return(null);
}
fetchedUser.email = (from Row in UserTable.AsEnumerable()
where Row.Field <string>("username") == username
where Row.Field <string>("password") == password
select Row.Field <string>("email")).ToList().ElementAt(0);
fetchedUser.id = (from Row in UserTable.AsEnumerable()
where Row.Field <string>("username") == username
where Row.Field <string>("password") == password
select Row.Field <int>("id")).ToList().ElementAt(0);
fetchedUser.username = username;
fetchedUser.passWord = password;
}
catch (SqlException e)
{
return(null);
}
return(fetchedUser);
}