private Sale InsertSaleLineItemsInSale(Sale sale, SqlConnection connection) { string saleLineItemquery = Utility.ReadSQLQueryFromFile("CreateSaleLineItem.txt"); string updateItemAmountQuery = Utility.ReadSQLQueryFromFile("UpdateItemAmount.txt"); foreach (SaleLineItem saleLineItem in sale.saleLineItems) { SQLQuery sQLQuery = new SQLQuery(saleLineItemquery); sQLQuery.AddParameter("@amount", saleLineItem.amount.ToString(), SqlDbType.Int); sQLQuery.AddParameter("@price", saleLineItem.price.ToString(), SqlDbType.Decimal); sQLQuery.AddParameter("@saleid", sale.saleID.ToString(), SqlDbType.Int); sQLQuery.AddParameter("@itemid", saleLineItem.item.itemID.ToString(), SqlDbType.Int); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery, connection); if (sQLQueryResult.code != 0 || sQLQueryResult.dataTable.Rows.Count == 0) { throw sQLQueryResult.exception; } else { if (saleLineItem.item.treatment == false) { int saleLineItemID = (int)sQLQueryResult.dataTable.Rows[0]["SaleLineItemsID"]; saleLineItem.UpdateSaleLineItemID(saleLineItemID); SQLQuery updateAmountSQLQuery = new SQLQuery(updateItemAmountQuery); updateAmountSQLQuery.AddParameter("@decreaseamount", saleLineItem.amount.ToString(), SqlDbType.Int); updateAmountSQLQuery.AddParameter("@itemid", saleLineItem.item.itemID.ToString(), SqlDbType.Int); sQLQueryResult = SQLDatabaseConnector.QueryDatabase(updateAmountSQLQuery, connection); if (sQLQueryResult.code != 0) { throw sQLQueryResult.exception; } } } if (saleLineItem.treatment != null) { UpdateTreatmentAsPaid(saleLineItem.treatment, connection); } if (saleLineItem.prescription != null) { UpdatePrescriptionAsPaid(saleLineItem.prescription, connection); } } return(sale); }
private void UpdatePrescriptionAsPaid(Prescription prescription, SqlConnection connection) { string UpdateQuery = Utility.ReadSQLQueryFromFile("UpdatePrescriptionAsPaid.txt"); SQLQuery updateSQLQuery = new SQLQuery(UpdateQuery); updateSQLQuery.AddParameter("@prescriptionid", prescription.prescriptionID.ToString(), SqlDbType.Int); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(updateSQLQuery, connection); if (sQLQueryResult.code != 0) { throw sQLQueryResult.exception; } }
public List <Species> GetAllSpecies() { string query = Utility.ReadSQLQueryFromFile("GetSpecies.txt"); SQLQuery sQLQuery = new SQLQuery(query); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery); List <Species> allspecies = new List <Species>(); allspecies = GetSpecies(sQLQueryResult); return(allspecies); }
//method to query database for single query static public SQLQueryResult QueryDatabase(SQLQuery sQLQuery) { try { //initialize new DataTable DataTable dataTable = new DataTable(); //start transaction scope using (TransactionScope transactionScope = new TransactionScope()) { //initialize new SqlConnection with connectionstring from Utility Class using (SqlConnection connection = new SqlConnection(Utility.connectionString)) { //open connection connection.Open(); //initialize SqlCommand using (SqlCommand command = new SqlCommand(sQLQuery.query, connection)) { //add parameters to query if needed foreach (SQLParameter sqlParameter in sQLQuery.sQLParameters) { if (sqlParameter.parameterValue == null) { command.Parameters.Add(sqlParameter.parameterName, sqlParameter.dataType).Value = DBNull.Value; } else { command.Parameters.Add(sqlParameter.parameterName, sqlParameter.dataType).Value = sqlParameter.parameterValue; } } //execute sql to server and fill dataTable with returned result SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(command); sqlDataAdapter.Fill(dataTable); } } //complete and commit transaction transactionScope.Complete(); } return(new SQLQueryResult(dataTable, 0, null)); } catch (Exception e) { string test = e.GetType().ToString(); //return empty datatable and error code + exception if there were any issues return(new SQLQueryResult(new DataTable(), 1, e)); } }
private void DeleteSale(Sale sale, SqlConnection connection) { string query = Utility.ReadSQLQueryFromFile("DeleteSale.txt"); SQLQuery sQLQuery = new SQLQuery(query); sQLQuery.AddParameter("@saleid", sale.saleID.ToString(), SqlDbType.Int); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery, connection); if (sQLQueryResult.code != 0) { throw sQLQueryResult.exception; } }
private void UpdateTreatmentAsPaid(Treatment treatment, SqlConnection connection) { string UpdateQuery = Utility.ReadSQLQueryFromFile("UpdateTreatmentAsPaid.txt"); SQLQuery updateSQLQuery = new SQLQuery(UpdateQuery); updateSQLQuery.AddParameter("@treatmentid", treatment.treatmentID.ToString(), SqlDbType.Int); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(updateSQLQuery, connection); if (sQLQueryResult.code != 0) { throw sQLQueryResult.exception; } }
private void CreatePrivateCustomer(PrivateCustomer privateCustomer) { string query = Utility.ReadSQLQueryFromFile("CreatePrivateCustomer.txt"); SQLQuery sQLQuery = new SQLQuery(query); sQLQuery.AddParameter("@customerID", privateCustomer.customerID.ToString(), SqlDbType.Int); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery); if (sQLQueryResult.code != 0) { throw new CantCreateCustomer("", sQLQueryResult.exception); } }
public List <Treatment> GetTreatment(int treatmentID) { string query = Utility.ReadSQLQueryFromFile("GetTreatment.txt"); SQLQuery sQLQuery = new SQLQuery(query); sQLQuery.AddParameter("@treatmentID", treatmentID.ToString(), SqlDbType.Int); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery); List <Treatment> treatments = new List <Treatment>(); treatments = GetTreatmentList(sQLQueryResult); return(treatments); }
public string DeleteTreatment(int treatmentID) { string query = Utility.ReadSQLQueryFromFile("DeleteTreatment.txt"); SQLQuery sQLQuery = new SQLQuery(query); sQLQuery.AddParameter("@treatmentID", treatmentID.ToString(), SqlDbType.Int); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery); if (sQLQueryResult.code != 0) { throw new CantDeleteTreatmentException("", sQLQueryResult.exception); } return("ok"); }
public DateTime GetLastUpdate() { string query = Utility.ReadSQLQueryFromFile("GetLastUpdate.txt"); SQLQuery sQLQuery = new SQLQuery(query); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery); if (sQLQueryResult.code == 0) { return((DateTime)sQLQueryResult.dataTable.Rows[0]["UpdateTime"]); } else { throw new DateNotFound("", sQLQueryResult.exception); } }
public List <OperationRoom> GetAllOperationRooms() { string query = Utility.ReadSQLQueryFromFile("GetAllOperationRooms.txt"); SQLQuery sQLQuery = new SQLQuery(query); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery); if (sQLQueryResult.code != 0) { throw new CantFindOperationRoomsException("", sQLQueryResult.exception); } List <OperationRoom> operationRooms = new List <OperationRoom>(); operationRooms = GetAllOperationRooms(sQLQueryResult); return(operationRooms); }
public List <Cage> GetAllCages() { string query = Utility.ReadSQLQueryFromFile("GetAllCages.txt"); SQLQuery sQLQuery = new SQLQuery(query); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery); if (sQLQueryResult.code != 0) { throw new CantFindCagesException("", sQLQueryResult.exception); } List <Cage> cages = new List <Cage>(); cages = GetCageList(sQLQueryResult); return(cages); }
public string UpdateMedicinePriceDate(MedicinePrice medicinePrice) { string query = Utility.ReadSQLQueryFromFile("UpdateMedicinPriceDate.txt"); SQLQuery sQLQuery = new SQLQuery(query); sQLQuery.AddParameter("@newdate", medicinePrice.date.ToString(), SqlDbType.DateTime); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery); if (sQLQueryResult.code == 0) { return("ok"); } else { throw new DateNotUpdated("", sQLQueryResult.exception); } }
private void CreateBusinessCustomer(BusinessCustomer businessCustomer) { SqlConnection con = new SqlConnection(Utility.connectionString); string query = Utility.ReadSQLQueryFromFile("CreateBusinessCustomer.txt"); SQLQuery sQLQuery = new SQLQuery(query); sQLQuery.AddParameter("@cvr", businessCustomer.cvr.ToString(), SqlDbType.Int); sQLQuery.AddParameter("@businesscustomerID", businessCustomer.customerID.ToString(), SqlDbType.Int); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery); if (sQLQueryResult.code != 0) { throw new CantCreateCustomer("", sQLQueryResult.exception); } }
public Customer CreateCustomer(Customer customer) { SqlConnection con = new SqlConnection(Utility.connectionString); string query = Utility.ReadSQLQueryFromFile("CreateCustomer.txt"); SQLQuery sQLQuery = new SQLQuery(query); sQLQuery.AddParameter("@phone", customer.phone.ToString(), SqlDbType.VarChar); sQLQuery.AddParameter("@name", customer.name.ToString(), SqlDbType.VarChar); sQLQuery.AddParameter("@address", customer.address.ToString(), SqlDbType.VarChar); sQLQuery.AddParameter("@email", customer.email.ToString(), SqlDbType.VarChar); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery); if (sQLQueryResult.code != 0) { throw new CantCreateCustomer("", sQLQueryResult.exception); } int customerID = (int)sQLQueryResult.dataTable.Rows[0]["CustomerID"]; customer.UpdateID(customerID); if (customer.GetType() == typeof(BusinessCustomer)) { //der laves en BusinessCustomer som castes til customer BusinessCustomer businessCustomer = (BusinessCustomer)customer; CreateBusinessCustomer(businessCustomer); } if (customer.GetType() == typeof(PrivateCustomer)) { //der laves en PrivateCustomer som castes til customer PrivateCustomer privateCustomer = (PrivateCustomer)customer; CreatePrivateCustomer(privateCustomer); } return(customer); }
public List <Treatment> GetUnpaidTreatmentsByCustomer(Customer customer) { string query = Utility.ReadSQLQueryFromFile("GetUnpaidTreatmentsByCustomer.txt"); SQLQuery sQLQuery = new SQLQuery(query); sQLQuery.AddParameter("@customerid", customer.customerID.ToString(), SqlDbType.Int); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery); if (sQLQueryResult.code != 0) { throw new CantGetUnPaidTreatments("", sQLQueryResult.exception); } List <Treatment> treatments = new List <Treatment>(); treatments = GetTreatmentList(sQLQueryResult); return(treatments); }
public string UndeleteCustomer(Customer customer) { string query = Utility.ReadSQLQueryFromFile("UndeleteCustomer.txt"); SQLQuery sQLQuery = new SQLQuery(query); //af en eller anden grund virker det her fint med varchars og strings sQLQuery.AddParameter("@customerID", customer.customerID.ToString(), SqlDbType.VarChar); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery); if (sQLQueryResult.code == 0) { return("Kunde gjort aktiv"); } else { return(sQLQueryResult.exception.Message.ToString()); } }
public string DeleteAnimal(Animal animal) { string query = Utility.ReadSQLQueryFromFile("DeleteAnimal.txt"); SQLQuery sQLQuery = new SQLQuery(query); sQLQuery.AddParameter("@animalid", animal.animalID.ToString(), SqlDbType.Int); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery); if (sQLQueryResult.code == 0) { return("ok"); } else { throw new AnimalNotDeletedException("", sQLQueryResult.exception); } }
public List <Treatment> GetManyTreatmentsByDateTime(DateTime startTime, DateTime endTime) { string query = Utility.ReadSQLQueryFromFile("GetManyTreatmentsByDateTime.txt"); SQLQuery sQLQuery = new SQLQuery(query); sQLQuery.AddParameter("@startTime", startTime.ToString("yyyy-MM-ddTHH:mm:ss"), SqlDbType.DateTime); sQLQuery.AddParameter("@endTime", endTime.ToString("yyyy-MM-ddTHH:mm:ss"), SqlDbType.DateTime); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery); if (sQLQueryResult.code != 0) { throw new CantFindTreatmentsException("", sQLQueryResult.exception); } List <Treatment> treatments = new List <Treatment>(); treatments = GetTreatmentList(sQLQueryResult); return(treatments); }
public List <TreatmentType> GetAllTreatmentTypes() { string query = Utility.ReadSQLQueryFromFile("GetManyTreatmentTypes.txt"); SQLQuery sQLQuery = new SQLQuery(query); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery); if (sQLQueryResult.code != 0 || sQLQueryResult.dataTable.Rows.Count == 0) { throw new NoTreatmentTypesFoundException("", sQLQueryResult.exception); } else { List <TreatmentType> treatments = new List <TreatmentType>(); treatments = GetTreatmentTypeList(sQLQueryResult); return(treatments); } }
public List <Animal> GetManyAnimalByCustomerID(Customer customer) { string query = Utility.ReadSQLQueryFromFile("GetManyAnimalsByCustomerID.txt"); SQLQuery sQLQuery = new SQLQuery(query); sQLQuery.AddParameter("@CustomerID", customer.customerID.ToString(), SqlDbType.VarChar); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery); if (sQLQueryResult.code == 0) { List <Animal> animals = new List <Animal>(); animals = GetAnimalList(sQLQueryResult); return(animals); } else { throw new CantGetAnimalList("", sQLQueryResult.exception); } }
public List <Prescription> GetUnpaidPrescriptionByCustomer(Customer customer) { string query = Utility.ReadSQLQueryFromFile("GetUnpaidPrescriptionsByCustomer.txt"); SQLQuery sQLQuery = new SQLQuery(query); sQLQuery.AddParameter("@customerid", customer.customerID.ToString(), SqlDbType.Int); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery); if (sQLQueryResult.code == 0) { List <Prescription> prescription = new List <Prescription>(); prescription = GetAllPrescriptionList(sQLQueryResult); return(prescription); } else { throw new CantGetUnPaidPrescription("", sQLQueryResult.exception); } }
public List <Employee> GetAllEmployees() { List <Employee> employees = new List <Employee>(); string query = Utility.ReadSQLQueryFromFile("GetAllEmployees.txt"); SQLQuery sQLQuery = new SQLQuery(query); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery); if (sQLQueryResult.code != 0 || sQLQueryResult.dataTable.Rows.Count == 0) { throw new NoEmployeesFoundException("", sQLQueryResult.exception); } else { employees = GetEmployeeList(sQLQueryResult); return(employees); } }
public List <Item> GetAllActiveItems() { string query = Utility.ReadSQLQueryFromFile("GetAllActiveItems.txt"); SQLQuery sQLQuery = new SQLQuery(query); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery); if (sQLQueryResult.code == 0) { List <Item> items = GetListOfItemsFromDatatable(sQLQueryResult.dataTable); if (items.Count == 0) { throw new NoItemsFoundException("1"); } return(items); } else { throw new NoItemsFoundException("", sQLQueryResult.exception); } }
public MedicalRecord CreateMedicalRecordEntry(MedicalRecord medicalRecord) { Animal animal = medicalRecord.animal; Treatment treatment = medicalRecord.treatment; string query = Utility.ReadSQLQueryFromFile("CreateMedicalRecordEntry.txt"); SQLQuery sQLQuery = new SQLQuery(query); sQLQuery.AddParameter("@entry", medicalRecord.entry.ToString(), SqlDbType.VarChar); sQLQuery.AddParameter("@animalID", animal.animalID.ToString(), SqlDbType.Int); sQLQuery.AddParameter("@treatmentID", treatment.treatmentID.ToString(), SqlDbType.Int); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery); int medicalrecordID = (int)sQLQueryResult.dataTable.Rows[0]["MedicalRecordID"]; if (sQLQueryResult.code != 0) { throw new CantUpdateTreatmentException("", sQLQueryResult.exception); } medicalRecord.UpdateMedicalRecordID(medicalrecordID); return(medicalRecord); }
public List <Sale> GetManySalesByCustomerID(Customer customer) { List <Sale> sales; string query = Utility.ReadSQLQueryFromFile("GetManySalesByCustomerID.txt"); SQLQuery sQLQuery = new SQLQuery(query); sQLQuery.AddParameter("@customerid", customer.customerID.ToString(), SqlDbType.Int); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery); if (sQLQueryResult.code != 0 || sQLQueryResult.dataTable.Rows.Count == 0) { return(new List <Sale>()); } else { sales = GetSalesFromDataTable(sQLQueryResult.dataTable, customer); } return(sales); }
public Animal CreateAnimal(Animal animal) { string query = Utility.ReadSQLQueryFromFile("CreateAnimal.txt"); SQLQuery sQLQuery = new SQLQuery(query); sQLQuery.AddParameter("@customerid", animal.customer.customerID.ToString(), SqlDbType.Int); sQLQuery.AddParameter("@species", animal.Species.speciesID.ToString(), SqlDbType.Int); sQLQuery.AddParameter("@birthday", animal.birthYear.ToString("yyyy-MM-ddTHH:mm:ss"), SqlDbType.DateTime); sQLQuery.AddParameter("@name", animal.name.ToString(), SqlDbType.VarChar); sQLQuery.AddParameter("@weight", animal.weight.ToString(), SqlDbType.Float); sQLQuery.AddParameter("@gender", animal.gender.ToString(), SqlDbType.Bit); sQLQuery.AddParameter("@active", "True", SqlDbType.Bit); if (animal.Employee == null) { sQLQuery.AddParameter("@employeeid", null, SqlDbType.Int); } else { sQLQuery.AddParameter("@employeeid", animal.Employee.employeeID.ToString(), SqlDbType.Int); } SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery); int animalID = (int)sQLQueryResult.dataTable.Rows[0]["AnimalID"]; animal.UpdateID(animalID); if (sQLQueryResult.code == 0) { return(animal); } else { throw new AnimalNotCreatedException("", sQLQueryResult.exception); } }
public List <Prescription> GetAllPrescriptionByAnimal(int animalID) { string query = Utility.ReadSQLQueryFromFile("GetAllPrescriptionByAnimal.txt"); SQLQuery sQLQuery = new SQLQuery(query); sQLQuery.AddParameter("@animalID", animalID.ToString(), SqlDbType.Int); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery); List <Prescription> prescription = new List <Prescription>(); prescription = GetAllPrescriptionList(sQLQueryResult); if (sQLQueryResult.code == 0) { return(prescription); } else { throw new PrescriptionNotFoundException("", sQLQueryResult.exception); } }
public List <MedicalRecord> GetAllMedicalRecordEntriesByAnimalID(Animal animal) { string query = Utility.ReadSQLQueryFromFile("GetAllMedicalRecordByAnimal.txt"); SQLQuery sQLQuery = new SQLQuery(query); sQLQuery.AddParameter("@animalid", animal.animalID.ToString(), SqlDbType.Int); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery); List <MedicalRecord> entries = new List <MedicalRecord>(); entries = GetMedicalRecordEntryList(animal, sQLQueryResult); if (sQLQueryResult.code == 0) { return(entries); } else { throw new MedicalRecordEntryNotFoundException("", sQLQueryResult.exception); } }
public string UpdateTreatment(Treatment treatment) { string query = Utility.ReadSQLQueryFromFile("UpdateTreatment.txt"); SQLQuery sQLQuery = new SQLQuery(query); sQLQuery.AddParameter("@startTime", treatment.startTime.ToString(), SqlDbType.DateTime); sQLQuery.AddParameter("@endTime", treatment.endTime.ToString(), SqlDbType.DateTime); sQLQuery.AddParameter("@payed", treatment.payed.ToString(), SqlDbType.Bit); sQLQuery.AddParameter("@headline", treatment.headline.ToString(), SqlDbType.VarChar); sQLQuery.AddParameter("@active", treatment.active.ToString(), SqlDbType.Bit); sQLQuery.AddParameter("@treatmentID", treatment.treatmentID.ToString(), SqlDbType.Int); sQLQuery.AddParameter("@status", treatment.status.ToString(), SqlDbType.Int); SQLQueryResult sQLQueryResult = SQLDatabaseConnector.QueryDatabase(sQLQuery); if (sQLQueryResult.code != 0) { throw new CantUpdateTreatmentException("", sQLQueryResult.exception); } return("ok"); }