public int Populate(int workTicketID)
{
//re-initialise lists
wtLogList = new List<WorkTicketLog>();
wtLogTimeList = new List<DateTime>();
//Build query - only need a list of IDs to know which bookings to populate list with
String queryFull = "SELECT " + attribNames[1] + " FROM " + tableName + " WHERE " + attribNames[0] + "=" + workTicketID;
//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
{
wtLogTimeList.Add(DateTime.Parse(dbReader[attribNames[1]].ToString()));
}
catch { return -1; }
}
//verify at least one result was returned, else error
if (wtLogTimeList.Count() < 1) { return -1; }
//Load found bookings into list
foreach (DateTime res in wtLogTimeList)
{
WorkTicketLog newWTLRecord = new WorkTicketLog();
int loadNewWTLRecord = newWTLRecord.Load(workTicketID, res);
if (loadNewWTLRecord < 0) { return -1; }
else
{
wtLogList.Add(newWTLRecord);
}
}
//verify that more than one booking has been added
if (wtLogList.Count() < 1) { return -1; }
//Clean-up DB connection
try
{
dbReader.Close();
dbConnection.Close();
}
catch { return -1; }
return 0;
}
public int Populate(int workTicketID, String criteria)
{
//re-initialise lists
wtLogList = new List<WorkTicketLog>();
wtLogTimeList = new List<DateTime>();
ids = new List<int>();
//Build query - only need a list of IDs to know which bookings to populate list with
String queryFull = "SELECT " + attribNames[0] + ", " + attribNames[1] + " FROM " + tableName + " ";
String queryCondition = "WHERE ";
//verify fieldname is valid - if it isn't, assume custom WHERE condition
Boolean fieldTest = false;
if (workTicketID > 0)
{
fieldTest = true;
}
if (!fieldTest) { queryCondition += criteria; }
else
{
queryCondition += attribNames[0] + "=" + 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
{
wtLogTimeList.Add(DateTime.Parse(dbReader[attribNames[1]].ToString()));
ids.Add(Convert.ToInt32(dbReader[attribNames[0]]));
}
catch { return -1; }
}
//verify at least one result was returned, else error
if (wtLogTimeList.Count() < 1) { return -1; }
//Load found bookings into list
foreach (DateTime res in wtLogTimeList)
{
foreach(int id in ids)
{
WorkTicketLog newWTLRecord = new WorkTicketLog();
int loadNewWTLRecord = newWTLRecord.Load(id, res);
if (loadNewWTLRecord > -1) {
wtLogList.Add(newWTLRecord);
}
}
}
//verify that more than one booking has been added
if (wtLogList.Count() < 1) { return -1; }
//Clean-up DB connection
try
{
dbReader.Close();
dbConnection.Close();
}
catch { return -1; }
return 0;
}
public int Delete()
{
String queryFull = "DELETE FROM " + tableName + " ", queryCondition = "WHERE " + attribNames[0] + "=" + workTicketLogLinkID + " AND " + attribNames[1] + "='" + workTicketLogTime + "'";
//check workTicketID is sensible
if (workTicketLogLinkID < 0) { return -1; }
//check record exists in first place
WorkTicketLog test = new WorkTicketLog();
int recordCheck = test.Load(workTicketLogLinkID, workTicketLogTime);
if (recordCheck < 0) { return -1; }
//build query
queryFull += queryCondition;
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
WorkTicketLog aTest = new WorkTicketLog();
recordCheck = aTest.Load(workTicketLogLinkID, workTicketLogTime);
if (recordCheck > -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;
}