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

Listing 14.1. Basic Creation and Use of an ADO.NET Data Set

'The data set we are going to load
Private m_myDataSet As System.Data.DataSet
'Constants we will use
Const FILE_EMPTY_DATASET As String = "EmptyDataSet.xml"
Const FILE_1TABLE_DATASET As String = "1TableDataSet.xml"
Const dividerLine As String = _
"-----------------------------" + vbCrLf
'----------------------------------------------------------
'Load the contents of a file and append it to the text
'in textBox1
'----------------------------------------------------------
Private Sub addFileContentsToTextBox(ByVal fileName As String)
'Open the file and read in its contents
Dim myStreamReader As System.IO.StreamReader
myStreamReader = System.IO.File.OpenText(fileName)
Dim fileText As String = myStreamReader.ReadToEnd()
'Close the file
myStreamReader.Close()
'Append the contents to the text in the text box
TextBox1.Text = TextBox1.Text + _
dividerLine + "FILE: '" + fileName + "'" + vbCrLf + _
dividerLine + fileText + vbCrLf
End Sub
'----------------------------------------------------------
'1. Creates a data set
'    persists DataSet out as XML,
'    displays results in textbox
'
'2. Adds a data table to the data set
'    adds two typed columns to the data table
'    adds to rows to the data table
'    persists data set out as XML,
'    displays results in text box
'----------------------------------------------------------
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
'Clear the contents of the text box
TextBox1.Text = "
'===========================================
'1. Create a new data set
'===========================================
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 data set
'   and add two rows of data to the data table
'===========================================
Dim myTestTable As System.Data.DataTable
myTestTable = m_myDataSet.Tables.Add("TestTable")
'------------------------------------
'Add two columns to the table
'------------------------------------
'Add a date column to the data table in the data set
myTestTable.Columns.Add("TestColumn0", _
GetType(System.DateTime))
'Add a string colum to the data table in the data set
myTestTable.Columns.Add("TestColumn1", GetType(String))
'------------------------------------
'Add data rows to the table
'------------------------------------
'Add a row of data to the data table
Dim rowOfData() As Object
ReDim rowOfData(1)
'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 second row of data to the data table
Dim rowOfData2() As Object
ReDim rowOfData2(1)
'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 data set contents as XML and show
'the file in the text box
m_myDataSet.WriteXml(FILE_1TABLE_DATASET)
addFileContentsToTextBox(FILE_1TABLE_DATASET)
End Sub

Listing 14.2. Using XMLWriteMode When Persisting ADO.NET Data Sets

'-------------------------------------------------------------
'This function exists because the .NET Compact Framework does
'not support the overload:
' "public Sub WriteXml(string, XmlWriteMode)"
'
'as a "public" member (it is private)
'-------------------------------------------------------------
Sub writeDataSetToFile(ByVal ds As System.Data.DataSet, _
ByVal filename As String, _
ByVal xmlWriteMode As System.Data.XmlWriteMode)
'Create an XML TextWriter to write out our XML
Dim xmlWriter As System.Xml.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!
End Sub

Listing 14.3. Comparing the Performance of Different Data Set Access Methods

