/// <summary>
/// Deletes the user with the specified user id.
///
/// This method assumes that the MySQL database contains a stored procedure that deleted the row with the given user id that has the following signature:
///
/// CREATE PROCEDURE 'delete_user' (IN userId BIGINT UNSIGNED)
///
/// </summary>
/// <param name="userId">The user id of the user to be deleted.</param>
/// <returns></returns>
public async Task DeleteUserAsync(ulong userId)
{
using (IDataAccessResult result = await _builder.CreateStoredProcedureCommand(DeleteUserProcedureName)
.WithInputParameter(UserIdParameterName, MySqlDbType.UInt64, userId)
.ExecuteNonQueryAsync())
{
}
}
/// <summary>
/// Adds a record to the user table with the specified information and returns the user id of the
/// new user.
///
/// This method assumes that the MySQL database contains a stored procedure that inserts a new user and has the following signature:
///
/// CREATE PROCEDURE 'create_user' (IN userName VARCHAR(80), IN emailAddress VARCHAR(150), IN age TINYINT UNSIGNED, IN dateOfBirth DATETIME, OUT userId BIGINT UNSIGNED)
///
/// </summary>
/// <param name="userName">The username of the new user.</param>
/// <param name="emailAddress">The email address of the new user.</param>
/// <param name="age">The age of the new user.</param>
/// <param name="dateOfBirth">The date of birth of the new user.</param>
/// <returns>The user id of the new user.</returns>
public async Task <ulong> CreateUserAsync(string userName, string emailAddress, ushort age, DateTime dateOfBirth)
{
using (IDataAccessResult result = await _builder.CreateStoredProcedureCommand(CreateUserProcedureName)
.WithInputParameter(UserNameParameterName, MySqlDbType.UInt64, userName)
.WithInputParameter(EmailAddressParameterName, MySqlDbType.VarString, emailAddress)
.WithInputParameter(AgeColumnName, MySqlDbType.VarString, age)
.WithInputParameter(DateOfBirthColumnName, MySqlDbType.VarString, dateOfBirth)
.WithOutputParameter(UserIdParameterName, MySqlDbType.UInt64)
.ExecuteNonQueryAsync())
{
return(Convert.ToUInt64(result.Command.Parameters[UserIdParameterName].Value));
}
}
/// <summary>
/// Retrieves the user with the given user id, null otherwise.
///
/// This method assumes that the MySQL database contains a stored procedure that selects the row with the given user id that has the following signature:
///
/// CREATE PROCEDURE 'get_user' (IN userId BIGINT UNSIGNED)
///
/// </summary>
/// <param name="userId">The user id of the user that will be retrieved.</param>
/// <returns>The user with the given user id, null otherwise.</returns>
public async Task <User> GetUserAsync(ulong userId)
{
User user = null;
using (IDataAccessResult result = await _builder.CreateStoredProcedureCommand(GetUserProcedureName)
.WithInputParameter(UserIdParameterName, MySqlDbType.UInt64, userId)
.ExecuteReaderAsync())
{
DbDataReader reader = result.Reader;
if (await reader.ReadAsync()) // If you need to iterate over a collection of results you could easily change this to a while loop.
{
string userName = Convert.ToString(reader[UserNameColumnName]);
string emailAddress = Convert.ToString(reader[EmailAddressColumnName]);
ushort age = Convert.ToUInt16(reader[AgeColumnName]);
DateTime dateOfBirth = Convert.ToDateTime(reader[DateOfBirthColumnName]);
user = new User(userId, userName, emailAddress, age, dateOfBirth);
}
}
return(user);
}
