Writing Mobile Code Essential Software Engineering for Building Mobile Applications [Electronic resources] نسخه متنی

.NET Compact Framework Specific: ADO.NET

ADO.NET is powerful and multilayered programming model for working with relational data of all kinds. ADO.NET is available on desktops and servers as part of the .NET Framework and on devices as part of the .NET Compact Framework. The .NET Compact Framework's support for ADO.NET is a subset of the desktop and server programming model.

A key innovation in the ADO.NET data model for servers, desktops, and devices is that the ADO.NET DataSet itself is completely separated from the source of the data. Once data is in an ADO.NET DataSet, it can be serialized as XML and stored to a local file or it can be sent over a network to a server, desktop, or mobile device. No persistent connection is held between the ADO.NET DataSet and the databases where the data was acquired from; this is great for server scalability because persistent connections are detrimental to server scalability.

The Very Basics of ADO.NET DataSets

ADO.NET offers many powerful concepts that can at first seem complicated and daunting to developers coming from other data access models. In reality, ADO.NET is quite easy to use, but it requires the developer to think in its model and this means letting go of the concept of the data cursor as the central mechanism for working with data.

To gain a better understanding, it is worth contrasting ADO.NET with its predecessor technology, ADO. As its name implies, an ADO.NET DataSet is closer to the mathematical idea of a "set of data" than it is to the traditional ADO idea of a record set that represents rows of records in a table and provides a cursor to iterate over them. ADO.NET DataSets are "cursorless," meaning that there is no concept of having a current record and a cursor that switches the context between records. In an ADO.NET DataSet all the records simply exist in a set and can be accessed nonsequentially without the need to cursor between current records. DataSets are also not table specific; an ADO.NET DataSet can contain any number of tables of data as well as information about the relations between tables. Old-style ADO RecordSet objects allow the iteration of one table of information, whereas ADO.NET DataSet objects allow the exploration of one or more tables of data.

The ADO.NET DataView and DataTable objects exist to bridge this "set-based" approach with the row-based model of working with data. A DataSet can contain any number of DataTable objects. A data table is basically an array of objects similar to a table in a database. DataViews are objects that present filters and sorts on top of DataTable objects, allowing the contents of a DataSet to be distilled down to the data that is immediately interesting to your application. DataView objects can also provide a sorted view on the data so that it is ordered in the most convenient manner for your application's use and display. Any number of DataView objects can be associated with a DataTable, each with its own sorting and filtering criteria to supply a custom view onto the data.

The desktop and server .NET Framework supports both "untyped" and "typed" DataSets. The .NET Compact Framework specifically only supports "untyped DataSets." This sounds limiting, but in reality is not because typed DataSets are just classes that are built on top of untyped DataSets that bind strongly typed field names onto the underlying untyped DataSet elements. Because a typed DataSet is built on top of an untyped DataSet class, it actually represents a convenient-to-use but slightly slower abstraction. Applications that use untyped DataSets properly by looking up and caching the DataColumn objects of the fields they use (as opposed to looking up the fields by name every time they are used) will get as good or better performance than developers using typed DataSets.

A Very Brief Example of Using DataSets, DataTables, and XML

An example is useful in showing some of the basics of working with ADO.NET DataSets. The .NET Framework documentation offers a comprehensive description of ADO.NET and DataSets. This example only shows the very basics of creating and using a DataSet to set the context for discussing the use of ADO.NET on mobile devices.

The code in Listing 14.1 enables you to build the application shown in Figure 14.1. Do the following to build the application:

Listing 14.1. Basic Creation and Use of an ADO.NET DataSet

//The DataSet we are going to load
System.Data.DataSet m_myDataSet;
//Constants we will use
const string FILE_EMPTY_DATASET = "EmptyDataSet.xml";
const string FILE_1TABLE_DATASET = "1TableDataSet.xml";
const string dividerLine = "-----------------------------\r\n";
const string nextLine = "\r\n";
//----------------------------------------------------------
//Load the contents of a file and append it to the text
//in textBox1
//----------------------------------------------------------
private void addFileContentsToTextBox(string fileName)
{
//Open the file and read in its contents
System.IO.StreamReader myStreamReader;
myStreamReader = System.IO.File.OpenText(fileName);
string fileText = myStreamReader.ReadToEnd();
//Close the file
myStreamReader.Close();
//Append the contents to the text in the text box
textBox1.Text = textBox1.Text +
dividerLine+ "FILE: '" + fileName + "'" + nextLine +
dividerLine + fileText + nextLine;
}
//----------------------------------------------------------
//1. Creates a dataset,
//    persists dataset out as XML,
//    displays results in text box
//
//2. Adds a data table to the dataset,
//    adds two typed columns to the data table,
//    adds to rows to the data table
//    persists dataset out as XML,
//    displays results in text box
//----------------------------------------------------------
private void button1_Click(object sender, System.EventArgs e)
{
//Clear the contents of the text box
textBox1.Text = ";
//===========================================
//1. Create a new dataset
//===========================================
m_myDataSet = new System.Data.DataSet("HelloWorld-DataSet");
//Write out the ADO.NET DataSet contents as XML and show
//the file in the textbox
m_myDataSet.WriteXml(FILE_EMPTY_DATASET);
addFileContentsToTextBox(FILE_EMPTY_DATASET);
//===========================================
//2. Add a data table to the ADO.NET dataset
//   and add 2 rows of data to the data table
//===========================================
System.Data.DataTable myTestTable;
myTestTable = m_myDataSet.Tables.Add("TestTable");
//------------------------------------
//Add 2 columns to the Table
//------------------------------------
//Add a date column to the DataTable in the DataSet
myTestTable.Columns.Add("TestColumn0",
typeof(System.DateTime));
//Add a string colum to the DataTable in the DataSet
myTestTable.Columns.Add("TestColumn1", typeof(string));
//------------------------------------
//Add data rows to the Table
//------------------------------------
//Add a row of data to the data table
object[] rowOfData;
rowOfData = new object[2];
//Column 0 is a date type
rowOfData[0] = System.DateTime.Today;
//Column 1 is a string type
rowOfData[1] = "a string of data today";
myTestTable.Rows.Add(rowOfData);
//Add a 2nd row of data to the data table
object[] rowOfData2;
rowOfData2 = new object[2];
//Column 0 is a date type
rowOfData2[0] = System.DateTime.Today.AddDays(1);
//Column 1 is a string type
rowOfData2[1] = "tomorrow's string";
myTestTable.Rows.Add(rowOfData2);
//Write out the ADO.NET DataSet contents as XML and show
//the file in the textbox
m_myDataSet.WriteXml(FILE_1TABLE_DATASET);
addFileContentsToTextBox(FILE_1TABLE_DATASET);
} //End function

Tracking Changes in Data

ADO.NET DataSet objects automatically keep track of changes made to the data in them, including the creation, deletion, and change of rows of data in data tables. These changes can then be accepted or rejected and the accepted changes committed to a database when appropriate. If the source of the data is distributed among several databases, the data can even be updated using distributed transactions.

It is important to understand that ADO.NET DataSet objects do not know anything about the databases where the data permanently resides; for this, DataAdapter classes are used. The purpose of an ADO.NET DataAdapter class is to move data between an ADO.NET DataSet's objects and long-term storage. All ADO.NET DataAdapter classes are custom code; the developer writes whatever logic is required to connect to, get data from, and update data to the data source being worked with. ADO.NET DataSets are essentially small in-memory databases and DataAdapter classes are written to synchronize DataSet data with database data; whether that database is on a device or server is known only to the data adapter's code.

DataAdapter objects can either exist on the client or on the server. If the data adapter is located on a server, a copy of the ADO.NET DataSet's data must be passed from client to server in order to be given to the data adapter there. The transfer of this DataSet from client to server is usually done by serializing the DataSet as XML, sending it to the server, and having the server reconstruct the DataSet from the XML. ADO.NET has built-in support to enable this. This model works well with Web services. Often there is a desire to remove the need for client devices to know about the underlying databases being accessed; this makes for a simpler architecture by centralizing access to the database on the server. In these cases, an ADO.NET DataSet's data is typically passed from a client device to a Web service running on the server. The Web service reloads the XML data into an ADO.NET DataSet and then passes it on to an ADO.NET data adapter, which then makes any necessary updates to the server. It is worth noting that ADO.NET DataSets have built-in efficiency mechanisms such as allowing you to pass only the DataSet data that has changed up to the server (known as a "diffgram"); this eliminates the need to move the whole DataSet's data around when only a few rows have changed.

Two Models for Use with ADO.NET

Because ADO.NET offers a layered approach to working with data, it enables you to choose the right level of programming abstraction appropriate for your mobile device application's needs and performance demands.

ADO.NET High-Level DataSet Centric Approach

