7

Having read others posts, it already seems to me that the concept "repository" and "database" do not go well hand in hand, as they are meant to be completely separate concepts.... but I'll ask the question anyway.

I currently have to import different types of data (one type of data may consist of several thousand records) from several databases that happen to be all different (oracle, sybase, sql server), process that data (depending on what kind of data set is) and then write that processed data into a different database. The language I am using now is C#.

I have been told that using the repository pattern in my situation might come in handy, but I am unsure how to engineer it, and more importantly, where to place all the different parametrized SQL queries in this context. Having so many different products and different database sources only contributes to increase my confusion.

For the reason mentioned in my first paragraph, I have the feeling my SQL queries should be part of my data access layer, while my repositories actually live in layers above. Am I getting all this wrong? Is the repository pattern actually a terrible way of solving my problem?

5

My 2 cents: A Database is a database. A repository is something that will abstract the concept of the database for you.

When we say database, it comes directly in mind some keywords like MS SQL Server, Oracle, DB2, MySQL, PostgreSQL.

But if you expand a little bit the concept, a database is a place where you can put records, usually in a structured way, and you run queries against these records.

In this sense, a database may be a directory in your file system, with a bunch of text files spread apart, each text file with several lines, each line representing a single record, for example.

That being said, what is a repository then ? A repository is a higher level of abstraction, above the database. It's where you store, retrieve and query objects, from some magical place, that your application may not be fully aware of.

Concrete example: Let's say you have records of 3 different types: Car, TrafficTicket and Person. They relate to each other in the following schema:

  • A person has zero or many cars;
  • A car have zero or many traffic tickets;

Also, they have these attributes:

  • Person { Name, SSN } = SSN is a unique identifier.
  • Car { OwnerSSN, LicensePlate } = License Plate is a unique identifier.
  • TrafficTicket { Id, LicensePlate, Severity, Points, Value } = Id is a unique identifier.

So far so good, right ?

Now, imagine that you have a huge database of Traffic Tickets, provided as text file you can download from somewhere. Nothing illegal, obviously.

Also, you have in-house an Oracle Database where you have a list of Persons (each one being an employee of your company).

Also, you have a list of Cars that you can query from a partner that gave you direct access to his MySQL database.

Your manager, a nice guy - with some shady ethical background - asks you to make him a report about how many of his employess have a bad driving record. He wants to use that to leverage parking spaces in the company's parking lot, and he threatens you to provide him this report OR ELSE...

The plot thickens. You have little time to do your report. The bills in your house are getting due, and you have a family to raise (what will little Ben do without his morning cereal ?). So you decide to cope with the shady manager, and report him to FBI later. After all, it's nice to have some technical challenge here and there.

So you decide to create a C# project, because you're smart, cool, and you know LINQ-fu. And you structure your project as following:

Namespaces:

ShadyReport.TextFiles.DAL
    TrafficTicketReader.cs
        public List<TrafficTicket> GetAllTrafficTicketsFromTextFile(string pathOfCompletelyLegalTextFile)

ShadyReport.Oracle.DAL
    EF6OracleContext.cs    *(your Entity Framework Oracle DB Context)*

ShadyReport.MySQL.DAL
    EF6MySQLContext.cs     *(your Entity Framework MySQL DB Context)*

ShadyReport.Repository
    PersonRepository.cs
        public List<Person> GetAllPersons()

    CarRepository.cs
        public List<Car> GetCarsOfPersons(List<Person> persons)

    TrafficTicketRepository.cs
        public List<TrafficTicket> GetTrafficTicketsOfCars(List<Car> cars)

ShadyReport.Entity
    TrafficTicket.cs *(simple POCO object)*
    Person.cs        *(simple POCO object)*
    Car.cs           *(simple POCO object)*

ShadyReport.Business
    ShadyReportGenerator.cs
        public void GenerateShadyReport()

I'm assuming you know how to work with Entity Framework here, and that you understand that different DBContexts may have different ConnectionStrings, each pointing to a different database.

That being said, this is how your GenerateShadyReport() method would look like:

public void GenerateShadyReport()
{
    var personRepository = new ShadyReport.Repository.PersonRepository();
    var carRepository = new ShadyReport.Repository.CarRepository();
    var ticketRepository = new ShadyReport.Repository.TrafficTicketRepository();

    var persons = personRepository.GetAllPersons();
    var cars = carRepository.GetCarsOfPersons(persons);
    var tickets = ticketRepository.GetTrafficTicketsOfCars(cars);

    var shadyReport = <LINQ magic stuff to tie the objects above together>
}

As you can see, your report generator knows nothing of where the data comes from. From the report generator point of view, the database is a non-existent concept, as it fetches the data from the repositories.

That being said, your specific parameterized queries should go in your specific repositories. A query that filters cars by persons must be in the Cars repository, while a query that ties things from different sources together is done in the Business layer, above the repositories. This is how they look like:

+-----------------+---+
| UI              | E |
+-----------------+ n |
| Business        | t |
+-----------------+ i |
| Repository      | t |
+-----------------+ y |
| DAL             |   |
+-----------------+---+

And the call graph is always one way: DOWN. UI calls Business, which calls Repositories, which calls DALs. And they all "speak" entities.

I hope this helps. It's NOT THE DEFINITIVE ANSWER about repositories, but these kind of answers usually help me understand the concepts.

  • and if we can assume that all repositories come from a same source, would it be a good idea to create ShadyReport.Repository.shadyReportRepository that would actually hold results of this report? – John Feb 20 '17 at 16:23
  • I'm not sure I'm following what you're suggesting, but I'll tell you this: If we're talking about generating a report, and this generation contains business logic, than the generated report must come from a Business object, even if the business object reads from a common repository. On the other hand, if you want to persist this report, then you'll need a persistence method somewhere in your repositories to persist it somehow. And then we have the 3rd option: Retrieving an already existing report. You'll need a repository method for that, and also a business method for calling this repository. – Machado Feb 20 '17 at 16:42
  • Good to hear a reply on such an old post. Basically the example above handles report from three different data sources and you will probably end up doing linq queries on data from memory. Now a different and much simpler example, if this report comes from one source (sql db) and Person, Cars, Traffictickets have mutual relationships such as pk/fk relation and you do query on sql instead in memory would you treat this query result as a repository? – John Feb 20 '17 at 17:26
  • 1
    That's a tough question, IMO, because it needs more information in order to be answered. Are you using the DB only as a repository of data ? Is it a requirement of your product to not be vendor-locked ? How huge is the amount of data we're talking about ? All decisions you make while designing a system come with a price. I have a tendency to design considering what is better for the user, and, if vendor-locking isn't a concern and the report isn't dynamic, I'd run the report in the DB directly, which means: – Machado Feb 20 '17 at 18:17
  • Business Layer calling Repository, and Repository reading a Stored Procedure, Function or View with the report done. – Machado Feb 20 '17 at 18:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.