I'm trying to decide which is the best way to call a stored procedure.
I'm new to ASP.NET MVC and I've been reading a lot about Linq to SQL and Entity Framework, as well as the Repository Pattern. To be honest, I'm having a hard time understanding the real differences between L2S and EF… but I want to make sure that what I'm building within my application is right.
For right now, I need to properly call stored procedures to: a) save some user information and get a response and, b) grab some inforation for a catalog of products.
So far, I've created a Linq to SQL .dbml file, selected the sotred procedure from the Server Explorer and dragged that instance into the .dbml. I'm currently calling the Stored Procedure like so:
MyLinqModel _db = new MyLinqModel();
_db.MyStoredProcedure(args);
I know there's got to be more involved… plus I'm doing this within my controller, which I understand to be not a good practice.
Can someone recognize what my issues are here?
Best Answer
LINQ and EF are probably overkill if all you're trying to do is call a stored proc.
I use Enterprise Library, but ADO.NET will also work fine.
See this tutorial.
Briefly (shamelessly copied-and-pasted from the referenced article):
Update
I missed the part where you said that you were doing this in your controller.
No, that's not the right way to do this.
Your controller should really only be involved with orchestrating view construction. Create a separate class library, called "Data Access Layer" or something less generic, and create a class that handles calling your stored procs, creating objects from the results, etc. There are many opinions on how this should be handled, but perhaps the most common is:
MSDN has a decent tutorial on the topic.