How to avoid many service methods each one for a different query

Question

This site only queries a database - NO CRUD

I have a Service DLL that I call from a ApiController the ApiController methods are called by ajax requests in my HTML.

My problem is it's getting pretty hairy in my service. Here are just a few examples of the method stubs. I have more methods than this. Then it get's even hairy as I have to wrap every service call in my APIController.

Service

    public IEnumerable<CodedValue> GetStreetSuffix()
    {
        using (var s = new SqlConnection(this.connection))
        {
            s.Open();
            var results = s.Query<CodedValue>(
                 @"SELECT DISTINCT STR_SFX Value,STR_SFX Code FROM Addresses  ORDER BY 1").ToList();
            return results;

        }            
    }

    public IEnumerable<dynamic> GetStreetNumbers(string term)
    {
        using (var s = new SqlConnection(this.connection))
        {
            s.Open();
            var results = s.Query<dynamic>(
                 @"SELECT DISTINCT STR_NUM FROM Addresses where ISNULL(RTRIM(STR_PFX) + ' ','') + RTRIM(STR) + ISNULL(' ' + STR_SFX,'') = @q", new { q = term }).ToList();
            return results;

        }
    }

APIController

[HttpGet]
public IEnumerable<dynamic> ListStreetNumbers(string term)
{
    var searchservice = new SearchService();
    var results = searchservice.GetStreetNumbers(term);
    return results;
}

[HttpGet,HttpPost]
public IEnumerable<dynamic> CountParcelsByAddresses(Address json)
{
    var searchservice = new SearchService();
    var results = searchservice.CountParcelsByAddress(json);
    return results;
}

What can I do to make this cleaner while not abstracting the hell out of it. Most of what I did was trying to avoid having SQL queries in my controllers or in my HTML.

score 1 · Accepted Answer · answered Feb 14 '14 at 18:58

1

I agree - there should be no SQL in controllers or HTML.

I create a separate interface-based persistence tier and isolate it in the implementation.

answered Feb 14 '14 at 18:58

duffymo

305,152
44
369
561

do you know of any projects on git that use this technique well? I'd like to see some examples of a better way to do things – Doug Chamberlain Feb 14 '14 at 19:03
Yes, this is how Spring framework for Java does things. There's a Spring.NET as well, but I'm not familiar with it. Controller is usually tightly tied to View, so it's better to keep view-agnostic things away from it. Spring would say (View->Controller)->Service->Persistence. Interface-based Service layer can be exposed as web services. That's who owns transactions and the model and persistence objects needed to fulfill use cases. – duffymo Feb 14 '14 at 19:40

score 1 · Answer 2 · answered Feb 14 '14 at 19:32

Take a look at SocialGoal.

The project uses Entity Framework that uses a repository pattern to hold an instance of the dbcontext object (Database mapping layer). Each repository also inherited from a repositorybase class that implements generic methods such as Get, GetAll, Create, Delete method for the dbset object. If you prefer SQL statement you can create in that layer. Your service layer will query the repository layer and get the domain objects you want.

How to avoid many service methods each one for a different query

2 Answers2