Is there a pattern for non-CRUD operations against a database?

Question

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?

• Robert Harvey: Of course. Simply execute an arbitrary SQL query. You can do that with ADO, Entity Framework, Dapper, and probably a half-dozen other ways.
• Michael Haddad: @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.
• Andy: It certainly can't be in the repository Why not?
• Caleth: Why do you think something involving reading data isn't a Read? Just because it isn't 'select * from table where pk = $key'?
• Michael Haddad: @Caleth- Well... yeah?

Answer 1

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.

• Jon Raynor: Right, your Count() repository method calls ExecuteScalar() under the covers. One may have multiple interfaces IFooRepository, IBarRepository once the method count grows.
• Adam Plocher: 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
• Doc Brown: @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.

Answer 2

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)

• Michael Haddad: 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!
• Robert Harvey: @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.
• Michael Haddad: @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?
• Robert Harvey: 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.