Baby Name Eliminator (Local Databases & Embedded Resources)

1
253

Baby Name Eliminator provides the perfect technique for Type A personalities to name their babies. (It’s the technique my wife and I used to name our two sons!) Rather than trying to brainstorm names and worrying that you’re missing the perfect one, this app enables you to use the process of elimination to name your baby!

Baby Name Eliminator starts with a massive database of essentially every name ever used in the United States: 36,065 boy names and 60,438 girl names. After you choose a gender, the app enables you to quickly narrow down the list with a variety of filters. These filters are based on the popularity of each name, its starting/ending letter, and the year the name was first in use. Once you’ve finished filtering the list, you can eliminate names one-by-one until your decision is made.

When naming our sons, we went through several rounds, eliminating names that were obviously bad and leaving names that we had any hesitation about. Once we got down to about 20 names, my wife and I each picked our top 5 choices. With our first son, we only had one name in common, so our decision was made! If you and your spouse both have a Windows phone, independently eliminating names can be a fun way to come up with a final list of candidate names.

So where does this massive database of names come from? The Social Security Administration, which provides data about almost every first name used in a Social Security card application from 1880 to the present. There are a few caveats to this list:

  • For privacy reasons, only names used at least five times in any given year are included.
  • One-character names are excluded.
  • Many people born before 1937 never applied for a Social Security card, so data from these years is spotty.
  • Distinct spellings of the same name are treated as different names.
  • The data is raw and uncorrected. Sometimes the sex on an application is incorrect, causing some boy names to show up in the girl names list and vice versa. In addition, some names are recorded as “Unknown,” “Unnamed,” or “Baby.” Restricting your list to the top 1,000 or so names in any year generally gets rid of such artifacts.

To enable its filtering, this app makes use of two local databases—one for boy names and one for girl names.

Working with Local Databases

The lack of local database support in Windows Phone 7 is one of its more publicized shortcomings. Apps are encouraged to work with server-side databases instead, but this adds extra burden for developers and extra hassle for users (latency, a working data connection, and potential data charges). Fortunately, several third-party database options exist. My favorite is an open-source port of SQLite for Windows Phone 7 created by Dan Ciprian Ardelean. You can read about it at http://sviluppomobile.blogspot.com/ 2010/03/sqlite-for-wp-7-series-proof-of-concept.html and get the latest version (at the time of this writing) at http://www.neologics.eu/Dan/WP7_Sqlite_20.09.2010.zip. This includes C# source code and a Community.CsharpSqlite.WP.dll assembly that you can reference in your project. It’s certainly not bug-free, but it works quite well for a number of scenarios (such as the needs of this app).

SQLite for Windows Phone 7 reads from and writes to database files in isolated storage. If you want to ship a database with your app that’s already filled with data, you can include the database file in your project with a Build Action of Content. At run-time, your app can retrieve the file then save it to isolated storage before its first use of SQLite.

How can I create a .db file that contains the database I want to ship with my app?

I followed the somewhat-cumbersome approach of writing a Windows Phone app that

  1. Uses SQLite to generate the database, executing CREATE TABLE and INSERT commands
  2. Retrieves the raw bytes from the .db file saved by SQLite to isolated storage, using thenormal isolated storage APIs
  3. Copies the bytes from the Visual Studio debugger as a Base64-encoded string and saves them to the needed .db file with a separate (desktop) program that decodes the string

Listing 24.1 contains a DatabaseHelper class used by Baby Name Eliminator that handles all interaction with the two SQLite databases included in the app.

LISTING 24.1 DatabaseHelper.cs—A Class That Wraps SQLite

