In the first article in this series I introduced the concept for the WiE Community, a mobile and location aware social networking community.  In last week's article we covered the high level architecture and the design for the WiE Mobile Client.  In this week's article I'll go into the next level of detail and cover the implementation of the SQL Server Compact based local data access layer (WiELocalModel) for the mobile client.

The Implementation of WiELocalModel

The WiELocalModel expects to store and retrieve data from an instance of a SQL Server Compact database.   I won’t include the entire implementation of the WiELocalModel in-line as we plan to eventually provide the entire project for download but I will highlight a couple of the methods to explain the implementation.

First steps first: Creating the SQL Compact Database

For those of you who have never created a SQL Server Compact database from scratch you can use SQL Server Management Studio to create a new instance of a compact database.  You can also use Visual Studio tools to add and modify a database to your project. 

In Management Studio, from the File menu choose Connect Object Explorer…  In the drop down for server type, select SQL Server Compact and in the Database file section choose New Database… and name the database WiELocalDatabase.

Create SQL Compact DB

Once you have connected to the new database, you will find the interface for creating new tables to be similar to what you have been used to with SQL Server database. 

Right click the Tables node and choose New Table…

For the WiELocalModel we expect 3 key tables to exist in the database file, their schema is described below:

·         Location History (tabDeviceLocationHistory): This table holds the location history for the device.

 

Field Name

Type

Description

Allow Null

guidLocationID

UniqueIdentifier

Unique ID for the location record

No

guidMemberID_FK

UniqueIdentifier

Reference to the member that owns the device

No

guidDeviceID_FK

UniqueIdentifier

Reference to the device that reported this location.

No

flLatitude

Float

Latitude

No

flLongitude

Float

Longitude

No

flSpeed

Float

Speed

Yes

flHeading

Float

Heading

Yes

flAltitudeWRTSeaLevel

Float

Altitude

Yes

flAltitudeWRTEllipsod

Float

Altitude

Yes

dtTimeCollected

DateTime

Date and time (in UTC) the position was collected

No

 

·         Member (tabMember): This table holds information about member(s) of the community.  On the local database this is normally limited to 1 record for the member who owns the device.

 

Field Name

Type

Description

Allow Null

guidMemberID

UniqueIdentifier

Unique ID for the member record

No

strFirstName

Nvarchar(100)

Member’s first name

No

strLastName

Nvarchar(100)

Member’s last name

No

strEmail

Nvarchar(100)

Member’s e-mail address

Yes

strPhoneNumber

Nvarchar(20)

Date and time of the most recent GPS / location data capture

No

strUserName

Nvarchar(100)

User name

No

strUserPassword

Nvarchar(100)

User password

No

 

·         Device (tabDevice): This table holds information about the device, including the time it was last started and the most recent location associated with the device.

Field Name

Type

Description

Allow Null

guidDeviceID

UniqueIdentifier

Unique ID for the location record

No

guidMemberID_FK

UniqueIdentifier

Reference to the member that owns this device

Yes

dtClientStarted

DateTime

Date and time the application was most recently started on the device

No

dtClientHeartbeat

DateTime

Date and time the application was most recently able to send a hearbeat to the server

Yes

dtLastLocationDateCollected

DateTime

Date and time of the most recent GPS / location data capture

Yes

flLastLocationLatitude

Float

Latitude of the most recent GPS reading

Yes

flLastLocationLongitude

Float

Longitude of the most recent GPS reading

Yes

In future articles we will add additional tables to the local database including Friends and Points of Interests.

Next Step: Implementing the Data Access Layer to the SQL Compact Database

I like to use ADO.Net prepared statements with named parameters because frankly I’ve been bit too many times trying to build dynamic SQL queries using string concatenations.  Users tend to do crazy things like enter quotes in their names or more maliciously attempt to inject some SQL statements as part of data entry.  Just be safe, leverage all the hard work the ADO.Net team has done and leverage parameterized queries.

A note about SQL Server Compact - What! No stored procedures?

 

I wish I had had a real database engine on that J2ME application I mentioned earlier.  Out of the box J2ME has a simple record store management system called RMS which provides for simple storage and retrieval of binary data (records).  For anything more complex, J2ME RMS required a lot of coding to achieve what a single SQL query can provide with SQL Server Compact.  

 

SQL Server Compact is a great low footprint database engine which provides many of the features you have come to rely from a database engine.  One feature that SQL Server Compact does not provide is support for stored procedures, and I’m a stored procedure kind of guy. 

 

That said, stored procedures are not as critical for an embedded, local database since you have the full power of .Net Compact Framework and ADO.Net to implement your database logic.   In a way, the WiELocalModel and its use of ADO.Net prepared statements provides a stored procedure like approach to implementing the data access layer.

The model’s Initialize() method is called once to initialize the model when the application starts up.  We create all the required prepared statements as part of the initialize phase.  Below is a snippet from the WiELocalModel implementation of the Initialize() method.

. . .

using System.Data.SqlServerCe;

. . .

 

class WiEModelLocal : IWiEModel

{

  private static string SQLCE_DBNAME = "WiELocalDatabase.sdf";

 

  // SQL Compact connection

  private SqlCeConnection m_sqlceConnection;

 

