3

I'm looking at a project where the customer has a legacy database (MS SQL) with a lot of sps (around 250). I need to be able to talk to all of these from my C# project but trying to find a good way to generate as much of the plumbing as possible.

I'm not sure if I should go with an ORM or some code generation tool? Pros and cons? I've looked at entity framework and it's ability to use complex types as strongly typed models but I would rather have a poco model and populate my objects in some way. It seems that miniORMs like ie. PetaPoco could be the way to go?

Anyone that has any tips and thoughts to share on this issue?

Markus Knappen Johansson
  • 1,461
  • 17
  • 19
  • Most ORM's have some method by which you can call stored procedures. – Robert Harvey Jul 16 '12 at 21:16
  • Thanks Robert, i was wondering if there is any way go get rid of the plumbing (write queries and so on) and which ORM that does the job best? I would prefer a lightweight solution EF feels a little bit to heavy. – Markus Knappen Johansson Jul 16 '12 at 21:27
  • You already mentioned PetaPoco. – Robert Harvey Jul 16 '12 at 21:28
  • Yepp PetaPoco is great i've used it for regular ORM-stuff but it laks in it's abstraction of the SPs-parts (you have to write "Execute SpName" as a query which ties the solution to SQL Server (MySQL ie. uses CALL SpName" for calling a sp. Robert, du you have any experience in this field, have to created any project with a simular set up? Whats your "way to go"? – Markus Knappen Johansson Jul 16 '12 at 21:32

2 Answers2

0

You'll need an ORM that does Reverse Stored-Procedure Mapping, like Telerik's OpenAccess ORM.

Telerik OpenAccess ORM provides the ability to import and use stored procedures already existing in the database. They can be mapped to static methods or used internally to replace the default write operations and implement custom logic.

Telerik OpenAccess is now free. I guess they realized they couldn't sell an ORM when there are so many free alternatives like nHibernate available.

If you're looking for a lightweight alternative that can map stored procedures, try Sam Saffron's Dapper. Dapper is what drives Stack Overflow.

https://stackoverflow.com/a/6645870
http://code.google.com/p/dapper-dot-net/

Community
  • 1
  • 1
Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
0

PetaPoco works well with stored procs.

I'm if you wanted to you could write a Stored Proc mapper with a T4 template.

The template that you get with PetaPoco doesn't (at least last time I looked) map stored procs.

You could probably edit the template used here :

Just seen your comment about the way petapoco stored procs are called, thats true... I have only ever used it with MSSQL so possibly an over site from my there.

David McLean
  • 1,462
  • 1
  • 12
  • 27
  • I don't think that the calling-feature have to be abstracted but its a downside, on the other hand I think that its extremely unlikely that my solution will shift data store solution, but if it will happen, rewriting the datalayer of this app will be 0.05% of the work. – Markus Knappen Johansson Jul 16 '12 at 22:15