} //ReadItemListFromDatabase //--------------------------------------------------------------------------------------------------------------- ///<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<mapping_note> itemList - an ordinary List<> of type mapping_note, will be cleared if not already empty </input> ///<output>List<mapping_note> itemList- an ordinary List<> of type mapping_note, extracted from the database </output> ///<param name="projectId"></param> public void ReadItemListFromDatabase_ByProjectID(int projectId) { itemList.Clear(); //First, empty the existing list contents string sQuery = "SELECT * FROM " + theTable + " WHERE projectId=" + "'" + projectId.ToString() + "'"; 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 intmappingId = Convert.ToInt32(dr["mappingId"]); String strnotes = dr["notes"].ToString(); //fill the itemList mapping_note newRec = new mapping_note(); newRec.ID = intID; newRec.mappingId = intmappingId; newRec.notes = strnotes; itemList.Add(newRec); } //for } } //ReadItemListFromDatabase_ByProjectID
}//UpdateItemListToDatabase //--------------------------------------------------------------------------------------------------------------- ///<summary> /// UpdateItemToDatabas - update one record within "theTable" in the database ///</summary> ///<input>mapping_note 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(mapping_note r) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("New connection failed to open; in mapping_note_Table.cs:UpdateItemToDatabas.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" + " WHERE " + " mappingId=@mappingId"; // <<<---- 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("@notes", r.notes); myCommand.Parameters.AddWithValue("@mappingId", r.mappingId); myCommand.ExecuteNonQuery(); myConnection.Close(); }//UpdateItemToDatabas
}//WriteItemListToDatabase //--------------------------------------------------------------------------------------------------------------- ///<summary> /// WriteItemToDatabase - read all records from this.itemList and write to "theTable" ///</summary> ///<output>mapping_note r - output one mapping_note object to the "theTable" in the database </output> public void WriteItemToDatabase(mapping_note r) { SqlConnection myConnection = DBUtils.GetNewSqlConnection(); if (myConnection == null) { LogManager.writeToLog("New connection failed to open; mapping_note_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 + " (mappingId, notes) " + "VALUES ( @mappingId, @notes);"; 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("@mappingId", r.mappingId); myCommand.Parameters.AddWithValue("@notes", r.notes); myCommand.ExecuteNonQuery(); myConnection.Close(); }//WriteItemToDatabaes
// <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(mapping_note other) { return( (this.mappingId == other.mappingId) && (this.notes == other.notes) ); }//Equals
}//TestDBI_T_mapping_note_Read_from_DB /// <summary> /// TestDBI_T_mapping_note_T3 - clear the SQLServer mapping_note table, write some demo data to SQLServer DB, /// query the affirmatin table by Project ID, /// </summary> static void TestDBI_T_mapping_note_T3() { Console.WriteLine(" --START: TestDBI_T_mapping_note_T3"); //Construct myTable in RAM SQLServerDB.mapping_note_Table myTable = new SQLServerDB.mapping_note_Table(); myTable.itemList = make_mapping_note_list_3(); //Count SQLServerDB mapping_note 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.mapping_note itemSeek = myTable.itemList[0]; itemSeek.Show(); Util.pause("visual inspection via SSMS?"); Console.WriteLine(" --DONE: TestDBI_T_mapping_note_T3"); }