At the highest level of data access abstraction, the .NET Compact Framework offers ADO.NET DataSets along with DataTable and DataView objects. Figure 14.2 shows the different logical tiers for this kind of an application. As discussed above, central to this model is the ADO.NET DataSet.

ADO.NET Lower-Level Approach Using Data Connectors

The ADO.NET DataSet offers a powerful model for working with data but this comes at a cost of in-memory overhead for having additional objects created to abstract and manage your application's data and its interrelations. Sometimes this overhead may not be necessary or appropriate; in these cases, a lower-level alternative is worth considering. An alternative to the DataSet centric approach that still utilizes the lower-level abstractions of ADO.NET is shown in Figure 14.3. This model builds a custom data management model on top of the data connection objects that ADO.NET data providers supply. For example, SQL Server supplies a System.Data.SqlClient.SqlConnection class to access its database, and SQL CE supplies a System.Data.SqlServerCe.SqlCeConnection to do the same. These classes are typically used in conjunction with DataAdapter classes to transfer data to and from DataSet objects, but they can also be used on their own to move data into and out of your own data formats. This model can be significantly more memory efficient when the mobile application's data access needs are narrower in scope than the rich general-purpose features offered by the ADO.NET DataSet model. Figure 14.3 schematically shows what this relationship looks like.

In choosing to use a lower-level approach, your application takes explicit responsibility for managing data loaded into memory, tracking any necessary changes, and committing these changes back to long-term storage as appropriate. The lower overhead of working at a lower abstraction level comes at the cost of more development responsibility to design and maintain an efficient data model.

The ADO.NET DataSet programming model is a great way of working with relational data, but it is not appropriate in every context. It is important to understand when and when not to use ADO.NET for your data access needs.

When to Use ADO.NET DataSets

It is appropriate to use ADO.NET DataSets if you have significant in-memory data-manipulation needs or the data needs to be traversed in a complex relational way. Because ADO.NET effectively maintains a small in-memory relational database for you that tracks changes made to the data, it can be a great way to automatically manage a set of highly dynamic data as well as to navigate relationships between pieces of data. ADO.NET DataSet objects are a good choice when (1) the size of the data you need to work with is not too great relative to the mobile device's memory capacity, and (2) the data is of a dynamic nature that requires the tracking and propagation of changes.

ADO.NET DataSet objects allow the transfer of data to and from long-term storage. This transfer is accomplished via one of three ways: (1) using DataAdapter classes that connect to databases via data connection objects, (2) serialization to or from XML files and streams, or (3) custom serialization code that reads data from or places data into the DataSet objects. The following list briefly describes each of these data-transfer methods.

Using DataAdapter Classes to Interact with Databases

The ADO.NET type System.Data.Common.DataAdapter is an abstract class, meaning that it provides a template other database-specific DataAdapter classes derive from but is not a complete implementation that can be used itself. Two data adapters are shipped alongside the .NET Compact Framework:

The SqlDataAdapter
The System.Data.SqlClient.SqlDataAdapter class is used when working with SQL Server databases running on servers.

The SqlCeDataAdapter
The System.Data.SqlServerCe.SqlCeDataAdapter is used when working with SQL CE databases running on the mobile device.

Third-party data adapters can be used to connect to other data sources. You can also author your own DataAdapter class if you have a custom data source you want to connect to.

Regardless of the data adapter chosen, DataAdapter objects work through "Command" objects that are attached to them. Unlike the DataAdapter class, there is no root Command class from which other database-specific Command classes derive; the concept of a "Command" class is just a concept that various data adapters use. SqlDataAdapter objects have SqlCommand objects associated with them; SQL CE SqlCeDataAdapter objects have SqlCECommand objects, and so on. The Command objects in turn have Connection objects associated with them that map the commands to specific database instances; for example, a SqlCommand would use a SqlConnection object to execute commands on a specific SQL Server connection. These Command objects execute the queries or other database commands that need to be performed to get data from or place data into the databases your application is using. The Command objects understand how to perform specific actions on the database they work with and execute SQL commands to select, update, insert, or delete data in them. For example, the SQLDataAdapter class has four properties for these four commands: the properties are named SelectCommand, UpdateCommand, DeleteCommand, and InsertCommand. Each of these properties is of type SqlCommand. Similarly, the SqlCeDataAdapter also has four properties with the same names, but the type of the properties is SQLCeCommand.

To recap: Data adapters broker communication between ADO.NET DataSets and databases. The data adapters typically use Command objects to do this. Command objects are specific to each type of database. Command objects in turn typically are associated with Connection objects that own the connection with a specific server. A Command object is typically the owner of SQL statements such as Select * from Customers, and the Connection objects own the logical connection with the database that gets these commands sent to them.

When you are building .NET desktop and server applications, Visual Studio .NET offers design-time tools to aid you in the configuration of data adapters and their associated Command classes; this simplifies accessing databases considerably. In contrast, creating and using a data adapter with the .NET Compact Framework will require you to manually write code for the DataAdapter and Command objects to configure them to work with your data sources. In the future, automated tools may also support the .NET Compact Framework and the DataAdapter and DataCommand classes for popular databases, but for now you are on your own. Because the desktop and server database tools work by autogenerating source code for applications, it can often be useful to look at the code generated and adapt this code for running on devices; some significant differences will exist but it is a good starting point.

Using XML Files and Streams to Store and Transfer Data

Storing an ADO.NET DataSet object's contents in an XML file can be thought of as "a poor man's database." Your application's data is stored in a text file in a format that can later be reloaded into memory as an ADO.NET DataSet. This is similar to getting a DataSet returned via a Web service request. In contrast to storing data in a database, using an XML text file for storage does not give your application the benefits of rich transactions and data integrity offered by modern databases. Despite this model's limitations for storing large amounts of data or providing transactional updates, it can be useful in storing small amounts of data (for example, 20KB XML files) or moving data up to a server via an XML stream. Think of XML-persisted DataSets as a simple way to package up a small to moderate amount of application state for later reuse.

When writing the contents of a DataSet to an XML file or stream it is important to look at the options offered by the System.Data.XmlWriteMode enumeration. For performance reasons, it is recommended to write out the XML schema (that is, use System.Data.XmlWriteMode.WriteSchema) when persisting DataSets to XML. Writing out the schema of the data along with the data itself can make reloading the XML into a DataSet significantly faster; if the schema is not explicitly persisted with the DataSet it will need to be dynamically inferred when the data is reloaded, and this requires additional work. Listing 14.2 shows a very simple example of DataSet to XML persistence. The sample allows the specification of the WriteSchema parameter.

To run the sample, add the code in Listing 14.2 to the code in Listing 14.1, and add a Button control that calls the writeDataSetToFile function.

Listing 14.2. Using XMLWriteMode When Persisting ADO.NET DataSets

//-------------------------------------------------------------
//This function exists because the .NET Compact Framework does
//not support the overload:
// "public void WriteXml(string, XmlWriteMode);"
//
//as a "public" member (it is private)
//-------------------------------------------------------------
void writeDataSetToFile(System.Data.DataSet ds,
string filename,
System.Data.XmlWriteMode xmlWriteMode)
{
//Create an XML TextWriter to write out our XML
System.Xml.XmlWriter xmlWriter;
xmlWriter = new System.Xml.XmlTextWriter(filename,
System.Text.Encoding.Default);
//NOTE: This overload is not public!
//ds.WriteXml(filename, xmlWriteMode);
//Instead use...
ds.WriteXml(xmlWriter, xmlWriteMode);
xmlWriter.Close(); //It is important to close the file!
}

Using Custom Logic to Get Data into and out of ADO.NET DataSets

As described previously, ADO.NET DataSet objects have built-in support for persisting to XML. However, this XML format is not arbitrary; it is a specific XML schema intended for ADO.NET DataSets. If your application needs to write out to or read in from a different XML schema or any other format, you can do so by writing custom logic. The DataSet model is flexible enough to support any kind of persistence you dream up. Because the ADO.NET DataSet maintains a simple in-memory database independent of storage format, you can persist an ADO.NET DataSet into any format (for example, custom XML, binary, simple text) that is appropriate for your needs. The only caveat to doing this is to make sure you have a good reason for taking on this extra design work.

When writing custom deserialization code to populate a DataSet your application's code will need to programmatically build the tables, columns, and relational definitions and then populate the tables with the data you are importing. Similarly, when custom persisting a DataSet, your application logic will have to iterate through all the tables and persist out the data using whatever mechanism you require. A hybrid model can also be used to read from and write to different data sources; for example, a DataSet might be automatically populated by a SQLDataAdapter that connects to a SQL database and later this data may be serialized to a custom format local to the device. The ADO.NET DataSet does not care how data got placed into it or where the data came from.

Working with Untyped DataSets

