Using stored procedures with Linq To Sql, which have additional parameters

Question

I have a very big problem and I can not find anyone else on the Internet, I have problems. I'm sure StackOverflow can help me ...

I am writing an ASP.NET MVC application and I am using the Repository concept with Linq To Sql as the data store. Everything works fine with respect to selecting rows from views. And catching the very simple limitations of business rules. However, I ran into a problem in stored procedure mappings for deletion, insertion, and update. Let me explain:

Our database administrator put a lot of effort into putting business logic in all of our stored procedures so that I don’t have to worry about it at the end. Of course, I am doing a basic check, but it manages data integrity and conflicting date restrictions, etc. The problem I ran into is that all stored procedures (and I mean everything) have 5 additional parameters (6 for inserts) that provide me with information. The idea is that when something breaks, I can ask the user for relevant 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)

3 , . , , . -, SQL "RAISEERROR" -. , OUTPUT. . "" . , , , -, , .

, Linq To Sql, . , "" 4 : CategoryID, CategoryName, UserId IsActive. , , , () , Model.

, :

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

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

CategoryController :

[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);
    }
}

Linq to Sql? () , ... , "Get" , , " ", CUD.

: ! (1 2009 .)

, . "()" . "Update()" CUD (.. Create/Update/Delete).

, , :

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; }
    }

}

MySprocException, MySprocArgs :

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; }
    }
}

, ... , , : 'AddCategory()':

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);
} 

:

[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");

    } 
}

MySprocException , HandleError , MySprocException.

, -.:)

Answer 1

, LINQ, .

LINQ .

(), .dbml, .

 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);
 }

Answer 2

, , , .

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

LINQ to SQL, .

Answer 3

dbContext.SubmitChanges(); ENTITY FRAMEWORK. , , 3 .