}//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
} //ReadItemListFromDatabase_ByProjectID
public void ReadItemListFromDatabase_ByProjectIDAndName(int projectId, string paName)
{
itemList.Clear(); //First, empty the existing list contents
string sQuery = "SELECT * FROM " + theTable +
" WHERE projectId = " + projectId.ToString() +
" AND paName = " + "'" + paName + "'";
DataSet dsObj = DBUtils.ExecuteSqlQuery(sQuery);
if (dsObj == null)
{
LogManager.writeToLog("The dataset is null in process_area_Table.ReadItemListFromDatabase_ByProjectIDAndName()");
}
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 intProcessAreaId = Convert.ToInt32(dr["processAreaId"]);
int intProjectId = Convert.ToInt32(dr["projectId"]);
string strname = dr["paName"].ToString();
string strtext = dr["text"].ToString();
bool boolIsActive = Convert.ToBoolean(dr["active"]);
bool boolHasArtifacts = Convert.ToBoolean(dr["hasArtifact"]);
bool boolHasAffirmations = Convert.ToBoolean(dr["hasAffirmation"]);
string strRating = (dr["rating"]).ToString();
bool boolCoverage = Convert.ToBoolean(dr["coverage"]);
//fill the itemList
process_area newRec = new process_area();
newRec.ID = intID;
newRec.processAreaId = intProcessAreaId;
newRec.projectId = intProjectId;
newRec.paName = strname;
newRec.text = strtext;
newRec.active = boolIsActive;
newRec.canContainArtifact = boolHasArtifacts;
newRec.canContainAffirmation = boolHasAffirmations;
newRec.rating = strRating;
newRec.coverage = boolCoverage;
itemList.Add(newRec);
}//for
}
}
// <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(process_area other)
{
return(
(this.processAreaId == other.processAreaId) &&
(this.projectId == other.projectId) &&
(this.paName == other.paName) &&
(this.text == other.text) &&
(this.active == other.active) &&
(this.canContainArtifact == other.canContainArtifact) &&
(this.canContainAffirmation == other.canContainAffirmation) &&
(this.rating == other.rating) &&
(this.coverage == other.coverage)
);
}//Equals
//---------------------------------------------------------------------------------------------------------------
///<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 processAreaId = Convert.ToInt32(dr["processAreaId"]);
int projectId = Convert.ToInt32(dr["projectId"]);
string name = dr["paName"].ToString();
string text = dr["text"].ToString();
bool boolIsActive = Convert.ToBoolean(dr["active"]);
bool hasArtifacts = Convert.ToBoolean(dr["hasArtifact"]);
bool hasAffirmations = Convert.ToBoolean(dr["hasAffirmation"]);
string strRating = (dr["rating"]).ToString();
bool boolCoverage = Convert.ToBoolean(dr["coverage"]);
//fill the itemList
process_area newRec = new process_area();
newRec.ID = intID;
newRec.processAreaId = processAreaId;
newRec.projectId = projectId;
newRec.paName = name;
newRec.text = text;
newRec.active = boolIsActive;
newRec.canContainArtifact = hasArtifacts;
newRec.canContainAffirmation = hasAffirmations;
newRec.rating = strRating;
newRec.coverage = boolCoverage;
itemList.Add(newRec);
} //foreach
}
} //ReadItemListFromDatabase
}//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
static void TestDBI_T_process_area_T5()
{
Console.WriteLine(" --START: TestDBI_T_process_area_T5");
#if Comment
//Construct a brand new myTable in RAM
SQLServerDB.process_area_Table myTable = new SQLServerDB.process_area_Table();
int iRowsStart = 5;
//put demo records into myTable
for (int i = 1; i <= iRowsStart; i++)
{
SQLServerDB.process_area affItem = new SQLServerDB.process_area();
affItem.process_areaId = i;
affItem.process_areaName = "aff_Name_" + i.ToString();
affItem.process_areaType = "aff_Type_" + i.ToString();
affItem.specificGoal = "aff_specificGoal_" + i.ToString();
affItem.specificPractice = "aff_specificPractice_" + i.ToString();
affItem.genericGoal = "aff_genericGoal_" + i.ToString();
affItem.genericPractice = "aff_genericPractice_" + i.ToString();
affItem.processArea = "aff_processArea_" + i.ToString();
affItem.projectId = 404; //setting each item to the same projectID to support find by projectID
myTable.itemList.Add(affItem);
}
//Count SQLServerDB process_area table rows before clearing
int iRows1 = myTable.CountRows();
Console.WriteLine("myTable.CountRows = " + iRows1.ToString());
myTable.Clear_Database_Table();
int iRows2 = myTable.CountRows();
if (iRows2 != 0)
{
Console.WriteLine("Error! iRows2=" + iRows2 + ". After Clear_Database_Table should be zero");
}
myTable.WriteItemListToDatabase();
int iRows3 = myTable.CountRows();
if (iRows3 != iRowsStart)
{
Console.WriteLine("Error! iRows3=" + iRows3 + ". After WriteItemListToDatabase should be " + iRowsStart);
}
else
{
Console.WriteLine("OK. CountRows=" + iRows3 + " After WriteItemListToDatabase");
}
Util.pause("examine table content with SSMS");
Util.pause("before table query by projectID");
int iProjectCount_404 = myTable.CountRows_By_projectId(404);
if (iProjectCount_404 != iRowsStart)
{
Console.WriteLine("ERROR. iProjectCount_404=" + iProjectCount_404 + ". Expected " + iRowsStart);
}
else
{
Console.WriteLine("OK. CountRows=" + iProjectCount_404 + " After WriteItemListToDatabase");
}
Util.pause();
#endif
Console.WriteLine(" --DONE: TestDBI_T_process_area_T5");
}
/// <summary>
/// TestDBI_T_process_area_T4 --
/// </summary>
static void TestDBI_T_process_area_T4()
{
Console.WriteLine(" --START: TestDBI_T_process_area_T4");
#if Comment
//Construct a brand new myTable in RAM
SQLServerDB.process_area_Table myTable = new SQLServerDB.process_area_Table();
//put demo records into myTable
for (int i = 1; i < 10; i++)
{
SQLServerDB.process_area affItem = new SQLServerDB.process_area();
affItem.process_areaId = i;
affItem.process_areaName = "aff_Name_" + i.ToString();
affItem.process_areaType = "aff_Type_" + i.ToString();
affItem.specificGoal = "aff_specificGoal_" + i.ToString();
affItem.specificPractice = "aff_specificPractice_" + i.ToString();
affItem.genericGoal = "aff_genericGoal_" + i.ToString();
affItem.genericPractice = "aff_genericPractice_" + i.ToString();
affItem.processArea = "aff_processArea_" + i.ToString();
affItem.projectId = i;
myTable.itemList.Add(affItem);
}
//Count SQLServerDB process_area 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();
foreach (SQLServerDB.process_area r in myTable.itemList)
{
myTable.WriteItemToDatabase(r);
}
Console.WriteLine("after writing to SQLServerDB");
Util.pause();
int iRows3 = myTable.CountRows();
Console.WriteLine("myTable.CountRows = " + iRows3.ToString());
Util.pause();
Console.WriteLine("---update the table");
//revise the myTable contents
myTable.itemList.Clear();
//put demo records into myTable
for (int i = 1; i < 10; i++)
{
SQLServerDB.process_area affItem = new SQLServerDB.process_area();
affItem.process_areaId = i;
affItem.process_areaName = "aff_Name_" + i.ToString() + "_A";
affItem.process_areaType = "aff_Type_" + i.ToString() + "_B";
affItem.specificGoal = "aff_specificGoal_" + i.ToString() + "_C";
affItem.specificPractice = "aff_specificPractice_" + i.ToString() + "_D";
affItem.genericGoal = "aff_genericGoal_" + i.ToString() + "_E";
affItem.genericPractice = "aff_genericPractice_" + i.ToString() + "_F";
affItem.processArea = "aff_processArea_" + i.ToString() + "_G";
affItem.projectId = i + 100;
myTable.itemList.Add(affItem);
}
Console.WriteLine("BEFORE the table update");
Util.pause();
myTable.UpdateItemListToDatabase();
Console.WriteLine("AFTER the table update");
Util.pause();
for (int i = 4; i <= 6; i++)
{
myTable.Clear_Database_Table_By_process_areaID(i);
}
Console.WriteLine("AFTER the table record deletions: process_areaI={4,5,6}");
Util.pause();
myTable.Clear_Database_Table_By_projectD(102);
myTable.Clear_Database_Table_By_projectD(108);
Console.WriteLine("AFTER the table record deletions: ProjectID = {102, 108}");
Util.pause();
#endif
Console.WriteLine(" --DONE: TestDBI_T_process_area_T4");
}
}//TestDBI_T_process_area_Read_from_DB
/// <summary>
/// TestDBI_T_process_area_T3 - clear the SQLServer process_area table, write some demo data to SQLServer DB,
/// query the affirmatin table by Project ID,
/// </summary>
static void TestDBI_T_process_area_T3()
{
Console.WriteLine(" --START: TestDBI_T_process_area_T3");
//Construct myTable in RAM
SQLServerDB.process_area_Table myTable = new SQLServerDB.process_area_Table();
#if Comment
//put demo records into myTable
for (int i = 1; i < 10; i++)
{
SQLServerDB.process_area affItem = new SQLServerDB.process_area();
affItem.process_areaId = i;
affItem.process_areaName = "aff_Name_" + i.ToString();
affItem.process_areaType = "aff_Type_" + i.ToString();
affItem.specificGoal = "aff_specificGoal_" + i.ToString();
affItem.specificPractice = "aff_specificPractice_" + i.ToString();
affItem.genericGoal = "aff_genericGoal_" + i.ToString();
affItem.genericPractice = "aff_genericPractice_" + i.ToString();
affItem.processArea = "aff_processArea_" + i.ToString();
affItem.projectId = i;
myTable.itemList.Add(affItem);
}
//Count SQLServerDB process_area 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();
int iSeek_ProjectID = 3;
Console.WriteLine("seek item: iSeek_ProjectID= " + iSeek_ProjectID);
myTable.ReadItemListFromDatabase_ByProjectID(iSeek_ProjectID);
Console.WriteLine("SEEK items found: myTable.itemList.Count =" + myTable.itemList.Count.ToString());
SQLServerDB.process_area affSeek = myTable.itemList[0];
Console.WriteLine("affSeek.process_areaId =" + affSeek.process_areaId);
Console.WriteLine("affSeek.process_areaName =" + affSeek.process_areaName);
Console.WriteLine("affSeek.process_areaType =" + affSeek.process_areaType);
Console.WriteLine("affSeek.specificGoal =" + affSeek.specificGoal);
Console.WriteLine("affSeek.specificPractice =" + affSeek.specificPractice);
Console.WriteLine("affSeek.genericGoal =" + affSeek.genericGoal);
Console.WriteLine("affSeek.genericPractice =" + affSeek.genericPractice);
Console.WriteLine("affSeek.processArea =" + affSeek.processArea);
Console.WriteLine("affSeek.projectId =" + affSeek.projectId);
Util.pause();
//set the search-by criteria
String strSeek_processArea = "aff_processArea_4";
myTable.ReadItemListFromDatabase_By_processArea(strSeek_processArea);
Console.WriteLine("SEEK items by processArea found: myTable.itemList.Count =" + myTable.itemList.Count.ToString());
SQLServerDB.process_area affSeek_processArea = myTable.itemList[0];
Console.WriteLine("affSeek_processArea.process_areaId =" + affSeek_processArea.process_areaId);
Console.WriteLine("affSeek_processArea.process_areaName =" + affSeek_processArea.process_areaName);
Console.WriteLine("affSeek_processArea.process_areaType =" + affSeek_processArea.process_areaType);
Console.WriteLine("affSeek_processArea.specificGoal =" + affSeek_processArea.specificGoal);
Console.WriteLine("affSeek_processArea.specificPractice =" + affSeek_processArea.specificPractice);
Console.WriteLine("affSeek_processArea.genericGoal =" + affSeek_processArea.genericGoal);
Console.WriteLine("affSeek_processArea.genericPractice =" + affSeek_processArea.genericPractice);
Console.WriteLine("affSeek_processArea.processArea =" + affSeek_processArea.processArea);
Console.WriteLine("affSeek_processArea.projectId =" + affSeek_processArea.projectId);
Util.pause();
#endif
Console.WriteLine(" --DONE: TestDBI_T_process_area_T3");
}