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 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 constructor
public clsEmailCollection()
{
//instance of the database connection class
clsDataConnection DB = new clsDataConnection();
//exectue the stored procedure
DB.Execute("sproc_tblEmail_SelectAll");
//get the count
Int32 RecordCount = DB.Count;
//set up index for the loop
Int32 Index = 0;
//while there are records to process
while (Index < RecordCount)
{
//create a new instance of the email class
clsEmail AEmail = new clsEmail();
//get the email subject
AEmail.EmailSubject = DB.DataTable.Rows[Index]["EmailSubject"].ToString();
//get the primary key
AEmail.EmailNo = Convert.ToInt32(DB.DataTable.Rows[Index]["EmailNo"]);
//get the email Content
AEmail.EmailContent = DB.DataTable.Rows[Index]["EmailContent"].ToString();
//get the email address recieving
AEmail.EmailAddressNo = Convert.ToInt32(DB.DataTable.Rows[Index]["EmailAddressNo"]);
//passess the EmailAddressNo to get the EmailAddress
AEmail.EmailAddress = GetEmailAddress(AEmail.EmailAddressNo);
//add the Email to the private data
allEmails.Add(AEmail);
//add to the index
Index++;
}
}
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 clsPaymentCollection()
{
//var for the index
Int32 Index = 0;
//var to store the record count
Int32 RecordCount = 0;
//object for the data connection
clsDataConnection DB = new clsDataConnection();
//execute the stored procedure
DB.Execute("sproc_tblPayment_SelectAll");
//get the count of records
RecordCount = DB.Count;
//while there are records to proccess
while (Index < RecordCount)
{
//create a blank customer
clsPayment APayment = new clsPayment();
// //read in the fields from the current record
APayment.PaymentNo = Convert.ToInt32(DB.DataTable.Rows[Index]["PaymentNo"]);
APayment.PaymentMethod = Convert.ToString(DB.DataTable.Rows[Index]["PaymentMethod"]);
APayment.Amount = Convert.ToDecimal(DB.DataTable.Rows[Index]["Amount"]);
APayment.Dateadded = Convert.ToDateTime(DB.DataTable.Rows[Index]["DateAdded"]);
// //add the record to the private data member
paymentList.Add(APayment);
// //point at the next record
Index++;
}
}
public clsAppointmentCollection()
{
//var for the index
Int32 Index = 0;
//var to store the record count
Int32 RecordCount = 0;
//object for the data connection
clsDataConnection DB = new clsDataConnection();
//execute the stored procedure
DB.Execute("sproc_tblAppointment_SelectAll");
//get the count of records
RecordCount = DB.Count;
//while there are records to proccess
while (Index < RecordCount)
{
//create a blank appointment
clsAppointments AnAppointment = new clsAppointments();
// //read in the fields from the current record
AnAppointment.AppointmentID = Convert.ToInt32(DB.DataTable.Rows[Index]["AppointmentID"]);
AnAppointment.AppointmentLocation = Convert.ToString(DB.DataTable.Rows[Index]["AppointmentLocation"]);
AnAppointment.AppointmentDate = Convert.ToDateTime(DB.DataTable.Rows[Index]["AppointmentDate"]);
// //add the record to the private data member
appointmentList.Add(AnAppointment);
// //point at the next record
Index++;
}
}
public clsProductCollection()
{
//var for the index
Int32 Index = 0;
//var to store the record count
Int32 RecordCount = 0;
//object for the data connection
clsDataConnection DB = new clsDataConnection();
//execute the stored procedure
DB.Execute("sproc_tblProduct_SelectAll");
//get the count of records
RecordCount = DB.Count;
//while there are records to proccess
while (Index < RecordCount)
{
//create a blank customer
clsProduct AProduct = new clsProduct();
// //read in the fields from the current record
AProduct.ProductNo = Convert.ToInt32(DB.DataTable.Rows[Index]["ProductNo"]);
AProduct.ProductName = Convert.ToString(DB.DataTable.Rows[Index]["ProductName"]);
AProduct.ProductType = Convert.ToString(DB.DataTable.Rows[Index]["ProductType"]);
AProduct.ProductDescription = Convert.ToString(DB.DataTable.Rows[Index]["ProductDescription"]);
AProduct.ProductPrice = Convert.ToDecimal(DB.DataTable.Rows[Index]["ProductPrice"]);
AProduct.ProductManufacturer = Convert.ToString(DB.DataTable.Rows[Index]["ProductManufacturer"]);
AProduct.ProductsInStock = Convert.ToInt32(DB.DataTable.Rows[Index]["ProductsInStock"]);
// //add the record to the private data member
productList.Add(AProduct);
// //point at the next record
Index++;
}
}
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 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;
}
public int Add()
{
//adds a new record to the database base on the values of mThisDestination
//connect to the DataBase
clsDataConnection DB = new clsDataConnection();
DB.AddParameter("@EndPointHouseNo", mThisDestination.EndPointHouseNo);
DB.AddParameter("@EndPointPostCode", mThisDestination.EndPointPostCode);
DB.AddParameter("@EndPointStreet", mThisDestination.EndPointStreet);
DB.AddParameter("@EndPointTown", mThisDestination.@EndPointTown);
DB.AddParameter("@PickupTime", mThisDestination.PickupTime);
DB.AddParameter("@DropoffTime", mThisDestination.DropoffTime);
//Execute the query returning the primary key value
return(DB.Execute("sproc_tblDestination_Insert"));
}
public void Update()
{
//update an existing record based on the value of thisStock
//connect to the database
clsDataConnection DB = new clsDataConnection();
//set the parameters for the stored procedure
DB.AddParameter("@StockID", mThisStock.StockID);
DB.AddParameter("@Name", mThisStock.Name);
DB.AddParameter("@Description", mThisStock.Description);
DB.AddParameter("@StockQuantity", mThisStock.StockQuantity);
DB.AddParameter("@StockItemPrice", mThisStock.StockItemPrice);
//execute the stored procedure
DB.Execute("sproc_tblStock_Update");
}
//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 int Add()
{
//adds the new record to the database on the values of mThisStock
//connects to the database
clsDataConnection DB = new clsDataConnection();
//set the parameters for the stored procedure
DB.AddParameter("@CarModel", mThisStock.CarModel);
DB.AddParameter("@BHP", mThisStock.BHP);
DB.AddParameter("@Price", mThisStock.Price);
DB.AddParameter("@Availability", mThisStock.Availability);
DB.AddParameter("@DateAdded", mThisStock.DateAdded);
//execute the query returining the primary key value
return(DB.Execute("sproc_tblStock_Insert"));
}
public int Add()
{
//adds new records to the database based on the value of mThisStock
//connect to the database
clsDataConnection DB = new clsDataConnection();
//set the parameters for the stored procedure
//DB.AddParameter("@StockID", mThisStock.StockID);
DB.AddParameter("@Name", mThisStock.Name);
DB.AddParameter("@Description", mThisStock.Description);
DB.AddParameter("@StockQuantity", mThisStock.StockQuantity);
DB.AddParameter("@StockItemPrice", mThisStock.StockItemPrice);
//execute the query returning the primary key value
return(DB.Execute("sproc_tblStock_Insert"));
}
public int Add()
{
//add new record to database based on the value of thiscar
//connect 2 db
clsDataConnection DB = new clsDataConnection();
//set the parameters for the stored procedure
DB.AddParameter("@RegPlate", mThisCar.RegPlate);
DB.AddParameter("@CarName", mThisCar.CarName);
DB.AddParameter("@CarModel", mThisCar.CarModel);
DB.AddParameter("@CarColour", mThisCar.CarColour);
DB.AddParameter("@EngineSize", mThisCar.EngineSize);
DB.AddParameter("@Price", mThisCar.Price);
return(DB.Execute("sproc_tblCars_Insert"));
}
public Int32 Add()
{
clsDataConnection NewDBStudent = new clsDataConnection();
NewDBStudent.AddParameter("@StudentPNumber", mThisStudent.StudentPNumber);
NewDBStudent.AddParameter("@StudentFullName", mThisStudent.StudentFullName);
NewDBStudent.AddParameter("@StudentAdditionDate", mThisStudent.StudentAdditionDate);
NewDBStudent.AddParameter("@StudentExpelled", mThisStudent.StudentExpelled);
NewDBStudent.AddParameter("@StudentCourseNo", mThisStudent.StudentCourseNo);
NewDBStudent.AddParameter("@StudentAttendancePercentage", mThisStudent.StudentAttendancePercentage);
NewDBStudent.AddParameter("@StudentStartingYear", mThisStudent.StudentStartingYear);
NewDBStudent.AddParameter("@StudentTutorNo", mThisStudent.StudentTutorNo);
return(NewDBStudent.Execute("sproc_tblStudent_Insert"));
}
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 int Add()
{
//adds a new record to the database based on the valyes of mThisStaff
//connect to the database
clsDataConnection DB = new clsDataConnection();
//set the parameters
DB.AddParameter("@FirstName", mThisStaff.FirstName);
DB.AddParameter("@LastName", mThisStaff.LastName);
DB.AddParameter("@Address", mThisStaff.StaffAddress);
DB.AddParameter("@PhoneNo", mThisStaff.StaffPhoneNo);
DB.AddParameter("@PostCode", mThisStaff.StaffPostCode);
//execute quey return the primary key value
return(DB.Execute("sproc_TblStaff_Insert"));
}
public void Update()
{
//update an existing record based on the values of ThisProduct
//database connection
clsDataConnection DB = new clsDataConnection();
//set the parameters fpr the stored procedure
DB.AddParameter("@ProductNo", mThisProduct.ProductNo);
DB.AddParameter("@ProductName", mThisProduct.ProductName);
DB.AddParameter("@Description", mThisProduct.Description);
DB.AddParameter("@Price", mThisProduct.Price);
DB.AddParameter("@Active", mThisProduct.Active);
//execute the stored procedure
DB.Execute("sproc_tblProduct_Update");
}
public int Add()
{
//adds a new record to the database based on the valyes of mThisProduct
//connect to the database
clsDataConnection DB = new clsDataConnection();
//set the parameters
DB.AddParameter("@ProductName", mThisProduct.ProductName);
DB.AddParameter("@Description", mThisProduct.Description);
DB.AddParameter("@Price", mThisProduct.Price);
DB.AddParameter("@Active", mThisProduct.Active);
//execute quey return the primary key value
return(DB.Execute("sproc_tblProduct_Insert"));
}
public void Update()
{
clsDataConnection DB = new clsDataConnection();
DB.AddParameter("@StaffID", mThisStaff.StaffID);
DB.AddParameter("@StaffName", mThisStaff.StaffName);
DB.AddParameter("@OfficeCode", mThisStaff.OfficeCode);
DB.AddParameter("@PositionID", mThisStaff.PositionID);
DB.AddParameter("@ContactNumber", mThisStaff.ContactNumber);
DB.AddParameter("@Address", mThisStaff.Address);
DB.AddParameter("@HireDate", mThisStaff.HireDate);
DB.AddParameter("@IsEmployed", mThisStaff.IsEmployed);
DB.Execute("sproc_tblStaff_Update");
}
/// <summary>
/// Update ThisCustomer record to the input values. Cannot change read-only field date_created
/// </summary>
public void Update()
{
//update an exiting record based on the valies of ThisCustomer
//connect to db
clsDataConnection db = new clsDataConnection();
//set the paramteres for the stores procedure
db.AddParameter("@uId", ThisCustomer.cusId);
db.AddParameter("@uName", ThisCustomer.cusName);
db.AddParameter("@uEmail", ThisCustomer.cusEmail);
db.AddParameter("@uPassword", ThisCustomer.cusPassword);
db.AddParameter("@uStatus", ThisCustomer.cusAccountStatus);
//execute
db.Execute("tblCustomerUpdate");
}
public int Update()
{
clsDataConnection DB = new clsDataConnection();
DB.AddParameter("@CustomerId", mThisCustomer.CustomerId);
DB.AddParameter("@Name", mThisCustomer.Name);
DB.AddParameter("@EmailAddress", mThisCustomer.EmailAddress);
DB.AddParameter("@Address", mThisCustomer.Address);
DB.AddParameter("@Password", mThisCustomer.Password);
DB.AddParameter("@IsEmailConfirmed", mThisCustomer.isEmailConfirmed);
DB.AddParameter("@LoyaltyPoints", mThisCustomer.LoyaltyPoints);
DB.AddParameter("@CreatedAt", mThisCustomer.CreatedAt);
return(DB.Execute("sproc_tblCustomer_Update"));
}
public int Add()
{
//adds a new record to the database based on the values of thisStaff
//connect to the database
clsDataConnection DB = new clsDataConnection();
//set the parameter for the stored procedure
DB.AddParameter("@StaffName", mThisStaff.StaffName);
DB.AddParameter("@StaffTelNumber", mThisStaff.StaffTelNumber);
DB.AddParameter("@StaffAddress", mThisStaff.StaffAddress);
DB.AddParameter("@DateJoined", mThisStaff.DateJoined);
DB.AddParameter("@Active", mThisStaff.Active);
//exeute the query retruning the primary key value
return(DB.Execute("sproc_tblStaff_Insert"));
}
public void Update()
{
clsDataConnection DB = new clsDataConnection();
DB.AddParameter("@ProductNo", mThisProduct.ProductNo);
DB.AddParameter("@StockAmount", mThisProduct.StockAmount);
DB.AddParameter("@UnitPrice", mThisProduct.UnitPrice);
DB.AddParameter("@ProductName", mThisProduct.ProductName);
DB.AddParameter("@ProductDescription", mThisProduct.ProductDescription);
DB.AddParameter("@InStock", mThisProduct.InStock);
DB.AddParameter("@DiscountPercentage", mThisProduct.DiscountPercentage);
DB.AddParameter("@DiscountActive", mThisProduct.DiscountActive);
DB.Execute("dbo.sproc_tblProduct_Update");
}
public int Add()
{
//adds a new record to the database based on the values of mThisSale
//connect to the database
clsDataConnection DB = new clsDataConnection();
//set the parameters for the stored procedure
DB.AddParameter("@ItemNo", mThisStock.ItemNo);
DB.AddParameter("@ItemName", mThisStock.ItemName);
DB.AddParameter("@AgeRating", mThisStock.AgeRating);
DB.AddParameter("@Genre", mThisStock.Genre);
DB.AddParameter("@Condition", mThisStock.Condition);
//execute the query returning the primary key value
return(DB.Execute("sproc_tblStock_Insert"));
}
public void Update()
{
//update an existing record based on the values of thisAddress
//connect to the database
clsDataConnection DB = new clsDataConnection();
//set the parameters for the stored procedure
DB.AddParameter("@OrderID", mThisOrder.OrderID);
DB.AddParameter("@CustomerID", mThisOrder.CustomerID);
DB.AddParameter("@StaffID", mThisOrder.StaffID);
DB.AddParameter("@OrderDate", mThisOrder.OrderDate);
DB.AddParameter("@OrderStatus", mThisOrder.OrderStatus);
//execute the stored procedure
DB.Execute("sproc_tblOrder_Update");
}
//function for the find public method
public Boolean Find(Int32 PhoneNo)
{
//initialised the db connection
clsDataConnection dBConnection = new clsDataConnection();
//add the phone No parameter
dBConnection.AddParameter("@PhoneNo", PhoneNo);
//execute the query
dBConnection.Execute("sproc_tblPhone_FilterByPhoneNo");
//if the record was found
if (dBConnection.Count == 1)
{
//get the phone no
mPhoneNo = Convert.ToInt32(dBConnection.DataTable.Rows[0]["PhoneNo"]);
//get the phone name
mPhoneName = Convert.ToString(dBConnection.DataTable.Rows[0]["PhoneName"]);
//get the brand
mBrand = Convert.ToString(dBConnection.DataTable.Rows[0]["Brand"]);
//get the screen size
mScreenSize = Convert.ToString(dBConnection.DataTable.Rows[0]["ScreenSize"]);
//get the Operating System
mOperatingSystem = Convert.ToString(dBConnection.DataTable.Rows[0]["OperatingSystem"]);
//get the back camera
mBackCamera = Convert.ToString(dBConnection.DataTable.Rows[0]["BackCamera"]);
//get the battery size
mBatterySize = Convert.ToString(dBConnection.DataTable.Rows[0]["BatterySize"]);
//get the release date
mReleaseDate = Convert.ToDateTime(dBConnection.DataTable.Rows[0]["ReleaseDate"]);
//get the company no
mCompanyNo = Convert.ToInt32(dBConnection.DataTable.Rows[0]["CompanyNo"]);
try
{
//get the Discontinued
mDiscontinued = Convert.ToBoolean(dBConnection.DataTable.Rows[0]["Discontinued"]);
}
catch
{
mDiscontinued = true;
}
//return success
return(true);
}
else
{
//return failure
return(false);
}
}
void DisplayUserAssignedMoods()
{
pnlMyTags.Controls.Clear();
clsDataConnection DB = new clsDataConnection();
DB.AddParameter("@UserId", userId);
DB.AddParameter("@FilmId", filmId);
DB.Execute("sproc_tblFilmMood_FilterByUserIdAndFilmId");
Int32 recordCount = DB.Count;
Int32 index = 0;
if (recordCount != 0)
{
while (index < recordCount)
{
Panel pnlTag = new Panel();
pnlTag.CssClass = "tagWholeContainer";
Panel pnlTextContainer = new Panel();
pnlTextContainer.CssClass = "textcontainer";
Label lblTag = new Label();
lblTag.Text = DB.DataTable.Rows[index]["Description"].ToString();
pnlTextContainer.Controls.Add(lblTag);
pnlTag.Controls.Add(pnlTextContainer);
Panel pnlRemoveContainer = new Panel();
pnlRemoveContainer.CssClass = "removeContainer";
ImageButton imgbtnRemoveTag = new ImageButton();
imgbtnRemoveTag.CssClass = "image";
imgbtnRemoveTag.ImageUrl = "Images/Remove.png";
imgbtnRemoveTag.OnClientClick = "return btnRemoveTag_Clicked()";
imgbtnRemoveTag.CommandArgument = DB.DataTable.Rows[index]["MoodId"].ToString();
imgbtnRemoveTag.Command += ImgbtnRemoveTag_Command;
pnlRemoveContainer.Controls.Add(imgbtnRemoveTag);
pnlTag.Controls.Add(pnlRemoveContainer);
pnlMyTags.Controls.Add(pnlTag);
index++;
}
pnlMyTags.Visible = true;
}
else
{
pnlMyTags.Visible = false;
}
}
public Boolean Find(int filmId)
{
clsDataConnection DB = new clsDataConnection();
DB.AddParameter("@FilmId", filmId);
DB.Execute("sproc_tblLinksFilterByFilmId");
if (DB.Count == 1)
{
mImdbId = Convert.ToInt32(DB.DataTable.Rows[0]["ImdbId"]);
return(true);
}
else
{
return(false);
}
}
public int Add()
{
// adds record to database based on mThisOrder
// connect to database
clsDataConnection DB = new clsDataConnection();
// set values
DB.AddParameter("@NumberPlate", mThisOrder.numberPlate);
DB.AddParameter("@CustomerID", mThisOrder.customerID);
DB.AddParameter("Quantity", mThisOrder.quantity);
DB.AddParameter("@Price", mThisOrder.price);
DB.AddParameter("@DateOrdered", mThisOrder.dateOrdered);
// return primary key of new record
return(DB.Execute("dbo.sproc_OrderTable_Insert"));
}
public int Add()
{
clsDataConnection DB = new clsDataConnection();
// ClsOrder AnOrder = new ClsOrder();
DB.AddParameter("@CustomerID", mThisOrder.CustomerID);
DB.AddParameter("@CarID", mThisOrder.CarID);
DB.AddParameter("@PaymentID", mThisOrder.PaymentID);
DB.AddParameter("@DateOfOrder", mThisOrder.DateOfOrder = DateTime.Now.Date);
DB.AddParameter("@ServiceID", mThisOrder.ServiceID);
DB.AddParameter("@OrderPrice", mThisOrder.OrderPrice);
DB.AddParameter("@OrderStatus", mThisOrder.OrderStatus);
DB.AddParameter("@Completed", mThisOrder.Completed);
return(DB.Execute("sproc_tblOrder_InsertOrder"));
}
public void Update()
{
// update an existing record based on ThisOrder value
// connect to database
clsDataConnection DB = new clsDataConnection();
// set parameters for stored procedure
DB.AddParameter("@OrderID", mThisOrder.orderID);
DB.AddParameter("@NumberPlate", mThisOrder.numberPlate);
DB.AddParameter("@CustomerID", mThisOrder.customerID);
DB.AddParameter("Quantity", mThisOrder.quantity);
DB.AddParameter("@Price", mThisOrder.price);
DB.AddParameter("@DateOrdered", mThisOrder.dateOrdered);
// execute stored procedure
DB.Execute("dbo.sproc_OrderTable_Update");
}
//function for the public Update method
public void Update()
{
//create a connection to the database
clsDataConnection NewOrder = new clsDataConnection();
NewOrder.AddParameter("@OrderID", ThisOrder.OrderId);
NewOrder.AddParameter("@Number", ThisOrder.OrderNumber.ToString());
NewOrder.AddParameter("@Name", ThisOrder.OrderName);
NewOrder.AddParameter("@Quantity", ThisOrder.OrderQuantity);
NewOrder.AddParameter("@Date", ThisOrder.OrderDate);
NewOrder.AddParameter("@Total", ThisOrder.OrderTotal);
//execute the query
NewOrder.Execute("sproc_tblOrder_Update");
}
public int Add()
{
//add a new record to the database based on the values of mThisOrder
//connect to the database
clsDataConnection DB = new clsDataConnection();
//set the parameters for the stored procedure
DB.AddParameter("@ItemName", mThisOrder.ItemName);
DB.AddParameter("@ItemType", mThisOrder.ItemType);
DB.AddParameter("@Price", mThisOrder.Price);
DB.AddParameter("@Date", mThisOrder.Date);
DB.AddParameter("@Quality", mThisOrder.Quality);
DB.AddParameter("@Quantity", mThisOrder.Quantity);
//execute the query returning the primary key data
return(DB.Execute("sproc_tblOrder_Insert"));
}
public void Update()
{
//update the existing record bsaed on the values of thisCustomer
//connect the database
clsDataConnection DB = new clsDataConnection();
//set the parameters to the stored procedure
DB.AddParameter("@CustomerID", mThisCustomer.CustomerID);
DB.AddParameter("@CustomerName", mThisCustomer.CustomerName);
DB.AddParameter("@CustomerTelNumber", mThisCustomer.CustomerTelNumber);
DB.AddParameter("@CustomerAddress", mThisCustomer.CustomerAddress);
DB.AddParameter("@DateJoined", mThisCustomer.DateJoined);
DB.AddParameter("@Active", mThisCustomer.Active);
//return the primary key of the new record
DB.Execute("sproc_tblCustomer_Update");
}
public int Add()
{
//adds a new recors to the database based on the values od mThisCourse
//connect to the database
clsDataConnection DB = new clsDataConnection();
// set the parameters for the stored procedure
DB.AddParameter("@Title", mThisCourse.Title);
DB.AddParameter("@Category", mThisCourse.Category);
DB.AddParameter("@Tutor", mThisCourse.Tutor);
DB.AddParameter("@LiveDate", mThisCourse.LiveDate);
DB.AddParameter("@Available", mThisCourse.Available);
DB.AddParameter("@Price", mThisCourse.Price);
//execute the query returning the primary key value
return(DB.Execute("sproc_Courses_Insert"));
}
public void Update()
{
//update an existing record based on the values of thiscars
//connect to the database
clsDataConnection DB = new clsDataConnection();
//set the paramters for the storted procedures
DB.AddParameter("@CarID", mThisCar.CarID);
DB.AddParameter("@RegPlate", mThisCar.RegPlate);
DB.AddParameter("@CarName", mThisCar.CarName);
DB.AddParameter("@CarModel", mThisCar.CarModel);
DB.AddParameter("@CarColour", mThisCar.CarColour);
DB.AddParameter("@EngineSize", mThisCar.EngineSize);
DB.AddParameter("@Price", mThisCar.Price);
DB.Execute("sproc_tblCars_Update");
}
public clsStaffCollection()
{
mStaffList = new List <clsStaff>();
this.thisStaff = new clsStaff();
Int32 Index = 0;
Int32 RecordCount = 0;
clsDataConnection DB = new clsDataConnection();
DB.Execute("sproc_tblStaff_SelectAll");
PopulateArray(DB);
}
public int Add(string someEmail, string someFirstName, string someLastName, string someTelephone, string someTitle)
{
//adds a new record to the database based on the values of thisTutor
//connect to the database
clsDataConnection DB = new clsDataConnection();
//set the parameters for the stored procedure
//DB.AddParameter("@OrderNo", mThisOrder.OrderNo);
DB.AddParameter("@Email", someEmail);
DB.AddParameter("@FirstName", someFirstName);
DB.AddParameter("@LastName", someLastName);
DB.AddParameter("@Telephone", someTelephone);
DB.AddParameter("@Title", someTitle);
//execute the query returning the primary key value
return(DB.Execute("sproc_tblStaff_Insert"));
}
//public void FindAllStockItems()
public clsStockCollection()
{
//re-set the connection
clsDataConnection myDB = new clsDataConnection();
//execute the stored procedure
myDB.Execute("sproc_tblStockItem_SelectAll");
//get the count of records
Int32 recordCount = myDB.Count;
//var to store the index
Int32 Index = 0;
//while there are still records to process
while (Index < myDB.Count)
////var to store the user number of the current record
//Int32 StockNo;
////var to flag that user was found
//Boolean StockFound;
{
//create an instance of the stock item class
clsStockItem AStockItem = new clsStockItem();
//get the stock name
AStockItem.StockName = myDB.DataTable.Rows[Index]["StockName"].ToString();
//get the primary key
AStockItem.StockNo = Convert.ToInt32(myDB.DataTable.Rows[Index]["StockNo"]);
//increment the index
Index++;
////get the user number from the database
//StockNo = Convert.ToInt32(myDB.DataTable.Rows[Index]["StockNo"]);
////find the user by invoking the find method
//StockFound = NewItem.Find(StockNo);
//if (StockFound == true)
//{
// //add the user to the list
allStock.Add(AStockItem);
//}
}
}
//public constructor for the class
//this constructor is a function that runs when class is created
public clsServiceOrderCollection()
{
//create instance of data connection class
clsDataConnection DB = new clsDataConnection();
//execute stored procedure to get list of data
DB.Execute("sproc_tblServiceOrder_SelectAll");
//get count of records
Int32 RecordCount = DB.Count;
//Set up index for the loop
Int32 Index = 0;
//while there are records to process
while (Index < RecordCount)
{
//create instance of Service Order class
clsServiceOrder AnOrder = new clsServiceOrder();
//get service
AnOrder.Service = DB.DataTable.Rows[Index]["Service"].ToString();
//get primary key
AnOrder.OrderNo = Convert.ToInt32(DB.DataTable.Rows[Index]["OrderNo"]);
//increment the index
Index++;
/* //create instance of order class to store an order
clsServiceOrder AnOrder = new clsServiceOrder();
//set service to 'Antivirus'
AnOrder.Service = "Antivirus";
//add the order to the private list of orders
allOrders.Add(AnOrder);
//re-initialise the AnOrder object to accept a new item
AnOrder = new clsServiceOrder();
//set the new service to 'Memory'
AnOrder.Service = "Memory";
//add second service to private list of orders
allOrders.Add(AnOrder);
//the private list now contains two orders*/
}
}
public clsCustomerCollection()
{
//var for the index
Int32 Index = 0;
//var to store the record count
Int32 RecordCount = 0;
//object for the data connection
clsDataConnection DB = new clsDataConnection();
//execute the stored procedure
DB.Execute("sproc_tblCustomer_SelectAll");
//get the count of records
RecordCount = DB.Count;
//while there are records to proccess
while (Index < RecordCount)
{
//create a blank customer
clsCustomer ACustomer = new clsCustomer();
//read in the fields from the current record
ACustomer.CustomerNo = Convert.ToInt32(DB.DataTable.Rows[Index]["CustomerNo"]);
ACustomer.FirstName = Convert.ToString(DB.DataTable.Rows[Index]["FirstName"]);
ACustomer.LastName = Convert.ToString(DB.DataTable.Rows[Index]["LastName"]);
ACustomer.AddressLine1 = Convert.ToString(DB.DataTable.Rows[Index]["AddressLine1"]);
ACustomer.AddressLine2 = Convert.ToString(DB.DataTable.Rows[Index]["AddressLine2"]);
ACustomer.Town = Convert.ToString(DB.DataTable.Rows[Index]["Town/City"]);
ACustomer.PostCode = Convert.ToString(DB.DataTable.Rows[Index]["PostCode"]);
ACustomer.EmailAddress = Convert.ToString(DB.DataTable.Rows[Index]["EmailAddress"]);
ACustomer.UserName = Convert.ToString(DB.DataTable.Rows[Index]["UserName"]);
ACustomer.Password = Convert.ToString(DB.DataTable.Rows[Index]["Password"]);
ACustomer.PhoneNo = Convert.ToString(DB.DataTable.Rows[Index]["PhoneNo"]);
//add the record to the private data member
customerList.Add(ACustomer);
//point at the next record
Index++;
}
}
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;
}
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");
}
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 void Update()
{
//update the recoed point ti by this staff
//connect to the database
clsDataConnection NewDBProducts = new clsDataConnection();
//set the paraters for the stored procedure
dBConnection.AddParameter("@staffid", ThisStaff.Staffid);
dBConnection.AddParameter("@Name", ThisStaff.Staffname);
dBConnection.AddParameter("@Age", ThisStaff.Age);
dBConnection.AddParameter("@Brief", ThisStaff.Brief);
dBConnection.AddParameter("@Gender", ThisStaff.Gender);
dBConnection.AddParameter("@Mobilesphone", ThisStaff.Mobilesphone);
dBConnection.AddParameter("@workage", ThisStaff.Workage);
dBConnection.AddParameter("@positiob", ThisStaff.Position);
//exectue the store procedure
NewDBProducts.Execute("sproc_tblStaff_Update");
}
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");
}
}
}
}
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 List<clsStaff> clsStaffListCollection()
{
//var for the index
Int32 Index = 0;
//var to store the record count
Int32 RecordCount = 0;
//object for the data connection
List<clsStaff> StaffList = new List<clsStaff>();
clsDataConnection DB = new clsDataConnection();
//execute the stored procedure
DB.Execute("sproc_tblStaff_SelectAll");
//get the count of records
RecordCount = DB.Count;
//while there are records to proccess
while (Index < RecordCount)
{
clsStaff StaffOne = new clsStaff();
StaffOne.Staffid = Convert.ToInt32(DB.DataTable.Rows[Index]["staffid"]);
StaffOne.Staffname = Convert.ToString(DB.DataTable.Rows[Index]["Name"]);
StaffOne.Age = Convert.ToInt32(DB.DataTable.Rows[Index]["age"]);
StaffOne.Brief = Convert.ToString(DB.DataTable.Rows[Index]["brief"]);
StaffOne.Gender = Convert.ToString(DB.DataTable.Rows[Index]["gender"]);
StaffOne.Mobilesphone = Convert.ToString(DB.DataTable.Rows[Index]["mobilesphone"]);
StaffOne.Workage = Convert.ToInt32(DB.DataTable.Rows[Index]["workage"]);
StaffOne.Position = Convert.ToString(DB.DataTable.Rows[Index]["position"]);
StaffList.Add(StaffOne);
// //point at the next record
Index++;
}
return StaffList;
}
//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 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 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 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;
}
}
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.");
//}
}
private void FrmEmailClient_Load(object sender, EventArgs e)
{
try
{
//clear the listbox
LstBxSavedSearch.Items.Clear();
//initalise the data connection
clsDataConnection DataConn = new clsDataConnection();
//add the sproc
DataConn.Execute("sproc_tblGroupList_GetAllGroupList");
//get the count of records
Int32 RecordCount = DataConn.Count;
//set up the index
Int32 Index = 0;
//create a new list
List<string> NewList = new List<string>();
//this runs whilst index is lower than record count
while (Index < RecordCount)
{
string zz = "Age(s) ";
zz += DataConn.DataTable.Rows[Index]["AgeFrom"].ToString();
zz += "-";
zz += DataConn.DataTable.Rows[Index]["AgeTo"].ToString();
zz += " Location ";
zz += DataConn.DataTable.Rows[Index]["Location"].ToString();
GroupListNo.Add(Convert.ToInt32(DataConn.DataTable.Rows[Index]["GroupListNo"]));
NewList.Add(zz);
Index++;
}
LstBxSavedSearch.DataSource = NewList;
}
catch
{
MessageBox.Show("Group List Retreival failed, please try again");
}
}
