public string SavedSearch (Int32 AgeFrom, Int32 AgeTo, string Location)
{
clsDataConnection DataConnection = new clsDataConnection();
DataConnection.AddParameter("AgeFrom", AgeFrom);
DataConnection.AddParameter("AgeTo", AgeTo);
DataConnection.AddParameter("Location", Location);
DataConnection.Execute("sproc_tblCustomer_GetEmailAddressFromAgeAndLocation");
string EmailAddress = "";
////get the count
Int32 RecordCount = DataConnection.Count;
Int32 Index = 0;
while (Index < RecordCount)
{
EmailAddress += DataConnection.DataTable.Rows[Index]["EmailAddress"].ToString();
if (Index == RecordCount - 1)
{
EmailAddress += "";
}
else
{
EmailAddress += ", ";
}
Index++;
}
return EmailAddress;
}
public void AddNewSavedSearch(Int32 AgeMin, Int32 AgeMax, string Location)
{
clsDataConnection NewConn = new clsDataConnection();
NewConn.AddParameter("AgeFrom", AgeMin);
NewConn.AddParameter("AgeTo", AgeMax);
NewConn.AddParameter("Location", Location);
NewConn.Execute("sproc_tblGroupList_AddNewGroupList");
}
public void RemoveItem(Int32 AgeFrom, Int32 AgeTo, string Location)
{
clsDataConnection DataConn = new clsDataConnection();
DataConn.AddParameter("AgeFrom", AgeFrom);
DataConn.AddParameter("AgeTo", AgeTo);
DataConn.AddParameter("Location", Location);
DataConn.Execute("sproc_tblGroupList_RemoveGroupList");
}
public void GetOneEmail(Int32 EmailNo, bool IsArchive, out string EmailSubject, out string EmailContent)
{
if (IsArchive == true)
{
//instance of the database connection class
clsDataConnection DB = new clsDataConnection();
//this adds the parameter ArchiveNo (masked as EmailNo for purposes of the retrieval
DB.AddParameter("ArchiveNo", EmailNo);
//this executes the stored procedure for the email address
DB.Execute("sproc_tblArchive_GetEmail");
//this retrieves one email content and subject from the Table "tblEmailAddress", index only needs to be 0
EmailContent = DB.DataTable.Rows[0]["EmailContent"].ToString();
//this retrieves the email subject.
EmailSubject = DB.DataTable.Rows[0]["EmailSubject"].ToString();
}
else
{
//instance of the database connection class
clsDataConnection DB = new clsDataConnection();
//this adds the parameter EmailNo
DB.AddParameter("EmailNo", EmailNo);
//this executes the stored procedure for the email address
DB.Execute("sproc_tblEmail_GetEmail");
//this retrieves one email content and subject from the Table "tblEmailAddress", index only needs to be 0
EmailContent = DB.DataTable.Rows[0]["EmailContent"].ToString();
//this retrieves the email subject.
EmailSubject = DB.DataTable.Rows[0]["EmailSubject"].ToString();
}
}
public bool Find(Int32 AppointmentID)
{
//create an instance of the data connection
clsDataConnection DB = new clsDataConnection();
//add the parameter for the address no to search for
DB.AddParameter("@AppointmentID", AppointmentID);
//execute the stored procedure
DB.Execute("sproc_tblAppointment_FilterByAppointmentID");
//if one record is found (there should either one or zero!)
if (DB.Count == 1)
{
//copy the data from the database to the private data members
appointmentID = Convert.ToInt32(DB.DataTable.Rows[0]["AppointmentID"]);
appointmentLocation = Convert.ToString(DB.DataTable.Rows[0]["AppointmentLocation"]);
appointmentDate = Convert.ToDateTime(DB.DataTable.Rows[0]["AppointmentDate"]);
//return that everything worked OK
return true;
}
//if no record was found
else
{
//return false indicating a probelm
return false;
}
}
public void Delete(int Staffid)
{
//deletes the recoed point ti by this staff
//connect to the database
clsDataConnection NewDBProducts = new clsDataConnection();
//set the paraters for the stored procedure
NewDBProducts.AddParameter("@staffid", Staffid);
//exectue the store procedure
NewDBProducts.Execute("sproc_tblStaff_Delete");
}
public string GetEmailAddress(Int32 EmailAddressNo)
{
//instance of the database connection class
clsDataConnection DB = new clsDataConnection();
//this adds the parameter EmailAddressNo
DB.AddParameter("EmailNo", EmailAddressNo);
//this executes the stored procedure for the email address
DB.Execute("sproc_tblEmailAddress_GetEmailAddress");
//this retrieves one email address from the Table "tblEmailAddress", index only needs to be 0
string EmailAddress = DB.DataTable.Rows[0]["EmailAddress"].ToString();
//this returns the output
return EmailAddress;
}
//Add Stafff Information
public int AddStaff(clsStaff staffModel)
{
dBConnection = new clsDataConnection();
//execute
dBConnection.AddParameter("@Name", staffModel.Staffname);
dBConnection.AddParameter("@Age", staffModel.Age);
dBConnection.AddParameter("@Brief", staffModel.Brief);
dBConnection.AddParameter("@Gender", staffModel.Gender);
dBConnection.AddParameter("@Mobilesphone", staffModel.Mobilesphone);
dBConnection.AddParameter("@workage", staffModel.Workage);
dBConnection.AddParameter("@positiob", staffModel.Position);
return dBConnection.Execute("sproc_Staff_Insert");
}
public bool Find(Int32 CostumerID)
{
clsDataConnection DB = new clsDataConnection();
DB.AddParameter("@CostumerID", CostumerID);
DB.Execute("sprac_tblCostumer_FilterByCostumerID");
if (DB.Count == 1)
{
mCostumerID = Convert.ToInt32(DB.DataTable.Rows[0]["CostumerID"]);
mActive = Convert.ToBoolean(DB.DataTable.Rows[0]["Active"]);
mName = Convert.ToString(DB.DataTable.Rows[0]["Name"]);
mCostumerDOB = Convert.ToDateTime(DB.DataTable.Rows[0]["CostumerDateOfBirth"]);
mEmail = Convert.ToString(DB.DataTable.Rows[0]["Email"]);
mCostumerAddress = Convert.ToString(DB.DataTable.Rows[0]["CostumerAddress"]);
return(true);
}
else
{
return(false);
}
}
public bool Find(int orderLineID)
{
// connect to DB
clsDataConnection DB = new clsDataConnection();
// add DB search parameter for Order ID
DB.AddParameter("@OrderLineID", orderLineID);
// execute stored procedure
DB.Execute("dbo.sproc_OrderLineTable_FilterByOrderLineID");
// if 1 record is found
if (DB.Count == 1)
{
mOrderLineID = Convert.ToInt32(DB.DataTable.Rows[0]["OrderLineID"]);
mOrderID = Convert.ToInt32(DB.DataTable.Rows[0]["OrderID"]);
mStaffID = Convert.ToInt32(DB.DataTable.Rows[0]["StaffID"]);
mOrderComplete = Convert.ToBoolean(DB.DataTable.Rows[0]["OrderComplete"]);
return(true);
}
else
{
return(false);
}
}
public bool find(int id)
{
clsDataConnection db = new clsDataConnection();
db.AddParameter(id, "@OrderId");
db.Execute("sproc_tblOrder_FilterByOrderId");
if (db.Count == 1)
{
mOrderId = Convert.ToInt32(db.DataTable.Rows[0]["OrderId"]);
mCustomerId = Convert.ToInt32(db.DataTable.Rows[0]["CustomerId"]);
mStaffId = Convert.ToInt32(db.DataTable.Rows[0]["StaffId"]);
mDate = Convert.ToDateTime(db.DataTable.Rows[0]["DatePlaced"]);
mDetails = Convert.ToString(db.DataTable.Rows[0]["Details"]);
return(true);
}
else
{
return(false);
}
}
//Methods
public bool Find(int ProductNo)
{
clsDataConnection DB = new clsDataConnection();
DB.AddParameter("@ProductNo", ProductNo);
DB.Execute("dbo.sproc_tblProduct_FilterByProductNo");
if (DB.Count == 1)
{
mProductNo = Convert.ToInt32(DB.DataTable.Rows[0]["ProductNo"]);
mProductName = Convert.ToString(DB.DataTable.Rows[0]["ProductName"]);
mProductDescription = Convert.ToString(DB.DataTable.Rows[0]["ProductDescription"]);
mUnitPrice = Convert.ToDouble(DB.DataTable.Rows[0]["UnitPrice"]);
mInStock = Convert.ToBoolean(DB.DataTable.Rows[0]["InStock"]);
mStockAmount = Convert.ToInt32(DB.DataTable.Rows[0]["StockAmount"]);
mDiscountPercentage = Convert.ToInt32(DB.DataTable.Rows[0]["DiscountPercentage"]);
mDiscountActive = Convert.ToBoolean(DB.DataTable.Rows[0]["DiscountActive"]);
return(true);
}
else
{
return(false);
}
}
public Boolean Find(Int32 staffId)
{
//connect to the database
clsDataConnection DB = new clsDataConnection();
//set the parameters for the stored procedure
//the parameter is the function argument
DB.AddParameter("@staffId", staffId);
//execute the procedure to get data
DB.Execute("sproc_tblStaff_FilterByStaffId");
//if there was a row returned, get data from it
if (DB.Count == 1)
{
//primary key
mStaffId = Convert.ToInt32(DB.DataTable.Rows[0]["staffId"]);
//foreign key
mStaffRoleId = Convert.ToInt32(DB.DataTable.Rows[0]["staffRoleId"]);
//common attributes
mFirstName = Convert.ToString(DB.DataTable.Rows[0]["staffFirstName"]);
mLastName = Convert.ToString(DB.DataTable.Rows[0]["staffLastName"]);
mDateOfBirth = Convert.ToDateTime(DB.DataTable.Rows[0]["staffDateOfBirth"]);
mDateOfHire = Convert.ToDateTime(DB.DataTable.Rows[0]["staffDateOfHire"]);
mPostCode = Convert.ToString(DB.DataTable.Rows[0]["staffPostCode"]);
mCityOfResidence = Convert.ToString(DB.DataTable.Rows[0]["staffCityOfResidence"]);
mStreetName = Convert.ToString(DB.DataTable.Rows[0]["staffStreetName"]);
mHouseNumber = Convert.ToString(DB.DataTable.Rows[0]["staffHouseNumber"]);
mContactEmail = Convert.ToString(DB.DataTable.Rows[0]["staffContactEmail"]);
mContactPhoneNo = Convert.ToString(DB.DataTable.Rows[0]["staffContactPhoneNo"]);
mOnHoliday = Convert.ToBoolean(DB.DataTable.Rows[0]["staffOnHoliday"]);
//row was found so return true as "found" is positive, a member was found
return(true);
}
else
{
return(false); //no row found means no staff member with this id exists
}
}
public int Update()
{
/*//add a new record to the database based on the values of mThisAddress
* //set the primary key value of the new record
* mThisAddress.AddressNo = 123;
* //Return the primary key of the new record
* return mThisAddress.AddressNo;*/
//Adds a new record to the database on the values of thisAddress
clsDataConnection DB = new clsDataConnection();
//set the parameters for the stored procedure
DB.AddParameter("@HouseNo", mThisAddress.HouseNo);
DB.AddParameter("@Street", mThisAddress.Street);
DB.AddParameter("@Town", mThisAddress.Town);
DB.AddParameter("@PostCode", mThisAddress.PostCode);
DB.AddParameter("@CountyNo", mThisAddress.CountyNo);
DB.AddParameter("@DateAdded", mThisAddress.DateAdded);
DB.AddParameter("@Active", mThisAddress.Active);
//execute the query returning the primary key value
return(DB.Execute("sproc_tlbAddress_Update"));
}
public bool Find(int staff_id)
{
clsDataConnection DB = new clsDataConnection();
DB.AddParameter("@staff_id", staff_id);
DB.Execute("sproc_staff_FilterByStaffID");
if (DB.Count == 1)
{
mstaff_id = Convert.ToInt32(DB.DataTable.Rows[0]["staff_id"]);
mName = Convert.ToString(DB.DataTable.Rows[0]["name"]);
mEmail = Convert.ToString(DB.DataTable.Rows[0]["email_address"]);
mActive = Convert.ToBoolean(DB.DataTable.Rows[0]["active"]);
mHire_Date = Convert.ToDateTime(DB.DataTable.Rows[0]["hire_date"]);
mSalary = Convert.ToDouble(DB.DataTable.Rows[0]["salary"]);
return(true);
}
else
{
return(false);
}
}
///public find method
public bool Find(int DestinationID)
{
//create an instance of the data connection
clsDataConnection DB = new clsDataConnection();
//add the parameter for the address no to search for
DB.AddParameter("@DestinationID", DestinationID);
//execute the stored procedure
DB.Execute("sproc_tblDestination_FilterByDestinationID");
//if one record is found (either one or 0)
if (DB.Count == 1)
{
//set the private data members with the data from the database
//private Int32 DestinationID;
mDestinationID = Convert.ToInt32(DB.DataTable.Rows[0]["DestinationID"]);
//private Int32 PickupTime;
mPickupTime = Convert.ToDateTime(DB.DataTable.Rows[0]["PickupTime"]);
//private Int32 EndPointHouseNo;
mEndPointHouseNo = Convert.ToString(DB.DataTable.Rows[0]["EndPointHouseNo"]);
//private string EndPointPostCode;
mEndPointPostCode = Convert.ToString(DB.DataTable.Rows[0]["EndPointPostCode"]);
//private string EndPointStreet;
mEndPointStreet = Convert.ToString(DB.DataTable.Rows[0]["EndPointStreet"]);
//private string EndPointTown;
mEndPointTown = Convert.ToString(DB.DataTable.Rows[0]["EndPointTown"]);
//private string DropoffTime;
mDropoffTime = Convert.ToDateTime(DB.DataTable.Rows[0]["DropoffTime"]);
//return success
return(true);
}
else //user no was invalid
{
//return that there was a problem
return(false);
}
}
public bool Find(int OfficeCode)
{
clsDataConnection DB = new clsDataConnection();
DB.AddParameter("@OfficeCode", OfficeCode);
DB.Execute("sproc_tblOffice_FilterByOfficeCode");
if (DB.Count == 1)
{
mOfficeCode = Convert.ToInt32(DB.DataTable.Rows[0]["OfficeCode"]);
mAddressLine1 = Convert.ToString(DB.DataTable.Rows[0]["AddressLine1"]);
mAddressLine2 = Convert.ToString(DB.DataTable.Rows[0]["AddressLine2"]);
mCity = Convert.ToString(DB.DataTable.Rows[0]["City"]);
mPostCode = Convert.ToString(DB.DataTable.Rows[0]["PostCode"]);
mPhoneNumber = Convert.ToString(DB.DataTable.Rows[0]["PhoneNumber"]);
mIsActive = Convert.ToBoolean(DB.DataTable.Rows[0]["IsActive"]);
mInspectionDate = Convert.ToDateTime(DB.DataTable.Rows[0]["InspectionDate"]);
return(true);
}
else
{
return(false);
}
}
public bool Find(int someOrderNo)
{
clsDataConnection DB = new clsDataConnection();
DB.AddParameter("@OrderNo", someOrderNo);
DB.Execute("sproc_tblOrder_FilterByOrderNo");
if (DB.Count == 1)
{
mOrderNo = Convert.ToInt32(DB.DataTable.Rows[0]["OrderNo"]);
mActive = Convert.ToBoolean(DB.DataTable.Rows[0]["Active"]);
mCollectionPostcode = Convert.ToString(DB.DataTable.Rows[0]["CollectionPostcode"]);
mDestinationCountry = Convert.ToString(DB.DataTable.Rows[0]["DestinationCountry"]);
mDestinationPostcode = Convert.ToString(DB.DataTable.Rows[0]["DestinationPostcode"]);
mDateCreated = Convert.ToString(DB.DataTable.Rows[0]["DateCreated"]);
mParcelSize = Convert.ToString(DB.DataTable.Rows[0]["ParcelSize"]);
mStatus = Convert.ToString(DB.DataTable.Rows[0]["Status"]);
mCustomerNo = Convert.ToInt32(DB.DataTable.Rows[0]["CustomerNo"]);
return(true);
}
else
{
return(false);
}
}
public bool Find(int ServiceID)
{
// Create a new database connection instance.
clsDataConnection DB = new clsDataConnection();
// Add the parameter for the Order ID to search for.
DB.AddParameter("@ServiceID", ServiceID);
// Execute the stored procedure
DB.Execute("sproc_tblServices_FilterByServiceID");
// If one record is found (there should be either one or zero)
if (DB.Count == 1)
{
pServiceID = Convert.ToInt32(DB.DataTable.Rows[0]["ServiceID"]);
pServiceName = Convert.ToString(DB.DataTable.Rows[0]["ServiceName"]);
pServiceDesc = Convert.ToString(DB.DataTable.Rows[0]["ServiceDescription"]);
pServicePrice = Convert.ToDouble(DB.DataTable.Rows[0]["ServicePrice"]);
return(true);
}
else
{
return(false);
}
}
public int Add()
{
//adds a new record to the database based on the values of thisOrder
//connect to the database
clsDataConnection DB = new clsDataConnection();
// set the parameters for the stored procedure
DB.AddParameter("@CustomerID", mThisOrder.CustomerID);
DB.AddParameter("@PhoneID", mThisOrder.PhoneID);
DB.AddParameter("@TariffID", mThisOrder.TariffID);
DB.AddParameter("@OrderDate", mThisOrder.OrderDate);
DB.AddParameter("@OrderMadeBy", mThisOrder.OrderMadeBy);
DB.AddParameter("@TotalPrice", mThisOrder.TotalPrice);
//execute the query returning the primary key value
return(DB.Execute("sproc_OrderTable_Insert"));
//mThisOrder.OrderID = 6;
//return mThisOrder.OrderID;
}
//function for the public Update method
public void Update()
{
//this function updates an existing record specified by the class level variable accessoryID
//it returns no value
//create a connection to the database
clsDataConnection NewAccessory = new clsDataConnection();
NewAccessory.AddParameter("@AccessoryID", thisAccessory.AccessoryID);
NewAccessory.AddParameter("@Description", thisAccessory.Description);
NewAccessory.AddParameter("@Name", thisAccessory.Name);
NewAccessory.AddParameter("@Price", thisAccessory.Price);
NewAccessory.AddParameter("@Quantity", thisAccessory.Quantity);
NewAccessory.AddParameter("@Type", thisAccessory.Type);
//execute the query
NewAccessory.Execute("sproc_tblAccessory_Update");
}
///[--week10--] This funtion will be used to add a new superhero to the database
public Boolean Add()
{
try
{
clsDataConnection myDatabase = new clsDataConnection();
///Pass both parameter and parameter value to myDatabase list object
///The following lines add a new entry into the database with attribute/variable values passed in the stored procedure
//pass the parameter name and value for Nickname to the AddParameter method
myDatabase.AddParameter("@Nickname", ThisSuperHero.Nickname);
//pass the parameter name and value for Gender to the AddParameter method
myDatabase.AddParameter("@Gender", ThisSuperHero.Gender);
//pass the parameter name and value for Weight_kg to the AddParameter method
myDatabase.AddParameter("@Weight_kg", ThisSuperHero.Weight_kg);
//pass the parameter name and value for Height_m to the AddParameter method
myDatabase.AddParameter("@Height_m", ThisSuperHero.Height_m);
//pass the parameter name and value for BirthDate to the AddParameter method
myDatabase.AddParameter("@BirthDate", ThisSuperHero.BirthDate);
//pass the parameter name and value for Age to the AddParameter method
myDatabase.AddParameter("@Age", ThisSuperHero.Age);
//pass the parameter name and value for CityNo to the AddParameter method
myDatabase.AddParameter("@CityNo", ThisSuperHero.CityNo);
//pass the parameter name and value for Speed to the AddParameter method
myDatabase.AddParameter("@Speed", ThisSuperHero.Speed);
//pass the parameter name and value for Strength to the AddParameter method
myDatabase.AddParameter("@Strength", ThisSuperHero.Strength);
//pass the parameter name and value for Flight to the AddParameter method
myDatabase.AddParameter("@Flight", ThisSuperHero.Flight);
//pass the parameter name and value for Teleportation to the AddParameter method
myDatabase.AddParameter("@Teleportation", ThisSuperHero.Teleportation);
//pass the parameter name and value for Invisibility to the AddParameter method
myDatabase.AddParameter("@Invisibility", ThisSuperHero.Invisibility);
//pass the parameter name and value for Telekenisis to the AddParameter method
myDatabase.AddParameter("@Telekenisis", ThisSuperHero.Telekenisis);
//pass the parameter name and value for Psychokenisis to the AddParameter method
myDatabase.AddParameter("@Psychokenisis", ThisSuperHero.Psychokenisis);
///Execute the stored procedure in the database for adding the details to the database
myDatabase.Execute("sproc_tblSuperHero_AddSuperHero");
//set the return value for the function
return(true);
}
catch
{
//do this if the try code above failed for some reason
//report back that there was an error
return(false);
}
}
public bool Find(Int32 ProductNo)
{
//create an instance of the data connection
clsDataConnection DB = new clsDataConnection();
//add the parameter for the address no to search for
DB.AddParameter("@ProductNo", ProductNo);
//execute the stored procedure
DB.Execute("sproc_tblProduct_FilterByProductNo");
//if one record is found (there should either one or zero!)
if (DB.Count == 1)
{
//copy the data from the database to the private data members
productNo = Convert.ToInt32(DB.DataTable.Rows[0]["ProductNo"]);
productName = Convert.ToString(DB.DataTable.Rows[0]["ProductName"]);
productType = Convert.ToString(DB.DataTable.Rows[0]["ProductType"]);
productDescription = Convert.ToString(DB.DataTable.Rows[0]["ProductDescription"]);
productPrice = Convert.ToDecimal(DB.DataTable.Rows[0]["ProductPrice"]);
productManufacturer = Convert.ToString(DB.DataTable.Rows[0]["ProductManufacturer"]);
productsInStock = Convert.ToInt32(DB.DataTable.Rows[0]["ProductsInStock"]);
//return that everything worked OK
return true;
}
//if no record was found
else
{
//return false indicating a probelm
return false;
}
}
public Panel GetImdbInformationWithOptions(Int32 filmId, string title, Int32 userId, bool favourite, bool displayOverlay)
{
this.userId = userId;
clsDataConnection DB = new clsDataConnection();
DB.AddParameter("@FilmId", filmId);
DB.Execute("sproc_tblLinksFilterByFilmId");
string imdbId = DB.DataTable.Rows[0]["ImdbId"].ToString();
var client = new RestClient("https://movie-database-imdb-alternative.p.rapidapi.com/?i=" + imdbId);
var request = new RestRequest(Method.GET);
request.AddHeader("x-rapidapi-key", ConfigurationManager.AppSettings["RapidApiKey"]);
request.AddHeader("x-rapidapi-host", "movie-database-imdb-alternative.p.rapidapi.com");
IRestResponse response = client.Execute(request);
clsImdbAPI filmInfoReturned = new clsImdbAPI();
filmInfoReturned = Newtonsoft.Json.JsonConvert.DeserializeObject <clsImdbAPI>(response.Content);
var imdbIdOk = filmInfoReturned.Response;
Int32 count = 0;
string numberOfZeroes = "0";
string newImdbId = "tt";
while (imdbIdOk == false)
{
newImdbId = "tt" + numberOfZeroes.PadRight(count, '0') + imdbId;
newImdbId = newImdbId.Replace(" ", string.Empty);
client = new RestClient("https://movie-database-imdb-alternative.p.rapidapi.com/?i=" + newImdbId);
response = client.Execute(request);
filmInfoReturned = new clsImdbAPI();
filmInfoReturned = Newtonsoft.Json.JsonConvert.DeserializeObject <clsImdbAPI>(response.Content);
imdbIdOk = filmInfoReturned.Response;
count++;
}
Panel pnlFilm = new Panel();
pnlFilm.CssClass = "filmWithTextContainer";
ImageButton imgbtnFilmPoster = new ImageButton();
imgbtnFilmPoster.CssClass = "image";
imgbtnFilmPoster.ImageUrl = filmInfoReturned.Poster;
imgbtnFilmPoster.PostBackUrl = "FilmInformation.aspx?FilmId=" + filmId + "&ImdbId=" + newImdbId;
pnlFilm.Controls.Add(imgbtnFilmPoster);
if (displayOverlay)
{
Panel pnlOverlay = new Panel();
pnlOverlay.CssClass = "overlay";
ImageButton imgbtnRemove = new ImageButton();
imgbtnRemove.ImageUrl = "Images/Remove.png";
imgbtnRemove.CssClass = "overlay-itemRight";
if (favourite == true)
{
imgbtnRemove.CommandArgument = filmId.ToString();
imgbtnRemove.Command += RemoveFromFavourites;
}
else
{
imgbtnRemove.CommandArgument = filmId.ToString();
imgbtnRemove.Command += RemoveFromWatchList;
}
pnlOverlay.Controls.Add(imgbtnRemove);
pnlFilm.Controls.Add(pnlOverlay);
}
Panel pnlFilmTitle = new Panel();
pnlFilmTitle.CssClass = "titleContainer";
Label lblFilmTitle = new Label();
lblFilmTitle.Text = title;
lblFilmTitle.ToolTip = title;
pnlFilmTitle.Controls.Add(lblFilmTitle);
pnlFilm.Controls.Add(pnlFilmTitle);
return(pnlFilm);
}
//adds a new record to the database
public int Add()
{
//connect to the database
clsDataConnection DB = new clsDataConnection();
//set the parameters for the stored procedure
DB.AddParameter("@staffRoleId", mThisStaff.staffRoleId);
DB.AddParameter("@staffFirstName", mThisStaff.firstName);
DB.AddParameter("@staffLastName", mThisStaff.lastName);
DB.AddParameter("@staffDateOfBirth", mThisStaff.dateOfBirth);
DB.AddParameter("@staffDateOfHire", mThisStaff.dateOfHire);
DB.AddParameter("@staffPostCode", mThisStaff.postCode);
DB.AddParameter("@staffCityOfResidence", mThisStaff.cityOfResidence);
DB.AddParameter("@staffStreetName", mThisStaff.streetName);
DB.AddParameter("@staffHouseNumber", mThisStaff.houseNumber);
DB.AddParameter("@staffContactEmail", mThisStaff.contactEmail);
DB.AddParameter("@staffContactPhoneNo", mThisStaff.contactPhoneNo);
DB.AddParameter("@staffOnHoliday", mThisStaff.onHoliday);
//execute the query returning the primary key value
return(DB.Execute("sproc_tblStaff_Insert"));
}
public void Update()
{
//connect to the database
clsDataConnection DB = new clsDataConnection();
//set the parameters for the stored procedure
DB.AddParameter("@staffId", mThisStaff.staffId);
DB.AddParameter("@staffRoleId", mThisStaff.staffRoleId);
DB.AddParameter("@staffFirstName", mThisStaff.firstName);
DB.AddParameter("@staffLastName", mThisStaff.lastName);
DB.AddParameter("@staffDateOfBirth", mThisStaff.dateOfBirth);
DB.AddParameter("@staffDateOfHire", mThisStaff.dateOfHire);
DB.AddParameter("@staffPostCode", mThisStaff.postCode);
DB.AddParameter("@staffCityOfResidence", mThisStaff.cityOfResidence);
DB.AddParameter("@staffStreetName", mThisStaff.streetName);
DB.AddParameter("@staffHouseNumber", mThisStaff.houseNumber);
DB.AddParameter("@staffContactEmail", mThisStaff.contactEmail);
DB.AddParameter("@staffContactPhoneNo", mThisStaff.contactPhoneNo);
DB.AddParameter("@staffOnHoliday", mThisStaff.onHoliday);
//update the record
DB.Execute("sproc_tblStaff_Update");
}
/******************************* Add a Candidate *******************************************/
public Boolean AddCandidate()
{
//try to add the candidate
try
{
//reset the connection object (referenced as inigmaITConnection)
inigmaITConnection = new clsDataConnection();
//using some candidate to pass CandidateTitle value for @CandidateTitle parameter
inigmaITConnection.AddParameter("@CandidateTitle", SomeCandidate.CandidateTitle);
//using some candidate to pass CandidateFirstName value for @CandidateFirstName parameter
inigmaITConnection.AddParameter("@CandidateFirstName", SomeCandidate.CandidateFirstName);
//using some candidate to pass CandidateLastName value for @CandidateLastName parameter
inigmaITConnection.AddParameter("@CandidateLastName", SomeCandidate.CandidateLastName);
//using some candidate to pass CandidateBirthDate value for @CandidateBirthDate parameter
inigmaITConnection.AddParameter("@CandidateBirthDate", SomeCandidate.CandidateBirthDate);
//using some candidate to pass CandidateAddress value for @CandidateAddress parameter
inigmaITConnection.AddParameter("@CandidateAddress", SomeCandidate.CandidateAddress);
//using some candidate to pass CandidatePostCode value for @CandidatePostCode parameter
inigmaITConnection.AddParameter("@CandidatePostCode", SomeCandidate.CandidatePostCode);
//using some candidate to pass CandidatePhone value for @CandidatePhone parameter
inigmaITConnection.AddParameter("@CandidatePhone", SomeCandidate.CandidatePhone);
//using some candidate to pass CandidateEmail value for @CandidateEmail parameter
inigmaITConnection.AddParameter("@CandidateEmail", SomeCandidate.CandidateEmail);
//using some candidate to pass CandidateUsername value for @CandidateUsername parameter
inigmaITConnection.AddParameter("@CandidateUsername", SomeCandidate.CandidateUsername);
//using some candidate to pass CandidatePassword value for @CandidatePassword parameter
inigmaITConnection.AddParameter("@CandidatePassword", SomeCandidate.CandidatePassword);
//using some candidate to pass CandidateSecurityAnswer value for @CandidateSecurityAnswer parameter
inigmaITConnection.AddParameter("@CandidateSecurityAnswer", SomeCandidate.CandidateSecurityAnswer);
//using some candidate to pass CandidateCVFile value for @CandidateCVFile parameter
inigmaITConnection.AddParameter("@CandidateCVFile", SomeCandidate.CandidateCVFile);
//Execute the stored procedure to add the candidate details to the tblCandidate in the database
inigmaITConnection.Execute("sproc_tblCandidate_AddCandidate");
//return true to report program stub completion
return(true);
}
//if the candidate can not be added
catch
{
//return false to report a problem
return(false);
}
}
public void Update()
{
clsDataConnection DB = new clsDataConnection();
//set the parameters for the stored procedure
DB.AddParameter("@VendorNo", mThisVendor.VendorNo);
DB.AddParameter("@VendorName", mThisVendor.VendorName);
DB.AddParameter("@HouseNo", mThisVendor.HouseNo);
DB.AddParameter("@Street", mThisVendor.Street);
DB.AddParameter("@City", mThisVendor.City);
DB.AddParameter("@PostCode", mThisVendor.PostCode);
DB.AddParameter("@Country", mThisVendor.Country);
DB.AddParameter("@DateAdded", mThisVendor.DateAdded);
DB.AddParameter("@VendorType", mThisVendor.VendorType);
DB.AddParameter("@Summary", mThisVendor.Summary);
DB.AddParameter("@OpenToBookings", mThisVendor.OpenToBookings);
//execute the stored procedure
DB.Execute("sproc_tblVendor_Update");
}
public clsStaff Find(Int32 Staffid)
{
//initialise the DBConnection
clsDataConnection dBConnection = new clsDataConnection();
//add the Product No parameter
dBConnection.AddParameter("@Staffid", Staffid);
//execute the query
dBConnection.Execute("sproc_tblStaff_FilterByStaffid");
//if the record was found
clsStaff clstaffs = new clsStaff();
if (dBConnection.Count == 1)
{
//get the Product No
clstaffs.Staffid = Convert.ToInt32(dBConnection.DataTable.Rows[0]["Staffid"]);
clstaffs.Staffname = Convert.ToString(dBConnection.DataTable.Rows[0]["Staffname"]);
clstaffs.Age = Convert.ToInt32(dBConnection.DataTable.Rows[0]["Age"]);
clstaffs.Brief = Convert.ToString(dBConnection.DataTable.Rows[0]["Brief"]);
clstaffs.Gender = Convert.ToString(dBConnection.DataTable.Rows[0]["Gender"]);
clstaffs.Mobilesphone = Convert.ToString(dBConnection.DataTable.Rows[0]["Mobilesphone"]);
clstaffs.Workage = Convert.ToInt32(dBConnection.DataTable.Rows[0]["Workage"]);
clstaffs.Position = Convert.ToString(dBConnection.DataTable.Rows[0]["Position"]);
}
//return success
return clstaffs;
}
private void btnArchiveEmail_Click(object sender, EventArgs e)
{
//try
//{
clsDataConnection Connection = new clsDataConnection();
Connection.AddParameter("EmailNo", EmailNo);
Connection.Execute("sproc_tblEmailAndArchive_PutEmailInArchive");
MessageBox.Show("Successful transfer!");
//}
//catch
//{
// MessageBox.Show("Sorry there was an error, please try again.");
//}
}
//public bool Active
//{
// get
//{
// //return the private data
// return active;
// }
// set
// {
// //set the private data
// active = value;
// }
//}
//public int StockNo { get; set; }
public bool Find(int StockNo)
{
//creat an instanc of the data connection
clsDataConnection DB = new clsDataConnection();
//add the parameter for the stock code to search for
DB.AddParameter("@StockNo", StockNo);
//execute the stored procedure
DB.Execute("sproc_tblStockItem_FilterByStockNo");
//if one record is found
if(DB.Count==1)
{
//copy the data from the databas to the private data member
stockCode = Convert.ToInt32(DB.DataTable.Rows[0]["StockNo"]);
itemPrice = Convert.ToDecimal(DB.DataTable.Rows[0]["ItemPrice"]);
stockLevel=Convert.ToInt32(DB.DataTable.Rows[0]["StockLevel"]);
stockItemDescription=Convert.ToString(DB.DataTable.Rows[0]["StockItemDescription"]);
stockName = Convert.ToString(DB.DataTable.Rows[0]["StockName"]);
supplierName = Convert.ToString(DB.DataTable.Rows[0]["SupplierName"]);
//dateAdded = Convert.ToDateTime(DB.DataTable.Rows[0]["DateAdded"]);
//active = Convert.ToBoolean(DB.DataTable.Rows[0]["Active"]);
//return that everything worked OK
return true;
}
//if no record was found
else
{
//return false indicating a problem
return false;
}
}
public string SignUp(string EMail, string Password1, string Password2, Boolean Active)
//public method allowing the user to sign up for an account
{
//var to store any errors
string Message = "";
//if the email address isn't taken
if (EMailTaken(EMail) == false)
{
//if the two passwords match
if (Password1 == Password2)
{
//check pasword complexity
Message = CheckPassword(Password1);
//if complexity OK
if (Message == "")
{
//if the email is valid
if (IsValidEmail(EMail))
{
//get the hash of the plain text password
string HashPassword = GetHashString(Password1 + EMail);
//add the record to the database
clsDataConnection DB = new clsDataConnection();
DB.AddParameter("@AccountEMail", EMail.ToLower());
DB.AddParameter("@AccountPassword", HashPassword);
DB.AddParameter("@Active", Active);
DB.Execute("sproc_tblAccount_Add");
//if active not set to true then request email activation
if (Active == false)
{
//send the activation email
SendActivationEMail(EMail);
//set the return message
Message = "An email has been sent to your account allowing you to activate the account";
}
else
{
//set the return message
Message = "The account has been created.";
}
}
else
{
//set the return message
Message = "The e-mail address is not in a valid format";
}
}
}
//if the passwords do not match
else
{
//generate an error message
Message = "The passwords do not match.";
}
}
else
{
//generate an error message
Message = "The account already exists.";
}
//return the error message (if there is one)
return(Message);
}
public int Add()
{
//adds a new record to the database based on the values of thisStockItem
//connect to the database
clsDataConnection DB = new clsDataConnection();
//set the parameters for the stored procedure
//DB.AddParameter("@StockNo", thisStockItem.StockNo);
DB.AddParameter("@ItemPrice", thisStockItem.ItemPrice);
DB.AddParameter("@StockLevel", thisStockItem.StockLevel);
DB.AddParameter("@StockItemDescription", thisStockItem.StockItemDescription);
DB.AddParameter("@StockName", thisStockItem.StockName);
DB.AddParameter("@SupplierName", thisStockItem.SupplierName);
//execute the query returning the primary key value
return DB.Execute("sproc_tblStockItem_Insert");
}
public void UpdateEmails()
{
using (ImapClient client = new ImapClient("imap.gmail.com", 993, "*****@*****.**", "DeMonfortUniversity2015", AuthMethod.Auto, true))
{
int EmailAddressNo = 0;
var uids = client.Search(SearchCondition.All());
var messages = client.GetMessages(uids);
foreach (var mail in messages)
{
//set up the data connection
clsDataConnection DB = new clsDataConnection();
DB.AddParameter("EmailContent", mail.Body);
DB.Execute("sproc_tblEmail_CheckIfExists");
DB.Execute("sproc_tblArchive_CheckIfExists");
//Int32 Result = Convert.ToInt32(DB.DataTable.Rows[0]["EmailNo"]);
if (DB.DataTable.Rows.Count == 0)
{
clsDataConnection DB5 = new clsDataConnection();
string from = Convert.ToString(mail.From);
//this uses substrings to extract the data we need from the email
string output = from.Substring(from.IndexOf("<") + 1, from.IndexOf(">") - from.IndexOf("<") - 1);
DB5.AddParameter("EmailAddress", output);
DB5.Execute("sproc_tblEmail_CheckEmailAddress");
if (DB.DataTable.Rows.Count == 0)
{
clsDataConnection DB3 = new clsDataConnection();
DB3.AddParameter("EmailAddress", output);
DB3.Execute("sproc_tblEmailAddress_InsertNewEmailAddress");
}
clsDataConnection DB4 = new clsDataConnection();
DB4.AddParameter("EmailAddress", output);
DB4.Execute("sproc_tblEmail_CheckEmailAddress");
EmailAddressNo = Convert.ToInt32(DB4.DataTable.Rows[0]["EmailAddressNo"]);
//new data connection for new parameters
clsDataConnection DB2 = new clsDataConnection();
var header = mail.Headers["Subject"];
string body = mail.Body;
DB2.AddParameter("EmailSubject", header);
DB2.AddParameter("EmailContent", body);
DB2.AddParameter("EmailAddressNo", EmailAddressNo);
DB2.Execute("sproc_tblEmail_InsertNewEmail");
}
}
}
}
/// <summary>
/// filters database by the clsCustomer in argument as reference. Uses id, name and email. If you get the wrong output try using fewer arguments (id and email are unique!)
/// </summary>
/// <param name="reference">a clsCustomer class with desired search parameters</param>
/// <returns>A clsCustomer class with the database row's attributes (or id -1 if none found)</returns>
public clsCustomer filterCustomer(clsCustomer reference)
{
// creates new instance of clsCustomer called aCustomer
clsCustomer aCustomer = new clsCustomer();
// creates new instance of dataBase connection
clsDataConnection DB = new clsDataConnection();
// checks which attributes are set in the reference clsCustomer class (as mentioned in this classes documentation)
if (reference.cusId > 0 || reference.cusName != "" || reference.cusEmail != "")
{
// create a temp counter to check a result is found (0 if not found)
int tempCounter = 0;
// check cusId is not empty (-1 is default value so must be set before being used - DB starts at id 10,000)
if (reference.cusId > 0)
{
// adds parameter and executes stored procedure
DB.AddParameter("@uId", reference.cusId);
DB.Execute("tblCustomerFilterId");
//checks for db output
if (DB.Count == 1)
{
// sets all attributes to retrieved database variables
aCustomer.cusId = Convert.ToInt32(DB.DataTable.Rows[0]["u_id"]);
aCustomer.cusName = Convert.ToString(DB.DataTable.Rows[0]["u_name"]);
aCustomer.cusEmail = Convert.ToString(DB.DataTable.Rows[0]["u_email"]);
aCustomer.cusPassword = Convert.ToString(DB.DataTable.Rows[0]["u_password"]);
aCustomer.cusDateRegister = Convert.ToDateTime(DB.DataTable.Rows[0]["u_creation_date"]);
aCustomer.cusAccountStatus = Convert.ToBoolean(DB.DataTable.Rows[0]["u_status"]);
// increment counter if row is found
tempCounter++;
}
}
// check cusName is not empty (default cusName = "")
if (reference.cusName != "")
{
// adds parameter and executes stored procedure
DB.AddParameter("@uName", reference.cusName);
DB.Execute("tblCustomerFilterId");
// checks for db output
if (DB.Count == 1)
{
// sets all attributes to retrieved database variables
aCustomer.cusId = Convert.ToInt32(DB.DataTable.Rows[0]["u_id"]);
aCustomer.cusName = Convert.ToString(DB.DataTable.Rows[0]["u_name"]);
aCustomer.cusEmail = Convert.ToString(DB.DataTable.Rows[0]["u_email"]);
aCustomer.cusPassword = Convert.ToString(DB.DataTable.Rows[0]["u_password"]);
aCustomer.cusDateRegister = Convert.ToDateTime(DB.DataTable.Rows[0]["u_creation_date"]);
aCustomer.cusAccountStatus = Convert.ToBoolean(DB.DataTable.Rows[0]["u_status"]);
// increment counter if row is found
tempCounter++;
}
}
// checks cusEmail is not empty (default cusEmail = "")
if (reference.cusEmail != "")
{
// adds parameter and executes stored procedure
DB.AddParameter("@uEmail", reference.cusEmail);
DB.Execute("tblCustomerFilterEmail");
// checks for db output
if (DB.Count == 1)
{
// sets all attributes to retrieved database variables
aCustomer.cusId = Convert.ToInt32(DB.DataTable.Rows[0]["u_id"]);
aCustomer.cusName = Convert.ToString(DB.DataTable.Rows[0]["u_name"]);
aCustomer.cusEmail = Convert.ToString(DB.DataTable.Rows[0]["u_email"]);
aCustomer.cusPassword = Convert.ToString(DB.DataTable.Rows[0]["u_password"]);
aCustomer.cusDateRegister = Convert.ToDateTime(DB.DataTable.Rows[0]["u_creation_date"]);
aCustomer.cusAccountStatus = Convert.ToBoolean(DB.DataTable.Rows[0]["u_status"]);
// increment counter if row is found
tempCounter++;
}
}
// Check the counter to make sure that at least 1 match was found and returns new populated clsCustomer class
if (tempCounter > 0)
{
return(aCustomer);
}
// if tempCounter is empty return a dummy clsCustomer class with cusId = -1
else
{
clsCustomer noCustomer = new clsCustomer();
noCustomer.cusId = -1;
return(noCustomer);
}
}
// if cusId, cusName and cusEmail are not set then return a dummy clsCustomer class with cusId = -1
else
{
clsCustomer noCustomer = new clsCustomer();
noCustomer.cusId = -1;
return(noCustomer);
}
}
public int Add()
{
clsDataConnection DB = new clsDataConnection();
DB.AddParameter("@FirstName", thisCustomer.FirstName);
DB.AddParameter("@LastName", thisCustomer.LastName);
DB.AddParameter("@AddressLine1", thisCustomer.AddressLine1);
DB.AddParameter("@AddressLine2", thisCustomer.AddressLine2);
DB.AddParameter("@Town", thisCustomer.Town);
DB.AddParameter("@PostCode", thisCustomer.PostCode);
DB.AddParameter("@PhoneNo", thisCustomer.PhoneNo);
DB.AddParameter("@EmailAddress", thisCustomer.EmailAddress);
DB.AddParameter("@UserName", thisCustomer.UserName);
DB.AddParameter("@Password", thisCustomer.Password);
//execute the query returning the primary key value
return DB.Execute("sproc_tblCustomer_Insert");
}
public bool Find(Int32 PaymentNo)
{
//create an instance of the data connection
clsDataConnection DB = new clsDataConnection();
//add the paramter for the payemntno to search for
DB.AddParameter("@PaymentNo", PaymentNo);
//excute the stored procedure
DB.Execute("sproc_tblPayment_FilterByPaymentNo");
//if one record is found( there should be either one or zero!)
if (DB.Count == 1)
{
//copy the data from the databse to the private data members
paymentNo = Convert.ToInt32(DB.DataTable.Rows[0]["PaymentNo"]);
amount = Convert.ToDecimal(DB.DataTable.Rows[0]["Amount"]);
paymentMethod = Convert.ToString(DB.DataTable.Rows[0]["PaymentMethod"]);
dateAdded = Convert.ToDateTime(DB.DataTable.Rows[0]["DateAdded"]);
//return that everthing worked Ok
return true;
}
//if no record was found
else
{
//return false indicating a problem
return false;
}
}
public int Add()
{
clsDataConnection DB = new clsDataConnection();
DB.AddParameter("@CustomerID", mThisCustomer.CustomerID);
DB.AddParameter("@CustomerFirstName", mThisCustomer.CustomerFirstName);
DB.AddParameter("@CustomerLastName", mThisCustomer.CustomerLastName);
DB.AddParameter("@Address", mThisCustomer.Address);
DB.AddParameter("@EmailAddress", mThisCustomer.CustomerEmail);
DB.AddParameter("@Password", mThisCustomer.CustomerPassword);
DB.AddParameter("@Postcode", mThisCustomer.PostCode);
DB.AddParameter("@DateOfBirth", mThisCustomer.CustomerDOB);
DB.AddParameter("@OptInMarketing", mThisCustomer.Marketing);
DB.AddParameter("@DateAdded", mThisCustomer.DateAdded);
DB.AddParameter("@Active", mThisCustomer.Active);
return(DB.Execute("sproc_tblCustomer_Add"));
}
private void btnRestoreEmail_Click(object sender, EventArgs e)
{
//try
//{
clsDataConnection NewConnection = new clsDataConnection();
NewConnection.AddParameter("ArchiveNo", ArchiveNo);
NewConnection.Execute("sproc_tblEmailAndArchive_PutArchiveBackInEmail");
MessageBox.Show("Successful Transfer!");
this.Refresh();
//}
//catch
//{
// MessageBox.Show("Unfortunately there was an error. Please try again.");
//}
}
public bool Find(Int32 CustomerNo)
{
//create an instance of the data connection
clsDataConnection DB = new clsDataConnection();
//add the parameter for the customer no to search for
DB.AddParameter("@CustomerNo", CustomerNo);
//Execute the stored Procedure
DB.Execute("sproc_tblCustomer_FilterByCustomerNo");
//if one record is found (there should be one or zero!)
if (DB.Count == 1)
{
//set the private data member to the test data value
customerNo = Convert.ToInt32(DB.DataTable.Rows[0]["CustomerNo"]);
firstName = Convert.ToString(DB.DataTable.Rows[0]["FirstName"]);
lastName = Convert.ToString(DB.DataTable.Rows[0]["LastName"]);
password = Convert.ToString(DB.DataTable.Rows[0]["Password"]);
phoneNo = Convert.ToString(DB.DataTable.Rows[0]["PhoneNo"]);
emailAddress = Convert.ToString(DB.DataTable.Rows[0]["EmailAddress"]);
addressLine1 = Convert.ToString(DB.DataTable.Rows[0]["AddressLine1"]);
addressLine2 = Convert.ToString(DB.DataTable.Rows[0]["AddressLine2"]);
postCode = Convert.ToString(DB.DataTable.Rows[0]["PostCode"]);
town = Convert.ToString(DB.DataTable.Rows[0]["Town/City"]);
userName = Convert.ToString(DB.DataTable.Rows[0]["UserName"]);
//return that everything worked ok
return true;
}
//if no record was found
else
{
//return false indicating a problem
return false;
}
}