/// <summary> /// Returns a list of services performed based on vehicleID /// </summary> /// <param name="vehicleID"></param> /// <returns></returns> public static List <ServiceRecordType> GetListServicesPerformedByVehicle(int vehicleID) { List <ServiceRecordType> servicesPerformedList = new List <ServiceRecordType>(); string selectStatement = "SELECT SC.categoryName, SR.dateOfService, SR.mileage " + "FROM serviceRecordType SRC " + "JOIN serviceCategory SC ON SRC.serviceCategoryID = SC.serviceCategoryID " + "JOIN serviceRecord SR ON SRC.serviceRecordID = SR.serviceRecordID " + "WHERE SR.vehicleID = @vehicleID " + "ORDER BY SR.dateOfService DESC"; SqlDataReader reader = null; SqlConnection connection = null; try { using (connection = DBConnection.GetConnection()) { connection.Open(); using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection)) { selectCommand.Parameters.AddWithValue("@vehicleID", vehicleID); using (reader = selectCommand.ExecuteReader()) { while (reader.Read()) { ServiceRecordType src = new ServiceRecordType(); src.categoryName = reader["categoryName"].ToString(); src.dateOfService = (DateTime)reader["dateOfService"]; src.mileage = (int)reader["mileage"]; servicesPerformedList.Add(src); } } } } } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { if (connection != null) { connection.Close(); } if (reader != null) { reader.Close(); } } return(servicesPerformedList); }
private void AddServiceRecord(ServiceRecordType type, TimeSpan addTime, string remark) { var now = DateTime.Now; var uid = _User.Uid; var day = (int)Math.Ceiling(addTime.TotalDays); _UserServiceStateUpdateRecordRepository.Add(new UserServiceStateUpdateRecord() { uid = uid, creted = now, IncreaseDays = day, remark = remark, type = type }); _UserServiceStateUpdateRecordRepository.SaveChanges(); }
/// <summary> /// Associates a ServiceRecord with a ServiceCategory /// </summary> /// <param name="newServiceRecordType"></param> /// <returns></returns> public static int CreateServiceRecordType(ServiceRecordType newServiceRecordType) { int exitStatus = 0; SqlConnection connection = DBConnection.GetConnection(); string insertStatement = "INSERT serviceRecordType " + "(serviceRecordID, serviceCategoryID)" + "VALUES" + "(@serviceRecordID, @serviceCategoryID)"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue("@serviceRecordID", newServiceRecordType.serviceRecordID); insertCommand.Parameters.AddWithValue("@serviceCategoryID", newServiceRecordType.serviceCategoryID); try { connection.Open(); insertCommand.ExecuteNonQuery(); exitStatus = 0; } catch (SqlException ex) { exitStatus = 1; StringBuilder errorDetails = new StringBuilder(); for (int i = 0; i < ex.Errors.Count; i++) { errorDetails.Append("Index #" + i + "\n" + "Message: " + ex.Errors[i].Message + "\n" + "Error Number: " + ex.Errors[i].Number + "\n" + "Procedure: " + ex.Errors[i].Procedure + "\n"); } MessageBox.Show(errorDetails.ToString(), "SQL Exception"); } finally { connection.Close(); } return(exitStatus); }
public int AddServiceRecordType(ServiceRecordType newServiceRecordType) { return(ServiceRecordTypeDAL.CreateServiceRecordType(newServiceRecordType)); }
public int RemoveServiceRecordType(ServiceRecordType oldServiceRecordType) { return(ServiceRecordTypeDAL.RemoveServiceRecordType(oldServiceRecordType)); }