public int Save()
{
//Strings to handle query as it's built
String queryFull = "", queryCondition = "";
//verify input username is valid
if (clientName == "" || clientName == "*" || clientName == " ") { 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 += "'" + clientName + "', ";
queryFull += "'" + clientOrg + "', ";
queryFull += "'" + clientAddFirstLine + "', ";
queryFull += "'" + clientAddSecondLine + "', ";
queryFull += "'" + clientAddThirdLine + "', ";
queryFull += "'" + clientAddPostCode + "', ";
queryFull += "'" + clientPhoneLandLine + "', ";
queryFull += "'" + clientPhoneMobile + "', ";
queryFull += "'" + clientAddEmail + "', ";
queryFull += "'" + clientJobTitle + "', ";
queryFull += "'" + clientJobDept + "', ";
queryFull += "'" + clientColour + "', ";
queryFull += "'" + createdTimeStamp.ToString("dd-MMMM-yyyy H:mm:ss") + "')";
}
else
{
//verify the record actually exists
ClientRecord test = new ClientRecord();
int initCheck = test.Load(clientName);
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] + "='" + clientName + "'";
queryFull += attribNames[1] + "='" + clientOrg + "', ";
queryFull += attribNames[2] + "='" + clientAddFirstLine + "', ";
queryFull += attribNames[3] + "='" + clientAddSecondLine + "', ";
queryFull += attribNames[4] + "='" + clientAddThirdLine + "', ";
queryFull += attribNames[5] + "='" + clientAddPostCode + "', ";
queryFull += attribNames[6] + "='" + clientPhoneLandLine + "', ";
queryFull += attribNames[7] + "='" + clientPhoneMobile + "', ";
queryFull += attribNames[8] + "='" + clientAddEmail + "', ";
queryFull += attribNames[9] + "='" + clientJobTitle + "', ";
queryFull += attribNames[10] + "='" + clientJobDept + "', ";
queryFull += attribNames[11] + "='" + clientColour + "', ";
queryFull += attribNames[12] + "='" + 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(clientName);
if (saveCheck < 0) { return -1; }
}
NewOrOld = false; //fail-safe
return 0;
}
public int Populate(String fieldName, String criteria)
{
//re-initialise lists
clientList = new List<ClientRecord>();
clientNames = 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 + " WHERE " + fieldName + "=" + criteria;
//verify fieldname is valid
Boolean fieldTest = false;
for (int i = 0; i <= numAttribs; i++)
{
if (attribNames[i] == fieldName)
{
fieldTest = true;
break;
}
}
if (!fieldTest) { 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 booking IDs
while (dbReader.Read())
{
try
{
clientNames.Add(dbReader[attribNames[0]].ToString());
}
catch { return -1; }
}
//verify at least one result was returned, else error
if (clientNames.Count() < 1) { return -1; }
//Load found bookings into list
foreach (String res in clientNames)
{
ClientRecord newClientRecord = new ClientRecord();
int loadNewClientRecord = newClientRecord.Load(res);
if (loadNewClientRecord < 0) { return -1; }
else
{
clientList.Add(newClientRecord);
}
}
//verify that more than one booking has been added
if (clientList.Count() < 1) { return -1; }
//Clean-up DB connection
try
{
dbReader.Close();
dbConnection.Close();
}
catch { return -1; }
return 0;
}
public int Delete()
{
//Use with caution! This will fail if orphaned entries exist
String queryFull = "DELETE FROM " + tableName, queryCondition = " WHERE " + attribNames[0] + "='" + clientName + "'";
//verify input username is valid
if (clientName == "" || clientName == "*" || clientName == " ") { return -1; }
//Check record exists in the first place
ClientRecord test = new ClientRecord();
int initCheck = test.Load(clientName);
if (initCheck < 0) { return -1; }
/* TO DO - UNCOMMENT AND TEST LINES BELOW */
// block delete if there are any bookings the client is associated with
//Booking bookingNew = new Booking();
//int bookClientCheck = bookingNew.Search(2, clientName); //uses attribID & string as parameters
//if(bookClientCheck > -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(clientName);
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;
}