public User GetUserByEmail(string theEmail)
{
try
{
Connection.Open();
var aPreparedCommand =
new NpgsqlCommand(
"SELECT id, firstname, lastname, email, customerid, isemployee from appuser where email = :value1", Connection);
var aParam = new NpgsqlParameter("value1", NpgsqlDbType.Text) { Value = theEmail };
aPreparedCommand.Parameters.Add(aParam);
var aReader = aPreparedCommand.ExecuteReader();
if (!aReader.HasRows)
return null;
var aReturn = new User();
while (aReader.Read())
{
aReturn = ReadUser(aReader);
}
return aReturn;
}
catch (NpgsqlException)
{
return null;
}
catch (InvalidOperationException)
{
return null;
}
catch (SqlException)
{
return null;
}
catch (ConfigurationErrorsException)
{
return null;
}
finally
{
if (Connection.State == ConnectionState.Open)
Connection.Close();
}
}
public async Task DeleteUserAsync(User user)
{
await _userRepository.DeleteUserAsync(user);
}
public async Task UpdateUserAsync(User user)
{
await _userRepository.UpdateUserAsync(user);
}
public async Task<int> AddUserAsync(User user)
{
return await _userRepository.AddUserAsync(user);
}
private static User ReadUser(IDataRecord aReader)
{
var aReturn = new User
{
Id = Convert.ToInt32(aReader["id"]),
FirstName = Convert.ToString(aReader["firstname"]),
LastName = Convert.ToString(aReader["lastname"]),
Email = Convert.ToString(aReader["email"]),
CustomerId = Convert.ToInt32(aReader["customerid"]),
IsEmployee = Convert.ToBoolean(aReader["isemployee"])
};
return aReturn;
}
public async Task UpdateUserAsync(User theUser)
{
try
{
await Connection.OpenAsync().ConfigureAwait(false);
var aCommand = new NpgsqlCommand(
"UPDATE appuser SET firstname = :value1, lastname = :value2, email = :value3, customerid = :value4, isemployee = :value5 where id=:value6;", Connection);
aCommand.Parameters.AddWithValue("value1", theUser.FirstName);
aCommand.Parameters.AddWithValue("value2", theUser.LastName);
aCommand.Parameters.AddWithValue("value3", theUser.Email);
aCommand.Parameters.AddWithValue("value4", theUser.CustomerId);
aCommand.Parameters.AddWithValue("value5", theUser.IsEmployee);
aCommand.Parameters.AddWithValue("value6", theUser.Id);
await aCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
}
// no catch here, this is a reference project
// TODO: add catch and actions here
finally
{
if (Connection.State == ConnectionState.Open)
Connection.Close();
}
}
public async Task DeleteUserAsync(User theUser)
{
try
{
await Connection.OpenAsync().ConfigureAwait(false);
var aCommand = new NpgsqlCommand("DELETE from appuser where id=:value1", Connection);
aCommand.Parameters.AddWithValue("value1", theUser.Id);
await aCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
}
// no catch here, this is a reference project
// TODO: add catch and actions here
finally
{
if (Connection.State == ConnectionState.Open)
Connection.Close();
}
}
public async Task<User> GetUserAsync(int theUserId)
{
try
{
await Connection.OpenAsync().ConfigureAwait(false);
var aPreparedCommand =
new NpgsqlCommand(
"SELECT id, firstname, lastname, email, customerid, isemployee from appuser where id = :value1", Connection);
var aParam = new NpgsqlParameter("value1", NpgsqlDbType.Integer) { Value = theUserId };
aPreparedCommand.Parameters.Add(aParam);
var aReader = await aPreparedCommand.ExecuteReaderAsync().ConfigureAwait(false);
if (!aReader.HasRows)
return null;
var aReturn = new User();
while (await aReader.ReadAsync().ConfigureAwait(false))
{
aReturn = ReadUser(aReader);
}
return aReturn;
}
catch (NpgsqlException)
{
return null;
}
catch (InvalidOperationException)
{
return null;
}
catch (SqlException)
{
return null;
}
catch (ConfigurationErrorsException)
{
return null;
}
finally
{
if (Connection.State == ConnectionState.Open)
Connection.Close();
}
}
public async Task<int> AddUserAsync(User theUser)
{
try
{
await Connection.OpenAsync().ConfigureAwait(false);
var aCommand = new NpgsqlCommand(
"Insert into appuser (firstname, lastname, email, customerid, isemployee) VALUES (:value1, :value2, :value3, :value4, :value5) RETURNING id", Connection);
aCommand.Parameters.AddWithValue("value1", theUser.FirstName);
aCommand.Parameters.AddWithValue("value2", theUser.LastName);
aCommand.Parameters.AddWithValue("value3", theUser.Email);
aCommand.Parameters.AddWithValue("value4", theUser.CustomerId);
aCommand.Parameters.AddWithValue("value5", theUser.IsEmployee);
// returns the id from the SELECT, RETURNING sql statement above
return Convert.ToInt32(await aCommand.ExecuteScalarAsync().ConfigureAwait(false));
}
catch (NpgsqlException)
{
return 0;
}
catch (InvalidOperationException)
{
return 0;
}
catch (SqlException)
{
return 0;
}
catch (ConfigurationErrorsException)
{
return 0;
}
finally
{
if (Connection.State == ConnectionState.Open)
Connection.Close();
}
}
public async Task DeleteUserAsync(User theUser)
{
await ConnectionAsync.DeleteAsync(theUser.ToDTO());
}
public void DeleteUser(User theUser)
{
try
{
Connection.Open();
var aCommand = new NpgsqlCommand("DELETE from appuser where id=:value1", Connection);
aCommand.Parameters.AddWithValue("value1", theUser.Id);
aCommand.ExecuteNonQuery();
}
// no catch here, this is a reference project
// TODO: add catch and actions here
finally
{
if (Connection.State == ConnectionState.Open)
Connection.Close();
}
}
public async Task UpdateUserAsync(User theUser)
{
await ConnectionAsync.UpdateAsync(theUser.ToDTO());
}
public async Task<int> AddUserAsync(User theUser)
{
return await ConnectionAsync.InsertAsync(theUser.ToDTO());
}
public void DeleteUser(User theUser)
{
Connection.Delete<RenterDTO>(theUser.Id);
}
public void UpdateUser(User theUser)
{
Connection.Update(theUser.ToDTO());
}
public int AddUser(User theUser)
{
return Connection.Insert(theUser.ToDTO());
}