3

In my very small C# application, I handle the SQL queries directly from the code using basic ADO.NET. I do not want to use an ORM, such as Entity Framework.

Now, I have a repository for CRUD operations. But I find I need flexibility for more complex queries.

For example, here is the (naive and simplified) process of logging into the application:

  1. The user types a password (no username, Windows style).
  2. A Count SQL-query counts the users with those credentials.
  3. If there is a user with those credentials, a users repository load the user's information from the database and retrieves a User object.

Now, where should that Count method be? It certainly can't be in the repository, so where should it be? Is there a pattern for handling such non-CRUD operations against the database?

Improve this question
5
  • Of course. Simply execute an arbitrary SQL query. You can do that with ADO, Entity Framework, Dapper, and probably a half-dozen other ways.
    – Robert Harvey
    Commented Sep 27, 2017 at 19:04
  • @RobertHarvey, Thanks! My question is a bit simplified. The operations I want to do are more complex than a simple count, and I want to reuse them in the application, so I want them to be inside a dedicated class or something like that. Did I think maybe a service? But I am not familiar enough with the concept to be sure.
    – Michael Haddad
    Commented Sep 27, 2017 at 19:11
  • It certainly can't be in the repository Why not?
    – Andy
    Commented Sep 27, 2017 at 21:40
  • 1
    Why do you think something involving reading data isn't a Read? Just because it isn't 'select * from table where pk = $key'?
    – Caleth
    Commented Sep 28, 2017 at 8:30
  • @Caleth- Well... yeah?
    – Michael Haddad
    Commented Sep 28, 2017 at 15:07

2 Answers 2

Reset to default
10

It certainly can't be in the repository

Sure it can. This may actually the best place for it. Your repos responsibility is to provide an abstraction for a subset of the database, which might be replaced by a mock repository for testing purposes. Putting something like a Count method into a repo will still fit into that "pattern". Note if client code of the repo does not need to use SQL directly, this makes that client code much easier to unit test (without a database).

Of course, if you get a dozen of such query methods, it might become necessary to split up the repo class further into separate classes, but how and when you do this is up to you, there is no brain-dead cargo-cult design pattern for this.

Improve this answer
3
  • Right, your Count() repository method calls ExecuteScalar() under the covers. One may have multiple interfaces IFooRepository, IBarRepository once the method count grows.
    – Jon Raynor
    Commented Sep 28, 2017 at 17:20
  • Correct me if I'm wrong, but shouldn't a repository be a common set of CRUD operations (common interface) to keep persistence impl abstracted away? I think including a simple count in that interface would still fit into that, but he included other examples. I have a stored procedure that is doing several joins that won't have any CUD operations and is joining several datasets (which have their own repos). I don't feel like my proc fits into a repository, but I do want to keep all data access abstracted away from the service layer. I feel like there should be another pattern utilized here
    – Adam Plocher
    Commented Sep 9, 2018 at 23:26
  • 1
    @AdamPlocher: "I think including a simple count wouls still fit into that" - yes, exactly what I wrote. "he included other examples" - huh? where did you find those? "but I do want to keep all data access abstracted away from the service layer" - sure, as I wrote, at some point you will need additional classes, and your situation is probably such a case. I think your only misconception here is that for every useful design decision there must a "pattern". Start using your own experience, don't believe anything in your program needs to have a pattern name.
    – Doc Brown
    Commented Sep 10, 2018 at 0:07
3

Typically this done via:

  • ExecuteReader - Returns 1 or more data records => Select * from table WHERE ...
  • ExecuteScalar - Returns a single value => Select count(*) from table WHERE ...
  • ExecuteNonQuery - Does not return anything => Delete from table WHERE ...

One can also have a return value from a stored procedure as well that can be referenced as well as out parameters that can be inspected.

Your data layer should support these operations. (ADO.Net does)

Improve this answer
4
  • Thanks. My question is how the data layer should support them, and if is there a pattern for it. For CRUD operations, there is a very well-established pattern: a repository. But what about non-CRUD operations? Thanks a lot!
    – Michael Haddad
    Commented Sep 27, 2017 at 19:14
  • 2
    @Sipo: There's no "pattern" required here, other than the use of Execute**. Just put the code into your Data Access Layer and use the resulting function in your upstream layer. BTW, these are essentially the same methods you can use from EF or Dapper.
    – Robert Harvey
    Commented Sep 27, 2017 at 19:16
  • @RobertHarvey- My data access layer is composed of more than one class. It's a collection of classes inside a namespace. Is it a good solution to dedicate a class (utility) for such operations?
    – Michael Haddad
    Commented Sep 27, 2017 at 19:23
  • 2
    Well, I consider custom queries a part of the business logic, so I generally put them in the Business Logic Layer or Service Layer. I usually expose an ExecuteNonQuery method on my Data Access Layer so that I don't have to take a reference to Entity Framework in my other layers.
    – Robert Harvey
    Commented Sep 27, 2017 at 19:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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