// 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
{
}
}
}
// 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);
}
}