/// <summary>
/// Reads the single CourseGroup data row matching the specified CourseGroupID.
/// </summary>
public async Task <CourseGroupDataRow> ReadByCourseGroupID(IDatabaseConnection databaseConnection, int courseGroupID)
{
// Build the SQL command.
using (SqlCommand sqlCommand = new SqlCommand("SELECT * FROM [CourseGroup] WHERE [CourseGroupID] = @courseGroupID;"))
{
// Use the specified database connection.
SqlConnection sqlConnection = (databaseConnection as DatabaseConnection).SqlConnection;
sqlCommand.Connection = sqlConnection;
// Set the SQL command parameter values.
sqlCommand.Parameters.Add("@courseGroupID", SqlDbType.Int).Value = courseGroupID;
// Execute the SQL command.
using (SqlDataReader sqlDataReader = await sqlCommand.ExecuteReaderAsync())
{
// Read the CourseGroup data row.
CourseGroupDataRow courseGroupDataRow = null;
if (await sqlDataReader.ReadAsync())
{
courseGroupDataRow = this.GetSqlDataReaderValues(sqlDataReader);
}
// Return the CourseGroup data row.
return(courseGroupDataRow);
}
}
}
public void Create_ShouldThrowException_GivenInvalidCourseScheduleID()
{
// Insert the master data rows in the database.
int courseScheduleID = CourseScheduleTestTable.InsertPlaceholder();
// Build the CourseGroup data row.
CourseGroupDataRow courseGroupDataRow = new CourseGroupDataRow();
courseGroupDataRow.CourseGroupCode = "5s1cgndj6e5x0uvz";
courseGroupDataRow.CourseScheduleID = -1;
courseGroupDataRow.PlacesCount = 1;
// Build the database connection.
using (DatabaseConnection databaseConnection = new DatabaseConnection(TestDatabase.ConnectionString))
{
// Open the database connection.
databaseConnection.Open().Wait();
try
{
// Create the CourseGroup data row.
CourseGroupDataAccessComponent courseGroupDataAccessComponent = new CourseGroupDataAccessComponent();
courseGroupDataAccessComponent.Create(databaseConnection, courseGroupDataRow).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 master data rows in the database.
int courseScheduleID = CourseScheduleTestTable.InsertPlaceholder();
// Insert the CourseGroup data row in the database.
int courseGroupID = CourseGroupTestTable.InsertWithValues(
"5s1cgndj6e5x0uvz",
courseScheduleID,
1);
// Build the CourseGroup data row.
CourseGroupDataRow courseGroupDataRow = new CourseGroupDataRow();
courseGroupDataRow.CourseGroupID = courseGroupID;
courseGroupDataRow.CourseGroupCode = "5s1cgndj6e5x0uvz";
courseGroupDataRow.CourseScheduleID = courseScheduleID;
courseGroupDataRow.PlacesCount = 1;
// Build the database connection.
using (DatabaseConnection databaseConnection = new DatabaseConnection(TestDatabase.ConnectionString))
{
// Open the database connection.
databaseConnection.Open().Wait();
// Delete the CourseGroup data row.
CourseGroupDataAccessComponent courseGroupDataAccessComponent = new CourseGroupDataAccessComponent();
courseGroupDataAccessComponent.Delete(databaseConnection, courseGroupDataRow).Wait();
}
// Validate the CourseGroup data row was deleted in the database.
CourseGroupTestTable.AssertAbsence(courseGroupID);
}
public void ReadByCourseGroupCode_ShouldReturnNull()
{
// Insert the master data rows in the database.
int courseScheduleID = CourseScheduleTestTable.InsertPlaceholder();
// Insert the CourseGroup data row in the database.
int courseGroupID = CourseGroupTestTable.InsertWithValues(
"5s1cgndj6e5x0uvz",
courseScheduleID,
1);
// Build the database connection.
CourseGroupDataRow courseGroupDataRow = null;
using (DatabaseConnection databaseConnection = new DatabaseConnection(TestDatabase.ConnectionString))
{
// Open the database connection.
databaseConnection.Open().Wait();
// Read the CourseGroup data row.
CourseGroupDataAccessComponent courseGroupDataAccessComponent = new CourseGroupDataAccessComponent();
courseGroupDataRow = courseGroupDataAccessComponent.ReadByCourseGroupCode(databaseConnection, "").Result;
}
// Validate the CourseGroup data row.
Assert.IsNull(courseGroupDataRow);
}
/// <summary>
/// Sets the parameter values on the specified SQL command.
/// </summary>
private void SetSqlCommandParameterValues(SqlCommand sqlCommand, CourseGroupDataRow courseGroupDataRow, bool setPrimaryKeyValue)
{
// Set the primary key if requested.
if (setPrimaryKeyValue)
{
sqlCommand.Parameters.Add("@courseGroupID", SqlDbType.Int).Value = courseGroupDataRow.CourseGroupID;
}
// Set the other parameters.
sqlCommand.Parameters.Add("@courseGroupCode", SqlDbType.NVarChar, 50).Value = courseGroupDataRow.CourseGroupCode;
sqlCommand.Parameters.Add("@courseScheduleID", SqlDbType.Int).Value = courseGroupDataRow.CourseScheduleID;
sqlCommand.Parameters.Add("@placesCount", SqlDbType.Int).Value = courseGroupDataRow.PlacesCount;
}
/// <summary>
/// Gets the values from the specified SQL data reader.
/// </summary>
private CourseGroupDataRow GetSqlDataReaderValues(SqlDataReader sqlDataReader)
{
// Build the CourseGroup data row.
CourseGroupDataRow courseGroupDataRow = new CourseGroupDataRow();
// Read the values.
courseGroupDataRow.CourseGroupID = (int)sqlDataReader["CourseGroupID"];
courseGroupDataRow.CourseGroupCode = (string)sqlDataReader["CourseGroupCode"];
courseGroupDataRow.CourseScheduleID = (int)sqlDataReader["CourseScheduleID"];
courseGroupDataRow.PlacesCount = (int)sqlDataReader["PlacesCount"];
// Return the CourseGroup data row.
return(courseGroupDataRow);
}
/// <summary>
/// Deletes the specified CourseGroup data row.
/// </summary>
public async Task Delete(IDatabaseConnection databaseConnection, CourseGroupDataRow courseGroupDataRow)
{
// Build the SQL command.
using (SqlCommand sqlCommand = new SqlCommand("DELETE FROM [CourseGroup] WHERE [CourseGroupID] = @courseGroupID;"))
{
// Use the specified database connection.
SqlConnection sqlConnection = (databaseConnection as DatabaseConnection).SqlConnection;
sqlCommand.Connection = sqlConnection;
// Set the SQL command parameter values.
sqlCommand.Parameters.Add("@courseGroupID", SqlDbType.Int).Value = courseGroupDataRow.CourseGroupID;
// Execute the SQL command.
await sqlCommand.ExecuteNonQueryAsync();
}
}
/// <summary>
/// Updates the specified CourseGroup data row.
/// </summary>
public async Task Update(IDatabaseConnection databaseConnection, CourseGroupDataRow courseGroupDataRow)
{
// Build the SQL command.
using (SqlCommand sqlCommand = new SqlCommand("UPDATE [CourseGroup] SET [CourseGroupCode] = @courseGroupCode, [CourseScheduleID] = @courseScheduleID, [PlacesCount] = @placesCount WHERE [CourseGroupID] = @courseGroupID;"))
{
// Use the specified database connection.
SqlConnection sqlConnection = (databaseConnection as DatabaseConnection).SqlConnection;
sqlCommand.Connection = sqlConnection;
// Set the SQL command parameter values.
this.SetSqlCommandParameterValues(sqlCommand, courseGroupDataRow, setPrimaryKeyValue: true);
// Execute the SQL command.
await sqlCommand.ExecuteNonQueryAsync();
}
}
public void Update_ShouldThrowException_GivenDuplicateCourseGroupCode()
{
// Insert the first master data rows in the database.
int firstCourseScheduleID = CourseScheduleTestTable.InsertPlaceholder();
// Insert the second master data rows in the database.
int secondCourseScheduleID = CourseScheduleTestTable.InsertPlaceholder();
// Insert the CourseGroup data row in the database.
int courseGroupID = CourseGroupTestTable.InsertWithValues(
"5s1cgndj6e5x0uvz",
firstCourseScheduleID,
1);
// Insert the duplicate CourseGroup data row in the database.
int duplicateCourseGroupID = CourseGroupTestTable.InsertPlaceholder(courseGroupCode: "78zcn25ynkaz50ef");
// Build the CourseGroup data row.
CourseGroupDataRow courseGroupDataRow = new CourseGroupDataRow();
courseGroupDataRow.CourseGroupID = courseGroupID;
courseGroupDataRow.CourseGroupCode = "78zcn25ynkaz50ef";
courseGroupDataRow.CourseScheduleID = secondCourseScheduleID;
courseGroupDataRow.PlacesCount = 2;
// Build the database connection.
using (DatabaseConnection databaseConnection = new DatabaseConnection(TestDatabase.ConnectionString))
{
// Open the database connection.
databaseConnection.Open().Wait();
try
{
// Update the CourseGroup data row.
CourseGroupDataAccessComponent courseGroupDataAccessComponent = new CourseGroupDataAccessComponent();
courseGroupDataAccessComponent.Update(databaseConnection, courseGroupDataRow).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>
/// Creates the specified CourseGroup data row.
/// </summary>
public async Task Create(IDatabaseConnection databaseConnection, CourseGroupDataRow courseGroupDataRow)
{
// Build the SQL command.
using (SqlCommand sqlCommand = new SqlCommand("INSERT INTO [CourseGroup] VALUES (@courseGroupCode, @courseScheduleID, @placesCount); 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, courseGroupDataRow, setPrimaryKeyValue: false);
// Execute the SQL command.
int courseGroupID = (int)await sqlCommand.ExecuteScalarAsync();
// Assign the generated CourseGroupID.
courseGroupDataRow.CourseGroupID = courseGroupID;
}
}
public void Update_ShouldSucceed()
{
// Insert the first master data rows in the database.
int firstCourseScheduleID = CourseScheduleTestTable.InsertPlaceholder();
// Insert the second master data rows in the database.
int secondCourseScheduleID = CourseScheduleTestTable.InsertPlaceholder();
// Insert the CourseGroup data row in the database.
int courseGroupID = CourseGroupTestTable.InsertWithValues(
"5s1cgndj6e5x0uvz",
firstCourseScheduleID,
1);
// Build the CourseGroup data row.
CourseGroupDataRow courseGroupDataRow = new CourseGroupDataRow();
courseGroupDataRow.CourseGroupID = courseGroupID;
courseGroupDataRow.CourseGroupCode = "78zcn25ynkaz50ef";
courseGroupDataRow.CourseScheduleID = secondCourseScheduleID;
courseGroupDataRow.PlacesCount = 2;
// Build the database connection.
using (DatabaseConnection databaseConnection = new DatabaseConnection(TestDatabase.ConnectionString))
{
// Open the database connection.
databaseConnection.Open().Wait();
// Update the CourseGroup data row.
CourseGroupDataAccessComponent courseGroupDataAccessComponent = new CourseGroupDataAccessComponent();
courseGroupDataAccessComponent.Update(databaseConnection, courseGroupDataRow).Wait();
}
// Validate the CourseGroup data row was updated in the database.
CourseGroupTestTable.AssertPresence(
courseGroupID,
"78zcn25ynkaz50ef",
secondCourseScheduleID,
2);
}
/// <summary>
/// Executes the NewCourseSchedule business operation.
/// </summary>
public async virtual Task <NewCourseScheduleBusinessResponse> NewCourseSchedule(IDatabaseConnection databaseConnection, NewCourseScheduleBusinessRequest businessRequest)
{
// Validate the business request.
this.ValidateNewCourseScheduleRequest(businessRequest);
// Initialize the operation data.
NewCourseScheduleOperationData operationData = new NewCourseScheduleOperationData();
// Validate the business operation.
await this.ValidateNewCourseScheduleOperation(databaseConnection, businessRequest, operationData);
// Generate a unique CourseSchedule code.
string courseScheduleCode = this.uniqueTokenGenerator.GenerateUniqueToken();
// Create the CourseSchedule data row.
operationData.CourseScheduleDataRow = new CourseScheduleDataRow();
operationData.CourseScheduleDataRow.CourseScheduleCode = courseScheduleCode;
operationData.CourseScheduleDataRow.SessionID = operationData.SessionDataRow.SessionID;
operationData.CourseScheduleDataRow.DayOfWeek = (int)businessRequest.CourseSchedule.DayOfWeek;
operationData.CourseScheduleDataRow.Time = businessRequest.CourseSchedule.Time;
await this.courseScheduleDataAccessComponent.Create(databaseConnection, operationData.CourseScheduleDataRow);
// Generate the unique CourseGroup codes.
operationData.CourseGroupCodes = new string[businessRequest.CourseSchedule.CourseGroups.Length];
for (int courseGroupIndex = 0; courseGroupIndex < businessRequest.CourseSchedule.CourseGroups.Length; courseGroupIndex++)
{
string courseGroupCode = this.uniqueTokenGenerator.GenerateUniqueToken();
operationData.CourseGroupCodes[courseGroupIndex] = courseGroupCode;
}
// Create the CourseGroup data rows.
for (int courseGroupIndex = 0; courseGroupIndex < businessRequest.CourseSchedule.CourseGroups.Length; courseGroupIndex++)
{
CourseGroupDataRow courseGroupDataRow = new CourseGroupDataRow();
courseGroupDataRow = new CourseGroupDataRow();
courseGroupDataRow.CourseGroupCode = operationData.CourseGroupCodes[courseGroupIndex];
courseGroupDataRow.CourseScheduleID = operationData.CourseScheduleDataRow.CourseScheduleID;
courseGroupDataRow.PlacesCount = businessRequest.CourseSchedule.CourseGroups[courseGroupIndex].PlacesCount;
await this.courseGroupDataAccessComponent.Create(databaseConnection, courseGroupDataRow);
}
// Build the business response.
NewCourseScheduleBusinessResponse businessResponse = new NewCourseScheduleBusinessResponse();
// Build the CourseSchedule business response element.
NewCourseScheduleBusinessResponse.CourseScheduleBusinessResponseElement courseScheduleBusinessResponseElement = new NewCourseScheduleBusinessResponse.CourseScheduleBusinessResponseElement();
courseScheduleBusinessResponseElement.CourseScheduleCode = operationData.CourseScheduleDataRow.CourseScheduleCode;
businessResponse.CourseSchedule = courseScheduleBusinessResponseElement;
// Build the CourseGroup business response elements.
List <NewCourseScheduleBusinessResponse.CourseScheduleBusinessResponseElement.CourseGroupBusinessResponseElement> courseGroupBusinessResponseElements = new List <NewCourseScheduleBusinessResponse.CourseScheduleBusinessResponseElement.CourseGroupBusinessResponseElement>();
for (int courseGroupIndex = 0; courseGroupIndex < businessRequest.CourseSchedule.CourseGroups.Length; courseGroupIndex++)
{
NewCourseScheduleBusinessResponse.CourseScheduleBusinessResponseElement.CourseGroupBusinessResponseElement courseGroupBusinessResponseElement = new NewCourseScheduleBusinessResponse.CourseScheduleBusinessResponseElement.CourseGroupBusinessResponseElement();
courseGroupBusinessResponseElement.CourseGroupCode = operationData.CourseGroupCodes[courseGroupIndex];
courseGroupBusinessResponseElements.Add(courseGroupBusinessResponseElement);
}
// Set the CourseGroup business response elements.
courseScheduleBusinessResponseElement.CourseGroups = courseGroupBusinessResponseElements.ToArray();
// Return the business response.
return(businessResponse);
}
