C
HAPTER
8
–
G
ETTING THE
F
EED
Now that we can post a status update and lets grab our feed even if at this stage it will be a feed of
our own status updates due to a lack of friends or stalkees
Now this actually requires some reasonable grunt work with SQL Server to make this happen and it is
a pretty convoluted and inefficient process is LINQ.
Therefore we are going to use a stored procedure to query the database and hand the results back to
LI
NQ.
A
DDING THE
S
TORED
P
ROCEDURE
Lets first add the stored procedure to SQL Server.
Open SQL Server Management Studio and then right click
stalkbook
database node and choose
New
Query
from the context menu.
Paste the following code into the new Query window
and click the
Execute
button.
USE
[StalkBook]
GO
SET
ANSI_NULLS
ON
GO
SET
QUOTED_IDENTIFIER
ON
GO
CREATE
PROCEDURE
[dbo]
.
[sp
GetStatusUpdates]
--
Add the parameters for the stored procedure here
@UserID
uniqueidentifier
,
@Page
int
,
@PageSize
int
AS
BEGIN
SET
NOCOUNT
ON
;
--
Determine the first record and last record
DECLARE
@FirstRec
int
,
@LastRec
int
SELECT
@FirstRec
=
(
@Page
-
1
)
*
@PageSize
SELECT
@LastRec
=
(
@Page
*
@PageSize
+
1
);
--
Insert statements for procedure here
WITH
TempResult
AS
(
SELECT
ROW_NUMBER
()
OVER
(
ORDER
BY
StatusUpdates
.
ID
DESC
)
as
RowNum
,
StatusUpdates
.
id
,
StatusUpdates
.
stalker
,
StatusUpdates
.
posted
,
StatusUpdates
.
statusUpdateText
,
Stalkings
.
UserId
FROM
dbo
.
StatusUpdate
AS
StatusUpdates
INNER
JOIN
(
SELECT
DISTINCT
dbo
.
aspnet_Users
.
UserId
FROM
dbo
.
aspnet_Users
INNER
JOIN
dbo
.
Stalking
ON
dbo
.
Stalking
.
stalker
=
dbo
.
aspnet_Users
.
UserId
OR
dbo
.
Stalking
.
stalkee
=
dbo
.
aspnet_Users
.
UserId
WHERE
(
dbo
.
Stalking
.
stalker
=
@UserID
)
OR
(
dbo
.
Stalking
.
stalkee
=
@UserID
))
AS
Stalkings
ON
StatusUpdates
.
stalker
=
Stalkings
.
UserId
)
SELECT
top
(
@LastRec
-
1
)
*
FROM
TempResult
WHERE
RowNum
>
@FirstRec
AND
RowNum
<
@LastRec
SET
NOCOUNT
OFF
END
GO
I am not going to go over the workings of this statement but will explain that it selects the Status
Updates of users whom you stalk. It also “paginates” I allows you to say that you want just the first 10
records, or records 11
-
20 etc.
Now that we have t
he object in the database, we
need to add it to our Entity Model.
The way this works is that it is added as a feature of
the Entity Context class, and can be called like a
method. Therefore we need to add it to there.
Open Visual Studio and then your
Stalk
Book.edmx
file.
Now, right click an empty area of the Entity Model
canvas, and choose
Update Model from Database
from the context menu. Now, expand the
Stored
Procedures
node. You will see a number of stored
procedures associated with the ASP.NET
membersh
ip providers, but some way down the list
you will find your
s
p
GetStatusUpdates
procedure.
Select it and click
Finish.
Now, our Stored Procedure has been added to the
Entity Class, but we still need to do a tiny bit of extra
configuration to allow its use.
Again, right click an empty area of the Entity Model
canvas and choose
Model Browser
from the context
menu.
Expand the nodes until you get to
Stored Procedures
.
Right click on your
spGetStatusUpdates
procedure and
choose
Add Function Import
from the conte
xt menu.
This is where we actaully add the function to the
entity class.
Change the
Function Import Name
to just
GetStatusUpdates
.
Now, changes the
Returns a Collection Of
section to
Entities
and select
StatusUpdate
from the drop down
list.
Now click the
O
K
button.
A
DDING THE
M
ANAGEMENT
C
LASS
M
ETHOD
Now that we have a method in our Entity Class we can call, we can add a function to our
clsManageStatusUpdates
class. Thankfully we can now leverage on a lot of the hard work that has
gone on in previous chapte
rs.
There is nothing that we haven’t encountered before in this method. You will note that we are
passing in the UserID that we want list of StatusUpdates for, and also the “page” of the result set that
we want. Basically
the stored procedure is cal
led and
assigned to a variable which is a
List<StalkBookModel.StatusUpdate>
.
We
then call a second method that turns the list of Entity StatusUpdates into DTO StatusUpdates.
Separating out this second method is useful as we will want to do this again with other m
ethods.
1
public
static
List
<StalkBookDTO.
StatusUpdate
> GetStatusUpdates(
Guid
prStalkerID,
int
prPage)
2
{
3
using
(StalkBookModel.
StalkBookEntities
db =
new
StalkBookModel.
StalkBookEntities
())
4
{
5
List
<StalkBookModel.
StatusUpdate
>
lcEntityStatusUpdates = db.GetStatusUpdates(prStalkerID,
prPage, PAGE_SIZE).ToList();
6
return
GetListStatusUpdatesDTO(lcEntityStatusUpdates);
7
}
8
}
9
10
public
static
List
<StalkBookDTO.
StatusUpdate
>
GetListStatusUpdatesDTO(
List
<
StalkBookModel.
StatusUpdate
> prStatusUpdates)
11
{
12
List
<StalkBookDTO.
StatusUpdate
> lcDTOStatusUpdates =
new
List
<StalkBookDTO.
StatusUpdate
>();
13
foreach
(StalkBookModel.
StatusUpdate
lcCurrentUpdate
in
prStatusUpdates)
14
{
15
lcDTOStatusUpdates.Add(GetStatusUpdateDTO(lcCurrentUpdate));
16
}
17
return
lcDTOStatusUpdates;
18
}
Now we can add a method to our
StatusUpdateService
to return the list of StatusUpdates to the
client.
Open your service file and add
the following method:
1
[
OperationContract
]
2
[
WebInvoke
(Method =
"POST"
, ResponseFormat =
WebMessageFormat
.Xml)]
3
public
System.Collections.Generic
.
L
ist
<
StalkBookDTO.
StatusUpdate
> GetStatusUpdates(
int
prPage)
4
{
5
WebOperationContext
.Current.OutgoingResponse.ContentType =
"text/xml"
;
6
return
clsManageStatusUpdates
.GetStatusUpdates((
Guid
)
Membership
.GetUser().ProviderUserKey,
prPage);
7
}
Now we can add some code to our interface to get the items and display them.
Open your
feed.aspx
page and insert the following into your Content Region with the
ContentPlaceHolderID
of “head”.
1
<
script
type
="text/javascript">
2
$(
function
() {
3
GetFeed();
4
});
5
function
GetFeed() {
6
XMLAJAXCall(
'/StatusUpdateService.svc/GetStatusUpdates'
,
'{"prPage": '
+ Page +
'}'
,
GetStatusUpdatesSuccess, GeneralServiceError);
7
}
8
9
function
GetStatusUpdatesSuccess(ajaxResponse) {
10
var
lcStatusUpdates = $.xml2json(ajaxResponse);
11
if
(lcStatusUpdates.StatusUpdate !=
null
)
12
if
(
typeof
lcStatusUpdates.StatusUpdate.length !=
"undefined"
)
13
UpdateFeed(lcStatusUpdates.StatusUpdate.reverse(),
'#ulTheFeed'
,
false
);
14
else
15
UpdateFeed(lcStatusUpdates.StatusUpdate,
'#ulTheFeed'
,
false
);
16
}
17
</
script
>
Most of this we have seen before, a couple of lines are worth commenting on however.
Line 6 makes use of the
Page
variable declared in the
stalkbook
-
shared
-
functions.js
script, this
variable will be updated will be updated later on in the tutorial.
Line 11 tests to make sure there were some
StatusUpdates
returned.
Line 12 is a bit of an obscure test to see if we got back a single StatusUpdat
e or many. If there are
many the
length
property when invoked with the
typeof
function
will
return “number”, if it is a single
object the function will return “
undefined”
due to StatusUpdate not being an array.
Now, you should be able to run the page and
see any previous status updates that you have made
automatically added to the feed.
Again, our hard work in the last chapter writing interface functions for getting <html> representations
of StatusUpdates has paid off as we are leveraging those functions.
M
AKING
O
UR
F
EED
P
AGE
P
OLL FOR
N
EW
U
PDATES
Now that we can prepopulate our page when the user first visits it, it would be nice to have it
automatically check for any new updates.
To do this, add a new stored procedure to the database:
USE
[StalkBook]
GO
SE
T
ANSI_NULLS
ON
GO
SET
QUOTED_IDENTIFIER
ON
GO
CREATE
PROCEDURE
[dbo]
.
[spGetStatusUpdatesSince]
@UserID
uniqueidentifier
,
@LastUpdate
int
AS
BEGIN
SET
NOCOUNT
ON
;
SELECT
StatusUpdates
.
id
,
StatusUpdates
.
stalker
,
StatusUpdates
.
posted
,
StatusUpdates
.
statusUpdateText
,
Stalkings
.
UserId
FROM
dbo
.
StatusUpdate
AS
StatusUpdates
INNER
JOIN
(
SELECT
DISTINCT
dbo
.
aspnet_Users
.
UserId
FROM
dbo
.
aspnet_Users
INNER
JOIN
dbo
.
Stalking
ON
dbo
.
Stalking
.
stalker
=
dbo
.
aspnet_Users
.
UserId
OR
dbo
.
Stalking
.
stalkee
=
dbo
.
aspnet_Users
.
UserId
WHERE
(
dbo
.
Stalking
.
stalker
=
@UserID
)
OR
(
dbo
.
Stalking
.
stalkee
=
@UserID
))
AS
Stalkings
ON
StatusUpdates
.
stalker
=
Stalkings
.
UserId
WHERE
StatusUpdates
.
id
>
@LastUpdate
SET
NOCOUNT
OFF
END
GO
Then add it to your
EntityModel
in the same way that we did above.
Now add a new function to your
clsManageStatusUpdates
class.
1
public
static
List
<StalkBookDTO.
StatusUpdate
> GetStatusUpdatesSince(
Guid
prStalkerID,
int
prLastUpdate)
2
{
3
using
(StalkBookModel.
StalkBookEntities
db =
new
StalkBookModel.
StalkBookEntities
())
4
{
5
List
<StalkBookModel.
StatusUpdate
> lcEntityStatusUpdates =
db.GetStatusUpdatesSince(prStalkerID, prLastUpdate).ToList();
6
return
GetListStatusUpdatesDTO(lcEntityStatusUpdates);
7
}
8
}
Finally add a method to your StatusUpdateService:
1
[
OperationContract
]
2
[
WebInvoke
(Method =
"POST"
, ResponseFormat =
WebMessageFormat
.Xml)]
3
public
System.Collections.Generic.
List
<
StalkbBookDTO.
StatusUpdate
> GetStatusUpdatesSince(
int
prLastUpdate)
4
{
5
WebOperationContext
.Current.OutgoingResponse.ContentType =
"text/xml"
;
6
return
clsManageStatusUpdates
.GetStatusUpdatesSince((
Guid
)
Membership
.GetUser().ProviderUserKey,prLastUpdate);
7
}
Now we can make this kick off the polling process, jump into your
feed.aspx
page and make the
highlighted changes.
1
function
GetStatusUpdatesSuccess(ajaxResponse) {
2
var
lcStatusUpdates = $.xml2json(ajaxResponse);
3
if
(lcStatusUpdates.StatusUpdate !=
null
)
4
if
(
typeof
lcStatusUpdates.StatusUpdate.length !=
"undefined"
)
5
UpdateFeed(lcStatusUpdates.StatusUpdate.reverse(),
'#ulTheFeed'
,
false
);
6
else
7
UpdateFeed(lcStatusUpdates.StatusUpdate,
'#ulTheFeed'
,
false
);
8
setTimeout(PollService, 15000);
9
}
10
11
function
PollService() {
12
XMLAJAXCall(
'/StatusUpdateService.svc/GetStatusUpdatesSince'
,
'{"prLastUpdate": '
+
LastStatusUpdate +
'}'
, GetStatusUpdatesSuccess, GeneralServiceError);
13
}
This works by waiting for the first request for a page updates to be dealt with then uses JavaScripts
setTimeout
function to wait 15 seconds before calling the
PollService
function. This function simply
makes the AJAX call to the service and returns the response back to the same function we used earlier
for dealing with status updates.
Now the only way we have to test this at the moment is by having loading our feed page an
d then
manually adding a new record to the database. If we add our own Status Update, it will be
automatically pulled down by our method, so load the
feed.aspx
page and insert a new row into the
StatusUpdate
table in SQL Server management studio. Be carefu
l to use the
UserID
of the user you
are currently logged on as then switch back to the browser and watch the new update come in.
Enter the password to open this PDF file:
File name:
-
File size:
-
Title:
-
Author:
-
Subject:
-
Keywords:
-
Creation Date:
-
Modification Date:
-
Creator:
-
PDF Producer:
-
PDF Version:
-
Page Count:
-
Preparing document for printing…
0%
Σχόλια 0
Συνδεθείτε για να κοινοποιήσετε σχόλιο