public Cyclist Get(int id)
{
var queryData = new SqlQueryData("SELECT * FROM cyclists WHERE [id] = @id", QueryType.Reader);
queryData.AddParameter("@id", SqlDbType.VarChar, Convert.ToString(id));
try
{
SqlDataReader reader = crud.Get(queryData);
while (reader.Read())
{
var cyclist = new Cyclist(reader.GetInt32(0), reader.GetString(1), reader.GetInt32(2));
return(cyclist);
}
reader.Close();
return(new Cyclist());
}
catch (Exception e)
{
MessageBox.Show("Unable to retrieve team. " + e.Message, "CyclistRegistration team error type: " +
e.GetType(), MessageBoxButton.OK, MessageBoxImage.Error);
Console.WriteLine(e.StackTrace);
Console.WriteLine(e.Message);
return(new Cyclist());
}
}
public int Add(User user)
{
if (DoesUserExist(user))
{
MessageBox.Show("Gebruiker bestaat al, gebruik a.u.b een ander e-mail adres");
return(0);
}
const string query = "INSERT INTO users " +
"(name,email,password,credentials) " +
"VALUES (@name, @email, @password, @credentials); " +
"SELECT SCOPE_IDENTITY();";
var queryData = new SqlQueryData(query);
queryData.AddParameter("@name", SqlDbType.VarChar, user.Name);
queryData.AddParameter("@email", SqlDbType.VarChar, user.Email);
queryData.AddParameter("@password", SqlDbType.VarChar, user.Password);
queryData.AddParameter("@credentials", SqlDbType.Int, user.Credentials.ToString());
try
{
return(crud.Create(queryData));
}
catch (Exception e)
{
MessageBox.Show("Unable to register new user. " + e.Message, "RegisterNewUser error type: " +
e.GetType(), MessageBoxButton.OK,
MessageBoxImage.Error);
Console.WriteLine(e.StackTrace);
return(0);
}
}
public List <Cyclist> GetAll()
{
var queryData = new SqlQueryData("SELECT * FROM cyclists", QueryType.Reader);
var allCyclists = new List <Cyclist>();
try
{
SqlDataReader reader = crud.Get(queryData);
while (reader.Read())
{
var cyclist = new Cyclist(reader.GetInt32(0), reader.GetString(1), reader.GetInt32(2));
allCyclists.Add(cyclist);
}
reader.Close();
return(allCyclists);
}
catch (Exception e)
{
MessageBox.Show("Unable to retrieve team. " + e.Message, "CyclistRegistration team error type: " +
e.GetType(), MessageBoxButton.OK, MessageBoxImage.Error);
Console.WriteLine(e.StackTrace);
Console.WriteLine(e.Message);
return(allCyclists);
}
}
public CyclistTeam Get(int id)
{
var queryData = new SqlQueryData("SELECT * FROM cyclist_team WHERE [id] = @id", QueryType.Reader);
queryData.AddParameter("@id", SqlDbType.VarChar, Convert.ToString(id));
var builder = new CyclistTeamBuilder();
CyclistTeam team = null;
try
{
SqlDataReader reader = crud.Get(queryData);
while (reader.Read())
{
team = builder
.SetId(id)
.SetName(reader.GetString(1))
.SetCountry(reader.GetString(2))
.Build();
}
return(team ?? new CyclistTeam());
}
catch (Exception e)
{
MessageBox.Show("Unable to retrieve team. " + e.Message, "CyclistRegistration team error type: " +
e.GetType(), MessageBoxButton.OK, MessageBoxImage.Error);
Console.WriteLine(e.StackTrace);
Console.WriteLine(e.Message);
return(new CyclistTeam());
}
}
public List <CyclistTeam> GetAll()
{
var queryData = new SqlQueryData("SELECT * FROM cyclist_team", QueryType.Reader);
var builder = new CyclistTeamBuilder();
var allTeams = new List <CyclistTeam>();
try
{
SqlDataReader reader = crud.Get(queryData);
while (reader.Read())
{
CyclistTeam team = builder
.SetId(reader.GetInt32(0))
.SetName(reader.GetString(1))
.SetCountry(reader.GetString(2))
.Build();
allTeams.Add(team);
}
reader.Close();
return(allTeams);
}
catch (Exception e)
{
MessageBox.Show("Unable to retrieve team. " + e.Message, "CyclistRegistration team error type: " +
e.GetType(), MessageBoxButton.OK, MessageBoxImage.Error);
Console.WriteLine(e.StackTrace);
Console.WriteLine(e.Message);
return(allTeams);
}
}
public bool Delete(int userId)
{
var queryData = new SqlQueryData("DELETE FROM users WHERE id = @userId;");
queryData.AddParameter("@userId", SqlDbType.Int, userId.ToString());
return(crud.Delete(queryData));
}
public bool Delete(int cyclistId)
{
var queryData = new SqlQueryData("DELETE FROM cyclists WHERE id = @cyclistId; ");
queryData.AddParameter("@cyclistId", SqlDbType.Int, cyclistId.ToString());
return(crud.Delete(queryData));
}
public bool Update(Cyclist cyclist)
{
var queryData = new SqlQueryData("UPDATE cyclists SET name = @name WHERE id = @cyclistId; ");
queryData.AddParameter("@name", SqlDbType.VarChar, cyclist.Name);
queryData.AddParameter("@cyclistId", SqlDbType.Int, cyclist.Id.ToString());
return(crud.Update(queryData));
}
public object InnerJoin(SqlQueryData a, SqlQueryData b, string aKeyName, string bKeyName)
{
var query = from aRow in a.Data
from bRow in b.Data
where JsonEquals(aRow[aKeyName], bRow[bKeyName])
select new Dictionary <string, object> ()
{
[a.Name] = aRow, [b.Name] = bRow
};
return(query);
}
public bool Update(CyclistTeam team)
{
const string query = "UPDATE cyclist_team " +
"SET name = @name, country = @country " +
"WHERE id = @cyclistId; ";
var queryData = new SqlQueryData(query);
queryData.AddParameter("@name", SqlDbType.VarChar, team.Name);
queryData.AddParameter("@country", SqlDbType.VarChar, team.Country);
queryData.AddParameter("@cyclistId", SqlDbType.Int, team.Id.ToString());
return(crud.Update(queryData));
}
public bool Update(User user)
{
const string query = "UPDATE users " +
"SET name = @name, email = @email, password = @password, credentials = @credentials " +
"WHERE id = @userId; ";
var queryData = new SqlQueryData(query);
queryData.AddParameter("@name", SqlDbType.VarChar, user.Name);
queryData.AddParameter("@email", SqlDbType.VarChar, user.Email);
queryData.AddParameter("@password", SqlDbType.VarChar, user.Password);
queryData.AddParameter("@credentials", SqlDbType.Int, user.Credentials.ToString());
queryData.AddParameter("@userId", SqlDbType.Int, user.Id.ToString());
return(crud.Update(queryData));
}
public User ValidateCredentials(string email, string password)
{
var queryData = new SqlQueryData("SELECT * FROM users WHERE [email] = @email AND [password] = @password", QueryType.Reader);
queryData.AddParameter("@email", SqlDbType.VarChar, email);
queryData.AddParameter("@password", SqlDbType.VarChar, password);
var userBuilder = new UserBuilder();
SqlDataReader reader = crud.Get(queryData);
try
{
if (reader != null)
{
while (reader.Read())
{
User user = userBuilder
.SetId(reader.GetInt32(0))
.SetCredentials(reader.GetInt32(1))
.SetName(reader.GetString(2))
.SetEmail(reader.GetString(3))
.Build();
reader.Close();
return(user);
}
reader.Close();
}
throw new UnauthorizedAccessException("Username or password incorrect");
}
catch (Exception e)
{
MessageBox.Show("Unable to login. " + e.Message, "IsUserPassword error type: " +
e.GetType(), MessageBoxButton.OK,
MessageBoxImage.Error);
Console.WriteLine(e.StackTrace);
Console.WriteLine(e.Message);
return(new User());
}
}
public int Add(Cyclist cyclist)
{
var queryData = new SqlQueryData("INSERT INTO cyclists (name, cyclist_team_id) VALUES (@name, @teamId); SELECT SCOPE_IDENTITY();");
queryData.AddParameter("@name", SqlDbType.VarChar, cyclist.Name);
queryData.AddParameter("@teamId", SqlDbType.Int, cyclist.CyclistTeamId.ToString());
try
{
return(crud.Create(queryData));
}
catch (Exception e)
{
MessageBox.Show("Unable to register new CyclistRegistration. " + e.Message, "Register new CyclistRegistration error type: " +
e.GetType(), MessageBoxButton.OK, MessageBoxImage.Error);
Console.WriteLine(e.StackTrace);
return(0);
}
}
public User Get(int id)
{
var queryData = new SqlQueryData("SELECT * FROM users WHERE [id] = @id", QueryType.Reader);
queryData.AddParameter("@id", SqlDbType.VarChar, Convert.ToString(id));
var userBuilder = new UserBuilder();
try
{
SqlDataReader reader = crud.Get(queryData);
while (reader.Read())
{
User user = userBuilder
.SetId(id)
.SetCredentials(reader.GetInt32(1))
.SetName(reader.GetString(2))
.SetEmail(reader.GetString(3))
.SetPassword(reader.GetString(4))
.Build();
reader.Close();
return(user);
}
reader.Close();
return(new User());
}
catch (Exception e)
{
MessageBox.Show("Unable to retrieve user. " + e.Message, "IsUserPassword error type: " +
e.GetType(), MessageBoxButton.OK,
MessageBoxImage.Error);
Console.WriteLine(e.StackTrace);
Console.WriteLine(e.Message);
return(new User());
}
}
private bool DoesUserExist(User user)
{
try
{
var queryData = new SqlQueryData("SELECT COUNT(*) FROM users WHERE [email] = @email", QueryType.Scalar);
queryData.AddParameter("@email", SqlDbType.VarChar, Convert.ToString(user.Email));
int foundUsers = crud.GetSingleValue(queryData);
return((foundUsers > 0) ? true : false);
}
catch (Exception e)
{
MessageBox.Show("Unable to register user. " + e.Message, " error type: " +
e.GetType(), MessageBoxButton.OK,
MessageBoxImage.Error);
Console.WriteLine(e.StackTrace);
Console.WriteLine(e.Message);
return(true);
}
}
public int Add(CyclistTeam team)
{
const string query = "INSERT INTO cyclist_team " +
"(name, country) " +
"VALUES (@name, @country); " +
"SELECT SCOPE_IDENTITY();";
var queryData = new SqlQueryData(query);
queryData.AddParameter("@name", SqlDbType.VarChar, team.Name);
queryData.AddParameter("@country", SqlDbType.VarChar, team.Country);
try
{
return(crud.Create(queryData));
}
catch (Exception e)
{
MessageBox.Show("Unable to register new CyclistRegistration team. " + e.Message, "Register new team error type: " +
e.GetType(), MessageBoxButton.OK, MessageBoxImage.Error);
Console.WriteLine(e.StackTrace);
return(0);
}
}