Private m_myDataSet As System.Data.DataSet 'Data set for test
'Column and table indexes to cache
Private m_indexesLookedUp As Boolean = False
Private Const INVALID_INDEX As Integer = -1
Private m_IndexOfTestColumn_CreditCard _
As Integer = INVALID_INDEX
Private m_IndexOfTestColumn_TravelDate _
As Integer = INVALID_INDEX
Private m_IndexOfTestTable As Integer = INVALID_INDEX
'Data columns and table to cache
Private m_TestColumn_CreditCard As System.Data.DataColumn
Private m_TestColumn_TravelDate As System.Data.DataColumn
Private m_TableCustomerInfo As System.Data.DataTable
Public Enum testType 'Three different kinds of tests we can run
textColumnLookup
cachedIndexLookup
cachedColumnObject
End Enum
'These determine the size of the test
Const DUMMY_ROWS_OF_DATA As Integer = 100
Const NUMBER_TEST_ITERATIONS As Integer = 500
'Datatable information
Const TABLE_NAME_PASSENGERINFO As String = "CustomerTravelInfo"
Const COLUMN_NAME_DATE_OF_TRAVEL As String = "DateOfTravel"
Const COLUMN_NAME_PASSENGER_NAME As String = "PassengerName"
Const COLUMN_NAME_PASSENGER_CREDIT_CARD As String = _
"PassengerCreditCard"
Const TEST_CREDIT_CARD As String = "IvoCard-987-654-321-000"
'----------------------------------------------------------
'Creates the data set
'----------------------------------------------------------
Private Sub createDataSet()
'1. Create a new data set
m_myDataSet = New System.Data.DataSet("TravelService Dataset")
'2. Add a data table to the ADO.NET data set
Dim myTestTable As System.Data.DataTable
myTestTable = m_myDataSet.Tables.Add(TABLE_NAME_PASSENGERINFO)
'Add two columns to the table
'Add a date column to the data table in the data set
myTestTable.Columns.Add(COLUMN_NAME_DATE_OF_TRAVEL, _
GetType(System.DateTime))
'Add a string colum to the data table in the data set
myTestTable.Columns.Add(COLUMN_NAME_PASSENGER_NAME, _
GetType(String))
'Add a string colum to the data table in the data set
myTestTable.Columns.Add(COLUMN_NAME_PASSENGER_CREDIT_CARD, _
GetType(String))
'Data to place into the data row
Dim objArray() As Object
ReDim objArray(2)
'------------------------------------
'Add data rows to the table
'------------------------------------
Dim buildTestString As System.Text.StringBuilder
buildTestString = New System.Text.StringBuilder
Dim addItemsCount As Integer
For addItemsCount = 1 To DUMMY_ROWS_OF_DATA
'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 data set row
myTestTable.Rows.Add(objArray)
Next
'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 data set row
myTestTable.Rows.Add(objArray)
End Sub
'--------------------------------------------------------
'Look up and cache all the data set indexes we will need
'--------------------------------------------------------
Private Sub cacheDataSetInfo()
'Exit if we've already loaded the indexes
If (m_indexesLookedUp = True) Then 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)
Dim dataColumnCount As Integer
dataColumnCount = m_TableCustomerInfo.Columns.Count
Dim myColumn As System.Data.DataColumn
Dim colIdx As Integer
While (colIdx < dataColumnCount)
myColumn = m_TableCustomerInfo.Columns(colIdx)
'Only try a lookup if we haven't already
If (m_IndexOfTestColumn_CreditCard = INVALID_INDEX) Then
'See if the name matches
If (myColumn.ColumnName = _
COLUMN_NAME_PASSENGER_CREDIT_CARD) Then
'Cache the index
m_IndexOfTestColumn_CreditCard = colIdx
'Cache the column
m_TestColumn_CreditCard = myColumn
GoTo next_loop_iteration 'Skip other compares...
End If 'Endif string compare
End If
If (m_IndexOfTestColumn_TravelDate = INVALID_INDEX) Then
'See if the name matches
If (myColumn.ColumnName = _
COLUMN_NAME_DATE_OF_TRAVEL) Then
'Cache the index
m_IndexOfTestColumn_TravelDate = colIdx
'Cache the column
m_TestColumn_TravelDate = myColumn
GoTo next_loop_iteration 'Skip other compares.
End If 'Endif string compare
End If
next_loop_iteration:
colIdx = colIdx + 1
End While
m_indexesLookedUp = True
End Sub
'------------------------------------------------
'Run the test.
'------------------------------------------------
Sub changeDayOfTravel_test(ByVal kindOfTest As testType)
'Show wait cursor
System.Windows.Forms.Cursor.Current = _
System.Windows.Forms.Cursors.WaitCursor
'Start at a known date.
Dim newDate As System.DateTime
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 testNumber As Integer = 0
'Set up properly depending on which test we are running
Select Case (kindOfTest)
Case testType.textColumnLookup
PerformanceSampling.StartSample(testNumber, _
"Text based Column lookup.")
Case testType.cachedIndexLookup
PerformanceSampling.StartSample(testNumber, _
"Cached Column Index lookup.")
Case testType.cachedColumnObject
PerformanceSampling.StartSample(testNumber, _
"Cached Column objects")
Case Else
Throw New Exception("Unknown state!")
End Select
'Run the test!
Dim testCount As Integer
For testCount = 1 To NUMBER_TEST_ITERATIONS
'Move the date forward one day
newDate = newDate.AddDays(1)
Dim numberRecordsChanged As Integer = 0
'Which kind of test are we running?
Select Case (kindOfTest)
Case testType.textColumnLookup
'BAD PERFORMANCE: Look up all names imports STRINGS
numberRecordsChanged = _
changeDayOfTravel_textColumnLookup( _
TEST_CREDIT_CARD, newDate)
Case testType.cachedIndexLookup
'BETTER PERFORMANCE: Use cached indexes
numberRecordsChanged = _
changeDayOfTravel_cachedColumnIndex( _
TEST_CREDIT_CARD, newDate)
Case testType.cachedColumnObject
'BEST PERFORMANCE: Use cached column objects
numberRecordsChanged = _
changeDayOfTravel_CachedColumns( _
TEST_CREDIT_CARD, newDate)
End Select
'Make sure the test is running as expected.
If (numberRecordsChanged <> 1) Then
MsgBox("No matching records found. Test aborted!")
Return
End If
Next
'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
Dim runInfo As String = NUMBER_TEST_ITERATIONS.ToString() + _
"x" + DUMMY_ROWS_OF_DATA.ToString() + ": "
MsgBox(runInfo + _
PerformanceSampling.GetSampleDurationText(testNumber))
End Sub
'POOR PERFORMANCE SEARCH FUNCTION
Private Function changeDayOfTravel_textColumnLookup( _
ByVal creditCardNumber As String, _
ByVal newTravelDate As System.DateTime) As Integer
Dim numberRecordsChanged As Integer
'Look up the table name
Dim dataTable_Customers As System.Data.DataTable
'BAD PERFORMANCE: Look up table by string comparison!
dataTable_Customers = _
m_myDataSet.Tables(TABLE_NAME_PASSENGERINFO)
Dim currentCustomerRow As System.Data.DataRow
For Each currentCustomerRow In dataTable_Customers.Rows
Dim currentCreditCard As String
'BAD PERFORMANCE: Look up table by string comparison!
currentCreditCard = CType( _
currentCustomerRow(COLUMN_NAME_PASSENGER_CREDIT_CARD), _
String)
'See if this is the credit card we are looking for
If (creditCardNumber = currentCreditCard) Then
'Change the date of travel
'BAD PERFORMANCE: Look up column by string comparison!
Dim currentTravelDate As System.DateTime = CType( _
currentCustomerRow(COLUMN_NAME_DATE_OF_TRAVEL), _
System.DateTime)
If (currentTravelDate <> newTravelDate) Then
'BAD PERFORMANCE: Look up column by string comparison!
currentCustomerRow(COLUMN_NAME_DATE_OF_TRAVEL) = _
newTravelDate
numberRecordsChanged = numberRecordsChanged + 1
End If
End If 'endif: string compare
Next 'end for each
Return numberRecordsChanged 'Number updated records
End Function
'SLIGHTLY BETTER PERFORMANCE FUNCTION
Private Function changeDayOfTravel_cachedColumnIndex( _
ByVal creditCardNumber As String, ByVal newTravelDate _
As DateTime) As Integer
Dim numberRecordsChanged As Integer
'Look up the table name
Dim dataTable_Customers As System.Data.DataTable
'BETTER PERFORMANCE: use a cached index
dataTable_Customers = _
m_myDataSet.Tables(m_IndexOfTestTable)
Dim currentCustomerRow As System.Data.DataRow
For Each currentCustomerRow In dataTable_Customers.Rows
Dim currentCreditCard As String
'BETTER PERFORMANCE: User a cached column index!
currentCreditCard = CType(currentCustomerRow( _
m_IndexOfTestColumn_CreditCard), String)
'See if there is a card number matches.
If (creditCardNumber = currentCreditCard) Then
'Change the date of travel
'BETTER PERFORMANCE: User a cached column index!
Dim currentTravelDate As System.DateTime = CType( _
currentCustomerRow(m_IndexOfTestColumn_TravelDate), _
System.DateTime)
If (currentTravelDate <> newTravelDate) Then
'BETTER PERFORMANCE: User a cached column index!
currentCustomerRow(m_IndexOfTestColumn_TravelDate) = _
newTravelDate
numberRecordsChanged = numberRecordsChanged + 1
End If
End If
Next
Return numberRecordsChanged  'Number updated records
End Function
'BEST PERFORMANCE FUNCTION
Private Function changeDayOfTravel_CachedColumns( _
ByVal creditCardNumber As String, _
ByVal newTravelDate As System.DateTime) As Integer
Dim numberRecordsChanged As Integer
'Look up the table name
Dim dataTable_Customers As System.Data.DataTable = _
m_TableCustomerInfo
Dim currentCustomerRow As System.Data.DataRow
For Each currentCustomerRow In dataTable_Customers.Rows
Dim currentCreditCard As String
'BEST PERFORMANCE: User a cached column index!
currentCreditCard = CType( _
currentCustomerRow(m_TestColumn_CreditCard), _
String)
'See if there is a card number that matches.
If (creditCardNumber = currentCreditCard) Then
'Change the date of travel
'BEST PERFORMANCE: User a cached column index!
Dim currentTravelDate As System.DateTime = CType( _
currentCustomerRow(m_TestColumn_TravelDate), _
System.DateTime)
If (currentTravelDate <> newTravelDate) Then
'BEST PERFORMANCE: User a cached column index!
currentCustomerRow(m_TestColumn_TravelDate) = _
newTravelDate
numberRecordsChanged = numberRecordsChanged + 1
End If
End If
Next
Return numberRecordsChanged  'Number updated records
End Function
'Button click event
Private Sub buttonRunTest_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles buttonRunTest.Click
createDataSet()
cacheDataSetInfo()
'BAD PERFORMANCE: Use string-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)
End Sub

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