  // Location related prepared statements to Read / Delete and Save Location to the

  // SQL Compact database

  private SqlCeCommand m_sqlceCommandInsertLocation;

  private SqlCeCommand m_sqlceCommandDeleteLocation;

  private SqlCeCommand m_sqlceCommandRetrieveLocation;

  private SqlCeCommand m_sqlceCommandRetrieveLocationsBetween;

  private SqlCeCommand m_sqlceCommandRetrieveLocationBefore;

 

. . .

 

  public void Initialize()

  {

. . .

 

  // Open connection to our local datastor

  // ASSUMPTION: assume that .sdf file is in the same directory as the application assembly.

  m_sqlceConnection = new SqlCeConnection();

  m_sqlceConnection.ConnectionString = ("Data Source =" +  System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\" + SQLCE_DBNAME));

  m_sqlceConnection.Open();

 

. . .

 

   // Create the Retrieve Location Prepared Statement

   m_sqlceCommandRetrieveLocation = new SqlCeCommand();

   m_sqlceCommandRetrieveLocation.Connection = m_sqlceConnection;

   m_sqlceCommandRetrieveLocation.CommandText = "SELECT * FROM tabDeviceLocationHistory WHERE guidLocationID=@guidLocationID";

   sqlceParam = new SqlCeParameter("@guidLocationID", SqlDbType.UniqueIdentifier);

   sqlceParam.IsNullable = false;

   m_sqlceCommandRetrieveLocation.Parameters.Add(sqlceParam);

   m_sqlceCommandRetrieveLocation.Prepare();

 

. . .

 

  }

}

 

The GetLocation(Guid p_guidLocationID) method from the WiELocalModel shows an example of the pattern for using prepared statements.  As was shown above we create the prepared statements in the model’s Initialize() method which is called once to initialize the model at startup and the statements are then used by individual methods to perform database operations.

 

/// <summary>

/// Query the local datastore for the specified LocationID.

/// </summary>

/// <param name="p_guidLocationID">Unique identifier for the location</param>

/// <returns></returns>

public WiELocationDataObject GetLocation(Guid p_guidLocationID)

{

  m_sqlceCommandRetrieveLocation.Parameters["@guidLocationID"].Value = p_guidLocationID;

 

  SqlCeDataReader dataReader = m_sqlceCommandRetrieveLocation.ExecuteReader();

 

  return (GetLocationDataObjectFromDataReader(dataReader));

}

 

 

The GetLocationDataObjectFromDataReader(dataReader) is a helper method that turns the current record from the data reader into a populated instance of a LocationDataObject.  There are similar helper methods for MemberDataObjects and DeviceDataObjects.

private WiELocationDataObject GetLocationDataObjectFromDataReader(SqlCeDataReader p_dataReader)

{

  WiELocationDataObject locationDataObject = null;

 

  if (p_dataReader.Read())

  {

    // We were able to retrieve the location record

    locationDataObject = new WiELocationDataObject();

 

    // Retrieve non-nullable fields

    locationDataObject.LocationID = (System.Guid)p_dataReader["guidLocationID"];

    locationDataObject.DeviceID = (System.Guid)p_dataReader["guidDeviceID_FK"];

    locationDataObject.MemberID = (System.Guid)p_dataReader["guidMemberID_FK"];

    locationDataObject.NumSatellites = (int)p_dataReader["nNumSatellites"];

    locationDataObject.Latitude = (double)p_dataReader["flLatitude"];

    locationDataObject.Longitude = (double)p_dataReader["flLongitude"];

 

    // Retrieve optional fields that could be set to DBNull

    if (DBNull.Value != p_dataReader["flHeading"])

      locationDataObject.Heading = (double)p_dataReader["flHeading"];

    if (DBNull.Value != p_dataReader["flSpeed"])

      locationDataObject.Speed = (double)p_dataReader["flSpeed"];

    if (DBNull.Value != p_dataReader["dtTimeCollected"])

      locationDataObject.DateCollected = (DateTime)p_dataReader["dtTimeCollected"];

    if (DBNull.Value != p_dataReader["flAltitudeWRTEllipsoid"])

      locationDataObject.AltitudeWRTEllipsoid = (double)p_dataReader["flAltitudeWRTEllipsoid"];

    if (DBNull.Value != p_dataReader["flAltitudeWRTSeaLevel"])

      locationDataObject.AltitudeWRTSeaLevel = (double)p_dataReader["flAltitudeWRTSeaLevel"];

   }

 

   return (locationDataObject);

}

That pretty much covers the implementation pattern for the local model (SQL Server Compact based implementation).  In the next article I will cover the implementation of the WiERemoteModelSSDSForMobile which provides for the storage of WiE related data objects to the SQL Server Cloud Storage offering called SQL Server Data Services (SSDS).   The WiEModelWithLocalCache will use the WiELocalModel (this week's article) and the WiERemoteModelSSDSForMobile to provide the WiE Mobile Client the ability to collect data and synchronize that data for the occasionally connected scenario that is typical with mobile applications.

As a bit of a preview of how this all comes together, here is a (very rough) pass at a simple Virtual Earth client that plots a selected friend's location and location history:

Preview of Virtual Earth Client 

See larger image... 

Olivier