ASP.NET MVC calling Stored Procedures

Question

I wanted to pick your minds...

So I've got this View that allows for a small register... and a stored procedure.

I've been seeing these pseudo "repositories" when making calls... to me, it just seems like an extra hole that needs to be drilled in order to make the call. This bring up three qeustions:

1) what's the point of pseudo repositories?

2) what's the best place to make my stored procedure call: within the views controller function or referencing some other function?

3) how do I recieve the stored procedues result (i.e. success of failure)?

Here's my call:

long? tmp = 1234;
LinqToPartyDataContext lq = new LinqToPartyDataContext();
lq.spCreatePersonaParty(ref tmp,
    model.FirstName,
    null,
    null,
    null,
    null,
    null,
    null,
    null,
    model.EmailAddress,
    null,
    null,
    null,
    null,
    null,
    model.ZipCode,
    null,
    null);

How do I get the result?

see my answer (which was before your edit). Are you using L2SQL or EF? Also, why all the null params? You should create overloads there. Makes your code a LOT cleaner. — RPM1984
– RPM1984, Commented Nov 23, 2010 at 0:35
Are you familiar with method overloading? Just create a method which accepts firstname, email, zipcode (what your passing through), which calls another method, passing through those params and null for the rest. the end result is your above code looks a lot cleaner. — RPM1984
– RPM1984, Commented Nov 23, 2010 at 2:46
Oh I see... so it's basically the exact same thing just separated out. I'll probably do that once these issues are resolved. — dcolumbus
– dcolumbus, Commented Nov 23, 2010 at 4:26

RPM1984 · Accepted Answer · 2010-11-23 00:29:04Z

1

1) what's the point of pseudo repositories?

To hide the underlying implementation detail from your UI. E.g:

IFooRepository repo;
var foo = repo.FindSingle(1);

All the UI knows is that it is calling something that will retrieve what is wants. Why should it care about the actual details? All it cares about is getting the results it wants.

Your actual implementation could be a L2SQL repository, Entity Framework, or even a classic ADO.NET implementation.

2) what's the best place to make my stored procedure call: within the views controller function or referencing some other function?

Definetely not from the UI.

Create an interface which exposes the parameters to the stored procedure, which the Controller can call into:

var result = repository.FindSomethingSpecific(param1, param2);
return View(result);

3) how do I recieve the stored procedues result (i.e. success of failure)?

This really depends on your persistence layer (EF/L2SQL/classic ADO).

If your using EF, you can return the result of the SPROC into a POCO, which the UI can access via model binding.

If your using L2SQL (no POCO support), you'll have to manually project into a POCO (left to right copy), and bind to that.

If your using classic ADO.NET, your have to manually traverse the result set and project into a POCO.

answered Nov 23, 2010 at 0:29

RPM1984

73.3k62 gold badges241 silver badges364 bronze badges

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

7 Comments

dcolumbus Over a year ago

I created a data model .dbml and dragged the stored procedure in. That's what I'm calling to update the record. I'm new to all of this so I'm trying to digest each peice as I need it. I appreciate your help.

RPM1984 Over a year ago

Right, so your using Linq to Sql. Means you'll have to do some stitching in your DAL to return a POCO (simple object), in order to bind to that in your UI.

dcolumbus Over a year ago

Maybe you can clear this all up for me... how would you connect to a SQL Server using Entity Framework? That's where I'd like to take this application, but I can't seem to be able to cut through all of the fat to get straight to the meat.

RPM1984 Over a year ago

@dcolumbus - That's really not something that can be answered quickly. Have you read the MSDN documentation for Entity Framework? It's not that difficult - add new item "Entity Data Model", drag some tables on, etc. Very similar to L2SQL (unless you want to go into POCO/custom data context, which i use - but i don't want to confuse you).

dcolumbus Over a year ago

Ultimately I'm just trying to figure out how to call my stored procedure and manage the response. If there's an error, I want to be able to handle that error. I tried wrapping the call within a try block but it didn't work... I'm missing something key.

|

Collectives™ on Stack Overflow

ASP.NET MVC calling Stored Procedures

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related