'These determine the size of the test
Const DUMMY_ROWS_OF_DATA As Integer = 100
Const NUMBER_TEST_ITERATIONS As Integer = 500
Const TABLE_NAME_PASSENGERINFO As String = "CustomerTravelInfo"
Const TEST_CREDIT_CARD As String = "IvoCard-987-654-321-000"
Private m_data_creditCards() As String
Private m_data_names() As String
Private m_data_travelDates() As System.DateTime
'-----------------------------------------------------------
'Creates the arrays of data (instead of imports a data set)
'-----------------------------------------------------------
Private Sub createDataSet()
'===========================================
'1. Create the space for our data
'===========================================
ReDim m_data_creditCards(DUMMY_ROWS_OF_DATA)
ReDim m_data_names(DUMMY_ROWS_OF_DATA)
ReDim m_data_travelDates(DUMMY_ROWS_OF_DATA)
'------------------------------------
'Add the rows of data
'------------------------------------
Dim buildTestString As System.Text.StringBuilder
buildTestString = New System.Text.StringBuilder
Dim addItemsCount As Integer
For addItemsCount = 0 To DUMMY_ROWS_OF_DATA
'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 data set column value
buildTestString.Length = 0
buildTestString.Append("IvoCard-000-000-0000-")
buildTestString.Append(addItemsCount)
m_data_creditCards(addItemsCount) = _
buildTestString.ToString()
Next
'Add the item we want to search for in our test.
'Pick a day for the first data set column value
m_data_travelDates(DUMMY_ROWS_OF_DATA) = _
System.DateTime.Today
'A string for the second data set 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 Sub
'------------------------------------------------
'Run the test.
'------------------------------------------------
Sub changeDayOfTravel_test()
'Show wait cursor
System.Windows.Forms.Cursor.Current = _
System.Windows.Forms.Cursors.WaitCursor
'Start at a known date.
Dim newDate As System.DateTime
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 testNumber As Integer = 0
'Start the timer running for the test
PerformanceSampling.StartSample(testNumber, _
"Custom Array implementation")
'Run the test!
Dim testCount As Integer
For testCount = 1 To NUMBER_TEST_ITERATIONS
'Move the date forward one day
newDate = newDate.AddDays(1)
Dim numberRecordsChanged As Integer
'Look up all names' import STRINGS
numberRecordsChanged = _
changeDayOfTravel_CustomArrays( _
TEST_CREDIT_CARD, newDate)
'Make sure the test is running as expected.
If (numberRecordsChanged <> 1) Then
MsgBox("No matching records found. Test aborted!")
Return
End If
Next
'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
Dim runInfo As String = NUMBER_TEST_ITERATIONS.ToString() + _
"x" + DUMMY_ROWS_OF_DATA.ToString() + ": "
MsgBox(runInfo + _
PerformanceSampling.GetSampleDurationText(testNumber))
End Sub
Private Function changeDayOfTravel_CustomArrays( _
ByVal creditCardNumber As String, ByVal newTravelDate _
As System.DateTime) As Integer
Dim numberRecordsChanged As Integer
'Look at each item in the array
Dim index As Integer
For index = 0 To DUMMY_ROWS_OF_DATA
Dim currentCreditCard As String
currentCreditCard = m_data_creditCards(index)
'If there is a match, update the record
If (creditCardNumber = currentCreditCard) Then
'Change the date of travel
Dim currentTravelDate As System.DateTime = _
m_data_travelDates(index)
'Only count the update if the date does not match
If (currentTravelDate <> newTravelDate) Then
m_data_travelDates(index) = _
newTravelDate
numberRecordsChanged = numberRecordsChanged + 1
End If
End If
Next
'Return the number of records we updated
Return numberRecordsChanged
End Function
Private Sub buttonRunTest_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles buttonRunTest.Click
createDataSet()
changeDayOfTravel_test()
End Sub

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

