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