[code]

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.IO;
using System.IO.IsolatedStorage;
using System.Windows;
using System.Windows.Resources;
using SQLiteClient;
namespace WindowsPhoneApp
{
public class DatabaseHelper
{
// The name of the file included as content in this project,
// also used as the isolated storage filename
public static string DatabaseName { get; set; }
// “Load” the database. If the file does not yet exist in isolated storage,
// copy it from the original file. If the file already exists,
// this is a no-op.
public static void LoadAsync(Action callback)
{
BackgroundWorker worker = new BackgroundWorker();
worker.DoWork += delegate(object sender, DoWorkEventArgs e)
{
if (!HasLoadedBefore)
{
StreamResourceInfo info = Application.GetResourceStream(
new Uri(DatabaseName, UriKind.Relative));
using (info.Stream)
SaveFile(DatabaseName, info.Stream);
}
if (callback != null)
callback();
};
worker.RunWorkerAsync();
}
// Retrieve a single value from the database
public static void ExecuteScalar(string command, Action<object> onSuccess,
Action<Exception> onError = null)
{
BackgroundWorker worker = new BackgroundWorker();
worker.DoWork += delegate(object sender, DoWorkEventArgs e)
{
try
{
object result = null;
using (SQLiteConnection db = new SQLiteConnection(DatabaseName))
{
db.Open();
SQLiteCommand c = db.CreateCommand(command);
result = c.ExecuteScalar();
}
if (onSuccess != null)
onSuccess(result);
}
catch (Exception ex)
{
if (onError != null)
onError(ex);
}
};
worker.RunWorkerAsync();
}
// Retrieve a collection of items from the database
public static void ExecuteQuery<T>(string command,
Action<IEnumerable<T>> onSuccess,
Action<Exception> onError = null) where T : new()
{
BackgroundWorker worker = new BackgroundWorker();
worker.DoWork += delegate(object sender, DoWorkEventArgs e)
{
try
{
IEnumerable<T> result = null;
List<T> copy = new List<T>();
using (SQLiteConnection db = new SQLiteConnection(DatabaseName))
{
db.Open();
SQLiteCommand c = db.CreateCommand(command);
result = c.ExecuteQuery<T>();
// Copy the data, because enumeration only
// works while the connection is open
copy.AddRange(result);
}
if (onSuccess != null)
onSuccess(copy);
}
catch (Exception ex)
{
if (onError != null)
onError(ex);
}
};
worker.RunWorkerAsync();
}
public static bool HasLoadedBefore
{
get
{
using (IsolatedStorageFile userStore =
IsolatedStorageFile.GetUserStoreForApplication())
return userStore.FileExists(DatabaseName);
}
}
// Save a stream to isolated storage
static void SaveFile(string filename, Stream data)
{
using (IsolatedStorageFile userStore =
IsolatedStorageFile.GetUserStoreForApplication())
using (IsolatedStorageFileStream stream = userStore.CreateFile(filename))
{
// Get the bytes
byte[] bytes = new byte[data.Length];
data.Read(bytes, 0, bytes.Length);
// Write the bytes to the new stream
stream.Write(bytes, 0, bytes.Length);
}
}
}
}

[/code]

  • To enable a responsive user interface while expensive database operations are conducted, interaction with SQLite is done on a background thread with the help of BackgroundWorker, and success/failure is communicated via callbacks.
  • The command strings passed to ExecuteScalar and ExecuteQuery can be SQL commands like SELECT COUNT(*) FROM table.
  • ExecuteQuery is a generic method whose generic argument (T) must be a class with a property corresponding to each column selected in the query.

Application.GetResourceStream works with files included in your project with a Build Action of Content or with a Build Action of Resource. For the latter case, the passed-in URI must have the following syntax:

/dllName;component/pathAndFilename

Note that dllName can refer to any DLL inside the .xap file, as long as it contains the requested resource. It should not contain the .dll suffix.

For this app, the DatabaseName string would look as follows for the database of boy names (Boys.db) included in the root of the project as a resource rather than content:

/WindowsPhoneApp;component/Boys.db

However, if this were done, Listing 24.1’s use of SaveFile would have to change, because the DatabaseName string would no longer be a valid filename for isolated storage.

Application.GetResourceStream Versus Assembly.GetManifestResourceStream

You might stumble across the Assembly.GetManifestResourceStream API as a way to read files included with your app.This works, but only for files marked with a Build Action of Embedded Resource (not Resource).Using this in Listing 24.1 instead of Application.GetResourceStream would look as follows:

[code]

if (!HasLoadedBefore)
{
using (Stream stream = typeof(DatabaseHelper).
Assembly.GetManifestResourceStream(DatabaseName))
SaveFile(DatabaseName, stream);
}

[/code]

