}//WriteItemListToDatabase
//---------------------------------------------------------------------------------------------------------------
///<summary>
/// WriteItemToDatabase - read all records from this.itemList and write to "theTable"
///</summary>
///<output>interview_session r - output one interview_session object to the "theTable" in the database </output>
public void WriteItemToDatabase(interview_session r)
{
SqlConnection myConnection = DBUtils.GetNewSqlConnection();
if (myConnection == null)
{
LogManager.writeToLog("New connection failed to open; interview_session_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 +
" (sessionId, sessionIndex, sessionName, sessionDurationHours,sessionDurationMinutes," +
"specificGoal, specificPractice, genericGoal, genericPractice, processArea) " +
"VALUES ( @sessionId, @sessionIndex, @sessionName, @sessionDurationHours, @sessionDurationMinutes, " +
"@specificGoal, @specificPractice, @genericGoal, @genericPractice, @processArea);";
SqlCommand myCommand = new SqlCommand(strQuery, myConnection);
// myCommand.Parameters.AddWithValue("@ID", r.ID); //cannot asign to a field having IDENTITY semantics
myCommand.Parameters.AddWithValue("@sessionId", r.sessionId);
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.ExecuteNonQuery();
myConnection.Close();
}//WriteItemToDatabase
//---------------------------------------------------------------------------------------------------------------
///<summary>
/// ReadItemListFromDatabase - read all records from "theTable" insert them into this.itemList
///</summary>
///<input>List<interview_session> itemList - an ordinary List<> of type interview_session, will be cleared if not already empty </input>
///<output>List<interview_session> itemList - an ordinary List<> of type interview_session, extracted from the database </output>
public void ReadItemListFromDatabase()
{
itemList.Clear(); //First, empty the existing list contents
string sQuery = "SELECT * FROM " + theTable;
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"]);
int intsessionId = Convert.ToInt32(dr["sessionId"]);
int intsessionIndex = Convert.ToInt32(dr["sessionIndex"]);
String strsessionName = dr["sessionName"].ToString();
int intsessionDurationHours = Convert.ToInt32(dr["sessionDurationHours"]);
int intsessionDurationMinutes = Convert.ToInt32(dr["sessionDurationMinutes"]);
string strsessionDuration = intsessionDurationHours.ToString() + ":" + intsessionDurationMinutes.ToString();
String strspecificGoal = dr["specificGoal"].ToString();
String strspecificPractice = dr["specificPractice"].ToString();
String strgenericGoal = dr["genericGoal"].ToString();
String strgenericPractice = dr["genericPractice"].ToString();
String strprocessArea = dr["processArea"].ToString();
//fill the itemList
interview_session newRec = new interview_session();
newRec.ID = intID;
newRec.sessionId = intsessionId;
newRec.sessionIndex = intsessionIndex;
newRec.sessionName = strsessionName;
newRec.sessionDurationHours = intsessionDurationHours;
newRec.sessionDurationMinutes = intsessionDurationMinutes;
newRec.sessionDuration = strsessionDuration;
newRec.specificGoal = strspecificGoal;
newRec.specificPractice = strspecificPractice;
newRec.genericGoal = strgenericGoal;
newRec.genericPractice = strgenericPractice;
newRec.processArea = strprocessArea;
itemList.Add(newRec);
} //for
}
} //ReadItemListFromDatabase
// <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_session other)
{
return(
(this.sessionId == other.sessionId) &&
(this.sessionIndex == other.sessionIndex) &&
(this.sessionName == other.sessionName) &&
(this.sessionDurationHours == other.sessionDurationHours) &&
(this.sessionDurationMinutes == other.sessionDurationMinutes) &&
(this.sessionDuration == other.sessionDuration) &&
(this.specificGoal == other.specificGoal) &&
(this.specificPractice == other.specificPractice) &&
(this.genericGoal == other.genericGoal) &&
(this.genericPractice == other.genericPractice) &&
(this.processArea == other.processArea)
);
}//Equals
}//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