The desktop and server .NET Framework offer the concept of a "typed DataSet." A typed DataSet is a strongly typed set of wrapper classes implemented via object inheritance on top of DataSet, DataTable, and other ADO.NET data objects. These inherited classes allow access to the underlying tables and rows in the DataSet using strongly typed class members with names matching the names of the tables and columns being worked with. For example, instead of using the late bound column name look up myDataRow["CustomerFirstName"] or requiring the use of a column index (for example, myDataRow[2]), the developer can use the early bound myDataRow.CustomerFirstName property. This design-time binding is what makes the DataSet "typed." The source code for typed DataSet's classes is automatically generated by the Visual Studio .NET design-time environment.

Note

It is important to note that strongly typed DataSets are not more efficient than untyped DataSets. This is a common and understandable misconception; it seems like early binding should be more efficient. However, typed DataSet early binding simply defers down to the DataSet's untyped core objects. Typed DataSet classes are not faster; they are simply easier to use. Having strongly typed wrapper objects allows design-time features such as auto statement completion to aid in developing code that uses the DataSet. Using strongly typed wrapper classes also allows some kinds of syntactical errors to be found at design time instead of at runtime. Beyond this, there is no performance advantage.

The .NET Compact Framework does not support compiling the desktop autogenerated typed DataSet code. This means that the most common way to work with a ADO.NET DataSet using the .NET Compact Framework is to work directly with the untyped DataSet class and its subordinate DataTable, DataColumn, and DataRow classes. Other than adding some complexity to your code and requiring you to be diligent to make sure you do not have typos in column or field names (a common error) code there are no disadvantages to using untyped DataSets. The fastest performance is achieved by working directly with the untyped DataSet classes themselves and not going through any wrapper layers.

Getting the Best Performance from ADO.NET DataSet Objects

When working with DataSets, it is important to keep efficiency in mind. It is almost equally as easy to write efficient ADO.NET code as it is to write poorly performing code. A common inefficiency mistake developers make is to look up tables and columns using their string names rather than using more efficient indexing mechanisms. This is particularly important for accessing fields inside rows of data because this is an operation that is often done in loops iterating over significant numbers of rows. When accessing a field of data inside a data row, there are three ways to reference the item. In order of increasing performance, they are as follows:

String-based field lookup
For example: myRow["myColumnName"]; this is the slowest way because the string name of the column must be compared with available columns to find the correct field.

Integer index-based field lookup
For example: myRow[2]; this is a moderate improvement over string-based lookup because an integer is used. To use this mechanism, your code must look up and cache the integer index of the column in advance.

Column object-based field lookup
For example: myRow-[myColumnObject]; where myColumnObject represents an object of type System.Data.DataColumn. This is a significant improvement in performance over the previous two mechanisms. To use this mechanism, your code must cache the column object that represents the field you want to use.

Listing 14.3 contains code to test the relative performance of the three different approaches described above. The code simulates the common data processing task of record lookup and modification.

The scenario:

Mobile devices are being used to allow agents working for a transportation company to make changes to customer travel information while in the field. A situation such as a big snowstorm occasionally occurs that requires the ability to change the travel plans of a set of customers waiting in a travel terminal or currently in mid-transport on a train or airplane. These customers have connections that need to be updated and travel plans that need to be revised. The simplest way to accomplish this is to arm the employees with mobile devices that contain the travel information for the customers. Having a mobile device application allows multiple agents to go out and solve the customers' problems without forcing customers to queue up in a line and often allows them to have new travel arrangements completed even before their current train or airplane completes its trip. The mobile devices contain a downloaded list of customers and their travel details. To make finding customers' records fast and less error-prone, the mobile devices are equipped with readers that can scan in a passenger's credit card number and do lookups based on this key. After customers' records are found, their travel date can be updated with new information.

Our test code will simplify this scenario and use only customer name, travel date, and credit card number in its data table. In reality, of course, there is much more data that would need to be accessed, but this simplified example contains all the basic components; there is on-device data, there is the need to look it up, and the need to make changes to it. Because real-world scenarios would do even more data lookups, the performance results for real-world scenarios should be able to be extrapolated from this simple test.

Table 14.1 shows the results of running the test on a physical Pocket PC device. Unsurprisingly, the text-based column lookup (column A) performs the worst, completing the test in 32.82 seconds. Integer-based lookup (column B) offers a respectable 8 percent improvement by completing the test in 30.28 seconds. Data column-based lookup (column C) offers a 28 percent improvement over string-based lookup in the test case below. These gains are significant and point to the benefits of caching DataColumn objects when doing loop operations that look up data in data tables.

The code in Listing 14.3 belongs inside a form in a Pocket PC project. Do the following to build and run the application:

Listing 14.3. Comparing the Performance of Different DataSet Access Methods

