public int Delete()
{
//Use with caution! This will fail if orphaned entries exist
String queryFull = "DELETE FROM " + tableName, queryCondition = " WHERE " + attribNames[0] + "=" + BookingID + " AND " + attribNames[1] + "='" + Creator + "'";
//Verify input keys
if (BookingID < 0) { return -1; }
if (Creator == "" || Creator == " " || Creator == "*") { return -1; }
/* TO-DO: Uncomment lines below when Booking class exists */
//Booking test = New Booking()
//int bTest = test.Load(BookingID);
//if( bTest < 0) { return -1; }
UserRecord uTest = new UserRecord();
int uInTest = uTest.Load(Creator);
if (uInTest < 0) { return -1; }
//Wot? No DB integrity checks? Resource Requests don't need them, as deleting won't result in orphan entries
queryFull += queryCondition; //pull query together
//attempt DB connection
try
{
dbConnection = new SqlConnection(dbInString);
dbConnection.Open();
}
catch { return -1; }
//create command that will run query
dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = queryFull;
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandTimeout = 15;
//Run command, and tidy-up if exceptions occur
try { dbReader = dbCommand.ExecuteReader(); }
catch
{
dbReader.Close();
dbConnection.Close();
return -1;
}
//Check was deleted successfully
int delCheck = Load(BookingID, Creator);
if (delCheck > -1) { return -1; }
NewOrOld = true; //as the record no longer exists in the database, is considered 'new'
//Clean-up DB connection
try
{
dbReader.Close();
dbConnection.Close();
}
catch { return -1; }
return 0;
}
public int Save()
{
//Strings to handle query as it's built
String queryFull = "", queryCondition = "";
//Verify input keys
if (BookingID < 0) { return -1; }
if (Creator == "" || Creator == " " || Creator == "*") { return -1; }
/* TO-DO: Uncomment lines below when Booking class exists */
//Booking test = New Booking()
//int bTest = test.Load(BookingID);
//if( bTest < 0) { return -1; }
UserRecord uTest = new UserRecord();
int uInTest = uTest.Load(Creator);
if (uInTest < 0) { return -1; }
//is this a new record? If so, we need an INSERT statement. Else, an UPDATE
if (NewOrOld == true)
{
queryFull = "INSERT INTO " + tableName + " VALUES (";
queryFull += BookingID + ", ";
queryFull += "'" + Creator + "', ";
queryFull += "'" + Note + "', ";
queryFull += "'" + createdTimeStamp.ToString("dd-MMMM-yyyy H:mm:ss") + "')";
}
else
{
//verify the record exists
BookingNote test = new BookingNote();
int initCheck = test.Load(BookingID, Creator);
if (initCheck < 0) { return -1; }
//build the UPDATE query (the two primary key fields are skipped here)
queryFull = "UPDATE " + tableName + " SET ";
queryCondition = "WHERE " + attribNames[0] + "=" + BookingID + " AND " + attribNames[1] + "='" + Creator + "'"; //compound key!
queryFull += attribNames[2] + "='" + Note + "', ";
queryFull += attribNames[3] + "='" + createdTimeStamp.ToString("dd-MMMM-yyyy H:mm:ss") + "' ";
queryFull += queryCondition;
}
//attempt DB connection
try
{
dbConnection = new SqlConnection(dbInString);
dbConnection.Open();
}
catch { return -1; }
//create command that will run query
dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = queryFull;
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandTimeout = 15;
//Run command, and tidy-up if exceptions occur
try { dbReader = dbCommand.ExecuteReader(); }
catch
{
dbReader.Close();
dbConnection.Close();
return -1;
}
//Clean-up DB connection
try
{
dbReader.Close();
dbConnection.Close();
}
catch { return -1; }
//Check it saved correctly
if (NewOrOld == true)
{
int testAgain = Load(BookingID, Creator);
if (testAgain < 0) { return -1; }
}
NewOrOld = false; //fail-safe
return 0;
}
public int Save()
{
//Strings to handle query as it's built
String queryFull = "", queryCondition="";
//verify input username is valid
if (userName == "" || userName == "*" || userName == " ") { return -1; }
//is this a new record? If so, we need an INSERT statement. Else, an UPDATE
if (NewOrOld == true)
{
queryFull = "INSERT INTO " + tableName + " VALUES (";
queryFull += "'" + userName + "', ";
queryFull += "'" + userPass + "', ";
queryFull += "'" + userRoles + "',";
queryFull += "'" + userFirstName + "',";
queryFull += "'" + userSecondName + "',";
queryFull += "'" + userEmail + "',";
queryFull += "'" + userJobTitle + "',";
queryFull += "'" + userDept + "',";
queryFull += "'" + colourCode + "',";
queryFull += "'" + createdTimeStamp.ToString("dd-MMMM-yyyy H:mm:ss") + "')";
}
else
{
//verify the record actually exists
UserRecord test = new UserRecord();
int initCheck = test.Load(userName);
if (initCheck < 0) { return -1; }
//build the UPDATE query
queryFull = "UPDATE " + tableName + " SET ";
queryCondition = "WHERE " + attribNames[0] + "='" + userName + "'";
/* TO ADD TO DOCUMENTATION */
//wot? no element '0'? This method does not update primary keys.
//this is because a) the database prevents this by design
//& b) we don't want orphaned entries.
//to change a username, the following should happen
//a) load in user record & verify it's the one you expected
//b) Load in any associated bookings & work tickets (they'll load in any dependencies)
//c) Run delete method for loaded bookings & work tickets (keep them in memory though)
//d) Run delete method on existing user record
//e) Use class setters to set username for loaded user, booking & work ticket objects
//f) Run save method for user object, THEN for bookings & work ticket
queryFull += attribNames[1] + "='" + userPass + "', ";
queryFull += attribNames[2] + "='" + userRoles + "', ";
queryFull += attribNames[3] + "='" + userFirstName + "', ";
queryFull += attribNames[4] + "='" + userSecondName + "', ";
queryFull += attribNames[5] + "='" + userEmail + "', ";
queryFull += attribNames[6] + "='" + userJobTitle + "', ";
queryFull += attribNames[7] + "='" + userDept + "', ";
queryFull += attribNames[8] + "='" + colourCode + "', ";
queryFull += attribNames[9] + "='" + createdTimeStamp.ToString("dd-MMMM-yyyy H:mm:ss") + "' ";
queryFull += queryCondition;
}
//attempt DB connection
try
{
dbConnection = new SqlConnection(dbInString);
dbConnection.Open();
}
catch { return -1; }
//create command that will run query
dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = queryFull;
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandTimeout = 15;
//Run command, and tidy-up if exceptions occur
try { dbReader = dbCommand.ExecuteReader(); }
catch
{
dbReader.Close();
dbConnection.Close();
return -1;
}
//Clean-up DB connection
try
{
dbReader.Close();
dbConnection.Close();
}
catch { return -1; }
//check if saved correctly if new
if (NewOrOld == true)
{
int saveCheck = Load(userName);
if (saveCheck < 0) { return -1; }
}
NewOrOld = false; //fail-safe
return 0;
}
public int Populate(String fieldName, String criteria)
{
//re-initialise lists
userList = new List<UserRecord>();
userNames = new List<String>();
//Build query - only need a list of IDs to know which bookings to populate list with
String queryFull = "SELECT " + attribNames[0] + " FROM " + tableName + " ";
String queryCondition = "WHERE " + fieldName + "=" + criteria;
//verify fieldname is valid. If not assume we have 'special criteria'
Boolean fieldTest = false;
for (int i = 0; i <= numAttribs; i++)
{
if (attribNames[i] == fieldName)
{
fieldTest = true;
break;
}
}
if (!fieldTest) {
if (criteria != "")
{
queryCondition = "WHERE " + criteria;
}
else
{
queryCondition = "";
}
}
queryFull += queryCondition;
//attempt DB connection
try
{
dbConnection = new SqlConnection(dbInString);
dbConnection.Open();
}
catch { return -1; }
//create command that will run query
dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = queryFull;
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandTimeout = 15;
//Run command, and tidy-up if exceptions occur
try { dbReader = dbCommand.ExecuteReader(); }
catch
{
dbReader.Close();
dbConnection.Close();
return -1;
}
//read in all the results and add them to list of booking IDs
while (dbReader.Read())
{
try
{
userNames.Add(dbReader[attribNames[0]].ToString());
}
catch { return -1; }
}
//verify at least one result was returned, else error
if (userNames.Count() < 1) { return -1; }
//Load found bookings into list
foreach (String res in userNames)
{
UserRecord newUserRecord = new UserRecord();
int loadNewUserRecord = newUserRecord.Load(res);
if (loadNewUserRecord < 0) { return -1; }
else
{
userList.Add(newUserRecord);
}
}
//verify that more than one booking has been added
if (userList.Count() < 1) { return -1; }
//Clean-up DB connection
try
{
dbReader.Close();
dbConnection.Close();
}
catch { return -1; }
return 0;
}
/* TO ADD TO DOCUMENTATION */
// Delete does not take parameters. This is on purpose.
// Before deleting a record it's important to load it in & verify this is what you want to do
// Records that have child entries in other tables will throw an SQL error as these are needed for stats purposes
public int Delete()
{
//Use with caution! This will fail if orphaned entries exist
String queryFull = "DELETE FROM " + tableName, queryCondition = " WHERE " + attribNames[0] + "='" + userName + "'";
//verify input username is valid
if (userName == "" || userName == "*" || userName == " ") { return -1; }
//Check record exists in the first place
UserRecord test = new UserRecord();
int initCheck = test.Load(userName);
if (initCheck < 0) { return -1; }
/* TO DO - UNCOMMENT AND TEST LINES BELOW */
// block delete if there are any bookings created by or assigned to user
//Booking bookingNew = new Booking();
//int bookCreatorCheck = bookingNew.Search(3, userName); //uses attribID & string as parameters
//int bookAssignCheck = bookingNew.Search(1, userName);
//if(bookCreatorCheck > -1 || bookAssignCheck > -1) { return -1; }
//...likewise for work tickets
//WorkTicket wtNew = new WorkTicket();
//int wtCreatorCheck = wtNew.Search(1, userName);
//int wtAssignCheck = wtNew.Search(3, userName);
//if(wtCreatorCheck > -1 || wtAssignCheck > -1) { return -1; }
//...and any requests
//RoomRequest rrNew = new RoomRequest();
//int rrCheck = rrNew.Search(2, userName);
//if(rrCheck > -1) { return -1; }
/* TO-DO: Make sure txtUserName is actually stored in resource request table */
//ResourceRequest rsrNew = New ResourceRequest();
//int rsrCheck = rsrNew.Search(2, userName);
//if(rsrCheck > -1) { return -1; }
//...and any log entries
//WorkTicketLog wtlNew = new WorkTicketLog();
//int wtlCheck = wtlNew.Search(3, userName);
//if(wtlCheck > -1) { return -1; }
queryFull += queryCondition; //pull query together
//attempt DB connection
try
{
dbConnection = new SqlConnection(dbInString);
dbConnection.Open();
}
catch { return -1; }
//create command that will run query
dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = queryFull;
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandTimeout = 15;
//Run command, and tidy-up if exceptions occur
try { dbReader = dbCommand.ExecuteReader(); }
catch
{
dbReader.Close();
dbConnection.Close();
return -1;
}
//Check was deleted successfully
int delCheck = Load(userName);
if (delCheck > -1) { return -1; }
NewOrOld = true; //as the record no longer exists in the database, is considered 'new'
//Clean-up DB connection
try
{
dbReader.Close();
dbConnection.Close();
}
catch { return -1; }
return 0;
}