CODEWITHSUNDEEP
asp.net-mvclinqentity-framework
Jeff
Jeff

Reputation: 43

MVC 4 Strongly Typed Data to View without ORM

I've seen other questions regarding using straight SQL for MVC data access, for example here. Most responses don't answer the question but ask

"Why would you not want to use ORM, EF, Linq, etc?"

  1. My group does custom reporting out of a data warehouse that requires a lot of complex, highly tuned Oracle queries that are manipulated based on user GUI parameter selections.

  2. My newest project is to develop a SQL plugin reporting tool for SQL report developers. They would create a pre-tuned SQL for the report with pseudo parameters and enter (and store) via the GUI. Then the GUI would prompt them for the parameter definitions (name and type) that need to be displayed/requested at run time to ultimately replace the pseudo variables.

So a SQL statement may look like:

SELECT * FROM orders WHERE order date BETWEEN '<Date1>' AND '<Date2>'

And the report developer would then, via the GUI, add two parameters named Date1 and Date2, and flag them as date fields.

End users would then select the report, get prompted for Date1 and Date2, and the GUI would do the substitution and run the SQL.

As you can see, I have no choice but to use straight SQL (especially in the 2nd example and understand I would have to forgo strongly typed in the 2nd also).

So my questions are:

  1. When is it necessary to bypass EF/Linq (and there are definitely reasons to), what is best practice in MVC 4?
  2. And how best to strongly type when I do know the output columns ahead of time?
  3. And CRUD processing?
  4. Can anyone point me to any examples of non-EF/Linq based coding in this regard?

Upvotes: 3

Views: 343

Answers (1)

Sleeper Smith
Sleeper Smith

Reputation: 3242

I think this is a bit open ended question, so here's my 2c. (If tl dr, go to last section)

  1. To me, it's not so much "by passing EF/Linq", but rather, the need to choose the appropriate data persistence library. I have used PetaPoco, Ado.Net, NHibernate/ActiveRecord, Linq2Sql, EF (My main choice) with MVC.

    Best practice actually comes from realising that Controllers are STILL a part of presentation layer, and that it should not deal with anything other than HttpContext related operations + calling business logic service classes.

    I arrange my classes as:
    Presentation (MVC) -> Logic Services (Simple classes) -> Data Access (Context wrapped in "repositories").
    So I can't quite imagine whether to use EF or not would have any implication on asp.net MVC.

  2. For me, Data Access returns data in DTO, e.g.

    public List GetAllFoos()

    Whether that method string concatenate from a xml, etc or do a simple Context.Foos.ToList() is irrelevant to the rest of the application. All I care is Data Access do NOT return me a DataSet with string matching for columns. Those stay in DAL.

  3. See point 1 and 2. My repositories have CRUD methods on it. How it's done is irrelevant to the rest of application. Take one of the most basic interface to my repositories classes:

    public interface IFooRepository { void Save(Foo foo) Foo Get(int id) void Create(Foo foo) void Delete(int id) }

    One point not mentioned yet, DI is also crucial. The concrete implimentation "FooRepository" may choose to request dependencies such as Web services, context classes, etc. Those are, however, again, completely irrelevant to the caller who depends on the interface.

  4. If you still require an example after the 3 points above, drop a comment and I'll whip up something extremely simple using Ado.net.

===========================================================================
To EF or not to EF.

For me, if starting a new project with new schema, I use EF code first.

Fitting new code to old database + old project has no ORM mapping I can reuse = PetaPoco.

===========================================================================
In the context of your project:

The "SQL plugin reporting tool for SQL report developers". "The" sql reporting service? I'm not sure why you need to do anything? Doesn't SSRS already do that? (Enter sql statement/data source, generate form for parameter, etc).

If not I'd question the design decision. IMVHO, the need for users of an application (I don't care if it's "report developer" or w/e) to enter SQL statements is usually stemmed from "architectural astronauts". How do you debug the SQL statement when you enter via GUI as a string? How do you know the tables and the relationships? You either dig into SSMS and come back to gui, or you build complex UI (aka rebuild SSMS).

At the end of day, if you want bazillion reports for gazillion different users, you have to pay for it. I see too many "architectural astronauts" who exposes application to accept SQL statements only to make everyone waste time guessing what should be put into it. No cost saving at all.

Ok, if you must do that, well eh... Good luck. Best bet is to return as a DataTable and dump the rows/columns/data on to the view with nested foreach looping through rows then columns.

Upvotes: 2

Related Questions