'Creates the database
Private Sub buttonCreateDatabase_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles buttonCreateDatabase.Click
DatabaseAccess.CreateAndFillDatabase()
End Sub
'Loads the data from the database and displays it
Private Sub buttonLoadGameData_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles buttonLoadGameData.Click
'Clear the text box
TextBox1.Text = "
'Load the data for the words
GameData.InitializeGameVocabulary()
'Go through each of the words and add it our text list
Dim thisStringBuilder As System.Text.StringBuilder
thisStringBuilder = New System.Text.StringBuilder
Dim thisWord As VocabularyWord
For Each thisWord In GameData.AllWords
thisStringBuilder.Append(thisWord.EnglishWord)
thisStringBuilder.Append(" = ")
thisStringBuilder.Append( _
thisWord.GermanWordWithArticleIfExists)
thisStringBuilder.Append(vbCrLf) 'New line
Next
'Show the list of word in the text box
TextBox1.Text = thisStringBuilder.ToString()
End Sub

Listing 14.6. Custom Data Management SampleCode for DatabaseAccess.cs

Option Strict On
'------------------------------------------------------------
'Database access code: This class manages our database access
'------------------------------------------------------------
Imports System
Friend Class DatabaseAccess
Const DATABASE_NAME As String = "LearnGerman.sdf"
Const CONNECT_STRING As String = _
"Data Source = " + DATABASE_NAME + "; Password = ''"
Const TRANSLATIONTABLE_NAME As String = "TranslationDictionary"
Const TRANSLATIONTABLE_ENGLISH_COLUMN As String = "EnglishWord"
Const TRANSLATIONTABLE_GERMAN_COLUMN As String = "GermanWord"
Const TRANSLATIONTABLE_GERMANGENDER_COLUMN As String = _
"GermanGender"
Const TRANSLATIONTABLE_WORDFUNCTION_COLUMN As String = _
"WordFunction"
Friend Const DS_WORDS_COLUMNINDEX_ENGLISHWORD As Integer = 0
Friend Const DS_WORDS_COLUMNINDEX_GERMANWORD As Integer = 1
Friend Const DS_WORDS_COLUMNINDEX_GERMANGENDER As Integer = 2
Friend Const DS_WORDS_COLUMNINDEX_WORDFUNCTION As Integer = 3
Public Shared Function GetListOfWords() As _
System.Data.IDataReader
Dim conn As System.Data.SqlServerCe.SqlCeConnection = Nothing
conn = New System.Data.SqlServerCe.SqlCeConnection( _
CONNECT_STRING)
conn.Open()
Dim cmd As System.Data.SqlServerCe.SqlCeCommand = _
conn.CreateCommand()
cmd.CommandText = "select " + _
TRANSLATIONTABLE_ENGLISH_COLUMN + ", " _
+ TRANSLATIONTABLE_GERMAN_COLUMN + ", " _
+ TRANSLATIONTABLE_GERMANGENDER_COLUMN + ", " _
+ TRANSLATIONTABLE_WORDFUNCTION_COLUMN + " " _
+ "from " + TRANSLATIONTABLE_NAME
'Execute the database command
Dim myReader As System.Data.SqlServerCe.SqlCeDataReader = _
cmd.ExecuteReader(System.Data.CommandBehavior.SingleResult)
Return myReader
End Function
'--------------------------------------------------
'Creates a database if needed
'--------------------------------------------------
Public Shared Sub CreateDatabaseIfNonExistant()
If (System.IO.File.Exists(DATABASE_NAME) = False) Then
CreateAndFillDatabase()
End If
End Sub
'--------------------------------------------------
'Creates and fills a database
'--------------------------------------------------
Public Shared Sub CreateAndFillDatabase()
'Delete the database if it's there
If (System.IO.File.Exists(DATABASE_NAME)) Then
System.IO.File.Delete(DATABASE_NAME)
End If
'Create a new database
Dim sqlCeEngine As System.Data.SqlServerCe.SqlCeEngine
sqlCeEngine = New System.Data.SqlServerCe.SqlCeEngine( _
CONNECT_STRING)
sqlCeEngine.CreateDatabase()
'-------------------------------------------------
'Try to connect to the database
'and populate it with data
'-------------------------------------------------
Dim conn As System.Data.SqlServerCe.SqlCeConnection = Nothing
Try
conn = New System.Data.SqlServerCe.SqlCeConnection( _
CONNECT_STRING)
conn.Open()
Dim cmd As System.Data.SqlServerCe.SqlCeCommand = _
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 eTableCreate As System.Exception
MsgBox("Error occured adding table :" + eTableCreate.ToString())
Finally
'Always close the database when we are done
conn.Close()
End Try
'Tell the user
MsgBox("Created language database!")
End Sub
Private Shared Sub FillDictionary( _
ByVal cmd As System.Data.SqlServerCe.SqlCeCommand)
'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)
End Sub
'------------------------------------------------
'Inserts a word into the database
'------------------------------------------------
Private Shared Sub InsertEnglishGermanWordPair( _
ByVal cmd As System.Data.SqlServerCe.SqlCeCommand, _
ByVal englishWord As String, ByVal germanWord As String, _
ByVal germanWordGender As VocabularyWord.WordGender, _
ByVal wordFunction As VocabularyWord.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(+ _
CType(germanWordGender, Integer)) + "', '" _
+ System.Convert.ToString(CType(wordFunction, Integer)) _
+ "')"
cmd.ExecuteNonQuery()
End Sub
End Class

Listing 14.7. Custom Data Management SampleCode for GameData.cs

Option Strict On
'---------------------------------------------------------
'In-memory data management code
'
'This code manages the in-memory representation of the code
'----------------------------------------------------------
Imports System
Friend Class GameData
'Array-Lists to store the data we have loaded in
Private Shared m_vocabularyWords_All As _
System.Collections.ArrayList
Private Shared m_vocabularyWords_Nouns As _
System.Collections.ArrayList
Private Shared m_vocabularyWords_Verbs As _
System.Collections.ArrayList
Private Shared m_vocabularyWords_Adjectives As _
System.Collections.ArrayList
Private Shared m_vocabularyWords_Adverbs As _
System.Collections.ArrayList
Private Shared m_vocabularyWords_Prepositions As _
System.Collections.ArrayList
Public Shared ReadOnly Property _
isGameDataInitialized() As Boolean
Get
'The game data is initialized if we have loaded the words
Return Not (m_vocabularyWords_All Is Nothing)
End Get
End Property
'Returns the collection of all words we have
Public Shared ReadOnly Property _
AllWords() As System.Collections.ArrayList
Get
'If the data has not been initialized, load it
If (m_vocabularyWords_All Is Nothing) Then
InitializeGameVocabulary()
End If
Return m_vocabularyWords_All
End Get
End property
'Returns the collection of all nouns we have
Public Shared ReadOnly Property _
Nouns() As System.Collections.ArrayList
Get
'If the data has not been initialized, load it
If (m_vocabularyWords_Nouns Is Nothing) Then
InitializeGameVocabulary()
End If
Return m_vocabularyWords_Nouns
End Get
End Property
'==========================================================
'Loads the data in from our database
'==========================================================
Public Shared Sub 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
Dim dataReader As System.Data.IDataReader
dataReader = DatabaseAccess.GetListOfWords()
Dim newWord As VocabularyWord
'Go through all of the records
While (dataReader.Read())
Dim thisword_gender As VocabularyWord.WordGender
Dim thisword_function As VocabularyWord.WordFunction
thisword_gender = CType(dataReader.GetInt32( _
DatabaseAccess.DS_WORDS_COLUMNINDEX_GERMANGENDER), _
VocabularyWord.WordGender)
thisword_function = CType(dataReader.GetInt32( _
DatabaseAccess.DS_WORDS_COLUMNINDEX_WORDFUNCTION), _
VocabularyWord.WordFunction)
'Place the data for the word we just read into a class
newWord = New VocabularyWord( _
dataReader.GetString( _
DatabaseAccess.DS_WORDS_COLUMNINDEX_ENGLISHWORD), _
dataReader.GetString( _
DatabaseAccess.DS_WORDS_COLUMNINDEX_GERMANWORD), _
thisword_gender, thisword_function)
'Add the new word to the array list
m_vocabularyWords_All.Add(newWord)
'Words can belong to more than one group, so check
'imports a logical AND to see if the word meets a
'given category
If ((newWord.getWordFunction And _
VocabularyWord.WordFunction.Noun) <> 0) Then
m_vocabularyWords_Nouns.Add(newWord)
End If
If ((newWord.getWordFunction And _
VocabularyWord.WordFunction.Verb) <> 0) Then
m_vocabularyWords_Verbs.Add(newWord)
End If
If ((newWord.getWordFunction And _
VocabularyWord.WordFunction.Adjective) <> 0) Then
m_vocabularyWords_Adjectives.Add(newWord)
End If
If ((newWord.getWordFunction And _
VocabularyWord.WordFunction.Adverb) <> 0) Then
m_vocabularyWords_Adverbs.Add(newWord)
End If
If ((newWord.getWordFunction And _
VocabularyWord.WordFunction.Preposition) <> 0) Then
m_vocabularyWords_Prepositions.Add(newWord)
End If
End While
'Close the data reader
dataReader.Close()
End Sub
End Class

Listing 14.8. Custom Data Management SampleCode for VocabularyWord.cs

Option Strict On
Imports System
'-----------------------------------------------
'Holds the data for a vocabulary word
'-----------------------------------------------
Friend Class VocabularyWord
<System.FlagsAttribute()> _
Public Enum WordFunction
Noun = 1
Verb = 2
Pronoun = 4
Adverb = 8
Adjective = 16
Preposition = 32
Phrase = 64
End Enum
Public Enum WordGender
notApplicable = 0
Masculine = 1
Feminine = 2
Neuter = 3
End Enum
Private m_englishWord As String
Private m_germanWord As String
Private m_germanGender As VocabularyWord.WordGender
Private m_wordFunction As VocabularyWord.WordFunction
Public ReadOnly Property EnglishWord() As String
Get
Return m_englishWord
End Get
End Property
Public ReadOnly Property GermanWord() As String
Get
Return m_germanWord
End Get
End Property
Public ReadOnly Property getWordFunction() As WordFunction
Get
Return m_wordFunction
End Get
End Property
Public ReadOnly Property getWordGender() As WordGender
Get
Return m_germanGender
End Get
End Property
'---------------------------------------------------------
'Returns the German word, prefixed with an article
'(e.g. 'der', 'die', 'das' if it exists)
'---------------------------------------------------------
Public ReadOnly Property GermanWordWithArticleIfExists() _
As String
Get
If (m_germanGender = WordGender.notApplicable) Then
Return Me.GermanWord
End If
Return Me.GenderArticle + " " + Me.GermanWord
End Get
End Property
Public ReadOnly Property GenderArticle() As String
Get
Select Case (m_germanGender)
Case WordGender.Masculine
Return "der"
Case WordGender.Feminine
Return "die"
Case WordGender.Neuter
Return "das"
End Select
Return "
End Get
End Property
Public Sub New(ByVal enlgishWord As String, ByVal germanWord _
As String, ByVal germanGender As WordGender, _
ByVal wordFunction As WordFunction)
m_englishWord = enlgishWord
m_germanWord = germanWord
m_germanGender = germanGender
m_wordFunction = wordFunction
End Sub
End Class