i try call a stored procedure from my linq code in order to validate a user credentials. I have tried via linq to sql and sql commands and everything works as expected. But when comes to calling a stored procedure via linq, i cannot get the results i expect.
This is my code:
stored procedure:
ALTER PROCEDURE [dbo].[ValidateUser] (@UserName varchar(50), @UserCode varchar(4))
AS
BEGIN
SET NOCOUNT ON;
SELECT ISNULL(FirstName, ''), ISNULL(LastName, '')
FROM Users
WHERE (UserName =@UserName AND UserCode=@UserCode)
END
source:
dbContext = new MyDBDataContext();
var result = dbContext.ValidateUser("Jim", "1234");
if(result != null)
{
//true, so user is validated so set response status to OK
response.Status = ResponseStatus.OK;
//set returned params to response
var paramsReturned = result.FirstOrDefault();
response.FirstName = paramsReturned.Column1;
response.LastName = paramsReturned.Column2;
}
else
{
//false, so set respone to the corresponding error
response.Status = ResponseStatus.ERROR_LOGIN_FAILED;
}
Where, responce is a status that i return.
The Column1 and Column2 fields returned are null, so i cannot get the first name and last name, neither i know whether user is valid or not.
First, please debug the code line by line and check the returned result to ensure the data is returned as expected. Second, LINQ to SQL will automatically generate a type for you to store the returned result, please try that instead. Thanks.
Please mark the replies as answers if they help or unmark if not.
If you have any feedback about my replies, please contact msdnmg@microsoft.com
There is a returned result and i also get the 2 columsn i want (First and Last names), but they are both nulls. When i use the same logic using SQL commands via SQL data reader, the columns are ok.
dkarantonis
Member
135 Points
214 Posts
Linq and stored procedure - cannot get results
Oct 11, 2010 11:46 AM|LINK
Hi to all,
i try call a stored procedure from my linq code in order to validate a user credentials. I have tried via linq to sql and sql commands and everything works as expected. But when comes to calling a stored procedure via linq, i cannot get the results i expect.
This is my code:
stored procedure:
source:
dbContext = new MyDBDataContext(); var result = dbContext.ValidateUser("Jim", "1234"); if(result != null) { //true, so user is validated so set response status to OK response.Status = ResponseStatus.OK; //set returned params to response var paramsReturned = result.FirstOrDefault(); response.FirstName = paramsReturned.Column1; response.LastName = paramsReturned.Column2; } else { //false, so set respone to the corresponding error response.Status = ResponseStatus.ERROR_LOGIN_FAILED; }Where, responce is a status that i return.
The Column1 and Column2 fields returned are null, so i cannot get the first name and last name, neither i know whether user is valid or not.
What is wrong?
thanks in advance
Wencui Qian ...
All-Star
56784 Points
5796 Posts
Microsoft
Re: Linq and stored procedure - cannot get results
Oct 15, 2010 07:43 AM|LINK
First, please debug the code line by line and check the returned result to ensure the data is returned as expected. Second, LINQ to SQL will automatically generate a type for you to store the returned result, please try that instead. Thanks.
If you have any feedback about my replies, please contact msdnmg@microsoft.com
Microsoft One Code Framework
dkarantonis
Member
135 Points
214 Posts
Re: Linq and stored procedure - cannot get results
Oct 15, 2010 08:29 AM|LINK
Of course i have debugged the code many times.
There is a returned result and i also get the 2 columsn i want (First and Last names), but they are both nulls. When i use the same logic using SQL commands via SQL data reader, the columns are ok.
riswadkarhar...
Contributor
2458 Points
561 Posts
Re: Linq and stored procedure - cannot get results
Oct 15, 2010 08:33 AM|LINK
Hi,
Please set the nocount to off in your procedure like following and see if it works:
----------------------------------------------------------
Please mark as answer if the post helped you.
Chandrapraka...
Member
213 Points
91 Posts
Re: Linq and stored procedure - cannot get results
Oct 15, 2010 08:44 AM|LINK
Have you tried after changing logic in SP ,like
in place of isnull(firstname,'') just use isnull(firstname,'0')
may be it can help you ....
dkarantonis
Member
135 Points
214 Posts
Re: Linq and stored procedure - cannot get results
Oct 19, 2010 05:14 PM|LINK
Hi,
yes, i have already done both you suggested.
Anyway, customer is pushing me, so i decided to go with sql commands.
thanks