/// <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); }