}//WriteItemListToDatabase
//---------------------------------------------------------------------------------------------------------------
///<summary>
/// WriteItemToDatabase - read all records from this.itemList and write to "theTable"
///</summary>
///<output>appraisal r - output one appraisal object to the "theTable" in the database </output>
public void WriteItemToDatabase(appraisal r)
{
SqlConnection myConnection = DBUtils.GetNewSqlConnection();
if (myConnection == null)
{
LogManager.writeToLog("New connection failed to open; appraisal_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 +
" (appraisalName, creator, maturityLevel, projects, samSelected, ssdSelected) " +
"VALUES ( @appraisalName, @creator, @maturityLevel, @projects, @samSelected, @ssdSelected);";
SqlCommand myCommand = new SqlCommand(strQuery, myConnection);
// myCommand.Parameters.AddWithValue("@ID", r.ID); //cannot asign to a field having IDENTITY semantics
myCommand.Parameters.AddWithValue("@appraisalName", r.AppraisalName);
myCommand.Parameters.AddWithValue("@creator", r.Creator);
myCommand.Parameters.AddWithValue("@maturityLevel", r.MaturityLevel);
myCommand.Parameters.AddWithValue("@projects", r.Projects);
myCommand.Parameters.AddWithValue("@samSelected", r.SAMSelected);
myCommand.Parameters.AddWithValue("@ssdSelected", r.SSDSelected);
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(appraisal other)
{
return(
(this.AppraisalName == other.AppraisalName) &&
(this.Creator == other.Creator) &&
(this.MaturityLevel == other.MaturityLevel) &&
(this.Projects == other.Projects) &&
(this.SAMSelected == other.SAMSelected) &&
(this.SSDSelected == other.SSDSelected)
);
}//Equals
} //ReadItemListFromDatabase
//---------------------------------------------------------------------------------------------------------------
///<summary>
/// ReadItemListFromDatabase_ByProjectID - read all records from "theTable" insert them into this.itemList, filter by appraisalName
/// 1) Erase the current itemList in memory first
/// 2) Read records from SQLServer, filling the itemList
///</summary>
///<input>List<appraisal> itemList</appraisal> itemList - an ordinary List<> of type appraisal, will be cleared if not already empty </input>
///<output>List<appraisal> itemList</appraisal> itemList - an ordinary List<> of type appraisal, extracted from the database </output>
///<param name="appraisalName"></param>
public void ReadItemListFromDatabase_ByAppraisalName(string appraisalName)
{
itemList.Clear(); //First, empty the existing list contents
string sQuery = "SELECT * FROM " + theTable +
" WHERE appraisalName=" + appraisalName;
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 strappraisalName = dr["appraisalName"].ToString();
String strcreator = dr["creator"].ToString();
int intmaturityLevel = Convert.ToInt32(dr["maturityLevel"]);
String strprojects = dr["projects"].ToString();
bool bsamSelected = Convert.ToBoolean(dr["samSelected"]);
bool bssdSelected = Convert.ToBoolean(dr["ssdSelected"]);
//fill the itemList
appraisal newRec = new appraisal();
newRec.ID = intID;
newRec.AppraisalName = strappraisalName;
newRec.Creator = strcreator;
newRec.MaturityLevel = intmaturityLevel;
newRec.Projects = strprojects;
newRec.SAMSelected = bsamSelected;
newRec.SSDSelected = bssdSelected;
itemList.Add(newRec);
} //for
}
} //ReadItemListFromDatabase_ByProjectID
}//UpdateItemListToDatabase
//---------------------------------------------------------------------------------------------------------------
///<summary>
/// UpdateItemToDatabase - update one record within "theTable" in the database
///</summary>
///<input>appraisal 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(appraisal r)
{
SqlConnection myConnection = DBUtils.GetNewSqlConnection();
if (myConnection == null)
{
LogManager.writeToLog("New connection failed to open; appraisal_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 " +
" creator=@creator," +
" maturityLevel=@maturityLevel," +
" samSelected=@samSelected," +
" ssdSelected=@ssdSelected," +
" projects=@projects" +
" WHERE " +
" appraisalName LIKE " + "@appraisalName"; // <<<---- 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("@appraisalName", r.AppraisalName);
myCommand.Parameters.AddWithValue("@creator", r.Creator);
myCommand.Parameters.AddWithValue("@maturityLevel", r.MaturityLevel);
myCommand.Parameters.AddWithValue("@samSelected", r.SAMSelected);
myCommand.Parameters.AddWithValue("@ssdSelected", r.SSDSelected);
myCommand.Parameters.AddWithValue("@projects", r.Projects);
myCommand.ExecuteNonQuery();
myConnection.Close();
}//UpdateItemToDatabase
}//TestDBI_T_appraisal_read_from_DB
//-------------------------------------------------------------------------------------------
static void TestDBI_T_appraisal_T3()
{
Console.WriteLine(" --START: TestDBI_T_appraisal_T3");
//Construct myTable in RAM
SQLServerDB.appraisal_Table myTable = new SQLServerDB.appraisal_Table();
//put demo records into myTable in RAM
myTable.itemList.Clear();
for (int i = 1; i < 10; i++)
{
SQLServerDB.appraisal iqlItem = new SQLServerDB.appraisal();
iqlItem.ID = i; //actually, a don't care; it will not be stored
iqlItem.AppraisalName = "Name_" + i.ToString();
iqlItem.Creator = "Creator_" + i.ToString();
iqlItem.MaturityLevel = i; //A valid maturity level is not really needed. Test only demonstrates storing/retreiving integers.
iqlItem.Projects = "Projects_" + i.ToString();
iqlItem.SAMSelected = Convert.ToBoolean(i % 2); // Alternate between true/false
iqlItem.SSDSelected = Convert.ToBoolean(i % 2); // Alternate between true/false
myTable.itemList.Add(iqlItem);
}
//Count SQLServerDB appraisal 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 in RAM
myTable.itemList.Clear();
for (int i = 1; i < 10; i++)
{
SQLServerDB.appraisal appraisalItem = new SQLServerDB.appraisal();
appraisalItem.ID = i; //actually, a don't care; it will not be stored
appraisalItem.AppraisalName = "Name_" + i.ToString();
appraisalItem.Creator = "Creator_" + i.ToString() + "_REV-A"; //modify the text!
appraisalItem.MaturityLevel = i + 100; //A valid maturity level is not really needed. Test only demonstrates storing/retreiving integers.
appraisalItem.Projects = "Projects_" + i.ToString() + "_REV-A"; //modify the text!
appraisalItem.SAMSelected = Convert.ToBoolean(1 + (i % 2)); // Alternate between true/false
appraisalItem.SSDSelected = Convert.ToBoolean(1 + (i % 2)); // Alternate between true/false
myTable.itemList.Add(appraisalItem);
}
myTable.Show();
Util.pause("--BEFORE the update, showing the planned updates in myTable.itemList");
SQLServerDB.appraisal appRef1 = myTable.itemList[0];
appRef1.MaturityLevel = appRef1.MaturityLevel + 200;
appRef1.Projects = appRef1.Projects + ",P99";
SQLServerDB.appraisal appRef2 = myTable.itemList[3];
appRef2.MaturityLevel = appRef2.MaturityLevel + 300;
appRef2.Projects = appRef2.Projects + ",P99";
myTable.UpdateItemListToDatabase();
Util.pause("-- AFTER the update, examine the appraisal Table using SSMS");
Console.WriteLine(" --DONE: TestDBI_T_appraisal_T3");
}//TestDBI_T_appraisal_T3