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; } }