}//WriteItemListToDatabase
//---------------------------------------------------------------------------------------------------------------
///<summary>
/// WriteItemToDatabase - write the specified affirmation_note to "theTable"
///</summary>
///<output>affirmation_note r - output one affirmation_note object to the "theTable" in the database </output>
public void WriteItemToDatabase(affirmation_note r)
{
SqlConnection myConnection = DBUtils.GetNewSqlConnection();
if (myConnection == null)
{
LogManager.writeToLog("New connection failed to open; affirmation_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 +
" (affirmationId, notes) " +
"VALUES ( @affirmationId, @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("@affirmationId", r.affirmationId);
myCommand.Parameters.AddWithValue("@notes", r.notes);
myCommand.ExecuteNonQuery();
myConnection.Close();
}//WriteItemToDatabase
}//UpdateItemListToDatabase
//---------------------------------------------------------------------------------------------------------------
///<summary>
/// UpdateItemToDatabase - update one record within "theTable" in the database
///</summary>
///<input>affirmation_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(affirmation_note r)
{
SqlConnection myConnection = DBUtils.GetNewSqlConnection();
if (myConnection == null)
{
LogManager.writeToLog("New connection failed to open; in affirmation_note_Table.cs: UpdateItemListToDatabase.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 " +
" 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("@notes", r.notes);
myCommand.ExecuteNonQuery();
myConnection.Close();
}//UpdateItemToDatabase
//---------------------------------------------------------------------------------------------------------------
///<summary>
/// ReadItemListFromDatabase - read all records from "theTable" insert them into this.itemList
///</summary>
///<input>List<affirmation_note> itemList - an ordinary List<> of type affirmation_note, will be cleared if not already empty </input>
///<output>List<affirmation_note> itemList - an ordinary List<> of type affirmation_note, extracted from the database </output>
public void ReadItemListFromDatabase()
{
itemList.Clear(); //First, empty the existing list contents
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 data
int intID = Convert.ToInt32(dr["ID"]);
int intaffirmationId = Convert.ToInt32(dr["affirmationID"]);
String strnotes = dr["notes"].ToString();
//fill the itemList
affirmation_note newRec = new affirmation_note();
newRec.ID = intID;
newRec.affirmationId = intaffirmationId;
newRec.notes = strnotes;
itemList.Add(newRec);
} //for
}
} //ReadItemListFromDatabase
// <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_note other)
{
return(
(this.affirmationId == other.affirmationId) &&
(this.notes == other.notes)
);
}//Equals
}//TestDBI_T_affirmation_note_Read_from_DB
//-------------------------------------------------------------------------------------------
static void TestDBI_T_affirmation_note_T3()
{
Console.WriteLine(" --START: TestDBI_T_affirmation_note_T3");
//Construct myTable in RAM
SQLServerDB.affirmation_note_Table myTable = new SQLServerDB.affirmation_note_Table();
//put demo records into myTable
for (int i = 1; i < 10; i++)
{
SQLServerDB.affirmation_note affItem = new SQLServerDB.affirmation_note();
affItem.affirmationId = i;
affItem.notes = "aff_note_" + i.ToString();
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();
//put demo records into myTable
myTable.itemList.Clear();
for (int i = 1; i < 10; i++)
{
SQLServerDB.affirmation_note affItem = new SQLServerDB.affirmation_note();
affItem.affirmationId = i;
affItem.notes = "aff_note_" + i.ToString() + "_Rev_A";
myTable.itemList.Add(affItem);
}
myTable.Show();
Util.pause("--BEFORE the update, showing the planned updates in myTable.itemList");
myTable.UpdateItemListToDatabase();
Util.pause("-- AFTER the update, examine the affirmation_note Table using SSMS");
myTable.Clear_Database_Table_By_AffirmationID(2);
myTable.Clear_Database_Table_By_AffirmationID(4);
myTable.Clear_Database_Table_By_AffirmationID(6);
Util.pause("-- AFTER Clear_Database_Table_By_AffirmationID {2,4,6} using SSMS");
Console.WriteLine(" --DONE: TestDBI_T_affirmation_note_T3");
}//TestDBI_T_affirmation_note_T3