System.Data.DataSet m_myDataSet; //Dataset for test
//Column and table indexes to cache
private bool m_indexesLookedUp = false;
private const int INVALID_INDEX = -1;
private int  m_IndexOfTestColumn_CreditCard = INVALID_INDEX;
private int  m_IndexOfTestColumn_TravelDate = INVALID_INDEX;
private int  m_IndexOfTestTable = INVALID_INDEX;
//Data columns and table to cache
System.Data.DataColumn m_TestColumn_CreditCard;
System.Data.DataColumn m_TestColumn_TravelDate;
private System.Data.DataTable m_TableCustomerInfo;
//3 different kinds of tests we can run
public enum testType
{ textColumnLookup,  cachedIndexLookup,  cachedColumnObject }
//These determine the size of the test
const int DUMMY_ROWS_OF_DATA = 100;
const int NUMBER_TEST_ITERATIONS = 500;
//Datatable information
const string TABLE_NAME_PASSENGERINFO = "CustomerTravelInfo";
const string COLUMN_NAME_DATE_OF_TRAVEL = "DateOfTravel";
const string COLUMN_NAME_PASSENGER_NAME = "PassengerName";
const string COLUMN_NAME_PASSENGER_CREDIT_CARD = "PassengerCreditCard";
const string TEST_CREDIT_CARD = "IvoCard-987-654-321-000";
//----------------------------------------------------------
//Creates the dataset
//----------------------------------------------------------
private void createDataSet()
{
//1. Create a new DataSet
m_myDataSet = new System.Data.DataSet("TravelService Dataset");
//2. Add a DataTable to the ADO.NET DataSet
System.Data.DataTable myTestTable;
myTestTable = m_myDataSet.Tables.Add(TABLE_NAME_PASSENGERINFO);
//Add 2 columns to the table
//Add a date column to the DataTable in the DataSet
myTestTable.Columns.Add(COLUMN_NAME_DATE_OF_TRAVEL,
typeof(System.DateTime));
//Add a string colum to the DataTable in the DataSet
myTestTable.Columns.Add(COLUMN_NAME_PASSENGER_NAME,
typeof(string));
//Add a string colum to the DataTable in the DataSet
myTestTable.Columns.Add(COLUMN_NAME_PASSENGER_CREDIT_CARD,
typeof(string));
//Data to place into the data row
object[] objArray;
objArray = new object[3];
//------------------------------------
//Add data rows to the table
//------------------------------------
System.Text.StringBuilder buildTestString;
buildTestString = new System.Text.StringBuilder();
for(int addItemsCount = 0; addItemsCount < DUMMY_ROWS_OF_DATA; addItemsCount++)
{
//Pick a travel day for the passenger
objArray[0] = System.DateTime.Today.AddDays(addItemsCount);
//Pick a name for the passenger
buildTestString.Length = 0;
buildTestString.Append("TestPersonName");
buildTestString.Append(addItemsCount);
objArray[1] = buildTestString.ToString();
//Assign the passenger a text credit card number
buildTestString.Length = 0;
buildTestString.Append("IvoCard-000-000-0000-");
buildTestString.Append(addItemsCount);
objArray[2] = buildTestString.ToString();
//Add the items in the array to the dataset row
myTestTable.Rows.Add(objArray);
}
//Add the item we want to search for in our test...
objArray[0] = System.DateTime.Today;
objArray[1] = "Ms. TestPerson";
objArray[2] = TEST_CREDIT_CARD;
//Add the items in the array to the dataset row
myTestTable.Rows.Add(objArray);
} //End function
//--------------------------------------------------------
//Look up and cache all the dataset indexes we will need
//--------------------------------------------------------
private void cacheDataSetInfo()
{
//Exit if we've already loaded the indexes
if (m_indexesLookedUp == true) {return;}
//Cache the index of the table
m_IndexOfTestTable =
m_myDataSet.Tables.IndexOf(TABLE_NAME_PASSENGERINFO);
//--------------------------------------------------------
//Iterate through all the columns in our table definition
//and cache the indexes of the ones we want
//--------------------------------------------------------
m_TableCustomerInfo = m_myDataSet.Tables[m_IndexOfTestTable];
int dataColumnCount = m_TableCustomerInfo.Columns.Count;
System.Data.DataColumn myColumn;
for(int colIdx = 0; colIdx < dataColumnCount;)
{
myColumn = m_TableCustomerInfo.Columns[colIdx];
//Only try a lookup if we haven't already
if (m_IndexOfTestColumn_CreditCard == INVALID_INDEX)
{
//See if the name matches
if (myColumn.ColumnName ==
COLUMN_NAME_PASSENGER_CREDIT_CARD)
{
//Cache the index
m_IndexOfTestColumn_CreditCard = colIdx;
//Cache the column
m_TestColumn_CreditCard = myColumn;
goto next_loop_iteration; //Skip other compares...
} //Endif string compare
} //Endif
if (m_IndexOfTestColumn_TravelDate == INVALID_INDEX)
{
//See if the name matches
if (myColumn.ColumnName ==
COLUMN_NAME_DATE_OF_TRAVEL)
{
//Cache the index
m_IndexOfTestColumn_TravelDate = colIdx;
//Cache the column
m_TestColumn_TravelDate = myColumn;
goto next_loop_iteration; //Skip other compares...
} //Endif string compare
} //Endif
next_loop_iteration:
colIdx++;
}
m_indexesLookedUp = true;
}
//------------------------------------------------
//Run the test...
//------------------------------------------------
void changeDayOfTravel_test(testType kindOfTest)
{
//Show wait cursor
System.Windows.Forms.Cursor.Current =
System.Windows.Forms.Cursors.WaitCursor;
//Start at a known date....
System.DateTime newDate;
newDate = System.DateTime.Today;
changeDayOfTravel_textColumnLookup(TEST_CREDIT_CARD, newDate);
//TEST CODE ONLY!!!
//Calling garbage collector in code will SLOW DOWN your app!
System.GC.Collect();
const int testNumber = 0;
//Set up properly depending on which test we are running
switch(kindOfTest)
{
case testType.textColumnLookup:
PerformanceSampling.StartSample(testNumber,
"Text based Column lookup.");
break;
case testType.cachedIndexLookup:
PerformanceSampling.StartSample(testNumber,
"Cached Column Index lookup.");
break;
case testType.cachedColumnObject:
PerformanceSampling.StartSample(testNumber,
"Cached Column objects");
break;
default:
throw new Exception("Unknown state!");
}
//Run the test!
for(int testCount = 0;
testCount < NUMBER_TEST_ITERATIONS; testCount++)
{
//Move the date forward one day
newDate = newDate.AddDays(1);
int numberRecordsChanged = 0;
//Which kind of test are we running?
switch(kindOfTest)
{
case testType.textColumnLookup:
//BAD PERFORMANCE: Look up all names using STRINGS
numberRecordsChanged =
changeDayOfTravel_textColumnLookup(
TEST_CREDIT_CARD, newDate);
break;
case testType.cachedIndexLookup:
//BETTER PERFORMANCE: Use cached indexes
numberRecordsChanged =
changeDayOfTravel_cachedColumnIndex(
TEST_CREDIT_CARD, newDate);
break;
case testType.cachedColumnObject:
//BEST PERFORMANCE: Use cached colum objects
numberRecordsChanged =
changeDayOfTravel_CachedColumns(
TEST_CREDIT_CARD, newDate);
break;
}
//Make sure the test is running as expected...
if(numberRecordsChanged != 1)
{
System.Windows.Forms.MessageBox.Show(
"No matching records found. Test aborted!");
return;
}
}
//Get the time it took to run the test
PerformanceSampling.StopSample(testNumber);
//Normal cursor
System.Windows.Forms.Cursor.Current =
System.Windows.Forms.Cursors.Default;
//Show the test results
string runInfo = NUMBER_TEST_ITERATIONS.ToString() + "x" +
DUMMY_ROWS_OF_DATA.ToString() + ": ";
System.Windows.Forms.MessageBox.Show(runInfo +
PerformanceSampling.GetSampleDurationText(testNumber));
}
//POOR PERFORMANCE SEARCH FUNCTION
private int changeDayOfTravel_textColumnLookup(
string creditCardNumber, System.DateTime newTravelDate)
{
int numberRecordsChanged = 0;
//Look up the table name
System.Data.DataTable dataTable_Customers;
//BAD PERFORMANCE: Look up table by string comparison!
dataTable_Customers =
m_myDataSet.Tables[TABLE_NAME_PASSENGERINFO];
foreach(System.Data.DataRow currentCustomerRow in
dataTable_Customers.Rows)
{
string currentCreditCard;
//BAD PERFORMANCE: Look up table by string comparison!
currentCreditCard = (string)
currentCustomerRow[COLUMN_NAME_PASSENGER_CREDIT_CARD];
//See if this is the credit card we are looking for
if(creditCardNumber == currentCreditCard)
{
//Change the date of travel
//BAD PERFORMANCE: Look up column by string comparison!
System.DateTime currentTravelDate = (System.DateTime)
currentCustomerRow[COLUMN_NAME_DATE_OF_TRAVEL];
if(currentTravelDate != newTravelDate)
{
//BAD PERFORMANCE: Look up column by string comparison!
currentCustomerRow[COLUMN_NAME_DATE_OF_TRAVEL] =
newTravelDate;
numberRecordsChanged++;
}
} //endif: string compare
} //end foreach
return numberRecordsChanged; //# updated records
}
//SLIGHTLY BETTER PERFORMANCE FUNCTION
private int changeDayOfTravel_cachedColumnIndex(
string creditCardNumber, System.DateTime newTravelDate)
{
int numberRecordsChanged = 0;
//Look up the table name
System.Data.DataTable dataTable_Customers;
//BETTER PERFORMANCE: use a cached index
dataTable_Customers =
m_myDataSet.Tables[m_IndexOfTestTable];
foreach(System.Data.DataRow currentCustomerRow in
dataTable_Customers.Rows)
{
string currentCreditCard;
//BETTER PERFORMANCE: User a cached column index!
currentCreditCard = (string)
currentCustomerRow[m_IndexOfTestColumn_CreditCard];
//See if there is a card number matches...
if(creditCardNumber == currentCreditCard)
{
//Change the date of travel
//BETTER PERFORMANCE: User a cached column index!
System.DateTime currentTravelDate = (System.DateTime)
currentCustomerRow[m_IndexOfTestColumn_TravelDate];
if(currentTravelDate != newTravelDate)
{
//BETTER PERFORMANCE: User a cached column index!
currentCustomerRow[m_IndexOfTestColumn_TravelDate] =
newTravelDate;
numberRecordsChanged++;
}
}
}
return numberRecordsChanged;  //# updated records
}
//BEST PERFORMANCE FUNCTION
private int changeDayOfTravel_CachedColumns(
string creditCardNumber, System.DateTime newTravelDate)
{
int numberRecordsChanged = 0;
//Look up the table name
System.Data.DataTable dataTable_Customers =
m_TableCustomerInfo;
foreach(System.Data.DataRow currentCustomerRow in
dataTable_Customers.Rows)
{
string currentCreditCard;
//BEST PERFORMANCE: User a cached column index!
currentCreditCard = (string)
currentCustomerRow[m_TestColumn_CreditCard];
//See if there is a card number matches...
if(creditCardNumber == currentCreditCard)
{
//Change the date of travel
//BEST PERFORMANCE: User a cached column index!
System.DateTime currentTravelDate = (System.DateTime)
currentCustomerRow[m_TestColumn_TravelDate];
if(currentTravelDate != newTravelDate)
{
//BEST PERFORMANCE: User a cached column index!
currentCustomerRow[m_TestColumn_TravelDate] =
newTravelDate;
numberRecordsChanged++;
}
}
}
return numberRecordsChanged;  //# updated records
}
//Button click event
private void buttonRunTest_Click(object sender,
System.EventArgs e)
{
createDataSet();  cacheDataSetInfo();
//BAD PERFORMANCE: Use string to based lookups
changeDayOfTravel_test(testType.textColumnLookup);
//BETTER PERFORMANCE: Use integer based lookups
changeDayOfTravel_test(testType.cachedIndexLookup);
//BEST PERFORMANCE: Use column object based lookups
changeDayOfTravel_test(testType.cachedColumnObject);
}

When Not to Use ADO.NET DataSet Objects

If your mobile device application's data is mostly read-only, if you have a large amount of data you need to keep in memory, or if your data's relationships are relatively simple, it is worth considering a customized data management model. ADO.NET DataSet objects are efficiently implemented, but they are a general-purpose data management model. Significant size and performance benefits can be realized by creating a specific data model that most efficiently meets your needs. The key to size efficiency is reducing the number of objects that need to be allocated to hold your data; fewer objects generally result in lower memory pressure and better application performance.

Table 14.2 and Listing 14.4 show the results of using an optimized custom data format for storing rows of data. The test code in Listing 14.4 accomplishes the same functional task as the code above in Listing 14.3 but does so using simple typed arrays of data instead of an ADO.NET DataSet. This benefits the application's performance in two specific ways:

