}//WriteItemListToDatabase //--------------------------------------------------------------------------------------------------------------- ///<summary> /// WriteItemToDatabase - read all records from this.itemList and write to "theTable" ///</summary> ///<output>improvement_opportunity r - output one improvement_opportunity object to the "theTable" in the database </output> public void WriteItemToDatabase(improvement_opportunity r) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { Console.WriteLine("New connection failed to open; WriteItemToDatabased.GetNewSqlConnection()"); LogManager.writeToLog("New connection failed to open; WriteItemToDatabased.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 + " (notes, specificGoal, specificPractice, genericGoal, genericPractice, projectId, processArea) " + " VALUES " + " ( @notes, @specificGoal, @specificPractice, @genericGoal, @genericPractice, @projectId, @processArea);"; SqlCommand myCommand = new SqlCommand(strQuery, myConnection); //DEPRECATED: myCommand.Parameters.Add(...), INSTEAD USE myCommand.Parameters.AddWithValue(...) // myCommand.Parameters.AddWithValue("@ID", r.ID); //cannot asign to a field having IDENTITY semantics myCommand.Parameters.AddWithValue("@notes", r.notes); 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("@projectId", r.projectId); myCommand.Parameters.AddWithValue("@processArea", r.processArea); 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(improvement_opportunity other) { return( (this.notes == other.notes) && (this.processArea == other.processArea) && (this.specificGoal == other.specificGoal) && (this.specificPractice == other.specificPractice) && (this.genericGoal == other.genericGoal) && (this.genericPractice == other.genericPractice) && (this.projectId == other.projectId) ); }//Equals
} //ReadItemListFromDatabase_ByProjectID //--------------------------------------------------------------------------------------------------------------- ///<summary> /// ReadItemListFromDatabase_ByProjectID - 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<improvement_opportunity> itemList - an ordinary List<> of type improvement_opportunity, will be cleared if not already empty </input> ///<output>List<improvement_opportunity> itemList- an ordinary List<> of type improvement_opportunity, extracted from the database </output> ///<param name="projectId"></param> public void ReadItemListFromDatabase_ByProcessArea(String v_processArea) { itemList.Clear(); //First, empty the existing list contents string sQuery = "SELECT * FROM " + theTable + " WHERE " + " processArea LIKE" + "@processArea"; 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 strnotes = dr["notes"].ToString(); String strspecificGoal = dr["specificGoal"].ToString(); String strspecificPractice = dr["specificPractice"].ToString(); String strgenericGoal = dr["genericGoal"].ToString(); String strgenericPractice = dr["genericPractice"].ToString(); int int_projectId = Convert.ToInt32(dr["projectId"]); String strprocessArea = dr["processArea"].ToString(); //fill the itemList improvement_opportunity newRec = new improvement_opportunity(); newRec.ID = intID; newRec.notes = strnotes; newRec.specificGoal = strspecificGoal; newRec.specificPractice = strspecificPractice; newRec.genericGoal = strgenericGoal; newRec.genericPractice = strgenericPractice; newRec.projectId = int_projectId; newRec.processArea = strprocessArea; itemList.Add(newRec); } //for } } //ReadItemListFromDatabase_ByProcessArea
static List <improvement_opportunity> make_improvement_opportunity_list_1() { List <improvement_opportunity> ioList = new List <improvement_opportunity>(); for (int i = 1; i < 10; i++) { SQLServerDB.improvement_opportunity improvement_opportunityItem = new SQLServerDB.improvement_opportunity(); improvement_opportunityItem.ID = i; //actually, a don't care; it will not be stored improvement_opportunityItem.notes = "notes_" + i.ToString(); improvement_opportunityItem.specificGoal = "sg_" + i.ToString(); improvement_opportunityItem.specificPractice = "sp_" + i.ToString(); improvement_opportunityItem.genericGoal = "gg_" + i.ToString(); improvement_opportunityItem.genericPractice = "gp_" + i.ToString(); improvement_opportunityItem.projectId = i; improvement_opportunityItem.processArea = "process_area_" + i.ToString(); ioList.Add(improvement_opportunityItem); }//for return(ioList); }//make_improvement_opportunity_list_1
}//UpdateItemListToDatabase //--------------------------------------------------------------------------------------------------------------- ///<summary> /// UpdateItemToDatabase - update one record within "theTable" in the database ///</summary> ///<input>improvement_opportunity 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(improvement_opportunity r) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("New connection failed to open; improvement_opportunity_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 " + " notes=@notes," + " processArea=@processArea," + " specificGoal=@specificGoal," + " specificPractice=@specificPractice," + " genericGoal=@genericGoal," + " genericPractice=@genericPractice" + " WHERE " + " 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("@notes", r.notes); myCommand.Parameters.AddWithValue("@processArea", r.processArea); 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("@projectId", r.projectId); myCommand.ExecuteNonQuery(); myConnection.Close(); }//UpdateItemToDatabase
//------------------------------------------------------------------------------------------- static void TestDBI_T_improvement_opportunity_T3() { Console.WriteLine(" --START: TestDBI_T_improvement_opportunity_T3"); //Construct myTable in RAM SQLServerDB.improvement_opportunity_Table myTable = new SQLServerDB.improvement_opportunity_Table(); //put demo records into myTable in RAM myTable.itemList.Clear(); for (int i = 1; i < 10; i++) { SQLServerDB.improvement_opportunity improvement_opportunityItem = new SQLServerDB.improvement_opportunity(); improvement_opportunityItem.ID = i; //actually, a don't care; it will not be stored improvement_opportunityItem.notes = "notes_" + i.ToString(); improvement_opportunityItem.specificGoal = "sg_" + i.ToString(); improvement_opportunityItem.specificPractice = "sp_" + i.ToString(); improvement_opportunityItem.genericGoal = "gg_" + i.ToString(); improvement_opportunityItem.genericPractice = "gp_" + i.ToString(); improvement_opportunityItem.projectId = i; improvement_opportunityItem.processArea = "process_area_" + i.ToString(); myTable.itemList.Add(improvement_opportunityItem); } #if __COMMENT_ //Count SQLServerDB improvement_opportunity table rows before clearing int iRows = myTable.CountRows(); Console.WriteLine("myTable.CountRows = " + iRows.ToString()); Console.WriteLine(" --before clear SQLServer database table"); pause(); #endif myTable.Clear_Database_Table(); int iRows2 = myTable.CountRows(); if (iRows2 != 0) { Console.WriteLine("ERROR! myTable.CountRows = " + iRows2.ToString() + "; should be zero(0)"); } 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.improvement_opportunity improvement_opportunityItem = new SQLServerDB.improvement_opportunity(); improvement_opportunityItem.ID = i; //actually, a don't care; it will not be stored improvement_opportunityItem.notes = "notes_" + i.ToString(); improvement_opportunityItem.specificGoal = "sg_" + i.ToString() + "_REV-A"; improvement_opportunityItem.specificPractice = "sp_" + i.ToString() + "_REV-A"; improvement_opportunityItem.genericGoal = "gg_" + i.ToString() + "_REV-A"; improvement_opportunityItem.genericPractice = "gp_" + i.ToString() + "_REV-A"; improvement_opportunityItem.projectId = i; improvement_opportunityItem.processArea = "process_area_" + i.ToString() + "_REV-A"; myTable.itemList.Add(improvement_opportunityItem); } myTable.Show(); Util.pause("--BEFORE the update, showing the planned updates in myTable.itemList"); myTable.UpdateItemListToDatabase(); Util.pause("-- AFTER the update, examine the improvement_opportunity Table using SSMS"); Console.WriteLine(" --DONE: TestDBI_T_improvement_opportunity_T3"); }//TestDBI_T_improvement_opportunity_T3