SQL Server stored procedure and Linq-to-SQL

Question

I'm working on a project right now where we have to implement all communications to the database via stored procedures. We are using Linq-to-SQL, so all the database entities are in place. My question is how you could write a stored procedure that returns an entity, if this is possible.

Example, we validate a user when he's logging in with username and pass, and then do a check, but how should I write to get the stored procedure to return an Employee entity?

CREATE PROCEDURE GetLogin (
    @UserName NVARCHAR(50),
    @Password NVARCHAR(50))

Answer 1

What does your GetLogin procedure do and/or return??

When you add a stored procedure to a Linq-to-SQL data context, you can define a "return type" in the properties window - if you're sure your stored procedure returns all the properties that make up a User, then you could set the return type of that stored proc to User and then this should work.

In that case, your code would look something like this:

User found = ctx.GetLogin("YourUserName", "TOP$SECRET").SingleOrDefault();

If the user was found in the database, found will contain that user - otherwise, it'll be NULL.

Update: once you have such a User object, of course, you can use it just like any other Linq-to-SQL object !

You can modify properties:

found.UserName = "New User Name";
ctx.SubmitChanges();

or you can delete it:

ctx.Users.DeleteOnSubmit(found);
ctx.SubmitChanges();

The object you're getting back is a Linq-to-SQL object just like any other!