However, the string passed to GetManifestResourceStream has its own unique syntax: dllName.filename, where dllName is the name of the DLL containing the embedded resource. That’s because the C# compiler automatically prepends the DLL name (minus the .dll extension) to the filename when naming each embedded resource. (You can see these names by opening a DLL in a tool such as .NET Reflector.) For this app, the two valid strings would be “WindowsPhoneApp.Boys.db” and “WindowsPhoneApp.Girls.db”.

There’s no significant reason to use this approach rather than the more flexible Application. GetResourceStream. Using GetResourceStream with files included as content is generally preferable compared to either scheme with files embedded as resources, because resources increase the size of DLLs, and that can increase an app’s load time.

The Filter Page

Rather than examine this app’s main page, which you can view in the included source code, we’ll examine the filter page that makes use of the DatabaseHelper class. The filter page, shown in Figure 24.1, displays how many names are in your list then enables you to filter it further with several options that map to SQL queries performed on the database. (The choice of boy names versus girl names is done previously on the main page.)

FIGURE 24.1 The filter page supports five different types of filters.
FIGURE 24.1 The filter page supports five different types of filters.

Each button reveals a dialog or other display, shown in Figure 24.2, that enables the user to control each relevant filter. Tapping the count of names reveals the actual list of names, as shown in Figure 24.3. This list doesn’t enable interactive elimination, however, as that is handled on the main page.

FIGURE 24.2 The result of tapping each button on the filter page.
FIGURE 24.2 The result of tapping each button on the filter page.
FIGURE 24.3 Previewing the filtered list of names.
FIGURE 24.3 Previewing the filtered list of names.

Listing 24.2 contains the XAML for the filter page.

LISTING 24.2 FilterPage.xaml—The User Interface for Baby Name Eliminator’s Filter Page

[code]

<phone:PhoneApplicationPage x:Name=”Page”
x:Class=”WindowsPhoneApp.FilterPage”
xmlns=”http://schemas.microsoft.com/winfx/2006/xaml/presentation”
xmlns:x=”http://schemas.microsoft.com/winfx/2006/xaml”
xmlns:phone=”clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone”
xmlns:local=”clr-namespace:WindowsPhoneApp”
FontFamily=”{StaticResource PhoneFontFamilyNormal}”
FontSize=”{StaticResource PhoneFontSizeNormal}”
Foreground=”{StaticResource PhoneForegroundBrush}”
SupportedOrientations=”PortraitOrLandscape”>
<Grid Background=”Transparent”>
<Grid.RowDefinitions>
<RowDefinition Height=”Auto”/>
<RowDefinition Height=”*”/>
</Grid.RowDefinitions>
<!– The standard header –>
<StackPanel Style=”{StaticResource PhoneTitlePanelStyle}”>
<TextBlock Text=”BABY NAME ELIMINATOR”
Style=”{StaticResource PhoneTextTitle0Style}”/>
<TextBlock Text=”apply filters”
Style=”{StaticResource PhoneTextTitle1Style}”/>
</StackPanel>
<ScrollViewer Grid.Row=”1”>
<Grid Margin=”12,0”>
<Grid.RowDefinitions>
<RowDefinition/>
<RowDefinition/>
<RowDefinition/>
<RowDefinition/>
<RowDefinition/>
<RowDefinition/>
</Grid.RowDefinitions>
<!– The current number of names –>
<StackPanel Background=”Transparent” local:Tilt.IsEnabled=”True”
MouseLeftButtonUp=”Preview_Click”>
<TextBlock Text=”Current # of names (tap to preview):”
HorizontalAlignment=”Center”
Style=”{StaticResource LabelStyle}”/>
<TextBlock x:Name=”NumberTextBlock” Text=”0” Margin=”0,-16,0,0”
HorizontalAlignment=”Center”
FontSize=”{StaticResource PhoneFontSizeExtraExtraLarge}”/>
</StackPanel>
<!– Progress indicator while a query is running –>
<Grid x:Name=”ProgressPanel”>
<Rectangle Fill=”{StaticResource PhoneBackgroundBrush}” Opacity=”.9”/>
<ProgressBar x:Name=”ProgressBar” VerticalAlignment=”Top”/>
<TextBlock x:Name=”ProgressText” TextWrapping=”Wrap”
HorizontalAlignment=”Center”
VerticalAlignment=”Top” Margin=”0,60,0,0” Text=”Loading”/>
</Grid>
<!– The five filter buttons –>
<ToggleButton x:Name=”RankMaxButton” Grid.Row=”1”
Content=”eliminate low-ranked names”
local:Tilt.IsEnabled=”True” Click=”RankMaxButton_Click”/>
<ToggleButton x:Name=”NameStartButton” Grid.Row=”2”
Content=”eliminate names starting with…”
local:Tilt.IsEnabled=”True” Click=”NameStartButton_Click”/>
<ToggleButton x:Name=”NameEndButton” Grid.Row=”3”
Content=”eliminate names ending with…”
local:Tilt.IsEnabled=”True” Click=”NameEndButton_Click”/>
<ToggleButton x:Name=”YearMaxButton” Grid.Row=”4”
Content=”eliminate modern names”
local:Tilt.IsEnabled=”True” Click=”YearMaxButton_Click”/>
<ToggleButton x:Name=”YearMinButton” Grid.Row=”5”
Content=”eliminate old-fashioned names”
local:Tilt.IsEnabled=”True” Click=”YearMinButton_Click”/>
<!– A user control that displays the letter grid in a popup –>
<local:LetterPicker x:Name=”LetterPicker”
Page=”{Binding ElementName=Page}”
Closed=”LetterPicker_Closed”/>
</Grid>
</ScrollViewer>
<!– Eliminate low-ranked names dialog –>
<local:Dialog x:Name=”RankMaxDialog” Grid.RowSpan=”2” Closed=”Dialog_Closed”>
<local:Dialog.InnerContent>
<StackPanel>
<TextBlock Text=”…” TextWrapping=”Wrap” Margin=”11,5,0,-5”/>
<TextBox MaxLength=”5” InputScope=”Number”
Text=”{Binding Result, Mode=TwoWay}”/>
<TextBlock Text=”Enter a number, or leave blank to clear this filter.”
TextWrapping=”Wrap” Margin=”11,-10,0,-10”
Foreground=”{StaticResource PhoneSubtleBrush}”/>
</StackPanel>
</local:Dialog.InnerContent>
</local:Dialog>
<!– Eliminate modern names dialog –>
<local:Dialog x:Name=”YearMaxDialog” Grid.RowSpan=”2” Closed=”Dialog_Closed”>
<local:Dialog.InnerContent>
<StackPanel>
<TextBlock TextWrapping=”Wrap” Margin=”11,5,0,-5”>

