ASP.NET MVC3: Reusable method which returns Database query results

Question

I want a method where I can call, that would query the database for my given query string. Which can be refferenced from different controllers/actions. (note, complexity of actual query is quite big).

So a few questions:

Where would the code go. A new controller? Helper?
How would I reference it (call it)?

What object, if following my current style, would be the return type.

public Something WebCostCentres()
{
    using (var db = Database.OpenConnectionString(Mynamespace.Properties.Settings.Default.dbConnString, 
                           "System.Data.SqlClient"))
    {
        //ViewBag.CostCentres = db.Query("SELECT DISTINCT CostCentreNo");
        return db.Query("SELECT DISTINCT CostCentreNo");
    }
}

Webmatrix.Data Sorry for slow reply, my internet cut off for half an hour. Reading through the answers provided. :) — IAmGroot
– IAmGroot, Commented Dec 12, 2011 at 10:25

slfan · Accepted Answer · 2011-12-12 09:57:35Z

2

I would create some kind of Service class for this.
You crate the service and call the method.
The same type as your Query method. IEnumerable<Something> would be an option. You might have to call ToList or ToArray to execute the query, because the connection might be closed.

The service layer is often called repository. Google for this and you will find tons of examples.

edited Dec 12, 2011 at 9:57

answered Dec 12, 2011 at 9:46

slfan

9,129115 gold badges69 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marc Gravell Over a year ago

re 3: As long as Query returns a completed list, not an active IEnumerable<T> - since the connection will be closed before it is consumed; might need a ToList()

wnascimento · Accepted Answer · 2011-12-12 10:09:32Z

2

1.Where would the code go. A new controller? Helper?

A class. Service oriented architecure wise.

2.How would I reference it (call it)?

As local variable in the page, filled via your trusted IOC container.

3.What object, if following my current style, would be the return type

None. Our current style is outdated. Ever heard of LINQ? The IQueryable extensions to .NET? It is not like they are new. It should return either IEnumerable or IQueryable, and be in general generic. Or a specific type in case only one number is returned.

edited Dec 12, 2011 at 10:09

wnascimento

1,9591 gold badge19 silver badges16 bronze badges

answered Dec 12, 2011 at 9:56

TomTom

62.4k11 gold badges95 silver badges152 bronze badges

Comments

wnascimento · Accepted Answer · 2011-12-12 10:10:37Z

2

repository pattern is applicable too

See an example http://mstecharchitect.blogspot.com/2009/08/aspnet-mvc-and-linq-to-sql-using.html

answered Dec 12, 2011 at 10:10

wnascimento

1,9591 gold badge19 silver badges16 bronze badges

Collectives™ on Stack Overflow

ASP.NET MVC3: Reusable method which returns Database query results

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related