Using Stored Procedures with Linq To Sql which have Additional Parameters

Question

I have a very big problem and can't seem to find anybody else on the internet that has my problem. I sure hope StackOverflow can help me...

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. Let me explain:

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.

For example:

sp_AddCategory(
    @userID INT,
    @categoryName NVARCHAR(100),
    @isActive BIT,
    @errNumber INT OUTPUT,
    @errMessage NVARCHAR(1000) OUTPUT,
    @errDetailLogID INT OUTPUT,
    @sqlErrNumber INT OUTPUT,
    @sqlErrMessage NVARCHAR(1000) OUTPUT,
    @newRowID INT OUTPUT)

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.

Theoretically what I would LIKE to do is this:

// note: Repository Methods
public void AddCategory(Category category)
{
    _dbContext.Categories.InsertOnSubmit(category);
}

public void Save()
{
    _dbContext.SubmitChanges();
}

And then from my CategoryController class I would simply do the following:

[AcceptVerbs(HttpVerbs.Post)]
public ActionResult Create(FormCollection collection)
{
    var category = new Category();
    try 
    {
        UpdateModel(category); // simple validation here...

        _repository.AddCategory(category);
        _repository.Save(); // should get error here!!

        return RedirectToAction("Index");
    }
    catch
    {
        // manage friendly messages here somehow... (??)
        // ...

        return View(category);
    }
}

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! (Dec. 1, 2009)

Here is what I did to fix my problem. I got rid of my 'Save()' method on all of my repositories. Instead, I added an 'Update()' method to each repository and actually commit the data to the database on each CUD (ie. Create / Update / Delete) call.

I knew that each stored procedure had the same parameters, so I created a class to hold them:

public class MySprocArgs 
{
    private readonly string _methodName;
    public int? Number;
    public string Message;
    public int? ErrorLogId;
    public int? SqlErrorNumber;
    public string SqlErrorMessage;
    public int? NewRowId;

    public MySprocArgs(string methodName)
    {
        if (string.IsNullOrEmpty(methodName))
            throw new ArgumentNullException("methodName");

        _methodName = methodName;
    }

    public string MethodName
    {
        get { return _methodName; }
    }

}

I also created a MySprocException that accepts the MySprocArgs in it's constructor:

public class MySprocException : ApplicationException
{

    private readonly MySprocArgs _args;
    public MySprocException(MySprocArgs args) : base(args.Message)
    {
       _args = args;
    }

    public int? ErrorNumber
    {
        get { return _args.Number; }
    }

    public string ErrorMessage
    {
        get { return _args.Message; }
    }

    public int? ErrorLogId
    {
        get { return _args.ErrorLogId; }
    }

    public int? SqlErrorNumber
    {
        get { return _args.SqlErrorNumber; }
    }

    public string SqlErrorMessage
    {
        get { return _args.SqlErrorMessage; }
    }
}

Now here is where it all comes together... Using the example that I started with in my initial inquiry, here is what the 'AddCategory()' method might look like:

public void AddCategory(Category category)
{
   var args = new MySprocArgs("AddCategory");
   var result = _dbContext.AddWidgetSproc(
                    category.CreatedByUserId,
                    category.Name,
                    category.IsActive,
                    ref args.Number, // <-- Notice use of 'args'
                    ref args.Message,
                    ref args.ErrorLogId,
                    ref args.SqlErrorNumber,
                    ref args.SqlErrorMessage,
                    ref args.NewRowId);

   if (result == -1)
      throw new MySprocException(args);
}

Now from my controller, I simply do the following:

[HandleError(ExceptionType = typeof(MySprocException), View = "SprocError")]
public class MyController : Controller
{
    [AcceptVerbs(HttpVerbs.Post)]
    public ActionResult Create(Category category)
    {
        if (!ModelState.IsValid)
        { 
            // manage friendly messages        
            return View(category);
        }

        _repository.AddCategory(category);
        return RedirectToAction("Index");

    } 
}

The trick to managing the new MySprocException is to simply trap it using the HandleError attribute and redirect the user to a page that understands the MySprocException.

I hope this helps somebody. :)

Answer 1

I haven' tried it yet, but you can look at this article, where he talks about stored procedures that return output parameters.

http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx

Basically drag the stored procedure into your LINQ to SQL designer then it should do the work for you.

Answer 2

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<int> errNumber = null;
    String errMessage = null;
    Nullable<int> errDetailLogID = null;
    Nullable<int> sqlErrNumber = null;
    String sqlErrMessage = null;
    Nullable<int> newRowID = null;
    Nullable<int> userID = 23;
    Nullable<bool> isActive=true;

    context.YourAddStoredProcedure(userID, "New Category", isActive, ref errNumber, ref errMessage, ref errDetailLogID, ref sqlErrNumber, ref sqlErrMessage, ref newRowID);
 }