}//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
// <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(affirmation other) { return( (this.affirmationId == other.affirmationId) && (this.affirmationName == other.affirmationName) && (this.affirmationType == other.affirmationType) && (this.specificGoal == other.specificGoal) && (this.specificPractice == other.specificPractice) && (this.genericGoal == other.genericGoal) && (this.genericGoal == other.genericGoal) && (this.processArea == other.processArea) && (this.processArea == other.processArea) ); }//Equals
} //ReadItemListFromDatabase_ByProjectID //--------------------------------------------------------------------------------------------------------------- ///<summary> /// ReadItemListFromDatabase_By_processArea - read all records from "theTable" insert them into this.itemList, filter by processArea /// 1) Erase the current itemList in memory first /// 2) Read records from SQLServer, filling the itemList ///</summary> ///<input>List<affirmation> itemList - an ordinary List<> of type affirmation, will be cleared if not already empty </input> ///<output>List<affirmation> itemList- an ordinary List<> of type affirmation, extracted from the database </output> ///<param name="processArea"></param> public void ReadItemListFromDatabase_By_processArea(String processArea) { String singleQuote = "'"; itemList.Clear(); //First, empty the existing list contents string sQuery = "SELECT * FROM " + theTable + " WHERE processArea LIKE " + singleQuote + processArea + singleQuote; 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 intaffirmationId = Convert.ToInt32(dr["affirmationId"]); String straffirmationName = dr["affirmationName"].ToString(); String straffirmationType = dr["affirmationType"].ToString(); String strspecificGoal = dr["specificGoal"].ToString(); String strspecificPractice = dr["specificPractice"].ToString(); String strgenericGoal = dr["genericGoal"].ToString(); String strgenericPractice = dr["genericPractice"].ToString(); int intprojectId = Convert.ToInt32(dr["projectId"]); //fill the itemList affirmation newRec = new affirmation(); newRec.ID = intID; newRec.affirmationId = intaffirmationId; newRec.affirmationName = straffirmationName; newRec.affirmationType = straffirmationType; newRec.specificGoal = strspecificGoal; newRec.specificPractice = strspecificPractice; newRec.genericGoal = strgenericGoal; newRec.genericPractice = strgenericPractice; newRec.processArea = processArea; newRec.projectId = intprojectId; itemList.Add(newRec); } //for } } //ReadItemListFromDatabase_By_processArea
}//UpdateItemListToDatabase //--------------------------------------------------------------------------------------------------------------- ///<summary> /// UpdateItemToDatabase - update one record within "theTable" in the database ///</summary> ///<input>affirmation 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(affirmation r) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("New connection failed to open; in affirmation_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 " + " affirmationName=@affirmationName," + " affirmationType=@affirmationType," + " specificGoal=@specificGoal," + " specificPractice=@specificPractice," + " genericGoal=@genericGoal," + " genericPractice=@genericPractice," + " processArea=@processArea," + "projectId=@projectId" + " WHERE " + " affirmationId=@affirmationId"; // <<<---- 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("@affirmationId", r.affirmationId); /// <<---this is the record selection parameter 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(); }//UpdateItemToDatabase
static void TestDBI_T_affirmation_T5() { Console.WriteLine(" --START: TestDBI_T_affirmation_T5"); //Construct a brand new myTable in RAM SQLServerDB.affirmation_Table myTable = new SQLServerDB.affirmation_Table(); int iRowsStart = 5; //put demo records into myTable for (int i = 1; i <= iRowsStart; i++) { SQLServerDB.affirmation affItem = new SQLServerDB.affirmation(); affItem.affirmationId = i; affItem.affirmationName = "aff_Name_" + i.ToString(); affItem.affirmationType = "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 affirmation 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(); Console.WriteLine(" --DONE: TestDBI_T_affirmation_T5"); }
/// <summary> /// TestDBI_T_affirmation_T4 -- /// </summary> static void TestDBI_T_affirmation_T4() { Console.WriteLine(" --START: TestDBI_T_affirmation_T4"); //Construct a brand new myTable in RAM SQLServerDB.affirmation_Table myTable = new SQLServerDB.affirmation_Table(); //put demo records into myTable for (int i = 1; i < 10; i++) { SQLServerDB.affirmation affItem = new SQLServerDB.affirmation(); affItem.affirmationId = i; affItem.affirmationName = "aff_Name_" + i.ToString(); affItem.affirmationType = "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 affirmation 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.affirmation 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.affirmation affItem = new SQLServerDB.affirmation(); affItem.affirmationId = i; affItem.affirmationName = "aff_Name_" + i.ToString() + "_A"; affItem.affirmationType = "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_AffirmationID(i); } Console.WriteLine("AFTER the table record deletions: AffirmationI={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(); Console.WriteLine(" --DONE: TestDBI_T_affirmation_T4"); }
}//TestDBI_T_affirmation_Read_from_DB /// <summary> /// TestDBI_T_affirmation_T3 - clear the SQLServer affirmation table, write some demo data to SQLServer DB, /// query the affirmatin table by Project ID, /// </summary> static void TestDBI_T_affirmation_T3() { Console.WriteLine(" --START: TestDBI_T_affirmation_T3"); //Construct myTable in RAM SQLServerDB.affirmation_Table myTable = new SQLServerDB.affirmation_Table(); //put demo records into myTable for (int i = 1; i < 10; i++) { SQLServerDB.affirmation affItem = new SQLServerDB.affirmation(); affItem.affirmationId = i; affItem.affirmationName = "aff_Name_" + i.ToString(); affItem.affirmationType = "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 affirmation 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.affirmation affSeek = myTable.itemList[0]; Console.WriteLine("affSeek.affirmationId =" + affSeek.affirmationId); Console.WriteLine("affSeek.affirmationName =" + affSeek.affirmationName); Console.WriteLine("affSeek.affirmationType =" + affSeek.affirmationType); 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.affirmation affSeek_processArea = myTable.itemList[0]; Console.WriteLine("affSeek_processArea.affirmationId =" + affSeek_processArea.affirmationId); Console.WriteLine("affSeek_processArea.affirmationName =" + affSeek_processArea.affirmationName); Console.WriteLine("affSeek_processArea.affirmationType =" + affSeek_processArea.affirmationType); 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(); Console.WriteLine(" --DONE: TestDBI_T_affirmation_T3"); }