Execution performance
As can be seen in Table 14.2, the custom data format runs in 38 percent of the time that the ADO.NET text index solution does. Comparing it to the optimized "column object" lookup shows that the custom data format solution runs in almost half the time as the most optimized ADO.NET result we could achieve with the code above (12.32 / 23.54 = 52.3 percent).

Lower memory pressure
Because the custom data format solution has only the minimum of objects required to hold the data and does not have any of the additional overhead of ADO.NET DataSets, our application will have lower memory pressure. All else being equal, this means fewer garbage collections and more working room for other code in our application. This result is not reflected in the table but is an overall application-wide performance advantage.

Of course, a custom data format solution has some disadvantages compared to ADO.NET as well. Most notably, if the data is going to need to be updated to a server, our code will need to manually keep track of changes made to the data. This means introducing at least one more column of Boolean data to indicate which rows have been updated. This may be easy for simple tabular data, but will be more complicated for data that has rich relationships between multiple tables of data. In addition, ADO.NET offers rich DataView objects to do sorting and filtering of data that are not available in custom data implementations. With richness comes overhead, and your application will need to make a prudent trade-off between the richness of ADO.NET and the potentially higher efficiency of a custom implementations. For device applications, the choice of high efficiency is often worth the additional design burdens that custom data models require.

The code in Listing 14.4 belongs inside a form in a Pocket PC project. Do the following to build and run the application:

Listing 14.4. Testing the Performance of Using a Custom Data Format Instead of a DataSet

//These determine the size of the test
const int DUMMY_ROWS_OF_DATA = 100;
const int NUMBER_TEST_ITERATIONS = 500;
const string TABLE_NAME_PASSENGERINFO = "CustomerTravelInfo";
const string TEST_CREDIT_CARD = "IvoCard-987-654-321-000";
string [] m_data_creditCards;
string [] m_data_names;
System.DateTime [] m_data_travelDates;
//-----------------------------------------------------------
//Creates the arrays of data (instead of using a dataset)
//-----------------------------------------------------------
private void createDataSet()
{
//===========================================
//1. Create the space for our data
//===========================================
m_data_creditCards = new string[DUMMY_ROWS_OF_DATA + 1];
m_data_names = new string[DUMMY_ROWS_OF_DATA + 1];
m_data_travelDates =
new System.DateTime[DUMMY_ROWS_OF_DATA + 1];
//------------------------------------
//Add the rows of data
//------------------------------------
System.Text.StringBuilder buildTestString;
buildTestString = new System.Text.StringBuilder();
for(int addItemsCount = 0; addItemsCount < DUMMY_ROWS_OF_DATA;
addItemsCount++)
{
//Pick a travel day for the passenger
m_data_travelDates[addItemsCount] =
System.DateTime.Today.AddDays(addItemsCount);
//------------------------------------
//Pick a name for the passenger
//------------------------------------
//Clear the text of the string
buildTestString.Length = 0;
buildTestString.Append("TestPersonName");
buildTestString.Append(addItemsCount);
m_data_names[addItemsCount] = buildTestString.ToString();
//------------------------------------
//Assign the passenger a text credit card number
//------------------------------------
//A string for the third dataset column value
buildTestString.Length = 0;
buildTestString.Append("IvoCard-000-000-0000-");
buildTestString.Append(addItemsCount);
m_data_creditCards[addItemsCount] =
buildTestString.ToString();
}
//Add the item we want to search for in our test...
//Pick a day for the first dataset column value
m_data_travelDates[DUMMY_ROWS_OF_DATA] =
System.DateTime.Today;
//A string for the second dataset column  value
m_data_names[DUMMY_ROWS_OF_DATA] = "Ms. TestPerson";
//A string credit-card ID
m_data_creditCards[DUMMY_ROWS_OF_DATA] = TEST_CREDIT_CARD;
} //End function
//------------------------------------------------
//Run the test...
//------------------------------------------------
void changeDayOfTravel_test()
{
//Show wait cursor
System.Windows.Forms.Cursor.Current =
System.Windows.Forms.Cursors.WaitCursor;
//Start at a known date....
System.DateTime newDate;
newDate = System.DateTime.Today;
changeDayOfTravel_CustomArrays(TEST_CREDIT_CARD, newDate);
//TEST CODE ONLY!!!
//Do NOT call the garbage collector in production
//code. It will SLOW DOWN your application performance
System.GC.Collect();
const int testNumber = 0;
//Start the timer running for the test
PerformanceSampling.StartSample(testNumber,
"Custom Array implementation");
//Run the test!
for(int testCount = 0;
testCount < NUMBER_TEST_ITERATIONS; testCount++)
{
//Move the date forward one day
newDate = newDate.AddDays(1);
int numberRecordsChanged = 0;
//Look up all names using STRINGS
numberRecordsChanged =
changeDayOfTravel_CustomArrays(
TEST_CREDIT_CARD, newDate);
//Make sure the test is running as expected...
if(numberRecordsChanged != 1)
{
System.Windows.Forms.MessageBox.Show(
"No matching records found. Test aborted!");
return;
}
}
//Get the time it took to run the test
PerformanceSampling.StopSample(testNumber);
//Normal cursor
System.Windows.Forms.Cursor.Current =
System.Windows.Forms.Cursors.Default;
//Show the test results
string runInfo = NUMBER_TEST_ITERATIONS.ToString() + "x" +
DUMMY_ROWS_OF_DATA.ToString() + ": ";
System.Windows.Forms.MessageBox.Show(runInfo +
PerformanceSampling.GetSampleDurationText(testNumber));
}
private int changeDayOfTravel_CustomArrays(
string creditCardNumber, System.DateTime newTravelDate)
{
int numberRecordsChanged = 0;
//Look at each item in the array
for(int index = 0; index <= DUMMY_ROWS_OF_DATA; index++)
{
string currentCreditCard;
currentCreditCard = m_data_creditCards[index];
//If there is a match, update the record
if(creditCardNumber == currentCreditCard)
{
//Change the date of travel
System.DateTime currentTravelDate =
m_data_travelDates[index];
//Only count the update if the date does not match
if(currentTravelDate != newTravelDate)
{
m_data_travelDates[index] =
newTravelDate;
numberRecordsChanged++;
}
}
}
//Return the number of records we updated
return numberRecordsChanged;
}
private void buttonRunTest_Click(object sender,
System.EventArgs e)
{
createDataSet();
changeDayOfTravel_test();
}

An Example of Using Custom Data Management and On-Device Databases

To illustrate custom in-memory representation and management of data read in from a database, it is useful to return an example from a previous chapter. For this example, we will consider again the foreign-language vocabulary game we designed the user interface for in Chapter 13, "Step 2: Design the Right User Interface." Previously we examined user interface design issues for this mobile application and now we will investigate the storage and in-memory representation of the vocabulary words themselves. Because the dictionary of words that we would like to use for this mobile application is potentially large, it is desirable to store the data in a database for fast access, efficiency, and flexibility reasons. The data is primarily read-only in nature; the user may add additional words occasionally, but dynamic updates to the existing data are not a primary requirement. In addition, the structure of the data is simple and can easily be represented in a single database table. All of these reasonsthe potentially large number of records, the low frequency of updates, and the simplicity of the data's structuremake the ADO.NET DataSet approach overkill for our needs. We can build an optimized solution that uses the lower-level SQL CE data reader (System.Data. SqlServerCe.SqlCeDataReader) to execute a query on our device's local SQL CE database. The query will return our application a forward-only cursor to data meeting our query criteria. This data can then be custom loaded into memory and stored in a format specifically designed to work with vocabulary-word data efficiently. For simplicity and speed, these objects will be placed into arrays. This can save us a significant amount of time and object allocation over a generic DataSet approach because we are only allocating objects we know we will use in our application.

It is worth noting that for simplicity the example does two things that would be unlikely in a real mobile device application:

It fills the contents of the database using the same application that loads the data from the database.
If we knew at design time all the data that was needed by the application at runtime, there is little need for an external database; the application could just populate its in-memory data structures directly from code and forego the overhead of any database. In a real version of this application, we would create and populate the database by one of the three following mechanisms: (a) downloading a fully populated database file to the device, the database having been prebuilt by us earlier; (b) synchronizing the SQL CE database with a server-based SQL server; and (c) running and then discarding a single-purpose application that created and populated the database.

It loads all of the data into memory at once.
As noted above, the vocabulary dictionary for our application is potentially large. If we have 20,000 vocabulary words in our database, we probably do not want to read them all into memory at once. The user receives no particular benefit from us doing this because they only get to work with a small set of words at any given time. What we should do is choose a reasonable limit for the number of vocabulary words our application will load into memory at any given time; the application can then periodically refresh the in-memory cache with new words. For example, if we want to keep an in-memory dictionary of no more than 500 words out of a total database dictionary of 20,000, only 1 of 40 words needs be loaded into memory at any given time. It would be easy to update the code that reads in vocabulary words to give each word it encountered a 1/40 random probability of being loaded. Other strategies for keeping the in-memory word count down are also possible such as grouping the words into related sets that are loaded together (for example, easy words, more difficult words, very difficult words). In any case, we want our mobile device application to have a memory management system that makes sure only a limited number of words are loaded into memory at any given time so that regardless of the size of the database our application performs in a predictable and acceptable way.

