Chapter 8 – Getting the Feed

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.