}//UpdateItemListToDatabase ///<summary> /// UpdateItemToDatabase - update one record within "theTable" in the database ///</summary> ///<input>interview_question r - one item to be updated within the "theTable" in the database </input> ///<input> r.currentProject - the projectId to match with one database record</input> public void UpdateItemToDatabase(interview_question r) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("New connection failed to open; in interview_question_Table.cs:UpdateItemToDatabase.GetNewSqlConnection()"); return; } //WARNING: A field, like "ID", defined with "IDENTITY" semantics, cannot be assigned a value since it Auto-Increments string strQuery = "UPDATE " + theTable + " SET " + " interviewQuestions=@interviewQuestions," + " questionNotes=@questionNotes" + " WHERE " + " sessionId=@sessionId"; // <<<---- match on the sessionId SqlCommand myCommand = new SqlCommand(strQuery, myConnection); //WARNING: myCommand.Parameters.AddWithValue("@ID", r.ID); //cannot assign/modify a field having IDENTITY semantics //myCommand.Parameters.AddWithValue("@interview_questionId", r.interview_questionId); <<---this was the record selection parameter myCommand.Parameters.AddWithValue("@interviewQuestions", r.interviewQuestions); myCommand.Parameters.AddWithValue("@questionNotes", r.questionNotes); myCommand.Parameters.AddWithValue("@sessionId", r.sessionId); myCommand.ExecuteNonQuery(); myConnection.Close(); }//UpdateItemToDatabase
}//WriteItemListToDatabase //--------------------------------------------------------------------------------------------------------------- ///<summary> /// WriteItemToDatabase - read all records from this.itemList and write to "theTable" ///</summary> ///<output>process_area r - output one process_area object to the "theTable" in the database </output> public void WriteItemToDatabase(process_area r) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("new connection failed to [email protected]()"); return; } //WARNING: A field, like "ID", defined with "IDENTITY" semantics, cannot be assigned a value since it Auto-Increments string strQuery = "INSERT INTO " + theTable + " (processAreaId, projectId, paName, text, active, hasArtifact, hasAffirmation, rating, coverage) " + "VALUES ( @processAreaId, @projectId, @paName, @text, @active, @hasArtifact, @hasAffirmation, @rating, @coverage);"; SqlCommand myCommand = new SqlCommand(strQuery, myConnection); // myCommand.Parameters.AddWithValue("@ID", r.ID); //cannot asign to a field having IDENTITY semantics myCommand.Parameters.AddWithValue("@processAreaId", r.processAreaId); myCommand.Parameters.AddWithValue("@projectId", r.projectId); myCommand.Parameters.AddWithValue("@paName", r.paName); myCommand.Parameters.AddWithValue("@text", r.text); myCommand.Parameters.AddWithValue("@active", r.active); myCommand.Parameters.AddWithValue("@hasArtifact", r.canContainArtifact); myCommand.Parameters.AddWithValue("@hasAffirmation", r.canContainAffirmation); myCommand.Parameters.AddWithValue("@rating", r.rating); myCommand.Parameters.AddWithValue("@coverage", r.coverage); myCommand.ExecuteNonQuery(); myConnection.Close(); }//WriteItemToDatabase
}//WriteItemListToDatabase //--------------------------------------------------------------------------------------------------------------- ///<summary> /// WriteItemToDatabase - read all records from this.itemList and write to "theTable" ///</summary> ///<output>improvement_opportunity r - output one improvement_opportunity object to the "theTable" in the database </output> public void WriteItemToDatabase(improvement_opportunity r) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { Console.WriteLine("New connection failed to open; WriteItemToDatabased.GetNewSqlConnection()"); LogManager.writeToLog("New connection failed to open; WriteItemToDatabased.GetNewSqlConnection()"); return; } //WARNING: A field, like "ID", defined with "IDENTITY" semantics, cannot be assigned a value since it Auto-Increments string strQuery = "INSERT INTO " + theTable + " (notes, specificGoal, specificPractice, genericGoal, genericPractice, projectId, processArea) " + " VALUES " + " ( @notes, @specificGoal, @specificPractice, @genericGoal, @genericPractice, @projectId, @processArea);"; SqlCommand myCommand = new SqlCommand(strQuery, myConnection); //DEPRECATED: myCommand.Parameters.Add(...), INSTEAD USE myCommand.Parameters.AddWithValue(...) // myCommand.Parameters.AddWithValue("@ID", r.ID); //cannot asign to a field having IDENTITY semantics myCommand.Parameters.AddWithValue("@notes", r.notes); myCommand.Parameters.AddWithValue("@specificGoal", r.specificGoal); myCommand.Parameters.AddWithValue("@specificPractice", r.specificPractice); myCommand.Parameters.AddWithValue("@genericGoal", r.genericGoal); myCommand.Parameters.AddWithValue("@genericPractice", r.genericPractice); myCommand.Parameters.AddWithValue("@projectId", r.projectId); myCommand.Parameters.AddWithValue("@processArea", r.processArea); myCommand.ExecuteNonQuery(); myConnection.Close(); }//WriteItemToDatabase
}//WriteItemListToDatabase ///<summary> /// WriteItemToDatabase - read all records from this.itemList and write to "theTable" ///</summary> ///<output>interview_question - output one interview_question object to the "theTable" in the database </output> public void WriteItemToDatabase(interview_question r) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("New connection failed to open; interview_question_Table.cs:WriteItemToDatabase.GetNewSqlConnection()"); return; } //WARNING: A field, like "ID", defined with "IDENTITY" semantics, cannot be assigned a value since it Auto-Increments string strQuery = "INSERT INTO " + theTable + " (interviewQuestions, questionNotes, sessionId)" + "VALUES ( @interviewQuestions, @questionNotes, @sessionId);"; SqlCommand myCommand = new SqlCommand(strQuery, myConnection); // myCommand.Parameters.AddWithValue("@ID", r.ID); //cannot asign to a field having IDENTITY semantics myCommand.Parameters.AddWithValue("@interviewQuestions", r.interviewQuestions); myCommand.Parameters.AddWithValue("@questionNotes", r.questionNotes); myCommand.Parameters.AddWithValue("@sessionId", r.sessionId); myCommand.ExecuteNonQuery(); myConnection.Close(); }//WriteItemToDatabase
}//WriteItemListToDatabase //--------------------------------------------------------------------------------------------------------------- ///<summary> /// WriteItemToDatabase - read all records from this.itemList and write to "theTable" ///</summary> ///<output>mapping_note r - output one mapping_note object to the "theTable" in the database </output> public void WriteItemToDatabase(mapping_note r) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("New connection failed to open; mapping_note_Table.cs:WriteItemToDatabase.GetNewSqlConnection()"); return; } //WARNING: A field, like "ID", defined with "IDENTITY" semantics, cannot be assigned a value since it Auto-Increments string strQuery = "INSERT INTO " + theTable + " (mappingId, notes) " + "VALUES ( @mappingId, @notes);"; SqlCommand myCommand = new SqlCommand(strQuery, myConnection); //DEPRECATED: myCommand.Parameters.Add(...), INSTEAD USE myCommand.Parameters.AddWithValue(...) // myCommand.Parameters.AddWithValue("@ID", r.ID); //cannot asign to a field having IDENTITY semantics myCommand.Parameters.AddWithValue("@mappingId", r.mappingId); myCommand.Parameters.AddWithValue("@notes", r.notes); myCommand.ExecuteNonQuery(); myConnection.Close(); }//WriteItemToDatabaes
}//SQLServer_WriteToDatabas //--------------------------------------------------------------------------------------------------------------- ///<summary> /// WriteItemToDatabase - write the given affirmation record to "theTable" ///</summary> ///<output>affirmation r - output one affirmation object to the "theTable" in the database </output> public void WriteItemToDatabase(affirmation r) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("New connection failed to open; affirmation_Table.cs:WriteItemToDatabase.GetNewSqlConnection()"); return; } //WARNING: A field, like "ID", defined with "IDENTITY" semantics, cannot be assigned a value since it Auto-Increments string strQuery = "INSERT INTO " + theTable + " (affirmationId, affirmationName, affirmationType, specificGoal, specificPractice, genericGoal, genericPractice, processArea, projectId) " + "VALUES ( @affirmationId, @affirmationName, @affirmationType, @specificGoal, @specificPractice, @genericGoal, @genericPractice, @processArea, @projectId);"; SqlCommand myCommand = new SqlCommand(strQuery, myConnection); // myCommand.Parameters.AddWithValue("@ID", r.ID); //Warning: We cannot asign to a field having IDENTITY semantics myCommand.Parameters.AddWithValue("@affirmationId", r.affirmationId); myCommand.Parameters.AddWithValue("@affirmationName", r.affirmationName); myCommand.Parameters.AddWithValue("@affirmationType", r.affirmationType); myCommand.Parameters.AddWithValue("@specificGoal", r.specificGoal); myCommand.Parameters.AddWithValue("@specificPractice", r.specificPractice); myCommand.Parameters.AddWithValue("@genericGoal", r.genericGoal); myCommand.Parameters.AddWithValue("@genericPractice", r.genericPractice); myCommand.Parameters.AddWithValue("@processArea", r.processArea); myCommand.Parameters.AddWithValue("@projectId", r.projectId); myCommand.ExecuteNonQuery(); myConnection.Close(); }//WriteItemToDatabase
}//WriteItemListToDatabase //--------------------------------------------------------------------------------------------------------------- ///<summary> /// WriteItemToDatabase - read all records from this.itemList and write to "theTable" ///</summary> ///<output>process_area r - output one process_area object to the "theTable" in the database </output> public void WriteItemToDatabase(goal_practice r) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("new connection failed to [email protected]()"); return; } //WARNING: A field, like "ID", defined with "IDENTITY" semantics, cannot be assigned a value since it Auto-Increments string strQuery = "INSERT INTO " + theTable + " (nodeId, processAreaId, projectId, name, isGoal, isPractice, rating, coverage) " + "VALUES ( @nodeId, @processAreaId, @projectId, @name, @isGoal, @isPractice, @rating, @coverage);"; SqlCommand myCommand = new SqlCommand(strQuery, myConnection); // myCommand.Parameters.AddWithValue("@ID", r.ID); //cannot asign to a field having IDENTITY semantics myCommand.Parameters.AddWithValue("@nodeId", r.nodeId); myCommand.Parameters.AddWithValue("@processAreaId", r.processAreaId); myCommand.Parameters.AddWithValue("@projectId", r.projectId); myCommand.Parameters.AddWithValue("@name", r.name); myCommand.Parameters.AddWithValue("@isGoal", r.isGoal); myCommand.Parameters.AddWithValue("@isPractice", r.isPractice); myCommand.Parameters.AddWithValue("@rating", r.rating); myCommand.Parameters.AddWithValue("@coverage", r.coverage); myCommand.ExecuteNonQuery(); myConnection.Close(); }//WriteItemToDatabase
}//UpdateItemListToDatabase //--------------------------------------------------------------------------------------------------------------- ///<summary> /// UpdateItemToDatabase - update one record within "theTable" in the database; /// match a record based on the projectId AND name ///</summary> ///<input>process_area r - one item to be updated within the "theTable" in the database </input> ///<input> r.currentProject - the projectId to match with one database record</input> public void UpdateItemToDatabase(goal_practice r) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("new connection failed to open; in goal_practice_Table.cs: UpdateItemToDatabase.GetNewSqlConnection()"); return; } //WARNING: A field, like "ID", defined with "IDENTITY" semantics, cannot be assigned a value since it Auto-Increments string strQuery = "UPDATE " + theTable + " SET " + " rating=@rating," + " coverage=@coverage" + " WHERE " + " projectId=" + "@projectId" + " AND name LIKE " + "@name"; SqlCommand myCommand = new SqlCommand(strQuery, myConnection); //WARNING: myCommand.Parameters.AddWithValue("@ID", r.ID); //cannot assign/modify a field having IDENTITY semantics myCommand.Parameters.AddWithValue("@rating", r.rating); myCommand.Parameters.AddWithValue("@coverage", r.coverage); myCommand.Parameters.AddWithValue("@projectId", r.projectId); myCommand.Parameters.AddWithValue("@name", r.name); myCommand.ExecuteNonQuery(); myConnection.Close(); }//UpdateItemToDatabas
}//WriteItemListToDatabase //--------------------------------------------------------------------------------------------------------------- ///<summary> /// WriteItemToDatabase - read all records from this.itemList and write to "theTable" ///</summary> ///<output>appraisal r - output one appraisal object to the "theTable" in the database </output> public void WriteItemToDatabase(appraisal r) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("New connection failed to open; appraisal_Table.cs:WriteItemToDatabase.GetNewSqlConnection()"); return; } //WARNING: A field, like "ID", defined with "IDENTITY" semantics, cannot be assigned a value since it Auto-Increments string strQuery = "INSERT INTO " + theTable + " (appraisalName, creator, maturityLevel, projects, samSelected, ssdSelected) " + "VALUES ( @appraisalName, @creator, @maturityLevel, @projects, @samSelected, @ssdSelected);"; SqlCommand myCommand = new SqlCommand(strQuery, myConnection); // myCommand.Parameters.AddWithValue("@ID", r.ID); //cannot asign to a field having IDENTITY semantics myCommand.Parameters.AddWithValue("@appraisalName", r.AppraisalName); myCommand.Parameters.AddWithValue("@creator", r.Creator); myCommand.Parameters.AddWithValue("@maturityLevel", r.MaturityLevel); myCommand.Parameters.AddWithValue("@projects", r.Projects); myCommand.Parameters.AddWithValue("@samSelected", r.SAMSelected); myCommand.Parameters.AddWithValue("@ssdSelected", r.SSDSelected); myCommand.ExecuteNonQuery(); myConnection.Close(); }//WriteItemToDatabase
}//UpdateItemListToDatabase //--------------------------------------------------------------------------------------------------------------- ///<summary> /// UpdateItemToDatabas - update one record within "theTable" in the database ///</summary> ///<input>mapping_note r - one item to be updated within the "theTable" in the database </input> ///<input> r.currentProject - the projectId to match with one database record</input> public void UpdateItemToDatabase(mapping_note r) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("New connection failed to open; in mapping_note_Table.cs:UpdateItemToDatabas.GetNewSqlConnection()"); return; } //WARNING: A field, like "ID", defined with "IDENTITY" semantics, cannot be assigned a value since it Auto-Increments string strQuery = "UPDATE " + theTable + " SET " + " notes=@notes" + " WHERE " + " mappingId=@mappingId"; // <<<---- match on the Primary Key SqlCommand myCommand = new SqlCommand(strQuery, myConnection); //WARNING: myCommand.Parameters.AddWithValue("@ID", r.ID); //cannot assign/modify a field having IDENTITY semantics myCommand.Parameters.AddWithValue("@notes", r.notes); myCommand.Parameters.AddWithValue("@mappingId", r.mappingId); myCommand.ExecuteNonQuery(); myConnection.Close(); }//UpdateItemToDatabas
}//UpdateItemListToDatabase //--------------------------------------------------------------------------------------------------------------- ///<summary> /// UpdateItemToDatabase - update one record within "theTable" in the database ///</summary> ///<input>user r - one item to be updated within the "theTable" in the database </input> ///<input> r.currentProject - the projectId to match with one database record</input> public void UpdateItemToDatabase(user r) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("New connection failed to open; in user_Table.cs: UpdateItemToDatabase.GetNewSqlConnection()"); return; } //WARNING: A field, like "ID", defined with "IDENTITY" semantics, cannot be assigned a value since it Auto-Increments string strQuery = "UPDATE " + theTable + " SET " + " organization=@organization," + " password=@password" + " WHERE " + " username=@username"; // <<<---- match on the Primary Key SqlCommand myCommand = new SqlCommand(strQuery, myConnection); //DEPRECATED: myCommand.Parameters.Add(...), INSTEAD USE myCommand.Parameters.AddWithValue(...) //WARNING: myCommand.Parameters.AddWithValue("@ID", r.ID); //cannot assign/modify a field having IDENTITY semantics myCommand.Parameters.AddWithValue("@username", r.username); myCommand.Parameters.AddWithValue("@organization", r.organization); myCommand.Parameters.AddWithValue("@password", r.password); myCommand.ExecuteNonQuery(); myConnection.Close(); }//UpdateItemToDatabase
}//WriteItemListToDatabase //--------------------------------------------------------------------------------------------------------------- ///<summary> /// WriteItemToDatabase - read all records from this.itemList and write to "theTable" ///</summary> ///<output>user r - output one user object to the "theTable" in the database </output> public void WriteItemToDatabase(user r) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("New connection failed to open; user_Table.cs:WriteItemToDatabase.GetNewSqlConnection()"); return; } //WARNING: A field, like "ID", defined with "IDENTITY" semantics, cannot be assigned a value since it Auto-Increments string strQuery = "INSERT INTO " + theTable + " (username, organization, password) " + "VALUES (@username, @organization, @password);"; SqlCommand myCommand = new SqlCommand(strQuery, myConnection); // myCommand.Parameters.AddWithValue("@ID", r.ID); //cannot asign to a field having IDENTITY semantics myCommand.Parameters.AddWithValue("@username", r.username); myCommand.Parameters.AddWithValue("@organization", r.organization); myCommand.Parameters.AddWithValue("@password", r.password); myCommand.ExecuteNonQuery(); myConnection.Close(); }//WriteItemToDatabase
}//UpdateItemListToDatabase ///<summary> /// UpdateItemToDatabase - update one record within "theTable" in the database ///</summary> ///<input>project r - one item to be updated within the "theTable" in the database </input> ///<input> r.currentProject - the projectId to match with one database record</input> public void UpdateItemToDatabase(project r) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("New connection failed to open; in project_Table.cs: UpdateItemToDatabase.GetNewSqlConnection()"); return; } //WARNING: A field, like "ID", defined with "IDENTITY" semantics, cannot be assigned a value since it Auto-Increments string strQuery = "UPDATE " + theTable + " SET " + " projectName=@projectName," + " projectIndex=@projectIndex," + " creator=@creator," + " standardProcess=@standardProcess" + " WHERE " + " projectId=@projectId"; // <<<---- match on the Primary Key SqlCommand myCommand = new SqlCommand(strQuery, myConnection); //WARNING: myCommand.Parameters.AddWithValue("@ID", r.ID); //cannot assign/modify a field having IDENTITY semantics myCommand.Parameters.AddWithValue("@projectId", r.projectId); myCommand.Parameters.AddWithValue("@projectIndex", r.projectIndex); myCommand.Parameters.AddWithValue("@projectName", r.projectName); myCommand.Parameters.AddWithValue("@creator", r.creator); myCommand.Parameters.AddWithValue("@standardProcess", r.standardProcess); myCommand.ExecuteNonQuery(); myConnection.Close(); }//UpdateItemToDatabase
}//WriteItemListToDatabase ///<summary> /// WriteItemToDatabase - read all records from this.itemList and write to "theTable" ///</summary> ///<output>project r - output one project object to the "theTable" in the database </output> public void WriteItemToDatabase(project r) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("New connection failed to open; project_Table.cs:WriteItemToDatabase.GetNewSqlConnection()"); return; } //WARNING: A field, like "ID", defined with "IDENTITY" semantics, cannot be assigned a value since it Auto-Increments string strQuery = "INSERT INTO " + theTable + " (projectId, projectIndex, projectName, creator, standardProcess) " + "VALUES ( @projectId, @projectIndex, @projectName, @creator, @standardProcess);"; SqlCommand myCommand = new SqlCommand(strQuery, myConnection); myCommand.Parameters.AddWithValue("@projectId", r.projectId); myCommand.Parameters.AddWithValue("@projectIndex", r.projectIndex); myCommand.Parameters.AddWithValue("@projectName", r.projectName); myCommand.Parameters.AddWithValue("@creator", r.creator); myCommand.Parameters.AddWithValue("@standardProcess", r.standardProcess); myCommand.ExecuteNonQuery(); myConnection.Close(); }//WriteItemToDatabase
//--------------------------------------------------------------------------------------------------------------- /// <summary> /// CountRows - count how many rows in the table /// </summary> /// <returns></returns> public int CountRows() { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("New connection failed to open; mapping_note_Table.cs:SQLServer_CountRows.GetNewSqlConnection()"); return(-1); } string strQuery = "SELECT COUNT(*) FROM " + theTable; return(DBUtils.ExecuteSqlQueryScalar(strQuery, myConnection)); }//CountRows
}//CountRows //--------------------------------------------------------------------------------------------------------------- /// <summary> /// CountRows_By_processAre - count how many rows match the processAre /// </summary> /// <param name="processAre"></param> /// <returns></returns> public int CountRows_By_processAre(String processAre) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("New connection failed to open; weakness_Table.cs: SQLServer_CountRows_By_processAre.GetNewSqlConnection"); return(-1); } string strQuery = "SELECT COUNT(*) FROM " + theTable + " WHERE " + "processAre=" + processAre; return(DBUtils.ExecuteSqlQueryScalar(strQuery, myConnection)); }//CountRows_By_processAre
}//CountRows //--------------------------------------------------------------------------------------------------------------- /// <summary> /// CountRows_By_projectId - count how many rows match the projectID /// </summary> /// <param name="projectId"></param> /// <returns></returns> public int CountRows_By_projectId(int projectId) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("New connection failed to open; affirmation_Table.cs:SQLServer_CountRows_By_projectId.GetNewSqlConnection()"); return(-1); } string strQuery = "SELECT COUNT(*) FROM " + theTable + " WHERE " + "projectId=" + projectId.ToString(); return(DBUtils.ExecuteSqlQueryScalar(strQuery, myConnection)); }//CountRows_By_projectId
} //ReadItemListFromDatabase_ByProjectID //--------------------------------------------------------------------------------------------------------------- ///<summary> /// WriteItemListToDatabase - read all records from this.itemList and write to "theTable" /// New records are added using the "INSERT INTO" SQL operation ///</summary> ///<input> String theTable - the table name</input> ///<output>List<mapping_note> itemList - an ordinary List<> of type mapping_note, output to the "theTable" in the database </output> public void WriteItemListToDatabase() { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("New connection failed to open"); return; } foreach (var r in itemList) { WriteItemToDatabase(r); }//foreach myConnection.Close(); }//WriteItemListToDatabase
}//WriteItemToDatabaes //--------------------------------------------------------------------------------------------------------------- ///<summary> /// UpdateItemListToDatabase - read all records from this.itemList and update matching recordsin "theTable" ///</summary> ///<input>List<mapping_note> itemList - an ordinary List<T> of type mapping_note, updated within the "theTable" in the database </input> public void UpdateItemListToDatabase() { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("New connection failed to open; mapping_note_Table:UpdateItemListToDatabase.GetNewSqlConnection()"); return; } foreach (var r in itemList) { UpdateItemToDatabase(r); }//foreach myConnection.Close(); }//UpdateItemListToDatabase
}//UpdateItemListToDatabase //--------------------------------------------------------------------------------------------------------------- ///<summary> /// UpdateItemToDatabase - update one record within "theTable" in the database ///</summary> ///<input>interview_session r - one item to be updated within the "theTable" in the database </input> ///<input> r.currentProject - the projectId to match with one database record</input> public void UpdateItemToDatabase(interview_session r) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("New connection failed to open; interview_session_Table.cs:UpdateItemToDatabase.GetNewSqlConnection()"); return; } //WARNING: A field, like "ID", defined with "IDENTITY" semantics, cannot be assigned a value since it Auto-Increments string strQuery = "UPDATE " + theTable + " SET " + "sessionIndex=@sessionIndex," + " sessionName=@sessionName," + " sessionDurationHours=@sessionDurationHours," + " sessionDurationMinutes=@sessionDurationMinutes," + " specificGoal=@specificGoal," + " specificPractice=@specificPractice," + " genericGoal=@genericGoal," + " genericPractice=@genericPractice," + " processArea=@processArea" + " WHERE " + " sessionId=@sessionId"; // <<<---- match on the Primary Key SqlCommand myCommand = new SqlCommand(strQuery, myConnection); //WARNING: myCommand.Parameters.AddWithValue("@ID", r.ID); //cannot assign/modify a field having IDENTITY semantics //myCommand.Parameters.AddWithValue("@interview_sessionId", r.interview_sessionId); <<---this was the record selection parameter myCommand.Parameters.AddWithValue("@sessionIndex", r.sessionIndex); myCommand.Parameters.AddWithValue("@sessionName", r.sessionName); myCommand.Parameters.AddWithValue("@sessionDurationHours", r.sessionDurationHours); myCommand.Parameters.AddWithValue("@sessionDurationMinutes", r.sessionDurationMinutes); myCommand.Parameters.AddWithValue("@specificGoal", r.specificGoal); myCommand.Parameters.AddWithValue("@specificPractice", r.specificPractice); myCommand.Parameters.AddWithValue("@genericGoal", r.genericGoal); myCommand.Parameters.AddWithValue("@genericPractice", r.genericPractice); myCommand.Parameters.AddWithValue("@processArea", r.processArea); myCommand.Parameters.AddWithValue("@sessionId", r.sessionId); myCommand.ExecuteNonQuery(); myConnection.Close(); }//UpdateItemToDatabase
} //ReadItemListFromDatabase_By_processArea //--------------------------------------------------------------------------------------------------------------- ///<summary> /// WriteItemListToDatabase - read all records from this.itemList and write to "theTable" /// New records are added using the "INSERT INTO" SQL operation ///</summary> ///<input> String theTable - the table name</input> ///<output>List<affirmation> itemList - an ordinary List<> of type affirmation, output to the "theTable" in the database </output> public void WriteItemListToDatabase() { //Console.WriteLine("START : WriteItemListToDatabase:" + theTable); SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("New connection failed to open"); return; } foreach (var r in itemList) { WriteItemToDatabase(r); }//foreach myConnection.Close(); //Console.WriteLine("DONE: WriteItemListToDatabase:" + theTable); }//SQLServer_WriteToDatabas
}//UpdateItemListToDatabase //--------------------------------------------------------------------------------------------------------------- ///<summary> /// UpdateItemToDatabase - update one record within "theTable" in the database ///</summary> ///<input>mapping r - one item to be updated within the "theTable" in the database </input> ///<input> r.currentProject - the projectId to match with one database record</input> public void UpdateItemToDatabase(mapping r) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("New connection failed to open; in mapping_Table.cs:UpdateItemToDatabase.GetNewSqlConnection()"); return; } //WARNING: A field, like "ID", defined with "IDENTITY" semantics, cannot be assigned a value since it Auto-Increments string strQuery = "UPDATE " + theTable + " SET " + "mappingName=@mappingName," + "mappingPath=@mappingPath," + "specificGoal=@specificGoal," + "specificPractice=@specificPractice," + "genericGoal=@genericGoal," + "genericPractice=@genericPractice," + "processArea=@processArea," + "projectId=@projectId" + " WHERE " + " mappingId=@mappingId"; // <<<---- match on the Primary Key SqlCommand myCommand = new SqlCommand(strQuery, myConnection); //DEPRECATED: myCommand.Parameters.Add(...), INSTEAD USE myCommand.Parameters.AddWithValue(...) //WARNING: myCommand.Parameters.AddWithValue("@ID", r.ID); //cannot assign/modify a field having IDENTITY semantics myCommand.Parameters.AddWithValue("@mappingId", r.mappingId); // <<---this is the record selection parameter myCommand.Parameters.AddWithValue("@mappingName", r.mappingName); myCommand.Parameters.AddWithValue("@mappingPath", r.mappingPath); myCommand.Parameters.AddWithValue("@specificGoal ", r.specificGoal); myCommand.Parameters.AddWithValue("@specificPractice ", r.specificPractice); myCommand.Parameters.AddWithValue("@genericGoal ", r.genericGoal); myCommand.Parameters.AddWithValue("@genericPractice ", r.genericPractice); myCommand.Parameters.AddWithValue("@processArea ", r.processArea); myCommand.Parameters.AddWithValue("@projectId ", r.projectId); myCommand.ExecuteNonQuery(); myConnection.Close(); }//UpdateItemToDatabase
}//Clear_Database_Table //--------------------------------------------------------------------------------------------------------------- /// <summary> /// Clear_Database_Table_By_username - delete user table records by username /// </summary> /// <param name="username"></param> public void Clear_Database_Table_By_username(String username) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { Console.WriteLine("New connection failed to open; user_Table.cs:Clear_Database_Table_By_username.GetNewSqlConnection()"); return; } string strQuery = "DELETE FROM " + theTable + " WHERE " + " username=@username"; SqlCommand myCommand = new SqlCommand(strQuery, myConnection); myCommand.Parameters.AddWithValue("@username", username); myCommand.ExecuteNonQuery(); myConnection.Close(); }//Clear_Database_Table_By_username
//--------------------------------------------------------------------------------------------------------------- /// <summary> /// SQLServer_Clear_Database_Table_By_mappingId- delete mapping note table records by mappingId /// </summary> /// <param name="mappingId"></param> public void Clear_Database_Table_By_mappingId(int mappingId) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { Console.WriteLine("New connection failed to open; mapping_note_Table.cs:SQLServer_Clear_Database_Table_By_mappingId.GetNewSqlConnection()"); return; } string strQuery = "DELETE FROM " + theTable + " WHERE " + " mappingId=@mappingId"; SqlCommand myCommand = new SqlCommand(strQuery, myConnection); myCommand.Parameters.AddWithValue("@mappingId", mappingId); myCommand.ExecuteNonQuery(); myConnection.Close(); }
//--------------------------------------------------------------------------------------------------------------- /// <summary> /// Clear_Database_Table_By_appraisalName - delete appraisal table records by appraisalName /// </summary> /// <param name="appraisalName"></param> public void Clear_Database_Table_By_appraisalName(String appraisalName) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("New connection failed to open; appraisal_Table.cs:SQLServer_Clear_Database_Table_By_appraisalName.GetNewSqlConnection()"); return; } string strQuery = "DELETE FROM " + theTable + " WHERE " + " appraisalName=@appraisalName"; SqlCommand myCommand = new SqlCommand(strQuery, myConnection); myCommand.Parameters.AddWithValue("@appraisalName", appraisalName); myCommand.ExecuteNonQuery(); myConnection.Close(); }//Clear_Database_Table_By_appraisalName
}//Clear_Database_Table_By_processAreaId //--------------------------------------------------------------------------------------------------------------- /// <summary> /// Clear_Database_Table_By_projectId- delete affirmation table records by projectId /// </summary> /// <param name="projectId"></param> public void Clear_Database_Table_By_projectId(int projectId) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { Console.WriteLine("new connection failed to open, goal_practice_Table.cs: SQLServer_Clear_Database_Table_By_appraisalName.GetNewSqlConnection"); return; } string strQuery = "DELETE FROM " + theTable + " WHERE " + " projectId= " + "'" + "@projectId" + "'"; SqlCommand myCommand = new SqlCommand(strQuery, myConnection); myCommand.Parameters.AddWithValue("@projectId", projectId); myCommand.ExecuteNonQuery(); myConnection.Close(); }// Clear_Database_Table_By_projectId
}//Clear_Database_Table //--------------------------------------------------------------------------------------------------------------- /// <summary> /// Clear_Database_Table_By_AffirmationID - delete affirmation table records by affirmationId /// </summary> /// <param name="affirmationId"></param> public void Clear_Database_Table_By_AffirmationID(int affirmationId) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { Console.WriteLine("New connection failed to open, affirmation_Table.cs:SQLServer_Clear_Database_Table_By_appraisalName.GetNewSqlConnection()"); return; } string strQuery = "DELETE FROM " + theTable + " WHERE " + " affirmationId=@affirmationId"; SqlCommand myCommand = new SqlCommand(strQuery, myConnection); myCommand.Parameters.AddWithValue("@affirmationId", affirmationId); myCommand.ExecuteNonQuery(); myConnection.Close(); }//Clear_Database_Table_By_AffirmationID
//--------------------------------------------------------------------------------------------------------------- /// <summary> /// Clear_Database_Table_By_processArea- delete affirmation table records by processArea /// </summary> /// <param name="paName"></param> public void Clear_Database_Table_By_processArea(String paName) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { Console.WriteLine("new connection failed to open, process_area_Table.cs: SQLServer_Clear_Database_Table_By_appraisalName.GetNewSqlConnection"); return; } string strQuery = "DELETE FROM " + theTable + " WHERE " + " paName=@paName"; SqlCommand myCommand = new SqlCommand(strQuery, myConnection); myCommand.Parameters.AddWithValue("@paName", paName); myCommand.ExecuteNonQuery(); myConnection.Close(); }//Clear_Database_Table_By_processArea
}//Clear_Database_Table_By_processArea //--------------------------------------------------------------------------------------------------------------- /// <summary> /// Clear_Database_Table_By_projectId- delete weakness table records by projectId /// </summary> /// <param name="projectId"></param> public void Clear_Database_Table_By_projectId(int projectId) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { Console.WriteLine("New connection failed to open; weakness_Table.cs: Clear_Database_Table_By_projectId.GetNewSqlConnection()"); return; } string strQuery = "DELETE FROM " + theTable + " WHERE " + " projectId=@projectId"; SqlCommand myCommand = new SqlCommand(strQuery, myConnection); myCommand.Parameters.AddWithValue("@projectId", projectId); myCommand.ExecuteNonQuery(); myConnection.Close(); }//Clear_Database_Table_By_projectId
}//UpdateItemListToDatabase //--------------------------------------------------------------------------------------------------------------- ///<summary> /// UpdateItemToDatabase - update one record within "theTable" in the database; /// match a record based on the projectId ///</summary> ///<input>process_area r - one item to be updated within the "theTable" in the database </input> ///<input> r.currentProject - the projectId to match with one database record</input> public void UpdateItemToDatabase(process_area r) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("new connection failed to open; in process_area_Table.cs: UpdateItemToDatabase.GetNewSqlConnection()"); return; } //WARNING: A field, like "ID", defined with "IDENTITY" semantics, cannot be assigned a value since it Auto-Increments string strQuery = "UPDATE " + theTable + " SET " + " paName=@paName," + " text=@text," + " active=@active," + " hasArtifact=@hasArtifact," + " hasAffirmation=@hasAffirmation," + " rating=@rating," + " coverage=@coverage" + " WHERE " + " processAreaId=@processAreaId" + " AND projectId=@projectId"; SqlCommand myCommand = new SqlCommand(strQuery, myConnection); //WARNING: myCommand.Parameters.AddWithValue("@ID", r.ID); //cannot assign/modify a field having IDENTITY semantics myCommand.Parameters.AddWithValue("@paName", r.paName); myCommand.Parameters.AddWithValue("@text", r.text); myCommand.Parameters.AddWithValue("@active", r.active); myCommand.Parameters.AddWithValue("@hasArtifact", r.canContainArtifact); myCommand.Parameters.AddWithValue("@hasAffirmation", r.canContainAffirmation); myCommand.Parameters.AddWithValue("@rating", r.rating); myCommand.Parameters.AddWithValue("@coverage", r.coverage); myCommand.Parameters.AddWithValue("@processAreaId", r.processAreaId); myCommand.Parameters.AddWithValue("@projectId", r.projectId); myCommand.ExecuteNonQuery(); myConnection.Close(); }//UpdateItemToDatabase