// <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 other)
{
return(
(this.mappingId == other.mappingId) &&
(this.mappingName == other.mappingName) &&
(this.mappingPath == other.mappingPath) &&
(this.processArea == other.processArea) &&
(this.specificGoal == other.specificGoal) &&
(this.specificPractice == other.specificPractice) &&
(this.genericGoal == other.genericGoal) &&
(this.genericPractice == other.genericPractice) &&
(this.processArea == other.processArea) &&
(this.projectId == other.projectId)
);
}//Equals
} //ReadItemListFromDatabase_ByProjectID
public void ReadItemListFromDatabase_ByProjectIDAndNode(int projectId, string pa, string goal, string practice)
{
itemList.Clear();
string sQuery = "SELECT * FROM " + theTable +
" WHERE " +
"projectId = " + projectId.ToString() +
" AND processArea = " + "'" + pa + "'" +
" AND goal = " + "'" + goal + "'" +
" AND practice = " + "'" + practice + "'";
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 int_ID = Convert.ToInt32(dr["ID"]);
int int_mappingId = Convert.ToInt32(dr["mappingId"]);
String strmappingName = dr["mappingName"].ToString();
String strmappingPath = dr["mappingPath"].ToString();
String strspecificGoal = dr["specificGoal"].ToString();
String strspecificPractice = dr["specificPractice"].ToString();
String strgenericGoal = dr["genericGoal"].ToString();
String strgenericPractice = dr["genericPractice"].ToString();
String strprocessArea = dr["processArea"].ToString();
int int_projectId = Convert.ToInt32(dr["projectId"]);
//fill the itemList
mapping newRec = new mapping();
newRec.ID = int_ID;
newRec.mappingId = int_mappingId;
newRec.mappingName = strmappingName;
newRec.mappingPath = strmappingPath;
newRec.specificGoal = strspecificGoal;
newRec.specificPractice = strspecificPractice;
newRec.genericGoal = strgenericGoal;
newRec.genericPractice = strgenericPractice;
newRec.processArea = strprocessArea;
newRec.projectId = int_projectId;
itemList.Add(newRec);
} //for
}
} //ReadItemListFromDatabase_ByProjectIDAndNode
}//TestDBI_T_mapping_Read_from_DB
/// <summary>
/// TestDBI_T_mapping_T3 - clear the SQLServer mapping table, write some demo data to SQLServer DB,
/// query the affirmatin table by Project ID,
/// </summary>
static void TestDBI_T_mapping_T3()
{
Console.WriteLine(" --START: TestDBI_T_mapping_T3");
//Construct myTable in RAM
SQLServerDB.mapping_Table myTable = new SQLServerDB.mapping_Table();
myTable.itemList = make_mapping_list_3();
//Count SQLServerDB mapping 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 affSeek = myTable.itemList[0];
affSeek.Show();
Util.pause();
Console.WriteLine(" --DONE: TestDBI_T_mapping_T3");
}
}//UpdateItemListToDatabase
//---------------------------------------------------------------------------------------------------------------
///<summary>
/// UpdateItemToDatabase - update one record within "theTable" in the database
///</summary>
///<input>mapping 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 r)
{
SqlConnection myConnection = DBUtils.GetNewSqlConnection();
if (myConnection == null)
{
LogManager.writeToLog("New connection failed to open; in mapping_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 " +
"mappingName=@mappingName," +
"mappingPath=@mappingPath," +
"specificGoal=@specificGoal," +
"specificPractice=@specificPractice," +
"genericGoal=@genericGoal," +
"genericPractice=@genericPractice," +
"processArea=@processArea," +
"projectId=@projectId" +
" WHERE " +
" mappingId=@mappingId"; // <<<---- match on the Primary Key
SqlCommand myCommand = new SqlCommand(strQuery, myConnection);
//DEPRECATED: myCommand.Parameters.Add(...), INSTEAD USE myCommand.Parameters.AddWithValue(...)
//WARNING: myCommand.Parameters.AddWithValue("@ID", r.ID); //cannot assign/modify a field having IDENTITY semantics
myCommand.Parameters.AddWithValue("@mappingId", r.mappingId); // <<---this is the record selection parameter
myCommand.Parameters.AddWithValue("@mappingName", r.mappingName);
myCommand.Parameters.AddWithValue("@mappingPath", r.mappingPath);
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("@processArea ", r.processArea);
myCommand.Parameters.AddWithValue("@projectId ", r.projectId);
myCommand.ExecuteNonQuery();
myConnection.Close();
}//UpdateItemToDatabase
//---------------------------------------------------------------------------------------------------------------
///<summary>
/// ReadItemListFromDatabase - read all records from "theTable" insert them into this.itemList
///</summary>
///<input>List<mapping> itemList - an ordinary List<> of type mapping, will be cleared if not already empty </input>
///<output>List<mapping> itemList - an ordinary List<> of type mapping, 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 int_ID = Convert.ToInt32(dr["ID"]);
int int_mappingId = Convert.ToInt32(dr["mappingId"]);
String strmappingName = dr["mappingName"].ToString();
String strmappingPath = dr["mappingPath"].ToString();
String strspecificGoal = dr["specificGoal"].ToString();
String strspecificPractice = dr["specificPractice"].ToString();
String strgenericGoal = dr["genericGoal"].ToString();
String strgenericPractice = dr["genericPractice"].ToString();
String strprocessArea = dr["processArea"].ToString();
int int_projectId = Convert.ToInt32(dr["projectId"]);
//fill the itemList
mapping newRec = new mapping();
newRec.ID = int_ID;
newRec.mappingId = int_mappingId;
newRec.mappingName = strmappingName;
newRec.mappingPath = strmappingPath;
newRec.specificGoal = strspecificGoal;
newRec.specificPractice = strspecificPractice;
newRec.genericGoal = strgenericGoal;
newRec.genericPractice = strgenericPractice;
newRec.processArea = strprocessArea;
newRec.projectId = int_projectId;
itemList.Add(newRec);
} //for
}
} //ReadItemListFromDatabase
}//WriteItemListToDatabase
//---------------------------------------------------------------------------------------------------------------
///<summary>
/// WriteItemToDatabase - given one item/record, write it to "theTable"
///</summary>
///<output>mapping r - output one mapping object to the "theTable" in the database </output>
public void WriteItemToDatabase(mapping r)
{
SqlConnection myConnection = DBUtils.GetNewSqlConnection();
if (myConnection == null)
{
LogManager.writeToLog("New connection failed to open; mapping_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, mappingName, mappingPath, specificGoal, specificPractice, genericGoal, genericPractice, processArea, projectId) " +
"VALUES " +
"( @mappingId, @mappingName, @mappingPath, @specificGoal, @specificPractice, @genericGoal, @genericPractice, @processArea, @projectId);";
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("@mappingName", r.mappingName);
myCommand.Parameters.AddWithValue("@mappingPath", r.mappingPath);
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("@processArea", r.processArea);
myCommand.Parameters.AddWithValue("@projectId", r.projectId);
myCommand.ExecuteNonQuery();
myConnection.Close();
}//WriteItemToDatabase