For years as developers we have tried to master CRUD (create, read, update, delete) when it comes to handling data. For the record CRUD is very outdated and actually incorrect. It should be:
CRRUDS
C – creating
R – reading
R – reporting
U – updating
D – deleting
S – searching
Remember you heard it hear first. (and I searched for the term and it doesn’t exist in this context)
Today I want to focus on searching within an application. Searching data is one of the things we take for granted but for some reason it is left out of many applications because developers think it is some magical thing that only Google and Bing can do.
The truth is if you have SQL Server you are just a few minutes away from leveraging it as well. Let’s first setup our database to handle what we call “Full-Text Searching”. I’ll show you how to set this up through Microsoft SQL Server Management Studio and then we will write some code leveraging RIA Services and Silverlight to search a TeamMember table in our application.
Setting Up SQL Server for Full Text Searching
Before we can get started we need to setup SQL Server to take advantage of searching. There are numerous articles on how Full-Text search works including the architecture, querying and so on. You should review those as it covers this in more detail. Here is a walk-through on setting this up in SQL Server Management Studio.
Step 1:
Connect to your database and drill down into Storage->Full Text Catalogs. Right click the “Full Text Catalogs” node and press “New Full-Text Catalog”.
Step 2:
Enter the name of your Full-text catalog name. I’m calling mine “TeamMemberSearch”. Set it as the default catalog and take the defaults on “Accent Sensitivity” which indicates whether the catalog is sensitive or insensitive to diacritics.
Step 3:
Double click on your new catalog to open the properties window. There are a lot of things in here such as actions to rebuild the catalog and check on the population status of the index. On the left side we are interested in “Tables/Views”. Click that.
Step 4:
Since the goal for this example is to search in TeamMember data in our Silverlight application let’s add that table to the catalog. All available columns will be checked. Now obviously we are searching “text” so any columns that aren’t text based will not be selected (int, long, boolean, etc).
That’s it, now click OK. Depending on the size of your table you may want to go back to the General properties of the catalog and check on the population status. The table I’m using has about 20,000 rows in it and populates pretty quickly.
We are now done in SQL Server Management Studio and can focus on making the search work in our Silverlight application.
Using RIA Services and Entity Framework with Full-Text Searching
Now to the good part, the coding part. Here is what our front-end Silverlight application looks like and where we want to allow our end-users to search. (more about this application at the bottom)
The idea is simple. As a user types into the “Search Team Members” field we want to display in real-time results from their typing. We need to take what they’ve entered, send that across the wire from the Silverlight application (we’ll use RIA Services), make sure we handle the asynchronous nature to make sure the search is canceled if they type another character before the search returns and then display the results. Got it? Good. Here we go.
Creating Our Model
In this example I have an existing Entity Framework database model. However, I’m not going to use it but I am going to use the data context of Entity Framework because it allows us to write a standard SQL Query and pass it to Entity Framework and wire it up to a model.
I know in the drop down I want to show the picture of the person I’m searching for and some basic information (phone, team, etc). So I’ll account for that in the model.
I’m going to create a POCO object and the only thing different I’m going to do to this is add a “Key” attribute to the Id property of the class.
1: public partial class FullTextSearchResult
2: {
3: [Key] // very important, won't work without it
4: public int Id { get; set; }
5: public string AdjustedFirstName { get; set; }
6: public string AdjustedLastName { get; set; }
7: public string MaidenName { get; set; }
8: public string FullJobTitle { get; set; }
9: public string PictureURL { get; set; }
10: public string SubTeam { get; set; }
11: public string Team { get; set; }
12: public string Location { get; set; }
13: public long? WorkPhone { get; set; }
14: public string IsActive { get; set; }
15: }
If you are curious what happens if you leave the [Key] attribute error off, you’ll get this lovely error:
Error 157 The type ‘NilliusFilius.Web.Models.FullTextSearchResult’ cannot be used as type parameter ‘TEntity’ in the generic type or method ‘System.ServiceModel.DomainServices.Client.LoadOperation<TEntity>’. There is no implicit reference conversion from ‘NilliusFilius.Web.Models.FullTextSearchResult’ to ‘System.ServiceModel.DomainServices.Client.Entity’. C:\Dev\NilliusFilius\NilliusFilius\Controls\SearchControl.xaml.cs 45 26 NilliusFilius
You’ll notice the namespace complaining is DomainServices (aka RIA Services) which we will be using next to return this object from a query.
Creating Our Function in RIA Services
While I don’t have time in this article to cover what RIA Services is basically what it does for us is allow us to add functions to handle CRRUDS calls in our application. While we could use something like a plain WCF service, the nice thing about RIA Services is we don’t have to update service references and deal with contracts and all of that. RIA Services will copy our code to our Silverlight application automatically and *just work*.
In our RIA Service we are going to add a function to search team members and have it return an IQuerable<FullTextSearchResult> which is the object we created earlier.
1: public IQueryable<FullTextSearchResult> SearchTeamMembers(string value)
This function takes one parameter which will be what the user is searching for.
Here’s the plan.
- take the input value and split it on spaces
- build up a list of SqlParameter objects we can pass to Entity Framework
- dynamically build a string that will query our Full-Text catalog
- invoke this query through current ObjectContext of EntityFramework using the ExecuteQuery() which will automatically map our FullTextSearchResult object from this dynamic query
Here is the finished function.
1: public IQueryable<FullTextSearchResult> SearchTeamMembers(string value)
2: {
3: List<SqlParameter> list = new List<SqlParameter>();
4: StringBuilder sb = new StringBuilder();
5: var index = 0;
6: foreach (var s in value.Split(' '))
7: {
8: sb.Append("CONTAINS(*, @p");
9: sb.Append(index);
10: sb.Append(")");
11: list.Add(new SqlParameter("p" + index++, String.Format("\"{0} *\"", s)));
12: sb.Append(" AND ");
13: }
14: sb.Remove(sb.Length - 5, 5);
15:
16:
17: string sql = String.Format("select TOP(10) CommonId, AdjustedLastName, FormerLastName as MaidenName, AdjustedFirstName, FullJobTitle, PictureURL, SubTeam, Team, Location, WorkPhone, IsActive from TeamMember where {0} ORDER BY AdjustedLastName, AdjustedFirstName ASC", sb);
18: var transOpt = new System.Transactions.TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted };
19: var transScope = new System.Transactions.TransactionScope(TransactionScopeOption.Required, transOpt);
20: using (transScope)
21: {
22: ObjectResult<FullTextSearchResult> result = this.ObjectContext.ExecuteStoreQuery<FullTextSearchResult>(sql, list.ToArray());
23: return result.AsQueryable();
24: }
25: }
Here is what is happening.
The input string is split based on spaces and then a Full-Text query using “CONTAINS” is created starting on line 6.
Next a transaction is started to avoid deadlocks and then our dynamic Full-Text query is executed and mapped to the FullTextSearchResult object and then returned from RIA Services. That is it, we are done with the backend.
Searching in Silverlight
In Silverlight I’ve made a SearchControl.xaml to handle the searching / returning part.
This control has a Popup control that will display when results are returned that also includes a picture of the team member being returned. Here is the XAML.
1: <UserControl x:Class="NilliusFilius.Controls.SearchControl"
2: xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
3: xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
4: xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
5: xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
6: xmlns:converter="clr-namespace:NilliusFilius.Converters"
7: mc:Ignorable="d" Height="50" Width="263">
8: <UserControl.Resources>
9: <converter:IFullNameConverter x:Key="IFullNameConverter"/>
10: <converter:IsActiveTeamMemberStyleConverter x:Key="IsActiveTeamMemberStyleConverter"/>
11: </UserControl.Resources>
12: <Border Background="White" Height="50" CornerRadius="5">
13: <StackPanel Orientation="Horizontal" Margin="0,0,5,-1">
14: <Image Width="38" HorizontalAlignment="Left" Source="/Images/searchMembersIcon.png" Margin="8,4,0,6"/>
15: <Canvas x:Name="z" Margin="30,1,0,0" Width="163">
16: <TextBox Height="26" Name="textBox2" Width="205" Text="{Binding Path=ApplicationStrings.SearchBoxText, Source={StaticResource ResourceWrapper}}" GotFocus="textBox2_GotFocus" TextChanged="textBox2_TextChanged" DataContext="{Binding}" Canvas.Left="-25" KeyDown="textBox2_KeyDown" BorderThickness="0" IsTabStop="True" LostFocus="textBox2_LostFocus" FontSize="14.667" Canvas.Top="11" FontStyle="Italic" Style="{StaticResource TextBoxNoHighlightBorder}" />
17: <Popup x:Name="searchPopUp" IsOpen="False" VerticalOffset="65" HorizontalOffset="-288" MouseLeave="searchPopUp_MouseLeave" MouseEnter="searchPopUp_MouseEnter" LostFocus="searchPopUp_LostFocus" KeyDown="searchPopUp_KeyDown" Height="61" Width="246" Canvas.Top="49" Canvas.Left="-75">
18: <ListBox Margin="286,-65,0,0" Name="lbSearchResults" Width="265" Background="#FF7C7373" DataContext="{Binding}" Visibility="Visible" Canvas.Left="-119" Canvas.Top="-11">
19: <ListBox.ItemTemplate>
20: <DataTemplate>
21: <Button Style="{Binding IsActive, Converter={StaticResource IsActiveTeamMemberStyleConverter}}" Width="250" Height="100" HorizontalContentAlignment="Left" Tag="{Binding CommonId}" Click="Button_Click" ClickMode="Press" Cursor="Hand">
22: <Button.Content>
23: <StackPanel Orientation="Horizontal" HorizontalAlignment="Left" VerticalAlignment="Center">
24: <Image Source="{Binding PictureURL}" Width="75" VerticalAlignment="Top">
25: <Image.Clip>
26: <RectangleGeometry RadiusX="4" RadiusY="4" Rect="0,0,75,90" />
27: </Image.Clip>
28: </Image>
29: <StackPanel Orientation="Vertical" VerticalAlignment="Stretch">
30: <TextBlock Text="{Binding Converter={StaticResource IFullNameConverter}}" FontSize="12" Margin="3,0,0,0" />
31: <TextBlock Text="{Binding FullJobTitle}" FontSize="10" Margin="3,0,0,0" />
32: <TextBlock Text="{Binding Team}" FontSize="10" Margin="3,0,0,0" />
33: <TextBlock Text="{Binding SubTeam}" FontSize="10" Margin="3,0,0,0" />
34: <TextBlock Text="{Binding Location}" FontSize="10" Margin="3,0,0,0" />
35: <TextBlock Text="{Binding WorkPhone, StringFormat=\{0:(###) ###-####\}}" FontSize="10" Margin="3,0,0,0" />
36: </StackPanel>
37: </StackPanel>
38:
39: </Button.Content>
40: </Button>
41: </DataTemplate>
42: </ListBox.ItemTemplate>
43: </ListBox>
44: </Popup>
45: </Canvas>
46: </StackPanel>
47: </Border>
48: </UserControl>
In the code behind we are going to watch for the TextChanged event to fire and then query through RIA Services. Remember that we need to make sure that as a user is typing we need to cancel a previous query so we do not get any errors. Here’s the code.
1: private void textBox2_TextChanged(object sender, TextChangedEventArgs e)
2: {
3: if (textBox2.Text.Length > 1)
4: {
5: if (loadOperation != null && loadOperation.IsComplete == false)
6: {
7: loadOperation.Cancel();
8: }
9: _state = new Object();
10: _context.FullTextSearchResults.Clear();
11: EntityQuery<FullTextSearchResult> query = _context.SearchTeamMembersQuery(textBox2.Text);
12: loadOperation = _context.Load(query, LoadBehavior.RefreshCurrent, searchTeamMembers_Completed, _state);
13: }
14: else
15: {
16: searchPopUp.IsOpen = false;
17: }
18: }
On line 3 we make sure the user has entered more than one character in the search box before searching. Next we check to see if the loadOperation is completed or needs to be canceled. Remember we are in the TextChanged event so if a user presses another character while they are searching we don’t want to wait on the previous search to return instead we want it canceled so we can issue another one.
After that we issue the query and tell it to respond to us back on the function searchTeamMembers_Completed. Here is the completed code that runs after the result is returned.
1: void searchTeamMembers_Completed(LoadOperation<FullTextSearchResult> loadOperation)
2: {
3: if (loadOperation.IsComplete)
4: {
5: if (textBox2.Text != ApplicationStrings.SearchBoxText)
6: {
7: LoggerWrapper logger = new LoggerWrapper();
8: logger.Log("Quick search for team members: " + textBox2.Text, NilliusFiliusService.LogLevel.Info);
9: }
10: }
11: if (loadOperation.Entities.Count() > 0)
12: {
13: searchPopUp.IsOpen = true;
14: }
15: }
The main thing that happens is we set the searchPopup to open if there are records found, otherwise it will remain closed.
Here’s the pay off and the end result, it works!
I took this a step further and parsed all of the phone numbers out into a varchar column in SQL Server and added that column to the Full-Text catalog so the extensions could also be searched.
And since the About column is indexed anything in a user’s profile works. In my profile I mentioned Deep Fried Bytes (our podcast) so searching for “deep fried” returns my information.
Full-Text searching rocks needless to say. But when leveraging RIA Services, Entity Framework and Silverlight the experience is really nice.
About The Application
By the way, the application called “Johnny” above will be released soon as a full blown open source project so stay tuned. I’ll update this post when the full source code to this project is available.
Until then, happy searching! – The Elder
