/// <summary>
/// Reads all the Session data rows.
/// </summary>
public async Task <SessionDataRow[]> ReadAll(IDatabaseConnection databaseConnection)
{
// Build the SQL command.
using (SqlCommand sqlCommand = new SqlCommand("SELECT * FROM [Session];"))
{
// Use the specified database connection.
SqlConnection sqlConnection = (databaseConnection as DatabaseConnection).SqlConnection;
sqlCommand.Connection = sqlConnection;
// Execute the SQL command.
using (SqlDataReader sqlDataReader = await sqlCommand.ExecuteReaderAsync())
{
// Read the Session data rows.
List <SessionDataRow> sessionDataRows = new List <SessionDataRow>();
while (await sqlDataReader.ReadAsync())
{
SessionDataRow sessionDataRow = this.GetSqlDataReaderValues(sqlDataReader);
sessionDataRows.Add(sessionDataRow);
}
// Return the Session data rows.
return(sessionDataRows.ToArray());
}
}
}
/// <summary>
/// Reads the single Session data row matching the specified SessionID.
/// </summary>
public async Task <SessionDataRow> ReadBySessionID(IDatabaseConnection databaseConnection, int sessionID)
{
// Build the SQL command.
using (SqlCommand sqlCommand = new SqlCommand("SELECT * FROM [Session] WHERE [SessionID] = @sessionID;"))
{
// Use the specified database connection.
SqlConnection sqlConnection = (databaseConnection as DatabaseConnection).SqlConnection;
sqlCommand.Connection = sqlConnection;
// Set the SQL command parameter values.
sqlCommand.Parameters.Add("@sessionID", SqlDbType.Int).Value = sessionID;
// Execute the SQL command.
using (SqlDataReader sqlDataReader = await sqlCommand.ExecuteReaderAsync())
{
// Read the Session data row.
SessionDataRow sessionDataRow = null;
if (await sqlDataReader.ReadAsync())
{
sessionDataRow = this.GetSqlDataReaderValues(sqlDataReader);
}
// Return the Session data row.
return(sessionDataRow);
}
}
}
public void Create_ShouldThrowException_GivenDuplicateSessionCode()
{
// Insert the duplicate Session data row in the database.
int sessionID = SessionTestTable.InsertPlaceholder(sessionCode: "6dk61ufcuzp3f7vs");
// Build the Session data row.
SessionDataRow sessionDataRow = new SessionDataRow();
sessionDataRow.SessionCode = "6dk61ufcuzp3f7vs";
sessionDataRow.Name = "Session Alpha";
sessionDataRow.StartDate = new DateTime(2001, 1, 1);
// Build the database connection.
using (DatabaseConnection databaseConnection = new DatabaseConnection(TestDatabase.ConnectionString))
{
// Open the database connection.
databaseConnection.Open().Wait();
try
{
// Create the Session data row.
SessionDataAccessComponent sessionDataAccessComponent = new SessionDataAccessComponent();
sessionDataAccessComponent.Create(databaseConnection, sessionDataRow).Wait();
// Validate an exception was thrown.
Assert.Fail();
}
catch (AggregateException ex)
{
// Validate an SQL exception was thrown.
Assert.IsInstanceOfType(ex.InnerExceptions[0], typeof(SqlException));
}
}
}
public void Delete_ShouldSucceed()
{
// Insert the Session data row in the database.
int sessionID = SessionTestTable.InsertWithValues(
"6dk61ufcuzp3f7vs",
"Session Alpha",
new DateTime(2001, 1, 1));
// Build the Session data row.
SessionDataRow sessionDataRow = new SessionDataRow();
sessionDataRow.SessionID = sessionID;
sessionDataRow.SessionCode = "6dk61ufcuzp3f7vs";
sessionDataRow.Name = "Session Alpha";
sessionDataRow.StartDate = new DateTime(2001, 1, 1);
// Build the database connection.
using (DatabaseConnection databaseConnection = new DatabaseConnection(TestDatabase.ConnectionString))
{
// Open the database connection.
databaseConnection.Open().Wait();
// Delete the Session data row.
SessionDataAccessComponent sessionDataAccessComponent = new SessionDataAccessComponent();
sessionDataAccessComponent.Delete(databaseConnection, sessionDataRow).Wait();
}
// Validate the Session data row was deleted in the database.
SessionTestTable.AssertAbsence(sessionID);
}
public void Create_ShouldSucceed()
{
// Build the Session data row.
SessionDataRow sessionDataRow = new SessionDataRow();
sessionDataRow.SessionCode = "6dk61ufcuzp3f7vs";
sessionDataRow.Name = "Session Alpha";
sessionDataRow.StartDate = new DateTime(2001, 1, 1);
// Build the database connection.
using (DatabaseConnection databaseConnection = new DatabaseConnection(TestDatabase.ConnectionString))
{
// Open the database connection.
databaseConnection.Open().Wait();
// Create the Session data row.
SessionDataAccessComponent sessionDataAccessComponent = new SessionDataAccessComponent();
sessionDataAccessComponent.Create(databaseConnection, sessionDataRow).Wait();
}
// Validate the SessionID was generated.
Assert.AreNotEqual(0, sessionDataRow.SessionID);
// Validate the Session data row was inserted in the database.
SessionTestTable.AssertPresence(
sessionDataRow.SessionID,
"6dk61ufcuzp3f7vs",
"Session Alpha",
new DateTime(2001, 1, 1));
}
public void ReadBySessionCode_ShouldReturnDataRow()
{
// Insert the Session data row in the database.
int sessionID = SessionTestTable.InsertWithValues(
"6dk61ufcuzp3f7vs",
"Session Alpha",
new DateTime(2001, 1, 1));
// Build the database connection.
SessionDataRow sessionDataRow = null;
using (DatabaseConnection databaseConnection = new DatabaseConnection(TestDatabase.ConnectionString))
{
// Open the database connection.
databaseConnection.Open().Wait();
// Read the Session data row.
SessionDataAccessComponent sessionDataAccessComponent = new SessionDataAccessComponent();
sessionDataRow = sessionDataAccessComponent.ReadBySessionCode(databaseConnection, "6dk61ufcuzp3f7vs").Result;
}
// Validate the Session data row.
Assert.IsNotNull(sessionDataRow);
Assert.AreEqual(sessionID, sessionDataRow.SessionID);
Assert.AreEqual("6dk61ufcuzp3f7vs", sessionDataRow.SessionCode);
Assert.AreEqual("Session Alpha", sessionDataRow.Name);
Assert.AreEqual(new DateTime(2001, 1, 1), sessionDataRow.StartDate);
}
/// <summary>
/// Sets the parameter values on the specified SQL command.
/// </summary>
private void SetSqlCommandParameterValues(SqlCommand sqlCommand, SessionDataRow sessionDataRow, bool setPrimaryKeyValue)
{
// Set the primary key if requested.
if (setPrimaryKeyValue)
{
sqlCommand.Parameters.Add("@sessionID", SqlDbType.Int).Value = sessionDataRow.SessionID;
}
// Set the other parameters.
sqlCommand.Parameters.Add("@sessionCode", SqlDbType.NVarChar, 50).Value = sessionDataRow.SessionCode;
sqlCommand.Parameters.Add("@name", SqlDbType.NVarChar, 50).Value = sessionDataRow.Name;
sqlCommand.Parameters.Add("@startDate", SqlDbType.Date).Value = sessionDataRow.StartDate;
}
/// <summary>
/// Gets the values from the specified SQL data reader.
/// </summary>
private SessionDataRow GetSqlDataReaderValues(SqlDataReader sqlDataReader)
{
// Build the Session data row.
SessionDataRow sessionDataRow = new SessionDataRow();
// Read the values.
sessionDataRow.SessionID = (int)sqlDataReader["SessionID"];
sessionDataRow.SessionCode = (string)sqlDataReader["SessionCode"];
sessionDataRow.Name = (string)sqlDataReader["Name"];
sessionDataRow.StartDate = (DateTime)sqlDataReader["StartDate"];
// Return the Session data row.
return(sessionDataRow);
}
/// <summary>
/// Deletes the specified Session data row.
/// </summary>
public async Task Delete(IDatabaseConnection databaseConnection, SessionDataRow sessionDataRow)
{
// Build the SQL command.
using (SqlCommand sqlCommand = new SqlCommand("DELETE FROM [Session] WHERE [SessionID] = @sessionID;"))
{
// Use the specified database connection.
SqlConnection sqlConnection = (databaseConnection as DatabaseConnection).SqlConnection;
sqlCommand.Connection = sqlConnection;
// Set the SQL command parameter values.
sqlCommand.Parameters.Add("@sessionID", SqlDbType.Int).Value = sessionDataRow.SessionID;
// Execute the SQL command.
await sqlCommand.ExecuteNonQueryAsync();
}
}
/// <summary>
/// Updates the specified Session data row.
/// </summary>
public async Task Update(IDatabaseConnection databaseConnection, SessionDataRow sessionDataRow)
{
// Build the SQL command.
using (SqlCommand sqlCommand = new SqlCommand("UPDATE [Session] SET [SessionCode] = @sessionCode, [Name] = @name, [StartDate] = @startDate WHERE [SessionID] = @sessionID;"))
{
// Use the specified database connection.
SqlConnection sqlConnection = (databaseConnection as DatabaseConnection).SqlConnection;
sqlCommand.Connection = sqlConnection;
// Set the SQL command parameter values.
this.SetSqlCommandParameterValues(sqlCommand, sessionDataRow, setPrimaryKeyValue: true);
// Execute the SQL command.
await sqlCommand.ExecuteNonQueryAsync();
}
}
/// <summary>
/// Creates the specified Session data row.
/// </summary>
public async Task Create(IDatabaseConnection databaseConnection, SessionDataRow sessionDataRow)
{
// Build the SQL command.
using (SqlCommand sqlCommand = new SqlCommand("INSERT INTO [Session] VALUES (@sessionCode, @name, @startDate); SELECT CAST(SCOPE_IDENTITY() AS INT);"))
{
// Use the specified database connection.
SqlConnection sqlConnection = (databaseConnection as DatabaseConnection).SqlConnection;
sqlCommand.Connection = sqlConnection;
// Set the SQL command parameter values.
this.SetSqlCommandParameterValues(sqlCommand, sessionDataRow, setPrimaryKeyValue: false);
// Execute the SQL command.
int sessionID = (int)await sqlCommand.ExecuteScalarAsync();
// Assign the generated SessionID.
sessionDataRow.SessionID = sessionID;
}
}
