public void Delete_ShouldSucceed()
{
// Insert the master data rows in the database.
int sessionID = SessionTestTable.InsertPlaceholder();
// Insert the CourseSchedule data row in the database.
int courseScheduleID = CourseScheduleTestTable.InsertWithValues(
"zzcj32kpd6huzp1n",
sessionID,
1,
new TimeSpan(9, 15, 0));
// Build the CourseSchedule data row.
CourseScheduleDataRow courseScheduleDataRow = new CourseScheduleDataRow();
courseScheduleDataRow.CourseScheduleID = courseScheduleID;
courseScheduleDataRow.CourseScheduleCode = "zzcj32kpd6huzp1n";
courseScheduleDataRow.SessionID = sessionID;
courseScheduleDataRow.DayOfWeek = 1;
courseScheduleDataRow.Time = new TimeSpan(9, 15, 0);
// Build the database connection.
using (DatabaseConnection databaseConnection = new DatabaseConnection(TestDatabase.ConnectionString))
{
// Open the database connection.
databaseConnection.Open().Wait();
// Delete the CourseSchedule data row.
CourseScheduleDataAccessComponent courseScheduleDataAccessComponent = new CourseScheduleDataAccessComponent();
courseScheduleDataAccessComponent.Delete(databaseConnection, courseScheduleDataRow).Wait();
}
// Validate the CourseSchedule data row was deleted in the database.
CourseScheduleTestTable.AssertAbsence(courseScheduleID);
}
public void Create_ShouldThrowException_GivenInvalidSessionID()
{
// Insert the master data rows in the database.
int sessionID = SessionTestTable.InsertPlaceholder();
// Build the CourseSchedule data row.
CourseScheduleDataRow courseScheduleDataRow = new CourseScheduleDataRow();
courseScheduleDataRow.CourseScheduleCode = "zzcj32kpd6huzp1n";
courseScheduleDataRow.SessionID = -1;
courseScheduleDataRow.DayOfWeek = 1;
courseScheduleDataRow.Time = new TimeSpan(9, 15, 0);
// Build the database connection.
using (DatabaseConnection databaseConnection = new DatabaseConnection(TestDatabase.ConnectionString))
{
// Open the database connection.
databaseConnection.Open().Wait();
try
{
// Create the CourseSchedule data row.
CourseScheduleDataAccessComponent courseScheduleDataAccessComponent = new CourseScheduleDataAccessComponent();
courseScheduleDataAccessComponent.Create(databaseConnection, courseScheduleDataRow).Wait();
// Validate an exception was thrown.
Assert.Fail();
}
catch (AggregateException ex)
{
// Validate an SQL exception was thrown.
Assert.IsInstanceOfType(ex.InnerExceptions[0], typeof(SqlException));
}
}
}
/// <summary>
/// Reads the single CourseSchedule data row matching the specified CourseScheduleID.
/// </summary>
public async Task <CourseScheduleDataRow> ReadByCourseScheduleID(IDatabaseConnection databaseConnection, int courseScheduleID)
{
// Build the SQL command.
using (SqlCommand sqlCommand = new SqlCommand("SELECT * FROM [CourseSchedule] WHERE [CourseScheduleID] = @courseScheduleID;"))
{
// Use the specified database connection.
SqlConnection sqlConnection = (databaseConnection as DatabaseConnection).SqlConnection;
sqlCommand.Connection = sqlConnection;
// Set the SQL command parameter values.
sqlCommand.Parameters.Add("@courseScheduleID", SqlDbType.Int).Value = courseScheduleID;
// Execute the SQL command.
using (SqlDataReader sqlDataReader = await sqlCommand.ExecuteReaderAsync())
{
// Read the CourseSchedule data row.
CourseScheduleDataRow courseScheduleDataRow = null;
if (await sqlDataReader.ReadAsync())
{
courseScheduleDataRow = this.GetSqlDataReaderValues(sqlDataReader);
}
// Return the CourseSchedule data row.
return(courseScheduleDataRow);
}
}
}
public void ReadByCourseScheduleCode_ShouldReturnNull()
{
// Insert the master data rows in the database.
int sessionID = SessionTestTable.InsertPlaceholder();
// Insert the CourseSchedule data row in the database.
int courseScheduleID = CourseScheduleTestTable.InsertWithValues(
"zzcj32kpd6huzp1n",
sessionID,
1,
new TimeSpan(9, 15, 0));
// Build the database connection.
CourseScheduleDataRow courseScheduleDataRow = null;
using (DatabaseConnection databaseConnection = new DatabaseConnection(TestDatabase.ConnectionString))
{
// Open the database connection.
databaseConnection.Open().Wait();
// Read the CourseSchedule data row.
CourseScheduleDataAccessComponent courseScheduleDataAccessComponent = new CourseScheduleDataAccessComponent();
courseScheduleDataRow = courseScheduleDataAccessComponent.ReadByCourseScheduleCode(databaseConnection, "").Result;
}
// Validate the CourseSchedule data row.
Assert.IsNull(courseScheduleDataRow);
}
/// <summary>
/// Sets the parameter values on the specified SQL command.
/// </summary>
private void SetSqlCommandParameterValues(SqlCommand sqlCommand, CourseScheduleDataRow courseScheduleDataRow, bool setPrimaryKeyValue)
{
// Set the primary key if requested.
if (setPrimaryKeyValue)
{
sqlCommand.Parameters.Add("@courseScheduleID", SqlDbType.Int).Value = courseScheduleDataRow.CourseScheduleID;
}
// Set the other parameters.
sqlCommand.Parameters.Add("@courseScheduleCode", SqlDbType.NVarChar, 50).Value = courseScheduleDataRow.CourseScheduleCode;
sqlCommand.Parameters.Add("@sessionID", SqlDbType.Int).Value = courseScheduleDataRow.SessionID;
sqlCommand.Parameters.Add("@dayOfWeek", SqlDbType.Int).Value = courseScheduleDataRow.DayOfWeek;
sqlCommand.Parameters.Add("@time", SqlDbType.Time).Value = courseScheduleDataRow.Time;
}
public void Update_ShouldThrowException_GivenDuplicateCourseScheduleCode()
{
// Insert the first master data rows in the database.
int firstSessionID = SessionTestTable.InsertPlaceholder();
// Insert the second master data rows in the database.
int secondSessionID = SessionTestTable.InsertPlaceholder();
// Insert the CourseSchedule data row in the database.
int courseScheduleID = CourseScheduleTestTable.InsertWithValues(
"zzcj32kpd6huzp1n",
firstSessionID,
1,
new TimeSpan(9, 15, 0));
// Insert the duplicate CourseSchedule data row in the database.
int duplicateCourseScheduleID = CourseScheduleTestTable.InsertPlaceholder(courseScheduleCode: "8zu96quwvk70ng1e");
// Build the CourseSchedule data row.
CourseScheduleDataRow courseScheduleDataRow = new CourseScheduleDataRow();
courseScheduleDataRow.CourseScheduleID = courseScheduleID;
courseScheduleDataRow.CourseScheduleCode = "8zu96quwvk70ng1e";
courseScheduleDataRow.SessionID = secondSessionID;
courseScheduleDataRow.DayOfWeek = 2;
courseScheduleDataRow.Time = new TimeSpan(10, 30, 0);
// Build the database connection.
using (DatabaseConnection databaseConnection = new DatabaseConnection(TestDatabase.ConnectionString))
{
// Open the database connection.
databaseConnection.Open().Wait();
try
{
// Update the CourseSchedule data row.
CourseScheduleDataAccessComponent courseScheduleDataAccessComponent = new CourseScheduleDataAccessComponent();
courseScheduleDataAccessComponent.Update(databaseConnection, courseScheduleDataRow).Wait();
// Validate an exception was thrown.
Assert.Fail();
}
catch (AggregateException ex)
{
// Validate an SQL exception was thrown.
Assert.IsInstanceOfType(ex.InnerExceptions[0], typeof(SqlException));
}
}
}
/// <summary>
/// Gets the values from the specified SQL data reader.
/// </summary>
private CourseScheduleDataRow GetSqlDataReaderValues(SqlDataReader sqlDataReader)
{
// Build the CourseSchedule data row.
CourseScheduleDataRow courseScheduleDataRow = new CourseScheduleDataRow();
// Read the values.
courseScheduleDataRow.CourseScheduleID = (int)sqlDataReader["CourseScheduleID"];
courseScheduleDataRow.CourseScheduleCode = (string)sqlDataReader["CourseScheduleCode"];
courseScheduleDataRow.SessionID = (int)sqlDataReader["SessionID"];
courseScheduleDataRow.DayOfWeek = (int)sqlDataReader["DayOfWeek"];
courseScheduleDataRow.Time = (TimeSpan)sqlDataReader["Time"];
// Return the CourseSchedule data row.
return(courseScheduleDataRow);
}
/// <summary>
/// Deletes the specified CourseSchedule data row.
/// </summary>
public async Task Delete(IDatabaseConnection databaseConnection, CourseScheduleDataRow courseScheduleDataRow)
{
// Build the SQL command.
using (SqlCommand sqlCommand = new SqlCommand("DELETE FROM [CourseSchedule] WHERE [CourseScheduleID] = @courseScheduleID;"))
{
// Use the specified database connection.
SqlConnection sqlConnection = (databaseConnection as DatabaseConnection).SqlConnection;
sqlCommand.Connection = sqlConnection;
// Set the SQL command parameter values.
sqlCommand.Parameters.Add("@courseScheduleID", SqlDbType.Int).Value = courseScheduleDataRow.CourseScheduleID;
// Execute the SQL command.
await sqlCommand.ExecuteNonQueryAsync();
}
}
/// <summary>
/// Updates the specified CourseSchedule data row.
/// </summary>
public async Task Update(IDatabaseConnection databaseConnection, CourseScheduleDataRow courseScheduleDataRow)
{
// Build the SQL command.
using (SqlCommand sqlCommand = new SqlCommand("UPDATE [CourseSchedule] SET [CourseScheduleCode] = @courseScheduleCode, [SessionID] = @sessionID, [DayOfWeek] = @dayOfWeek, [Time] = @time WHERE [CourseScheduleID] = @courseScheduleID;"))
{
// Use the specified database connection.
SqlConnection sqlConnection = (databaseConnection as DatabaseConnection).SqlConnection;
sqlCommand.Connection = sqlConnection;
// Set the SQL command parameter values.
this.SetSqlCommandParameterValues(sqlCommand, courseScheduleDataRow, setPrimaryKeyValue: true);
// Execute the SQL command.
await sqlCommand.ExecuteNonQueryAsync();
}
}
public void Update_ShouldSucceed()
{
// Insert the first master data rows in the database.
int firstSessionID = SessionTestTable.InsertPlaceholder();
// Insert the second master data rows in the database.
int secondSessionID = SessionTestTable.InsertPlaceholder();
// Insert the CourseSchedule data row in the database.
int courseScheduleID = CourseScheduleTestTable.InsertWithValues(
"zzcj32kpd6huzp1n",
firstSessionID,
1,
new TimeSpan(9, 15, 0));
// Build the CourseSchedule data row.
CourseScheduleDataRow courseScheduleDataRow = new CourseScheduleDataRow();
courseScheduleDataRow.CourseScheduleID = courseScheduleID;
courseScheduleDataRow.CourseScheduleCode = "8zu96quwvk70ng1e";
courseScheduleDataRow.SessionID = secondSessionID;
courseScheduleDataRow.DayOfWeek = 2;
courseScheduleDataRow.Time = new TimeSpan(10, 30, 0);
// Build the database connection.
using (DatabaseConnection databaseConnection = new DatabaseConnection(TestDatabase.ConnectionString))
{
// Open the database connection.
databaseConnection.Open().Wait();
// Update the CourseSchedule data row.
CourseScheduleDataAccessComponent courseScheduleDataAccessComponent = new CourseScheduleDataAccessComponent();
courseScheduleDataAccessComponent.Update(databaseConnection, courseScheduleDataRow).Wait();
}
// Validate the CourseSchedule data row was updated in the database.
CourseScheduleTestTable.AssertPresence(
courseScheduleID,
"8zu96quwvk70ng1e",
secondSessionID,
2,
new TimeSpan(10, 30, 0));
}
/// <summary>
/// Creates the specified CourseSchedule data row.
/// </summary>
public async Task Create(IDatabaseConnection databaseConnection, CourseScheduleDataRow courseScheduleDataRow)
{
// Build the SQL command.
using (SqlCommand sqlCommand = new SqlCommand("INSERT INTO [CourseSchedule] VALUES (@courseScheduleCode, @sessionID, @dayOfWeek, @time); 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, courseScheduleDataRow, setPrimaryKeyValue: false);
// Execute the SQL command.
int courseScheduleID = (int)await sqlCommand.ExecuteScalarAsync();
// Assign the generated CourseScheduleID.
courseScheduleDataRow.CourseScheduleID = courseScheduleID;
}
}