The code in Listing 14.5 belongs inside a form in a Pocket PC project. The code in Listings 14.6, 14.7, and 14.8 are separate classes listed in their entirety. Do the following to build and run the application:

Listing 14.5. Custom Data Management SampleCode That Goes Inside Form1.cs

//Creates the database
private void buttonCreateDatabase_Click(object sender,
System.EventArgs e)
{
DatabaseAccess.CreateAndFillDatabase();
}
//Loads the data from the database and displays it
private void buttonLoadGameData_Click(object sender,
System.EventArgs e)
{
//Clear the text box
textBox1.Text = ";
//Load the data for the words
GameData.InitializeGameVocabulary();
//Go through each of the words and add it to our text list
System.Text.StringBuilder thisStringBuilder;
thisStringBuilder = new System.Text.StringBuilder();
foreach(VocabularyWord thisWord in GameData.AllWords)
{
thisStringBuilder.Append(thisWord.EnglishWord);
thisStringBuilder.Append(" = ");
thisStringBuilder.Append(
thisWord.GermanWordWithArticleIfExists);
thisStringBuilder.Append("\r\n"); //New line
}
//Show the list of word in the text box
textBox1.Text = thisStringBuilder.ToString();
}

Listing 14.6. Custom Data Management SampleCode for DatabaseAccess.cs

//----------------------------------------------------------
//Database access code
//
//This class manages our applications database access
//----------------------------------------------------------
using System;
internal class DatabaseAccess
{
const string DATABASE_NAME = "LearnGerman.sdf";
const string  CONNECT_STRING =
"Data Source = "+DATABASE_NAME+"; Password = ''";
const string TRANSLATIONTABLE_NAME = "TranslationDictionary";
const string TRANSLATIONTABLE_ENGLISH_COLUMN =  "EnglishWord";
const string TRANSLATIONTABLE_GERMAN_COLUMN =  "GermanWord";
const string TRANSLATIONTABLE_GERMANGENDER_COLUMN =
"GermanGender";
const string TRANSLATIONTABLE_WORDFUNCTION_COLUMN =
"WordFunction";
internal const int DS_WORDS_COLUMNINDEX_ENGLISHWORD = 0;
internal const int DS_WORDS_COLUMNINDEX_GERMANWORD = 1;
internal const int DS_WORDS_COLUMNINDEX_GERMANGENDER = 2;
internal const int DS_WORDS_COLUMNINDEX_WORDFUNCTION = 3;
static public System.Data.IDataReader GetListOfWords()
{
System.Data.SqlServerCe.SqlCeConnection conn = null;
conn = new System.Data.SqlServerCe.SqlCeConnection(
CONNECT_STRING);
conn.Open();
System.Data.SqlServerCe.SqlCeCommand cmd =
conn.CreateCommand();
cmd.CommandText = "select " +
TRANSLATIONTABLE_ENGLISH_COLUMN             + ", "
+ TRANSLATIONTABLE_GERMAN_COLUMN            + ", "
+ TRANSLATIONTABLE_GERMANGENDER_COLUMN      + ", "
+ TRANSLATIONTABLE_WORDFUNCTION_COLUMN      + " "
+ "from " + TRANSLATIONTABLE_NAME;
//Execute the database command
System.Data.SqlServerCe.SqlCeDataReader myReader =
cmd.ExecuteReader(System.Data.CommandBehavior.SingleResult);
return myReader;
}
//--------------------------------------------------
//Creates and a database if needed
//--------------------------------------------------
static public void CreateDatabaseIfNonExistant()
{
if (System.IO.File.Exists(DATABASE_NAME) == false)
{
CreateAndFillDatabase();
}
}
//--------------------------------------------------
//Creates and fills a database
//--------------------------------------------------
static public void  CreateAndFillDatabase()
{
//Delete the database if it's there
if (System.IO.File.Exists(DATABASE_NAME))
{ System.IO.File.Delete(DATABASE_NAME);  }
//Create a new database
System.Data.SqlServerCe.SqlCeEngine sqlCeEngine;
sqlCeEngine = new System.Data.SqlServerCe.SqlCeEngine(
CONNECT_STRING);
sqlCeEngine.CreateDatabase();
//-------------------------------------------------
//Try to connect to the database
//and populate it with data
//-------------------------------------------------
System.Data.SqlServerCe.SqlCeConnection conn = null;
try
{
conn = new System.Data.SqlServerCe.SqlCeConnection(
CONNECT_STRING);
conn.Open();
System.Data.SqlServerCe.SqlCeCommand cmd =
conn.CreateCommand();
//Create a translation table
//Fields:
//   1. EnglishWord
//   2. GermanWord
//   3. Gender of word
//   4. Type of word
cmd.CommandText = "CREATE TABLE " + TRANSLATIONTABLE_NAME +
" (" +
TRANSLATIONTABLE_ENGLISH_COLUMN      + " ntext" + ", " +
TRANSLATIONTABLE_GERMAN_COLUMN       + " ntext" + ", " +
TRANSLATIONTABLE_GERMANGENDER_COLUMN + " int"   + ", " +
TRANSLATIONTABLE_WORDFUNCTION_COLUMN + " int"   +
")";
cmd.ExecuteNonQuery();
//Fill the database with words
FillDictionary(cmd);
}
catch (System.Exception eTableCreate)
{
System.Windows.Forms.MessageBox.Show(
"Error occured adding table :" + eTableCreate.ToString());
}
finally
{
//Always close the database when we are done
conn.Close();
}
//Tell the user
System.Windows.Forms.MessageBox.Show(
"Created language database!");
}
static private void FillDictionary(
System.Data.SqlServerCe.SqlCeCommand cmd)
{
//Verbs
InsertEnglishGermanWordPair(cmd, "to pay", "zahlen",
VocabularyWord.WordGender.notApplicable,
VocabularyWord.WordFunction.Verb);
InsertEnglishGermanWordPair(cmd, "to catch", "fangen",
VocabularyWord.WordGender.notApplicable,
VocabularyWord.WordFunction.Verb);
//Add more words...
//Pronouns
InsertEnglishGermanWordPair(cmd, "What", "was",
VocabularyWord.WordGender.notApplicable,
VocabularyWord.WordFunction.Pronoun);
//Add more words...
//Adverb
InsertEnglishGermanWordPair(cmd, "where", "wo",
VocabularyWord.WordGender.notApplicable,
VocabularyWord.WordFunction.Adverb);
InsertEnglishGermanWordPair(cmd, "never", "nie",
VocabularyWord.WordGender.notApplicable,
VocabularyWord.WordFunction.Adverb);
//Add more words...
//Preposition
InsertEnglishGermanWordPair(cmd, "at the", "am",
VocabularyWord.WordGender.notApplicable,
VocabularyWord.WordFunction.Preposition);
//Adjective
InsertEnglishGermanWordPair(cmd, "invited", "eingeladen",
VocabularyWord.WordGender.notApplicable,
VocabularyWord.WordFunction.Verb);
InsertEnglishGermanWordPair(cmd, "yellow", "gelbe",
VocabularyWord.WordGender.notApplicable,
VocabularyWord.WordFunction.Adjective);
InsertEnglishGermanWordPair(cmd, "one", "eins",
VocabularyWord.WordGender.notApplicable,
VocabularyWord.WordFunction.Adjective);
InsertEnglishGermanWordPair(cmd, "two", "zwei",
VocabularyWord.WordGender.notApplicable,
VocabularyWord.WordFunction.Adjective);
//Masculine nouns
InsertEnglishGermanWordPair(cmd, "Man", "Mann",
VocabularyWord.WordGender.Masculine,
VocabularyWord.WordFunction.Noun);
InsertEnglishGermanWordPair(cmd, "Marketplace", "Marktplatz",
VocabularyWord.WordGender.Masculine,
VocabularyWord.WordFunction.Noun);
InsertEnglishGermanWordPair(cmd, "Spoon", "Löffel ",
VocabularyWord.WordGender.Masculine,
VocabularyWord.WordFunction.Noun);
//Feminine nouns
InsertEnglishGermanWordPair(cmd, "Woman", "Frau",
VocabularyWord.WordGender.Feminine,
VocabularyWord.WordFunction.Noun);
InsertEnglishGermanWordPair(cmd, "Clock", "Uhr",
VocabularyWord.WordGender.Feminine,
VocabularyWord.WordFunction.Noun);
InsertEnglishGermanWordPair(cmd, "Cat", "Katze",
VocabularyWord.WordGender.Feminine,
VocabularyWord.WordFunction.Noun);
//Neuter nouns
InsertEnglishGermanWordPair(cmd, "Car", "Auto",
VocabularyWord.WordGender.Neuter,
VocabularyWord.WordFunction.Noun);
InsertEnglishGermanWordPair(cmd, "Book", "Buch",
VocabularyWord.WordGender.Neuter,
VocabularyWord.WordFunction.Noun);
}
//------------------------------------------------
//Inserts a word into the database
//------------------------------------------------
static private void InsertEnglishGermanWordPair(
System.Data.SqlServerCe.SqlCeCommand cmd,
string englishWord, string germanWord,
VocabularyWord.WordGender germanWordGender,
VocabularyWord.WordFunction wordFunction)
{
cmd.CommandText = "INSERT INTO " + TRANSLATIONTABLE_NAME +
"("+ TRANSLATIONTABLE_ENGLISH_COLUMN + ", " +
TRANSLATIONTABLE_GERMAN_COLUMN + ", " +
TRANSLATIONTABLE_GERMANGENDER_COLUMN + ", " +
TRANSLATIONTABLE_WORDFUNCTION_COLUMN +
")  VALUES ('"
+ englishWord + "', '"
+ germanWord +  "', '"
+ System.Convert.ToString(((int) germanWordGender))+ "', '"
+ System.Convert.ToString(((int) wordFunction))
+ "')";
cmd.ExecuteNonQuery();
}
} //End of class

