} //ReadItemListFromDatabase
///<summary>
/// ReadItemListFromDatabase_BySessionId - read all records from "theTable" insert them into this.itemList, filter by projectID
/// 1) Erase the current itemList in memory first
/// 2) Read records from SQLServer, filling the itemList
///</summary>
///<input>List<interview_question> itemList - an ordinary List<> of type interview_question, will be cleared if not already empty </input>
///<output>List<interview_question> itemList- an ordinary List<> of type interview_question, extracted from the database </output>
///<param name="projectId"></param>
public void ReadItemListFromDatabase_BySessionId(int sessionId)
{
itemList.Clear(); //First, empty the existing list contents
string sQuery = "SELECT * FROM " + theTable +
" WHERE sessionId=" + sessionId.ToString();
DataSet dsObj = DBUtils.ExecuteSqlQuery(sQuery);
if (dsObj != null && dsObj.Tables[0].Rows.Count > 0)
{
DataTable dtObj = dsObj.Tables[0]; //get the DataTable reference once
foreach (DataRow dr in dtObj.Rows)
{
//extract data
int intID = Convert.ToInt32(dr["ID"]);
String strinterviewQuestions = dr["interviewQuestion"].ToString();
String strquestionNotes = dr["questionNotes"].ToString();
int intsessionId = Convert.ToInt32(dr["sessionId"]);
//fill the itemList
interview_question newRec = new interview_question();
newRec.ID = intID;
newRec.interviewQuestions = strinterviewQuestions;
newRec.questionNotes = strquestionNotes;
newRec.sessionId = sessionId;
itemList.Add(newRec);
} //for
}
} //ReadItemListFromDatabase_BySessionId
}//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
}//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
// <summary>
/// Equals - compare for equivalence of two objects, comparing each field individually, except for the autonumbered ID field
/// true = identical content
/// false = NOT identical content
/// </summary>
/// <param name="other"></param>
/// <returns>bool</returns>
public bool Equals(interview_question other)
{
return(
(this.interviewQuestions == other.interviewQuestions) &&
(this.questionNotes == other.questionNotes) &&
(this.sessionId == other.sessionId)
);
}//Equals