}//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>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
}//goal_practice
// <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(goal_practice other)
{
return(
(this.nodeId == other.nodeId) &&
(this.processAreaId == other.processAreaId) &&
(this.projectId == other.projectId) &&
(this.name == other.name) &&
(this.isGoal == other.isGoal) &&
(this.isPractice == other.isPractice) &&
(this.isPractice == other.isPractice) &&
(this.rating == other.rating) &&
(this.coverage == other.coverage)
);
}//Equals
} // ReadItemListFromDatabase_ByProjectIDAndProcessAreaId
/// <summary>
/// Retrieve a specific node from the goal_practice table and put it in the item list.
/// </summary>
/// <param name="nodeName"></param>
public void ReadItemFromDatabase_ByProjectIDAndNodeName(int projectId, string nodeName)
{
itemList.Clear();
string sQuery = "SELECT * FROM " + theTable +
" WHERE " +
" projectId = " + "'" + projectId + "'" +
" AND name LIKE " + "'" + nodeName + "'";
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 all fields of the current row
int intID = Convert.ToInt32(dr["Id"]);
int intNodeId = Convert.ToInt32(dr["nodeId"]);
int intProcessAreaId = Convert.ToInt32(dr["processAreaId"]);
string strnodename = dr["name"].ToString();
bool boolIsGoal = Convert.ToBoolean(dr["isGoal"]);
bool boolIsPractice = Convert.ToBoolean(dr["isPractice"]);
string strRating = (dr["rating"]).ToString();
bool boolCoverage = Convert.ToBoolean(dr["coverage"]);
//fill the itemList
goal_practice newRec = new goal_practice();
newRec.ID = intID;
newRec.nodeId = intNodeId;
newRec.processAreaId = intProcessAreaId;
newRec.name = strnodename;
newRec.isGoal = boolIsGoal;
newRec.isPractice = boolIsPractice;
newRec.rating = strRating;
newRec.coverage = boolCoverage;
itemList.Add(newRec);
} //foreach
} //if
} //ReadItemFromDatabase_ByProjectIDAndNodeName
//---------------------------------------------------------------------------------------------------------------
///<summary>
/// ReadItemListFromDatabase - read all records from "theTable" insert them into this.itemList
///</summary>
///<input>List<process_area> itemList - an ordinary List<> of type pa_tree_node, will be cleared if not already empty </input>
///<output>List<process_area> itemList - an ordinary List<> of type pa_tree_node, extracted from the database </output>
public void ReadItemListFromDatabase()
{
itemList.Clear(); // ensure itemList is empty
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 all fields of the current row
int intID = Convert.ToInt32(dr["Id"]);
int nodeId = Convert.ToInt32(dr["nodeId"]);
int processAreaId = Convert.ToInt32(dr["processAreaId"]);
int projectId = Convert.ToInt32(dr["projectId"]);
string name = dr["name"].ToString();
bool boolIsGoal = Convert.ToBoolean(dr["isGoal"]);
bool boolIsPractice = Convert.ToBoolean(dr["isPractice"]);
string strRating = (dr["rating"]).ToString();
bool boolcoverage = Convert.ToBoolean(dr["coverage"]);
//fill the itemList
goal_practice newRec = new goal_practice();
newRec.ID = intID;
newRec.nodeId = nodeId;
newRec.processAreaId = processAreaId;
newRec.projectId = projectId;
newRec.name = name;
newRec.isGoal = boolIsGoal;
newRec.isPractice = boolIsPractice;
newRec.rating = strRating;
newRec.coverage = boolcoverage;
itemList.Add(newRec);
} //for
}
} //ReadItemListFromDatabase
}//TestDBI_T_goal_practice_Read_From_DB
//-------------------------------------------------------------------------------------------
static void TestDBI_T_goal_practice_T3()
{
Console.WriteLine(" --START: TestDBI_T_goal_practice_T3");
//Construct myTable in RAM
SQLServerDB.goal_practice_Table myTable = new SQLServerDB.goal_practice_Table();
//put demo records into myTable in RAM
myTable.itemList.Clear();
for (int i = 1; i < 10; i++)
{
SQLServerDB.goal_practice goal_practiceItem = new SQLServerDB.goal_practice();
goal_practiceItem.ID = i; //actually, a don't care; it will not be stored
goal_practiceItem.nodeId = i; // Test only demonstrates storing/retreiving integers.
goal_practiceItem.processAreaId = i; // Test only demonstrates storing/retreiving integers.
goal_practiceItem.projectId = i; // Test only demonstrates storing/retreiving integers.
goal_practiceItem.name = "name_" + i.ToString();
goal_practiceItem.isGoal = Convert.ToBoolean(i % 2);
goal_practiceItem.isPractice = Convert.ToBoolean(i % 2); // Alternate between true/false
goal_practiceItem.rating = "rating_" + i.ToString();
goal_practiceItem.coverage = Convert.ToBoolean(i % 2);
myTable.itemList.Add(goal_practiceItem);
}
//Count SQLServerDB goal_practice table rows before clearing
int iRows = myTable.CountRows();
Console.WriteLine("myTable.CountRows = " + iRows.ToString());
Console.WriteLine(" --before clear SQLServer database table");
Util.pause();
myTable.Clear_Database_Table();
int iRows2 = myTable.CountRows();
Console.WriteLine("myTable.CountRows = " + iRows2.ToString());
Util.pause();
myTable.WriteItemListToDatabase();
Console.WriteLine("after writing to SQLServerDB");
Util.pause();
int iRows3 = myTable.CountRows();
Console.WriteLine("myTable.CountRows = " + iRows3.ToString());
Util.pause();
//put demo records into myTable in RAM
myTable.itemList.Clear();
for (int i = 1; i < 10; i++)
{
SQLServerDB.goal_practice goal_practiceItem = new SQLServerDB.goal_practice();
goal_practiceItem.ID = i; //actually, a don't care; it will not be stored
goal_practiceItem.nodeId = i; // Test only demonstrates storing/retreiving integers.
goal_practiceItem.processAreaId = i; // Test only demonstrates storing/retreiving integers.
goal_practiceItem.projectId = i; // this one, used for matching, should not change
goal_practiceItem.name = "name_" + i.ToString(); // this one, used for matching, should not change
goal_practiceItem.isGoal = Convert.ToBoolean(i % 2);
goal_practiceItem.isPractice = Convert.ToBoolean(i % 2); // Alternate between true/false
goal_practiceItem.rating = "rating_" + i.ToString() + "_REV-A";
goal_practiceItem.coverage = Convert.ToBoolean(i % 2);
myTable.itemList.Add(goal_practiceItem);
}
myTable.Show();
Util.pause("--BEFORE the update, showing the planned updates in myTable.itemList");
myTable.UpdateItemListToDatabase();
Util.pause("-- AFTER the update, examine the goal_practice Table using SSMS");
Console.WriteLine(" --DONE: TestDBI_T_goal_practice_T3");
}//TestDBI_T_goal_practice_T3
