public int Save()
{
//Strings to handle query as it's built
String queryFull = "", queryCondition = "";
//is this a new record? If so, we need an INSERT statement. Else, an UPDATE
if (NewOrOld == true)
{
queryFull = "INSERT INTO " + tableName + " (";
//the numerical ID increments for us, so we just need to state which fields we're inserting into
int i = 0;
foreach (String att in attribNames)
{
if (i > 0 && i < attribNames.Count()-1)
{
queryFull += att + ", ";
}
if(i==attribNames.Count()-1)
{
queryFull += att + ") ";
}
i++;
}
queryFull += "VALUES (";
queryFull += "'" + bookUserAssigned + "', ";
queryFull += "'" + bookClient + "', ";
queryFull += "'" + bookCreator + "', ";
queryFull += "'" + bookStart.ToString("dd-MMMM-yyyy H:mm:ss") + "', ";
queryFull += "'" + bookEnd.ToString("dd-MMMM-yyyy H:mm:ss") + "', ";
queryFull += "'" + bookTitle + "', ";
queryFull += "'" + bookDesc + "', ";
queryFull += "'" + Convert.ToString(bookApproved) + "', ";
queryFull += "'" + Convert.ToString(bookActive) + "', ";
queryFull += "'" + bookStatus + "', ";
queryFull += "'" + Convert.ToString(bookRecurStatus) + "', ";
queryFull += "'" + bookRecurInterval + "', ";
queryFull += "'" + Convert.ToString(bookChargeable) + "', ";
queryFull += bookAmount + ", ";
queryFull += "'" + bookColour + "', ";
queryFull += "'" + createdTimeStamp.ToString("dd-MMMM-yyyy H:mm:ss") + "')";
}
else
{
//verify the record actually exists
Booking test = new Booking();
int initCheck = test.Load(bookID);
if (initCheck < 0) { return -1; }
//build the UPDATE query (clientName is skipped because it's a primary key)
queryFull = "UPDATE " + tableName + " SET ";
queryCondition = "WHERE " + attribNames[0] + "=" + bookID;
queryFull += attribNames[1] + "='" + bookUserAssigned + "', "; //Note: Check this
queryFull += attribNames[2] + "='" + bookClient + "', ";
queryFull += attribNames[3] + "='" + bookCreator + "', ";
queryFull += attribNames[4] + "='" + bookStart.ToString("dd-MMMM-yyyy H:mm:ss") + "', ";
queryFull += attribNames[5] + "='" + bookEnd.ToString("dd-MMMM-yyyy H:mm:ss") + "', ";
queryFull += attribNames[6] + "='" + bookTitle + "', ";
queryFull += attribNames[7] + "='" + bookDesc + "', ";
queryFull += attribNames[8] + "='" + Convert.ToString(bookApproved) + "', ";
queryFull += attribNames[9] + "='" + Convert.ToString(bookActive) + "', ";
queryFull += attribNames[10] + "='" + bookStatus + "', ";
queryFull += attribNames[11] + "='" + Convert.ToString(bookRecurStatus) + "', ";
queryFull += attribNames[12] + "='" + bookRecurInterval + "', ";
queryFull += attribNames[13] + "='" + Convert.ToString(bookChargeable) + "', ";
queryFull += attribNames[14] + "=" + bookAmount + ", ";
queryFull += attribNames[15] + "='" + bookColour + "', ";
queryFull += attribNames[16] + "='" + createdTimeStamp.ToString("dd-MMMM-yyyy HH: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; }
NewOrOld = false; //fail-safe
return 0;
}
public int Populate(String fieldName, String criteria)
{
//re-initialise lists
bookingList = new List<Booking>();
bookingIDs = new List<int>();
//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 ";
//verify fieldname is valid - if it isn't, assume criteria is a special case.
Boolean fieldTest = false;
for (int i = 0; i <= numAttribs; i++)
{
if (attribNames[i] == fieldName)
{
fieldTest = true;
break;
}
}
if (!fieldTest) { queryCondition += criteria; }
else
{
queryCondition += fieldName + "=" + criteria;
}
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
{
bookingIDs.Add(Convert.ToInt32(dbReader[attribNames[0]].ToString()));
}
catch { return -1; }
}
//verify at least one result was returned, else error
if (bookingIDs.Count() < 1) { return -1; }
//Load found bookings into list
foreach (int id in bookingIDs)
{
Booking newBooking = new Booking();
int loadNewBooking = newBooking.Load(id);
if (loadNewBooking < 0) { return -1; }
else
{
bookingList.Add(newBooking);
}
}
//verify that more than one booking has been added
if (bookingList.Count() < 1) { return -1; }
//Clean-up DB connection
try
{
dbReader.Close();
dbConnection.Close();
}
catch { return -1; }
return 0;
}