Listing 14.7. Custom Data Management SampleCode for GameData.cs

//---------------------------------------------------------
//In-Memory Data Management Code
//
//This code manages the in-memory representation of the code
//----------------------------------------------------------
using System;
internal class GameData
{
//Array-Lists to store the data we have loaded in
private static System.Collections.ArrayList
m_vocabularyWords_All;
private static System.Collections.ArrayList
m_vocabularyWords_Nouns;
private static System.Collections.ArrayList
m_vocabularyWords_Verbs;
private static System.Collections.ArrayList
m_vocabularyWords_Adjectives;
private static System.Collections.ArrayList
m_vocabularyWords_Adverbs;
private static System.Collections.ArrayList
m_vocabularyWords_Prepositions;
public static bool isGameDataInitialized
{
//The game data is initialized if we have loaded the words
get {return (m_vocabularyWords_All != null);}
}
//Returns the collection of all words we have
public static System.Collections.ArrayList AllWords
{
get
{
//If the data has not been initialized, load it
if (m_vocabularyWords_All == null)
{InitializeGameVocabulary();}
return m_vocabularyWords_All;
}
}
//Returns the collection of all nouns we have
public static System.Collections.ArrayList Nouns
{
get
{
//If the data has not been initialized, load it
if (m_vocabularyWords_Nouns == null)
{InitializeGameVocabulary();}
return m_vocabularyWords_Nouns;
}
}
//==========================================================
//Loads the data in from our database
//==========================================================
static public void InitializeGameVocabulary()
{
//Create a new array list to hold our words
m_vocabularyWords_All = new System.Collections.ArrayList();
m_vocabularyWords_Nouns = new System.Collections.ArrayList();
m_vocabularyWords_Verbs = new System.Collections.ArrayList();
m_vocabularyWords_Adjectives =
new System.Collections.ArrayList();
m_vocabularyWords_Adverbs =
new System.Collections.ArrayList();
m_vocabularyWords_Prepositions =
new System.Collections.ArrayList();
System.Data.IDataReader dataReader;
dataReader = DatabaseAccess.GetListOfWords();
VocabularyWord newWord;
//Go through all of the records
while(dataReader.Read())
{
//Place the data for the word we just read in into a class
newWord = new VocabularyWord(
dataReader.GetString(
DatabaseAccess.DS_WORDS_COLUMNINDEX_ENGLISHWORD),
dataReader.GetString(
DatabaseAccess.DS_WORDS_COLUMNINDEX_GERMANWORD),
(VocabularyWord.WordGender) dataReader.GetInt32(
DatabaseAccess.DS_WORDS_COLUMNINDEX_GERMANGENDER),
(VocabularyWord.WordFunction) dataReader.GetInt32(
DatabaseAccess.DS_WORDS_COLUMNINDEX_WORDFUNCTION)
);
//Add the new word to the array list
m_vocabularyWords_All.Add(newWord);
//Words can belong to more than one group, so check
//using a logical AND to see if the word meets a
//given category
if((newWord.getWordFunction &
VocabularyWord.WordFunction.Noun) != 0)
{m_vocabularyWords_Nouns.Add(newWord);}
if((newWord.getWordFunction &
VocabularyWord.WordFunction.Verb) != 0)
{m_vocabularyWords_Verbs.Add(newWord);}
if((newWord.getWordFunction &
VocabularyWord.WordFunction.Adjective) != 0)
{m_vocabularyWords_Adjectives.Add(newWord);}
if((newWord.getWordFunction &
VocabularyWord.WordFunction.Adverb) != 0)
{m_vocabularyWords_Adverbs.Add(newWord);}
if((newWord.getWordFunction &
VocabularyWord.WordFunction.Preposition) != 0)
{m_vocabularyWords_Prepositions.Add(newWord);}
}
//Close the data reader
dataReader.Close();
}
} //End class

Listing 14.8. Custom Data Management SampleCode for VocabularyWord.cs

using System;
//-----------------------------------------------
//Holds the data for a vocabulary word
//-----------------------------------------------
internal class VocabularyWord
{
[System.FlagsAttribute] //Indicates values can be logically ORed
public enum WordFunction
{
Noun = 1,
Verb = 2,
Pronoun = 4,
Adverb = 8,
Adjective = 16,
Preposition = 32,
Phrase = 64
}
public enum WordGender
{
notApplicable = 0,
Masculine = 1,
Feminine = 2,
Neuter = 3,
}
private string m_englishWord;
private string m_germanWord;
private VocabularyWord.WordGender m_germanGender;
private VocabularyWord.WordFunction m_wordFunction;
public string EnglishWord {get {return m_englishWord;}}
public string GermanWord {get {return m_germanWord;}}
public WordFunction getWordFunction
{get {return m_wordFunction;}}
public WordGender GermanGender {get {return m_germanGender;}}
//---------------------------------------------------------
//Returns the German word, prefixed with an article
//(for example, 'der', 'die', 'das' if it exists)
//---------------------------------------------------------
public string GermanWordWithArticleIfExists
{
get
{
if (m_germanGender == WordGender.notApplicable)
{
return this.GermanWord;
}
return this.GenderArticle + " " + this.GermanWord;
}
} //End Property
public string GenderArticle
{
get
{
switch (m_germanGender)
{
case WordGender.Masculine:
return "der";
case WordGender.Feminine:
return "die";
case WordGender.Neuter:
return "das";
}
return ";
}
}
public VocabularyWord(string enlgishWord, string germanWord,
WordGender germanGender, WordFunction wordFunction)
{
m_englishWord = enlgishWord;
m_germanWord = germanWord;
m_germanGender = germanGender;
m_wordFunction = wordFunction;
}
}  //End class

Options for Storing Long-Term Data

There exist many different options for storing mobile application data. Data can be stored in binary files, text files, or in databases. (A database can be considered a special purpose binary file.) The storage of the data can occur off the device or on the device. Long-term data can be synchronized between devices and servers. Below are described the benefits and drawbacks of the most common choices along with suggestions on how to go about making long-term storage decisions in your mobile application's design.

Storage of Data in XML Files on Device

Benefits
Text files can be a great way to store moderate amounts of long-term data. XML files offer a nice balance between custom data formats and structured formats and are an improvement over generic text files. XML files can easily be passed between desktops, servers, and devices and can be interpreted by different applications with little difficulty. For simplicity and flexibility, it is hard to beat XML files.

Drawbacks
Text files are verbose, and XML files are particularly verbose. If your mobile applications works with a lot of data they need to store efficiently, an XML file will not be appropriate for their needs. Also, because XML files are formatted text, they are easily readable and offer no data protection; for this reason, their use for sensitive information storage should be avoided unless you have a reliable mechanism for encrypting the files. Note: See the chapter on XML performance for further guidelines on using XML efficiently for data storage.

Storage of Data in SQL CE Databases on Device

Benefits
The on-device database engine is a powerful concept. Having an on-device database allows your application to store, manage, and query large amounts of information locally. SQL CE databases can be password encrypted and thus can offer data protection for sensitive information. Partnerships can be set up between SQL CE databases and server-based SQL databases allowing for rich automated synchronization between them. For applications that deal with a lot of data that needs to be managed locally, it is probably the best choice.

