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 Populate(int bookingID)
{
//re-initialise lists
notes = new List<BookingNote>();
userNames = new List<String>();
//Build query - only need a list of creators' usernames, so query has been tailored accordingly
String queryFull = "SELECT " + attribNames[1] + " FROM " + tableName + " WHERE " + attribNames[0] + "=" + bookingID;
//Verify the ID paramerer value is sensible
if (bookingID < 0) { return -1; }
//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 users
while (dbReader.Read())
{
try
{
userNames.Add(dbReader[attribNames[1]].ToString());
}
catch { return -1; }
}
//verify more than one result. If not, report error
if (userNames.Count() < 1) { return -1; }
//now we know both booking ID & their creators (primary key values)
//...and can now use these to populate list of notes
foreach (String user in userNames)
{
BookingNote newNote = new BookingNote();
int importNote = newNote.Load(bookingID, user);
if (importNote < 0) { return -1; } //if failed, bail with error code
else
{
notes.Add(newNote); //if all went well, add to list
}
}
//verify at least one note. If not, send error code to verify there are no notes related to this booking
if (notes.Count() < 1) { return -1; }
//Clean-up DB connection
try
{
dbReader.Close();
dbConnection.Close();
}
catch { return -1; }
return 0;
}