예제 #1
0
        }//WriteItemListToDatabase

        //---------------------------------------------------------------------------------------------------------------
        ///<summary>
        /// WriteItemToDatabase - read all records from this.itemList and write to "theTable"
        ///</summary>
        ///<output>process_area r - output one process_area object to the "theTable" in the database </output>
        public void WriteItemToDatabase(process_area r)
        {
            SqlConnection myConnection = DBUtils.GetNewSqlConnection();

            if (myConnection == null)
            {
                LogManager.writeToLog("new connection failed to [email protected]()");
                return;
            }

            //WARNING: A field, like "ID", defined with "IDENTITY" semantics, cannot be assigned a value since it Auto-Increments
            string strQuery = "INSERT INTO  " + theTable +
                              " (processAreaId, projectId, paName, text, active, hasArtifact, hasAffirmation, rating, coverage) " +
                              "VALUES ( @processAreaId, @projectId, @paName, @text, @active, @hasArtifact, @hasAffirmation, @rating, @coverage);";

            SqlCommand myCommand = new SqlCommand(strQuery, myConnection);

            // myCommand.Parameters.AddWithValue("@ID", r.ID); //cannot asign to a field having IDENTITY semantics
            myCommand.Parameters.AddWithValue("@processAreaId", r.processAreaId);
            myCommand.Parameters.AddWithValue("@projectId", r.projectId);
            myCommand.Parameters.AddWithValue("@paName", r.paName);
            myCommand.Parameters.AddWithValue("@text", r.text);
            myCommand.Parameters.AddWithValue("@active", r.active);
            myCommand.Parameters.AddWithValue("@hasArtifact", r.canContainArtifact);
            myCommand.Parameters.AddWithValue("@hasAffirmation", r.canContainAffirmation);
            myCommand.Parameters.AddWithValue("@rating", r.rating);
            myCommand.Parameters.AddWithValue("@coverage", r.coverage);

            myCommand.ExecuteNonQuery();

            myConnection.Close();
        }//WriteItemToDatabase
예제 #2
0
        }         //ReadItemListFromDatabase_ByProjectID

        public void ReadItemListFromDatabase_ByProjectIDAndName(int projectId, string paName)
        {
            itemList.Clear();  //First, empty the existing list contents

            string sQuery = "SELECT * FROM " + theTable +
                            " WHERE projectId = " + projectId.ToString() +
                            " AND paName = " + "'" + paName + "'";

            DataSet dsObj = DBUtils.ExecuteSqlQuery(sQuery);

            if (dsObj == null)
            {
                LogManager.writeToLog("The dataset is null in process_area_Table.ReadItemListFromDatabase_ByProjectIDAndName()");
            }

            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 all fields of the current row
                    int    intID               = Convert.ToInt32(dr["Id"]);
                    int    intProcessAreaId    = Convert.ToInt32(dr["processAreaId"]);
                    int    intProjectId        = Convert.ToInt32(dr["projectId"]);
                    string strname             = dr["paName"].ToString();
                    string strtext             = dr["text"].ToString();
                    bool   boolIsActive        = Convert.ToBoolean(dr["active"]);
                    bool   boolHasArtifacts    = Convert.ToBoolean(dr["hasArtifact"]);
                    bool   boolHasAffirmations = Convert.ToBoolean(dr["hasAffirmation"]);
                    string strRating           = (dr["rating"]).ToString();
                    bool   boolCoverage        = Convert.ToBoolean(dr["coverage"]);

                    //fill the itemList
                    process_area newRec = new process_area();
                    newRec.ID                    = intID;
                    newRec.processAreaId         = intProcessAreaId;
                    newRec.projectId             = intProjectId;
                    newRec.paName                = strname;
                    newRec.text                  = strtext;
                    newRec.active                = boolIsActive;
                    newRec.canContainArtifact    = boolHasArtifacts;
                    newRec.canContainAffirmation = boolHasAffirmations;
                    newRec.rating                = strRating;
                    newRec.coverage              = boolCoverage;

                    itemList.Add(newRec);
                }//for
            }
        }
예제 #3
0
 // <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(process_area other)
 {
     return(
         (this.processAreaId == other.processAreaId) &&
         (this.projectId == other.projectId) &&
         (this.paName == other.paName) &&
         (this.text == other.text) &&
         (this.active == other.active) &&
         (this.canContainArtifact == other.canContainArtifact) &&
         (this.canContainAffirmation == other.canContainAffirmation) &&
         (this.rating == other.rating) &&
         (this.coverage == other.coverage)
         );
 }//Equals
