Stored Procedures

The database includes six stored procedures, as described here:

• AddPeer creates a peer record for a newly registered user.

• CreateSession inserts a new session record when an existing user logs in.

• DeleteSession removes a session record.

• GetPeers retrieves a list of all the peers who are currently logged in. In a large system, this would be adapted so that it retrieved a list of logged-in users according to a contact list.

• GetPeerAndSessionInfo retrieves information about the peer, and the current contact information if the peer is logged in. This could be split into two stored procedures, but for simplicity's sake it's handled in one.

• RefreshSession updates the expiration date on the current session record. Peer sessions that haven't been updated within three minutes will be ignored (and optionally, can be removed).

The stored procedure code is similar to what you saw in Chapter 8. For example, AddPeer wraps a simple SQL Insert statement:

CREATE Procedure AddPeer
(
    @EmailAddress nvarchar(50)
)
AS

INSERT INTO Peers ( EmailAddress ) VALUES ( @EmailAddress )
GO

The CreateSession stored procedure is more sophisticated. Before adding the session record, it removes any existing session records with the same e-mail address. It also performs a lookup to map the supplied peer e-mail address to the unique identity number the database uses in the Sessions table.

CREATE Procedure CreateSession
(
    @ID             uniqueidentifier,
    @EmailAddress  nvarchar(50),
    @ObjRef         varbinary(1500)
)
AS

DECLARE @PeerID int
SELECT @PeerID = ID FROM Peers WHERE EmailAddress = @EmailAddress

DELETE FROM Sessions WHERE PeerID=@PeerID

INSERT INTO Sessions (ID, PeerID, LastUpdate, ObjRef)

  VALUES (@ID, @PeerID, GETDATE(), @ObjRef)
GO

Note that the unique identifier is generated by the server rather than the database engine, and as such it doesn't need to be returned using a parameter.

The RefreshSession stored procedure simply updates the LastUpdate field.

CREATE Procedure RefreshSession
(
    @ID uniqueidentifier
)
AS
UPDATE Sessions SET LastUpdate=GETDATE() WHERE [ID]=@ID
GO

The DeleteSession stored procedure removes the session based on its unique identifier.

CREATE Procedure DeleteSession
(
    @ID uniqueidentifier
)
AS

DELETE FROM Sessions WHERE ID = @ID
GO

The GetPeers method returns the e-mail addresses for all the currently logged-on users by joining the Sessions and Peers tables. At the same time, any entry that hasn't been updated in more than three minutes is ignored.

CREATE PROCEDURE GetPeers AS

SELECT EmailAddress FROM Sessions INNER JOIN Peers ON Peers.ID = PeerID
  WHERE DATEDIFF(MINUTE, LastUpdate, GETDATE()) < 3
GO

Optionally, you could remove old sessions records, either by using a separate long-running application that periodically scans the database, or by adding the necessary code to a frequently invoked stored procedure such as CreateSession or DeleteSession. However, this additional step probably isn't necessary. The CreateSession stored procedure code already ensures that all of a user's old records are removed every time the user logs in.

Finally, the GetPeerAndSessionInfo stored procedure returns peer and session information. A left join is used to ensure that peer information is returned, even if the user isn't currently logged in and doesn't have a session record.

CREATE PROCEDURE GetPeerAndSessionInfo
(
    @EmailAddress nvarchar(50)
)
AS
SELECT TOP 1 * FROM Peers Left JOIN Sessions ON PeerID = Peers.ID
  WHERE EmailAddress = @EmailAddress ORDER BY LastUpdate DESC
GO

If the system is working correctly, this will return only a single record. But just to be defensive, this stored procedure returns only the first record that was most recently updated by using the TOP 1 and ORDER BY clauses.

The Database Class

As in Chapter 8, a class named P2PDatabase is used to wrap the stored procedure code with the required ADO.NET commands. Information about a peer and its current session can be packaged into a PeerInfo object, as shown here:

Public Class PeerInfo

    Public ID As Integer
    Public EmailAddress As String
    Public PublicKeyXml As String
    Public ObjRef() As Byte

End Class

The P2PDatabase includes a method for each stored procedure. You won't see the full code here, but it's provided online with the Chapter 11 examples, and it's quite straightforward. However, there are two fine points worth identifying.

First of all, note how the CreateSession() method takes special care to validate that the ObjRef is less than the 1,500 bytes allocated for storage in the database. Because it's presumably impossible for an ObjRef to be larger, the code makes this check using a debug assertion. Alternatively, you might want to throw a custom error indicating the problem if the byte array is too large.

Public Function CreateSession(ByVal emailAddress As String, _
  ByVal objRef() As Byte) As Guid

    ' Define command and connection.
    Dim SessionID As Guid = Guid.NewGuid()

    Dim con As New SqlConnection(ConnectionString)
    Dim cmd As New SqlCommand("CreateSession", con)
    cmd.CommandType = CommandType.StoredProcedure
    ' Add parameters.
    Dim param As SqlParameter
    param = cmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier)
    param.Value = SessionID
    param = cmd.Parameters.Add("@EmailAddress", SqlDbType.NVarChar, 300)
    param.Value = emailAddress

    Debug.Assert(objRef.Length < 1500)

    param = cmd.Parameters.Add("@ObjRef", SqlDbType.VarBinary, 1500)
    param.Value = objRef

    Try
        con.Open()
        cmd.ExecuteNonQuery()
    Finally
        con.Close()
    End Try

    Return SessionID

End Function

The GetPeerInfo() method also requires special care. It calls the GetPeerAnd SessionInfo stored procedure, which may or may not return session information. To prevent a possible null reference exception, the code must check if session data is returned before trying to assign it to the properties of a PeerInfo object.

Public Function GetPeerInfo(ByVal email As String) As PeerInfo

    ' Define command and connection.
    Dim con As New SqlConnection(ConnectionString)
    Dim cmd As New SqlCommand("GetPeerAndSessionInfo", con)
    cmd.CommandType = CommandType.StoredProcedure

    ' Add parameters.
    Dim param As SqlParameter
    param = cmd.Parameters.Add("@EmailAddress", SqlDbType.VarChar, 50)
    param.Value = email
    Dim Peer As New PeerInfo()
    Try
        con.Open()
        Dim r As SqlDataReader = cmd.ExecuteReader()
        r.Read()
        Peer.EmailAddress = r("EmailAddress")
        Peer.ID = r("ID")
        If Not (r("ObjRef") Is DBNull.Value) Then
            Peer.ObjRef = r("ObjRef")
        End If
      Finally
        con.Close()
    End Try
    Return Peer

End Function