In this article we will create one more SP but this will be returning one output parameter. We will map this SP to LIQ file and consume it using LIQ. So let’s crate one stored procedure which will take two parameters as input which is username and password of the user and if the user credentials matches, we will return one output parameter.
This output parameter will tell us weather user is authenticated or not. If he is authenticated we will redirect our users to links pages where he can see all the links that he has stored in his Favorite list. First of all let’s crate stored procedure in our database.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE fm_AuthenticateUser
@Username varchar(150),
@Password varchar(16),
@Uid int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
Declare @Pass varchar(16)
Declare @id int
SELECT @Pass = Password, @id = UserId
FROM dbo.lnkUsers
WHERE Username = @Username
IF @Password <> @Pass
BEGIN
SET @Uid = 0
END
ELSE
BEGIN
SET @Uid = @id
END
END
GO
As you see if the user credentials are not matching we will set the output parameter to zero. If they are matches we will return the user id of that user. On the basis of this return parameter we will redirect our user to appropriate page.
Once this is done go to your and map this stored procedure to your .net application similar way we did before. If you have not read previous articles please read them here. As I mentioned this is a special case where the stored procedure is returning us some parameter which we need to handle in LIQ. Before we go ahead and handle that let’s first create a login page where user can enter his/her credentials. For now I will just add this stuff to my default page as that will be the start page of our application. Make your page look like as below.
Now let’s add this code into the click event of your login button.
protected void btnLogin_Click(object sender, EventArgs e)
{
LinkManagerDataContext lm = new LinkManagerDataContext();
Nullable<int> userid = null;
lm.fm_AuthenticateUser(txtUsername.Text.ToString(), txtPassword.Text.ToString(),ref userid);
if (userid != 0)
{
Session["uid"] = userid;
Response.Redirect("links.aspx");
}
else
{
lblREsult.Text = "Authentication Failed";
}
}
If you see here we defined our userid integer is because LIQ to SQL maps output parameters from SP as reference type (using ref keyword)
Once the user is authenticated we will store his userid into session and foreword him to the links page where we will retrieve the user’s favorite links using the userid we have in session.
For this write code below in the page load event of the links page.
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (Session["uid"] != null)
{
LinkManagerDataContext lm = new LinkManagerDataContext();
var links = lm.fm_GetLinks(Convert.ToInt32(Session["uid"]));
grdLinks.DataSource = links;
grdLinks.DataBind();
}
}
}
Go ahead and run the application. Once the credentials are matched you should see some thing like below.
NOTE:
I have written a separate article on how to retrieve data using stored procedure accepting an input parameter before this one so the links page might have some extra controls and its code behind may have some extra code as well.
Watch the gray out area on the above screen shot.
So in this article we understood how we can handle the stored procedure’s output parameters using LIQ to SQL functionality in your application.
Thanks
Raju Jetla
No comments:
Post a Comment