The database has a table of test results in the Russian language and математике (SubjectCode=2) :

dbo.Result enter image description here

In the second table are just information about schools:

dbo.School

enter image description here

Such results are 40.000-70.000. It is necessary to get the output for each test person here such a report in the pdf-файле :

enter image description here

My decision:

  1. Created an Excel template;
  2. I take data from the database and put it into this xlsx-template;
  3. I save this template to pdf-file;
  4. And so for each student.

enter image description here

LearnerReport.cs

 namespace so16092016.Models { public class LearnerReport { public string SNS { get; set; } //Surname Name SecondName public string SchoolName { get; set; } public string ClassName { get; set; } public int TestResult5 { get; set; } } }

Program.cs

 using Excel = Microsoft.Office.Interop.Excel; namespace so16092016 { class Program { static void Main(string[] args) { resultsEntities context = new resultsEntities(); ResultsRepository resultsRepository = new ResultsRepository(context); var ma_results = resultsRepository.GetTList().Where(x => x.SubjectCode == 2); //получить результаты по математике Excel.Application app = new Excel.Application(); app.DisplayAlerts = false; Excel.Workbook book_template = app.Workbooks.Open(@"шаблон_отчета.xlsx"); Excel._Worksheet sheet_template = book_template.Sheets["отчет"]; foreach(var ob in ma_results) { //1. Создаем объкт LearnerReport из БД LearnerReport report = new LearnerReport { SNS = $"{ob.surname} {ob.name} {ob.SecondName}", SchoolName = ob.SchoolName, ClassName = ob.ClassName, TestResult5 = ob.TestResult5 }; //2. Экспорт объкта LearnerReport в шаблон xlsx sheet_template.Range["C4"].Value2 = report.SNS; sheet_template.Range["C5"].Value2 = report.SchoolName; sheet_template.Range["C6"].Value2 = report.ClassName; sheet_template.Range["C9"].Value2 = report.TestResult5; //3. Сохраняем полученный файл в .pdf на рабочем столе string file_name = $@"{Environment.GetFolderPath(Environment.SpecialFolder.Desktop)}\{report.SNS}.pdf"; sheet_template.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, file_name); } book_template.Close(0); book_template = null; app.Quit(); app = null; } } }

Necessary: ​​The application works and gives the desired result. But you probably see that it is far from the PLO ( SOLID ) and as a result it is very difficult to “finish” it and scale it. Help to design this mechanism for generating such reports correctly:

  • Should the model export logic in .xlsx be at the model itself or is it necessary to create a separate class manager for this?
  • What should be the model?
  • How to create a model report based on database objects?
  • which generative pattern is more appropriate here?

    1 answer 1

    Specifically, in your case there is no need to apply any patterns, because you have a fairly simple program and patterns will add only unnecessary complexity (using patterns for the sake of patterns is bad practice). Patterns will do if you write large Enterprise applications where flexibility and scalability are needed.

    If I understand correctly, then you want to refactor the program.

    1) From the code I did not understand what you are using to get data from the database, but I would advise you to use some ORM, for example, Entity Framework 6 .

    Create a DbContext and use it in the ResultsRepository and SchoolRepository repositories. Put all samples in the appropriate repository for example.

     class ResultsRepository { // возвращает все результаты public IEnumerable<Result> GetResults() // возвращает все результаты по заданому предмету. public IEnumerable<Result> GetResults(Subject subject) // возвращает все результаты для заданой школы. public IEnumerable<Result> GetResults(int idSchool) // возвращает все результаты для заданой школы по заданому предмету. public IEnumerable<Result> GetResults(int idSchool, Subject subject) } class SchoolRepository { // возвращает список всех школ. public IEnumerable<School> GetSchools() }

    2) The data model looks something like this.

     class Result { [Key] public Guid Id { get; set; } public string Surname { get; set; } public string Name { get; set; } public string SecondName { get; set; } [NotMapped] public string SNS => $"{Surname} {Name} {SecondName}"; public School School { get; set; } public string ClassName { get; set; } public Subject Subject { get; set; } public int TestResult5 { get; set; } } class School { [Key] public int Id { get; set; } public string Name { get; set; } } enum Subject { RussianLanguage = 1, Mathematics = 2 }

    3) You can create a separate class for working with reports.

     class LearnerReportManager { public Excel._Worksheet CreateReport(Result result) public SaveReport(Excel._Worksheet reportWorksheet, string fileName) }

    4) To generate an Excel ray report, use EPPlus or Syncfusion's cooler File Formats (they have a free version). You will not depend on the installed MS Office.

    5) If the count. generated reports at a time from 40 to 70 thousand, it would not be bad to add multi-threaded report creation, it will significantly reduce the time to create reports.

    • LearnerReportManager class violate the Single Responsibility Principle ? After all, this was the question: how правильно build such classes managers / services? - Adam
    • @adamshakhabov In this case, I put the responsibility of creating / deleting / saving / copying a report on the LearnerReportManager class. If a class has a lot of responsibility, then it will change often. You can take out every responsibility in separate classes, but I did not do it because I did not want to split up and blur logic into small classes. If you misuse the SRP, it will lead to a huge number of small classes and blurring the logic between them. There are many good articles and books online how to use SOLID correctly. - Murad