</TextBlock>
<TextBox MaxLength=”4” InputScope=”Number”
Text=”{Binding Result, Mode=TwoWay}”/>
<TextBlock Text=”…” TextWrapping=”Wrap” Margin=”11,-10,0,-10”
Foreground=”{StaticResource PhoneSubtleBrush}”/>
</StackPanel>
</local:Dialog.InnerContent>
</local:Dialog>
<!– Eliminate old-fashioned names dialog –>
<local:Dialog x:Name=”YearMinDialog” Grid.RowSpan=”2” Closed=”Dialog_Closed”>
<local:Dialog.InnerContent>
<StackPanel>
<TextBlock TextWrapping=”Wrap” Margin=”11,5,0,-5”>

</TextBlock>
<TextBox MaxLength=”4” InputScope=”Number”
Text=”{Binding Result, Mode=TwoWay}”/>
<TextBlock Text=”…” TextWrapping=”Wrap” Margin=”11,-10,0,-10”
Foreground=”{StaticResource PhoneSubtleBrush}”/>
</StackPanel>
</local:Dialog.InnerContent>
</local:Dialog>
<!– The list of names shown when tapping the current number –>
<Grid x:Name=”PreviewPane” Grid.RowSpan=”2” Visibility=”Collapsed”>
<Grid.RowDefinitions>
<RowDefinition Height=”Auto”/>
<RowDefinition Height=”*”/>
</Grid.RowDefinitions>
<Rectangle Grid.RowSpan=”2” Fill=”{StaticResource PhoneChromeBrush}”
Opacity=”.9”/>
<StackPanel Style=”{StaticResource PhoneTitlePanelStyle}”>
<TextBlock x:Name=”PreviewHeader”
Style=”{StaticResource PhoneTextTitle0Style}”/>
</StackPanel>
<ListBox Grid.Row=”1” x:Name=”PreviewListBox” Margin=”24,0,0,0”/>
</Grid>
</Grid>
</phone:PhoneApplicationPage>

