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