예제 #4
0
        //---------------------------------------------------------------------------------------------------------------
        ///<summary>
        /// ReadItemListFromDatabase - read all records from "theTable" insert them into this.itemList
        ///</summary>
        ///<input>List<process_area>  itemList - an ordinary List<> of type pa_tree_node, will be cleared if not already empty </input>
        ///<output>List<process_area> itemList - an ordinary List<> of type pa_tree_node, extracted from the database </output>
        public void ReadItemListFromDatabase()
        {
            itemList.Clear();  // ensure itemList is empty

            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 all fields of the current row
                    int    intID           = Convert.ToInt32(dr["Id"]);
                    int    processAreaId   = Convert.ToInt32(dr["processAreaId"]);
                    int    projectId       = Convert.ToInt32(dr["projectId"]);
                    string name            = dr["paName"].ToString();
                    string text            = dr["text"].ToString();
                    bool   boolIsActive    = Convert.ToBoolean(dr["active"]);
                    bool   hasArtifacts    = Convert.ToBoolean(dr["hasArtifact"]);
                    bool   hasAffirmations = Convert.ToBoolean(dr["hasAffirmation"]);
                    string strRating       = (dr["rating"]).ToString();
                    bool   boolCoverage    = Convert.ToBoolean(dr["coverage"]);

                    //fill the itemList
                    process_area newRec = new process_area();
                    newRec.ID                    = intID;
                    newRec.processAreaId         = processAreaId;
                    newRec.projectId             = projectId;
                    newRec.paName                = name;
                    newRec.text                  = text;
                    newRec.active                = boolIsActive;
                    newRec.canContainArtifact    = hasArtifacts;
                    newRec.canContainAffirmation = hasAffirmations;
                    newRec.rating                = strRating;
                    newRec.coverage              = boolCoverage;

                    itemList.Add(newRec);
                } //foreach
            }
        }         //ReadItemListFromDatabase