[/code]

  • The five filter buttons are toggle buttons whose IsChecked state is managed by code-behind. If a filter is active, its corresponding button is checked (highlighted) so the user can see this without tapping every button and double-checking its filter settings.
  • The progress bar and related user interface, shown while a query is executing on a background thread, is shown in Figure 24.4. Because it does not occupy the whole screen, it enables the user to continue working if he or she doesn’t care to wait for the current count of names.
FIGURE 24.4 Showing progress while a database query executes on a background thread.
FIGURE 24.4 Showing progress while a database query executes on a background thread.

Listing 24.3 contains the code-behind for the filter page.

LISTING 24.3 FilterPage.xaml.cs—The Code-Behind for Baby Name Eliminator’s Filter Page

[code]

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Windows;
using System.Windows.Input;
using System.Windows.Navigation;
using Microsoft.Phone.Controls;
namespace WindowsPhoneApp
{
public partial class FilterPage : PhoneApplicationPage
{
public FilterPage()
{
InitializeComponent();
}
protected override void OnNavigatedTo(NavigationEventArgs e)
{
base.OnNavigatedTo(e);
RefreshCount();
RefreshButtons();
}
protected override void OnBackKeyPress(CancelEventArgs e)
{
base.OnBackKeyPress(e);
// If a dialog, letter picker, or preview pane is open,
// close it instead of leaving the page
if (this.RankMaxDialog.Visibility == Visibility.Visible)
{
e.Cancel = true;
this.RankMaxDialog.Hide(MessageBoxResult.Cancel);
}
else if (this.YearMaxDialog.Visibility == Visibility.Visible)
{
e.Cancel = true;
this.YearMaxDialog.Hide(MessageBoxResult.Cancel);
}
else if (this.YearMinDialog.Visibility == Visibility.Visible)
{
e.Cancel = true;
this.YearMinDialog.Hide(MessageBoxResult.Cancel);
}
else if (this.PreviewPane.Visibility == Visibility.Visible)
{
e.Cancel = true;
this.PreviewPane.Visibility = Visibility.Collapsed;
}
}
void RefreshCount()
{
// Choose one of the included databases: boy names or girl names
if (Settings.IsBoy.Value.Value)
DatabaseHelper.DatabaseName = “Boys.db”;
else
DatabaseHelper.DatabaseName = “Girls.db”;
if (!DatabaseHelper.HasLoadedBefore)
{
ShowProgress(“Preparing database for the first time…”);
this.RankMaxButton.IsEnabled = false;
this.NameStartButton.IsEnabled = false;
this.NameEndButton.IsEnabled = false;
this.YearMaxButton.IsEnabled = false;
this.YearMinButton.IsEnabled = false;
}
DatabaseHelper.LoadAsync(delegate()
{
// The callback is called on a background thread, so transition back
// to the main thread for manipulating UI
this.Dispatcher.BeginInvoke(delegate()
{
ShowProgress(“Counting names…”);
this.RankMaxButton.IsEnabled = true;
this.NameStartButton.IsEnabled = true;
this.NameEndButton.IsEnabled = true;
this.YearMaxButton.IsEnabled = true;
this.YearMinButton.IsEnabled = true;
// Execute a query
DatabaseHelper.ExecuteScalar(“SELECT COUNT(*) FROM Names “ +
Settings.BuildQuerySuffix(), delegate(object result)
{
// The callback is called on a background thread, so transition back
// to the main thread for manipulating UI
this.Dispatcher.BeginInvoke(delegate()
{
HideProgress();
this.NumberTextBlock.Text = ((int)result).ToString(“N0”);
});
});
});
});
}
void RefreshButtons()
{
// Check (highlight) any button whose filter is active
this.RankMaxButton.IsChecked =
Settings.RankMax.Value != Settings.RankMax.DefaultValue;
this.NameStartButton.IsChecked =
Settings.ExcludedStartingLetters.Value.Count > 0;
this.NameEndButton.IsChecked =
Settings.ExcludedEndingLetters.Value.Count > 0;
this.YearMaxButton.IsChecked =
Settings.YearMax.Value != Settings.YearMax.DefaultValue;
this.YearMinButton.IsChecked =
Settings.YearMin.Value != Settings.YearMin.DefaultValue;
}
void Preview_Click(object sender, MouseButtonEventArgs e)
{
this.PreviewHeader.Text = “LOADING…”;
this.PreviewListBox.ItemsSource = null;
this.PreviewPane.Visibility = Visibility.Visible;
// Choose one of the included databases: boy names or girl names
if (Settings.IsBoy.Value.Value)
DatabaseHelper.DatabaseName = “Boys.db”;
else
DatabaseHelper.DatabaseName = “Girls.db”;
DatabaseHelper.LoadAsync(delegate()
{
// It’s okay to execute this on the background thread
DatabaseHelper.ExecuteQuery<Record>(“SELECT Name FROM Names “ +
Settings.BuildQuerySuffix(), delegate(IEnumerable<Record> result)
{
// Transition back to the main thread for manipulating UI
this.Dispatcher.BeginInvoke(delegate()
{
this.PreviewHeader.Text = “PRESS BACK WHEN DONE”;
this.PreviewListBox.ItemsSource = result;
});
});
});
}
void ShowProgress(string message)
{
this.ProgressText.Text = message;
this.ProgressBar.IsIndeterminate = true;
this.ProgressPanel.Visibility = Visibility.Visible;
}
void HideProgress()
{
this.ProgressPanel.Visibility = Visibility.Collapsed;
this.ProgressBar.IsIndeterminate = false; // Avoid a perf problem
}
// A click handler for each of the five filter buttons
void RankMaxButton_Click(object sender, RoutedEventArgs e)
{
if (Settings.RankMax.Value != null)
RankMaxDialog.Result = Settings.RankMax.Value.Value;
RankMaxDialog.Show();
}
void NameStartButton_Click(object sender, RoutedEventArgs e)
{
this.LetterPicker.SetBinding(LetterPicker.ExcludedLettersProperty,
new Binding { Path = new PropertyPath(“Value”),
Source = Settings.ExcludedStartingLetters,
Mode = BindingMode.TwoWay });
this.LetterPicker.ShowPopup();
}
void NameEndButton_Click(object sender, RoutedEventArgs e)
{
this.LetterPicker.SetBinding(LetterPicker.ExcludedLettersProperty,
new Binding { Path = new PropertyPath(“Value”),
Source = Settings.ExcludedEndingLetters,
Mode = BindingMode.TwoWay });
this.LetterPicker.ShowPopup();
}
void YearMaxButton_Click(object sender, RoutedEventArgs e)
{
if (Settings.YearMax.Value != null)
YearMaxDialog.Result = Settings.YearMax.Value.Value;
YearMaxDialog.Show();
}
void YearMinButton_Click(object sender, RoutedEventArgs e)
{
if (Settings.YearMin.Value != null)
YearMinDialog.Result = Settings.YearMin.Value.Value;
YearMinDialog.Show();
}
// Two handlers for the dialog or letter picker being closed
void LetterPicker_Closed(object sender, EventArgs e)
{
RefreshCount();
RefreshButtons();
}
void Dialog_Closed(object sender, MessageBoxResultEventArgs e)
{
if (e.Result == MessageBoxResult.OK)
{
// Update or clear a setting, depending on which dialog was just closed
int result;
if (sender == RankMaxDialog)
{
if (RankMaxDialog.Result != null &&
int.TryParse(RankMaxDialog.Result.ToString(), out result))
Settings.RankMax.Value = result;
else
Settings.RankMax.Value = null;
}
if (sender == YearMaxDialog)
{
if (YearMaxDialog.Result != null &&
int.TryParse(YearMaxDialog.Result.ToString(), out result))
Settings.YearMax.Value = (short)result;
else
Settings.YearMax.Value = null;
}
if (sender == YearMinDialog)
{
if (YearMinDialog.Result != null &&
int.TryParse(YearMinDialog.Result.ToString(), out result))
Settings.YearMin.Value = (short)result;
else
Settings.YearMin.Value = null;
}
// Only bother refreshing the count if the dialog result is OK
RefreshCount();
}
// Refresh buttons when the dialog is closed for any reason,
// to undo automatic check-when-tapped
RefreshButtons();
}
}
}

