示例#1
0
        // Implement a interface method!
        public void SqlQuery(CleaningData userinput, string connectionString)
        {
            // Use connection to database!
            using (SqlConnection sqlConn = new SqlConnection(connectionString))
            {
                // Open database connection!
                sqlConn.Open();

                // SQL Commands!
                string     checkClean = "SELECT statusID FROM RooSta_Keys JOIN Room ON Room.roomID = RooSta_Keys.roomID WHERE roomNum = " + userinput.RoomNO;
                SqlCommand cmd2       = new SqlCommand(checkClean, sqlConn);

                SqlDataReader reader = cmd2.ExecuteReader();
                while (reader.Read())
                {
                    CheckRoom = reader["statusID"].ToString();
                }

                // Close database connection!
                sqlConn.Close();

                // Open database connection!
                sqlConn.Open();

                // SQL Commands!
                string     checkClean1 = "SELECT roomID FROM Room WHERE roomNum = " + userinput.RoomNO;
                SqlCommand cmd3        = new SqlCommand(checkClean1, sqlConn);

                SqlDataReader reader1 = cmd3.ExecuteReader();
                while (reader1.Read())
                {
                    RoomID = reader1["roomID"].ToString();
                }

                // Close database connection!
                sqlConn.Close();

                // Statement!
                if (CheckRoom == "8")
                {
                    // Open database connection!
                    sqlConn.Open();

                    // SQL Commands!
                    string     updateClean = "UPDATE RooSta_Keys SET statusID = 9 WHERE roomID = @RoomID";
                    SqlCommand cmd4        = new SqlCommand(updateClean, sqlConn);
                    cmd4.Parameters.AddWithValue("@RoomID", RoomID);

                    cmd4.ExecuteNonQuery();

                    // Close database connection!
                    sqlConn.Close();
                }
                else
                {
                }
            }
        }
示例#2
0
        // Implement from interface - List used to symbolizes the connection between a class and database table!
        public List <CleaningData> SqlQuery(string connectionString)
        {
            // Use connection to database!
            using (SqlConnection sqlConn = new SqlConnection(connectionString))
            {
                // Create list!
                var cleaningList = new List <CleaningData>();

                // Create dataset to store multiple dataTables in a single collection!
                DataSet ds = new DataSet();

                // Open database connection!
                sqlConn.Open();

                // SQL Commands!
                string     cleanView = "SELECT cleanStatus, roomName, STUFF ((SELECT ', ' + inteName FROM Interior JOIN RooInt_Keys ON RooInt_Keys.inteID = Interior.inteID WHERE RooInt_Keys.roomID = Room.roomID FOR XML PATH('')),1,1,'') AS 'inteName', STUFF((SELECT ', ' + inteDesc FROM Interior JOIN RooInt_Keys ON RooInt_Keys.inteID = Interior.inteID WHERE RooInt_Keys.roomID = Room.roomID FOR XML PATH('')),1,1,'') AS 'inteDesc' FROM Status JOIN RooSta_Keys ON RooSta_Keys.statusID = Status.statusID JOIN Room ON Room.roomID = RooSta_Keys.roomID JOIN ResRoo_Keys ON ResRoo_Keys.roomID = Room.roomID";
                SqlCommand cmd       = new SqlCommand(cleanView, sqlConn);

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);

                // Close database connection!
                sqlConn.Close();

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    // Create object!
                    var cleaningData = new CleaningData
                    {
                        GetCleanSta = Convert.ToString(dr["cleanStatus"]),
                        Rnum        = Convert.ToString(dr["roomName"]),
                        Rinterior   = Convert.ToString(dr["inteName"]),
                        RinteDesc   = Convert.ToString(dr["inteDesc"])
                    };

                    // Adding to list!
                    cleaningList.Add(cleaningData);
                }
                return(cleaningList);
            }
        }