예제 #5
0
        }//UpdateItemListToDatabase

        //---------------------------------------------------------------------------------------------------------------
        ///<summary>
        /// UpdateItemToDatabase - update one record within "theTable" in the database;
        /// match a record based on the projectId
        ///</summary>
        ///<input>process_area 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(process_area r)
        {
            SqlConnection myConnection = DBUtils.GetNewSqlConnection();

            if (myConnection == null)
            {
                LogManager.writeToLog("new connection failed to open; in process_area_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 " +
                              " paName=@paName," +
                              " text=@text," +
                              " active=@active," +
                              " hasArtifact=@hasArtifact," +
                              " hasAffirmation=@hasAffirmation," +
                              " rating=@rating," +
                              " coverage=@coverage" +
                              " WHERE " +
                              " processAreaId=@processAreaId" +
                              " AND 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("@paName", r.paName);
            myCommand.Parameters.AddWithValue("@text", r.text);
            myCommand.Parameters.AddWithValue("@active", r.active);
            myCommand.Parameters.AddWithValue("@hasArtifact", r.canContainArtifact);
            myCommand.Parameters.AddWithValue("@hasAffirmation", r.canContainAffirmation);
            myCommand.Parameters.AddWithValue("@rating", r.rating);
            myCommand.Parameters.AddWithValue("@coverage", r.coverage);
            myCommand.Parameters.AddWithValue("@processAreaId", r.processAreaId);
            myCommand.Parameters.AddWithValue("@projectId", r.projectId);

            myCommand.ExecuteNonQuery();
            myConnection.Close();
        }//UpdateItemToDatabase
예제 #6
0
        static void TestDBI_T_process_area_T5()
        {
            Console.WriteLine("  --START: TestDBI_T_process_area_T5");
#if Comment
            //Construct a brand new myTable in RAM
            SQLServerDB.process_area_Table myTable = new SQLServerDB.process_area_Table();

            int iRowsStart = 5;
            //put demo records into myTable
            for (int i = 1; i <= iRowsStart; i++)
            {
                SQLServerDB.process_area affItem = new SQLServerDB.process_area();
                affItem.process_areaId   = i;
                affItem.process_areaName = "aff_Name_" + i.ToString();
                affItem.process_areaType = "aff_Type_" + i.ToString();
                affItem.specificGoal     = "aff_specificGoal_" + i.ToString();
                affItem.specificPractice = "aff_specificPractice_" + i.ToString();
                affItem.genericGoal      = "aff_genericGoal_" + i.ToString();
                affItem.genericPractice  = "aff_genericPractice_" + i.ToString();
                affItem.processArea      = "aff_processArea_" + i.ToString();
                affItem.projectId        = 404; //setting each item to the same projectID to support find by projectID

                myTable.itemList.Add(affItem);
            }

            //Count SQLServerDB process_area table rows before clearing
            int iRows1 = myTable.CountRows();
            Console.WriteLine("myTable.CountRows = " + iRows1.ToString());

            myTable.Clear_Database_Table();
            int iRows2 = myTable.CountRows();
            if (iRows2 != 0)
            {
                Console.WriteLine("Error! iRows2=" + iRows2 + ".  After Clear_Database_Table should be zero");
            }

            myTable.WriteItemListToDatabase();

            int iRows3 = myTable.CountRows();
            if (iRows3 != iRowsStart)
            {
                Console.WriteLine("Error! iRows3=" + iRows3 + ".  After WriteItemListToDatabase should be " + iRowsStart);
            }
            else
            {
                Console.WriteLine("OK. CountRows=" + iRows3 + " After WriteItemListToDatabase");
            }

            Util.pause("examine table content with SSMS");

            Util.pause("before table query by projectID");
            int iProjectCount_404 = myTable.CountRows_By_projectId(404);
            if (iProjectCount_404 != iRowsStart)
            {
                Console.WriteLine("ERROR.  iProjectCount_404=" + iProjectCount_404 + ". Expected " + iRowsStart);
            }
            else
            {
                Console.WriteLine("OK. CountRows=" + iProjectCount_404 + " After WriteItemListToDatabase");
            }
            Util.pause();
#endif
            Console.WriteLine("  --DONE: TestDBI_T_process_area_T5");
        }
예제 #7
0
        /// <summary>
        /// TestDBI_T_process_area_T4 --
        /// </summary>
        static void TestDBI_T_process_area_T4()
        {
            Console.WriteLine("  --START: TestDBI_T_process_area_T4");

 #if Comment
            //Construct a brand new myTable in RAM
            SQLServerDB.process_area_Table myTable = new SQLServerDB.process_area_Table();

            //put demo records into myTable
            for (int i = 1; i < 10; i++)
            {
                SQLServerDB.process_area affItem = new SQLServerDB.process_area();
                affItem.process_areaId   = i;
                affItem.process_areaName = "aff_Name_" + i.ToString();
                affItem.process_areaType = "aff_Type_" + i.ToString();
                affItem.specificGoal     = "aff_specificGoal_" + i.ToString();
                affItem.specificPractice = "aff_specificPractice_" + i.ToString();
                affItem.genericGoal      = "aff_genericGoal_" + i.ToString();
                affItem.genericPractice  = "aff_genericPractice_" + i.ToString();
                affItem.processArea      = "aff_processArea_" + i.ToString();
                affItem.projectId        = i;

                myTable.itemList.Add(affItem);
            }

            //Count SQLServerDB process_area 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();

            foreach (SQLServerDB.process_area r in myTable.itemList)
            {
                myTable.WriteItemToDatabase(r);
            }
            Console.WriteLine("after writing to SQLServerDB");
            Util.pause();

            int iRows3 = myTable.CountRows();
            Console.WriteLine("myTable.CountRows = " + iRows3.ToString());
            Util.pause();

            Console.WriteLine("---update the table");

            //revise the myTable contents
            myTable.itemList.Clear();

            //put demo records into myTable
            for (int i = 1; i < 10; i++)
            {
                SQLServerDB.process_area affItem = new SQLServerDB.process_area();
                affItem.process_areaId   = i;
                affItem.process_areaName = "aff_Name_" + i.ToString() + "_A";
                affItem.process_areaType = "aff_Type_" + i.ToString() + "_B";
                affItem.specificGoal     = "aff_specificGoal_" + i.ToString() + "_C";
                affItem.specificPractice = "aff_specificPractice_" + i.ToString() + "_D";
                affItem.genericGoal      = "aff_genericGoal_" + i.ToString() + "_E";
                affItem.genericPractice  = "aff_genericPractice_" + i.ToString() + "_F";
                affItem.processArea      = "aff_processArea_" + i.ToString() + "_G";
                affItem.projectId        = i + 100;

                myTable.itemList.Add(affItem);
            }

            Console.WriteLine("BEFORE the table update");
            Util.pause();
            myTable.UpdateItemListToDatabase();
            Console.WriteLine("AFTER the table update");
            Util.pause();


            for (int i = 4; i <= 6; i++)
            {
                myTable.Clear_Database_Table_By_process_areaID(i);
            }
            Console.WriteLine("AFTER the table record deletions:  process_areaI={4,5,6}");
            Util.pause();

            myTable.Clear_Database_Table_By_projectD(102);
            myTable.Clear_Database_Table_By_projectD(108);
            Console.WriteLine("AFTER the table record deletions:  ProjectID = {102, 108}");
            Util.pause();
#endif
            Console.WriteLine("  --DONE: TestDBI_T_process_area_T4");
        }
예제 #8
0
        }//TestDBI_T_process_area_Read_from_DB

        /// <summary>
        /// TestDBI_T_process_area_T3 -  clear the SQLServer process_area table, write some demo data to SQLServer DB,
        /// query the affirmatin table by Project ID,
        /// </summary>
        static void TestDBI_T_process_area_T3()
        {
            Console.WriteLine("  --START: TestDBI_T_process_area_T3");

            //Construct myTable in RAM
            SQLServerDB.process_area_Table myTable = new SQLServerDB.process_area_Table();

#if Comment
            //put demo records into myTable
            for (int i = 1; i < 10; i++)
            {
                SQLServerDB.process_area affItem = new SQLServerDB.process_area();
                affItem.process_areaId   = i;
                affItem.process_areaName = "aff_Name_" + i.ToString();
                affItem.process_areaType = "aff_Type_" + i.ToString();
                affItem.specificGoal     = "aff_specificGoal_" + i.ToString();
                affItem.specificPractice = "aff_specificPractice_" + i.ToString();
                affItem.genericGoal      = "aff_genericGoal_" + i.ToString();
                affItem.genericPractice  = "aff_genericPractice_" + i.ToString();
                affItem.processArea      = "aff_processArea_" + i.ToString();
                affItem.projectId        = i;

                myTable.itemList.Add(affItem);
            }


            //Count SQLServerDB process_area 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.process_area affSeek = myTable.itemList[0];

            Console.WriteLine("affSeek.process_areaId =" + affSeek.process_areaId);
            Console.WriteLine("affSeek.process_areaName =" + affSeek.process_areaName);
            Console.WriteLine("affSeek.process_areaType =" + affSeek.process_areaType);
            Console.WriteLine("affSeek.specificGoal =" + affSeek.specificGoal);
            Console.WriteLine("affSeek.specificPractice =" + affSeek.specificPractice);
            Console.WriteLine("affSeek.genericGoal =" + affSeek.genericGoal);
            Console.WriteLine("affSeek.genericPractice =" + affSeek.genericPractice);
            Console.WriteLine("affSeek.processArea =" + affSeek.processArea);
            Console.WriteLine("affSeek.projectId =" + affSeek.projectId);

            Util.pause();

            //set the search-by criteria
            String strSeek_processArea = "aff_processArea_4";


            myTable.ReadItemListFromDatabase_By_processArea(strSeek_processArea);
            Console.WriteLine("SEEK items by processArea found: myTable.itemList.Count =" + myTable.itemList.Count.ToString());

            SQLServerDB.process_area affSeek_processArea = myTable.itemList[0];

            Console.WriteLine("affSeek_processArea.process_areaId =" + affSeek_processArea.process_areaId);
            Console.WriteLine("affSeek_processArea.process_areaName =" + affSeek_processArea.process_areaName);
            Console.WriteLine("affSeek_processArea.process_areaType =" + affSeek_processArea.process_areaType);
            Console.WriteLine("affSeek_processArea.specificGoal =" + affSeek_processArea.specificGoal);
            Console.WriteLine("affSeek_processArea.specificPractice =" + affSeek_processArea.specificPractice);
            Console.WriteLine("affSeek_processArea.genericGoal =" + affSeek_processArea.genericGoal);
            Console.WriteLine("affSeek_processArea.genericPractice =" + affSeek_processArea.genericPractice);
            Console.WriteLine("affSeek_processArea.processArea =" + affSeek_processArea.processArea);
            Console.WriteLine("affSeek_processArea.projectId =" + affSeek_processArea.projectId);

            Util.pause();
#endif

            Console.WriteLine("  --DONE: TestDBI_T_process_area_T3");
        }