SQL Server and Linq-to-SQL Stored Procedure

Question

I am working on a project right now, when we need to inject all communications into the database using stored procedures. We use Linq-to-SQL, so all database objects are in place. My question is how can you write a stored procedure that returns an object, if possible.

For example, we check the user when he logs in with the username and transfers it, and then performs the check, but how can I write to return the stored procedure to return the Employee object?

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

Answer 1

What does your GetLogin procedure GetLogin and / or return?

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

In this case, your code will look something like this:

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

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

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

You can change the properties:

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

or you can delete it:

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

The object you return is a Linq-to-SQL object, like any other!