[/code]

  • This project includes two databases (Boys.db and Girls.db) that have an identical schema. They contain a single table called Names with three columns: Name, BestRank (its best single-year ranking), and FirstYear (the first year the name appeared in Social Security data).
  • The query to refresh the count of names is “SELECT COUNT(*) FROM Names” with a WHERE clause based on settings whose values are determined by the filters. The settings and the BuildQuerySuffix method are defined in Listing 24.4.
  • The query to display the list of actual names is “SELECT Name FROM Names” with the same WHERE clause. The Record class used with ExecuteQuery is therefore a class with a single string Name property:

    [code]
    public class Record
    {
    public string Name { get; set; }
    public override string ToString()
    {
    return this.Name;
    }
    }
    [/code]
    The ToString method enables the collection of Records to be used as the data source for the preview list box without any item template, as the default ToStringin- a-text-block rendering is sufficient.

  • Just like the date picker in the preceding chapter, this app leverages two-way data binding with each letter picker.

LISTING 24.4 Settings.cs—The Settings Class for Baby Name Eliminator

[code]

using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Text;
using Microsoft.Phone.Controls;
namespace WindowsPhoneApp
{
public static class Settings
{
// Step 1: Gender
public static readonly Setting<bool?> IsBoy =
new Setting<bool?>(“IsBoy”, null);
// Step 2: Filters
public static readonly Setting<int?> RankMax =
new Setting<int?>(“RankMax”, null);
public static readonly Setting<List<char>> ExcludedStartingLetters =
new Setting<List<char>>(“IncludedStartingLetters”, new List<char>());
public static readonly Setting<List<char>> ExcludedEndingLetters =
new Setting<List<char>>(“ExcludedEndingLetters”, new List<char>());
public static readonly Setting<short?> YearMax =
new Setting<short?>(“YearMax”, null);
public static readonly Setting<short?> YearMin =
new Setting<short?>(“YearMin”, null);
// Step 3: Elimination
public static readonly Setting<ObservableCollection<string>> FilteredList =
new Setting<ObservableCollection<string>>(“FilteredList”, null);
public static readonly Setting<double> ScrollPosition =
new Setting<double>(“ScrollPosition”, 0);
// Orientation lock for the main page
public static readonly Setting<SupportedPageOrientation>
SupportedOrientations = new Setting<SupportedPageOrientation>(
“SupportedOrientations”, SupportedPageOrientation.PortraitOrLandscape);
// Build up a WHERE clause if any filters have been chosen
public static string BuildQuerySuffix()
{
List<string> conditions = new List<string>();
if (Settings.RankMax.Value != null)
conditions.Add(“ BestRank <= “ + Settings.RankMax.Value.Value);
foreach (char c in Settings.ExcludedStartingLetters.Value)
conditions.Add(“ NOT Name LIKE ‘“ + c + “%’”);
foreach (char c in Settings.ExcludedEndingLetters.Value)
conditions.Add(“ NOT Name LIKE ‘%” + c + “‘“);
if (Settings.YearMax.Value != null)
conditions.Add(“ FirstYear <= “ + Settings.YearMax.Value.Value);
if (Settings.YearMin.Value != null)
conditions.Add(“ FirstYear >= “ + Settings.YearMin.Value.Value);
if (conditions.Count == 0)
return “”;
else
{
StringBuilder whereClause = new StringBuilder(“WHERE “);
whereClause.Append(conditions[0]);
for (int i = 1; i < conditions.Count; i++)
whereClause.Append(“ AND “ + conditions[i]);
return whereClause.ToString();
}
}
}
}

[/code]

The Finished Product

Baby Name Eliminator (Local Databases & Embedded Resources)

 

1 COMMENT

  1. Hi ‘admin’

    In WP7.5/7.8/8 we have SQLCE now, officially. Will you continue using SQLite, a no-official DLL builded in 2010?

    In WP8/Win8 SQLite is official now! But SQLCE + LINQ2SQL working fine!

    And now, that do? =]

    Hugs from Brazil

    Marcio Maciel

Comments are closed.