}//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