Drawbacks
The largest drawback of using an on-device database is the need to ensure the database engine is installed on your target devices. The SQL CE database itself takes up a significant chunk of storage space on the device (1 to 3MB) and usually needs to be installed onto devices because it is not often part of a device's ROM image. Because of the space requirements, not all classes of devices support hosting the SQL CE database engine. For example, although the Pocket PC supports hosting SQL CE, the Smartphone presently does not. In addition to size, there is the need to consider synchronization requirements. Moving data stored on one local device database to another computer will require business logic to be written; you may need to package the data as an ADO.NET DataSet or use a custom format to move data between devices.

Storage of Data in Off-Device (Server) SQL Databases

Benefits
Storing and accessing data on a server-based database can be a useful way to access an almost limitless amount of data.

Drawbacks
Connectivity is required to get data from the server or update data on the server. If your mobile device application uses an external database as its primary data source, plan on writing custom logic to temporarily store the data locally if the server connection is lost; it is an extremely bad user experience to lose the data when an attempt to perform a server update fails. Intermittent network failure is an inherent challenge for mobile devices and your mobile application will have to deal with this reality.

A second potential drawback to accessing data on a server is the need to deal with private network access rules and firewalls. Most server databases containing valuable information are located inside protected environments behind firewalls. These firewalls may not allow your mobile application to connect to the database server when the device is outside the private network. If access to data on a protected server is required, you will need to consider how to enable that access.

Accessing Off-Device Data via Web Services

Benefits
Web services are increasingly being used to wrap access to private data sources. Access to information in databases can be exposed via Web services without exposing the underlying databases themselves. Web services usually work through HTTP or HTTPS network protocols; these protocols are generally firewall friendly. If a network already supports an outward-facing Web server, it is a relatively straightforward process to build a Web service hosted on that Web server.

Newer database engines increasingly support returning data as XML without the need for an intermediary Web server. These databases effectively offer their own Web services. XML returned directly by a database will not necessarily be in the ADO.NET XML DataSet format, but any returned XML can be parsed and processed on your device if this is the best way to work with the data.

In both cases, data can be exchanged with mobile devices using ADO.NET DataSets streamed as XML or via other XML formats. The choice of communicating directly with a database or insulating the database by using a Web server intermediary should be based on security, performance, and ease of development and deployment criteria.

Drawbacks
As with using an off-device database for storage, network access to a Web service cannot be guaranteed at all times. To be useful and robust, your mobile device application must always have a fallback strategy to work with cached data locally if the network is not available.

A second drawback is efficiency of communication. Because Web services use XML as their communication mechanism, they are more verbose than specialized formats such as those used to synchronize databases. If you have a large amount of data you need to download or upload, you might want to consider a direct access mechanism to the database that uses the most efficient protocol available.

SQL CE

Smaller amounts of simply structured data may be manageable in an XML file, but past a certain point of size and complexity a formal database adds a great deal of value and will simplify your mobile application development. Although there are no absolute rules for when to use a database versus an XML file, as the data size starts getting larger a true database adds increasing value. As a rule of thumb, if your mobile device application is going to be working with more than a hundred rows of data or the information needs to be stored in multiple tables, it probably makes sense to use an on-device database such as SQL CE.

Good documentation exists online that describes SQL CE. Those interested in learning detailed specifics about SQL CE should search the MSDN online (or Visual Studio product) documentation for "SQL Server CE." Instead of attempting to repeat the detailed documentation already available there, this section briefly overviews SQL CE and then describes the important decisions that must be made when developing mobile application code that works with on-device databases such as SQL CE. For a simple example showing how to use SQL CE, see Listings 14.5, 14.6, 14.7, and 14.8 earlier in this chapter.

What Is SQL CE?

Formally named SQL Server CE, SQL CE is a database engine for mobile and embedded devices. It offers relatively rich functionality for storing, querying, and synchronizing structured data with servers. Multiple SQL CE databases can be created on a single device. Each database is represented in a single binary file on the device; these files can be manually copied from one device to another or synchronized automatically with a server database. A SQL CE database's contents can be password encrypted to offer a reasonable level of data security in case a device is lost, stolen, or has its data contents copied surreptitiously.

SQL CE offers a subset of the database functionality present on its larger desktop and server SQL Server sibling. SQL CE supports storing relational data in tables and supports a significant portion of the desktop SQL data types and query syntax for adding, retrieving, and modifying data. Notably, SQL CE does not presently support stored procedures or named parameters in queries.

SQL CE Standalone and Synchronized Modes

SQL CE can be used in one of three modes:

Standalone
In this usage model, the SQL CE database serves as a standalone database on the device. Think of this as similar to using a local SQL Server or the Access runtime on a desktop application to manage a local database. One of the main challenges in standalone SQL CE usage is devising a strategy to populate the database. Your application can either dynamically populate the database with external data pulled down to the device at runtime or you can use a prefilled database file that is copied onto the device. These models can also be combined; data can be dynamically added to a preexisting database file that was downloaded to the device. Which database population strategy is best depends on the nature of your application. If you are using a prefilled database, the easiest way to provision devices with this data is to build a master copy of the database on a Pocket PC as part of your development process and then copy this database file off of the device and onto your development machine. This prepopulated database can then be copied to all target devices as part of your application's deployment.

Synchronized with SQL server through a Web server (IIS)
This mechanism is known as RDA, or remote data access. This is a simple method of synchronization where the SQL server that contains the master copy of the data has no special relationship to SQL CE databases synchronizing with it. SQL CE databases access the server data just like any other client application of the database except that the access occurs through a Web server interface. Because the server database does not need to keep track of what data any of the synchronized SQL CE clients are holding, the RDA strategy has low server overhead and scales well. An unlimited number of devices can have local SQL CE databases that are synchronized with a master SQL server in this manner. Data synchronization occurs through a special SQL CE synchronization engine that runs on top of Windows Internet Information Server (a Web server). Because the synchronization is Web serverbased, it can be done over a public network if desired. The SQL server has no knowledge that client databases are synchronizing with its data, so the responsibility for ensuring data that changes get updated on client and server is left to the mobile device application. For more information on RDA, read the help topic titled "Using Remote Data Access" in Microsoft's "SQL Server CE Books Online"; this documentation ships as part of Visual Studio .NET (2003 or later). To sum up: RDA = low overhead and high scalability, but at the cost of needing to handle data updates manually. RDA is a great choice when the data being synchronized with is being used in a mostly read-only way.

Synchronized directly with SQL Server: This mechanism is known as merge replication
Merge replication creates a partnership between a device-local SQL CE database and the SQL server it is synchronizing with. This is a powerful model because the SQL server has intimate knowledge of what data each partnered SQL CE client is holding; updates to data both on device and server can be handled in a much more automated and systematic way versus synchronization via RDA. Because SQL Server needs to maintain information regarding each partnering SQL CE client, this model does not scale as well as the RDA model; supporting a huge number of clients will be taxing on the server. Regardless, when the most robust synchronization behavior is needed, it is an excellent choice. For more information on merge replication read the topic titled "Using Replication" in Microsoft's "SQL Server CE Books Online."

SQL CE Multiple Application Usage

Presently, SQL CE supports only one concurrent connection to any given database. This rule is true for both inside a single application as well as across application boundaries. Each database is stored in a separate file; these files cannot be opened concurrently using SQL CE. This means that although multiple databases can be open simultaneously on the same device, any single database can only have one concurrent connection to it at any time. If an application attempts to open a SQL CE database file already opened by another application or already opened by itself, an exception will be thrown by the database engine and the second connection attempt will fail.

If your application is using a database that does not need to be shared with other applications, you only need to ensure that your code does not attempt to establish more than one simultaneous connection to the database; the database connection should be managed as an application global resource. If your application is working with a SQL CE database that may be shared with other mobile device applications, you need to do two additional things in your mobile application's design:

You should ensure that your mobile application does not hold an open database connection any longer than absolutely necessary. A connection should be established only right before database access is required and freed as soon as possible following the database access. Your application should be designed not to require a constant database connection.

You should code defensively and assume that the database will already be opened every time you try to access it. Your application should have a model for informing the user that the database is currently in use by another local application so that he can take steps to close the other application or force it to release its database connection. Your mobile device application should also have a model for deferring the database access until a database connection can be established; the application should not stall if the database cannot be connected to.

SQL CE Is Not Available on All Device Types

It is important to know that SQL CE is presently not available for Smartphone devices. This is primarily due to size, memory, and storage constraints. Pocket PC devices have a battery-powered RAM file system they can use for fast file system access; Smartphones use flash RAM for their longer-term storage needs and have smaller amounts of working RAM; both of these factors make running a database engine on the Smartphone less desirable.

If you are building two versions of an application, one for a Pocket PC and another intended for a Smartphone device, you should consider this to be another design decision you will need to think about. The Smartphone application will not only have different user interface demands than the Pocket PC application, it will also have different data-storage capabilities. The amount of data a Smartphone application will keep in long-term storage is usually smaller than the amount of data a similar Pocket PC application will store. It may be acceptable to use SQL CE on the Pocket PC application and to use an XML-persisted data file on a Smartphone.