Using Stored Procedures with Linq To Sql which have Additional Parameters?

I don't believe you can add the output parameters to any of your LINQ classes because the parameters do not persist in any table in your database But you can handle output parameters in LINQ in the following way Add the stored procedure(s) you whish to call to your . Dbml using the designer Call your stored procedure in your code using (YourDataContext context = new YourDataContext()) { Nullable errNumber = null; String errMessage = null; Nullable errDetailLogID = null; Nullable sqlErrNumber = null; String sqlErrMessage = null; Nullable newRowID = null; Nullable userID = 23; Nullable isActive=true; context. YourAddStoredProcedure(userID, "New Category", isActive, ref errNumber, ref errMessage, ref errDetailLogID, ref sqlErrNumber, ref sqlErrMessage, ref newRowID); }.

I don't believe you can add the output parameters to any of your LINQ classes because the parameters do not persist in any table in your database. But you can handle output parameters in LINQ in the following way. Add the stored procedure(s) you whish to call to your .

Dbml using the designer. Call your stored procedure in your code using (YourDataContext context = new YourDataContext()) { Nullable errNumber = null; String errMessage = null; Nullable errDetailLogID = null; Nullable sqlErrNumber = null; String sqlErrMessage = null; Nullable newRowID = null; Nullable userID = 23; Nullable isActive=true; context. YourAddStoredProcedure(userID, "New Category", isActive, ref errNumber, ref errMessage, ref errDetailLogID, ref sqlErrNumber, ref sqlErrMessage, ref newRowID); }.

1 Very slick. Thanks for the feedback. I am now tinkering with an idea to create a global 'SprocResult' object that will be used on all CUD calls and manage them by hand as you've suggested.

I've created a method on my DataContext (using a partial class) that will hold this call like so: context. CallSproc(SprocResult result, params SqlParameter args); Thanks again. Sometimes, I just need someone to plant me a seed.

:) You've done just that... I'll be sure to update my question with my resolution when I have something working. – Luc Sep 14 '09 at 19:39 1 RESOLVED! Your suggestion helped me fix my problem.

Thank you! Here is what I did: created a SprocArgs class with the "common" error variables as public. So context.

AddCategory(..., ref args. ErrorNumber, ref args. ErrorMessage, etc.).

For each CUD method on my repository I handled them manually one by one. If a sproc failed (ie.(AddCategory(...)! = 0) then I would simply throw new SprocException(args).

The only thing left to do now is to trap property changes for my model objects in order to 'Update()' any dirty model objects on 'Save()'. Thank you! – Luc Sep 14 '09 at 21:08 @Luc: could you possibly put your solution at the end of your question?

Here in a comment, it's really hard to decipher, since you cannot format code snippets........ thanks! – marc_s Sep 14 '09 at 21:34 @marc_s: Done. :) I hope you get value from it.

Also, thanks again @cptScarlet for the tip that led me to my solution. – Luc Sep 14 '09 at 20:10.

I haven' tried it yet, but you can look at this article, where he talks about stored procedures that return output parameters. weblogs.asp.net/scottgu/archive/2007/08/... Basically drag the stored procedure into your LINQ to SQL designer then it should do the work for you.

Thanks, I've already read this. However, it does not solve my particular problem... My problem is more of an issue regarding output parameters that are UNRELATED to the model object itself (for example: errLogID should not be a property of a 'Category' model object IMO). Thanks anyway for reading and providing feedback... – Luc Sep 14 '09 at 19:12 If you just use the dlinq model in your DAO class then you can have a model with just the data you need passed around, and handle just don't inlucde errLogID for example.It sounds more like an architecture problem than a DLINQ issue.

– James Black Sep 14 '09 at 19:24.

I am writing an ASP.NET MVC application and I'm using the Repository concept with Linq To Sql as my data store. Everything is working great in regards to selecting rows from views. And trapping very basic business rule constraints.

However, I'm faced with a problem in my stored procedure mappings for deletes, inserts, and updates. Our DBA has put a lot of work into putting the business logic into all of our stored procedures so that I don't have to worry about it on my end. Sure, I do basic validation, but he manages data integrity and conflicting date constraints, etc... The problem that I'm faced with is that all of the stored procedures (and I mean all) have 5 additional parameters (6 for inserts) that provide information back to me.

The idea is that when something breaks, I can prompt the user with the appropriate information from our database. From the above stored procedure, the first 3 parameters are the only parameters that are used to "Create" the Category record. The remaining parameters are simply used to tell me what happened inside the method.

If a business rule is broken inside the stored procedure, he does NOT use the SQL 'RAISEERROR' keyword when business rules are broken. Instead, he provides information about the error back to me using the OUTPUT parameters. He does this for every single stored procedure in our database even the Updates and Deletes.

All of the 'Get' calls are done using custom views. They have all been tested and the idea was to make my job easier since I don't have to add the business logic to trap all of the various scenarios to ensure data quality. As I said, I'm using Linq To Sql, and I'm now faced with a problem.

The problem is that my "Category" model object simply has 4 properties on it: CategoryID, CategoryName, UserId, and IsActive. When I opened up the designer to started mapping my properties for the insert, I realized that there is really no (easy) way for me to account for the additional parameters unless I add them to my Model object. What is the best way to manage this using Linq to Sql?

I (personally) don't feel that it makes sense to have all of these additional properties added to each model object... For example, the 'Get' should NEVER have errors and I don't want my repository methods to return one type of object for Get calls, but accept another type of object for CUD calls. Update: My Solution! Here is what I did to fix my problem.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.

Using Stored Procedures with Linq To Sql which have Additional Parameters?

Related Questions

While registering a report and a pl/sql block we pass some parameters, for any pl/sql block we pass 2 additional parameters. Can you list them?

Does linq to sql make stored procedures in ms sql 2005?

In Linq to SQL is it better return multiple results sets at once or call the DB using multiple stored procedures?

Is there a bug or limitation in LINQ to SQL which would cause stored procedures to timeout?

How do I Execute SQL Stored Procedures from within another Stored Procedure?

LINQ-to-SQL vs stored procedures?