*Words of Wisdom From The Elder

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

Yesterday late in the evening after an incredibly long day I had a brain fart and twittered this.

Literally a few seconds later I was bombarded with “update your .gitignore” file. 

I literally slapped my forehead and said DUH!

Why? Well it is typical for version control systems such as GIT or CVS or SVN to have ignore files, I was just being lazy and not using my brain. Probably because it had been all but used up for that day.  Anyway there were some sample .gitignore files my fellow twitters sent me to but really they didn’t do the job so I am posting a sample file to help someone else out. 

Actually this is a sample “exclude” file which is in .git/info/exclude.  The exclude file is local to the repository it is in and isn’t global.  See http://help.github.com/git-ignore/ for more details on how this works.  Here is my current file which I will keep up-to-date as I go.

#git ls-files –others –exclude-from=.git/info/exclude
# Lines that start with ‘#’ are comments.
# For a project mostly in C, the following would be a good set of
# exclude patterns (uncomment them if you want to use them):
# *.[oa]
# *~

# there are two bins here because apparently Silverlight calls the folder Bin instead of bin
# these folders should never be checked in as this contains the compiled code for configurations
bin
Bin
obj

# this is a Visual Studio user solution file, it contains your personal settings and should not be checked in
# if sharing the repo with other people, if it is just your repo you can remove this
*.suo

# this folder is created when using RIA Services with a Silverlight application, the code from one project
# is copied here, thus it shouldn’t be checked into source control
Generated_Code

# Projects can also have user settings so this ignores those files as well
*.user

This probably isn’t all inclusive just yet but as I find more things I’ll update this post.  Thanks.

Last year I received an email from the leaders of the Ann Arbor .NET User Group (AADND http://aadnd.org/) asking me if I could make the trip up to Ann Arbor, MI and speak at their 5th anniversary meeting.  I checked the calendar and quickly agreed for a chance to get back to Ann Arbor where we lived for eight years.  Thus on February 9th I’ll be in Ann Arbor speaking to the group and getting to visit with a lot of friends I haven’t seen in awhile.  Here’s what I’ll be speaking on:

Talk Title: Building the Nullius Filius of Twitter and Facebook in Silverlight, RIA Services and Entity Framework

Abstract

Let’s imagine for a minute Twitter and Facebook had a passionate love affair and produced a child (nillius filius) named Johnny. Although Johnny was born from web-based parents there was a genetic defect that caused Johnny to be born a Rich Internet Application (RIA). In this talk we are going to look at the lessons, trials, tribulations and successes Johnny had growing up being a Rich Internet Application. Hopefully we’ll answer the question: “Is Johnny happy with himself or does he wishes he was born from different parents so he wasn’t a RIA.”

If we’ve met face to face at one point or another and you live within 50 miles of Ann Arbor I fully expect you to be there! (kidding)

Help the organizers plan by RSVP’ing here:

http://www.facebook.com/event.php?eid=187695341259851 

Also check out the giveaways! This should be a great event with great food, prizes and a very sub-par speaker that promises nothing except he will take a bath before showing up.

See ya there!

It is with great joy that I get to announce a new user group in Mississippi starting up.  The group is meeting in Biloxi, MS and is called the Mississippi Coast .NET Users Group.

I will be speaking at the first meeting of the group on November 23rd, 2010. 

The topic will be “Structure and Guidance for Organizing Applications within Visual Studio”.  Here is an abstract:

Visual Studio is an outstanding tool when it comes to building applications on the .Net Framework. It can be confusing for users when trying to initialize a new software deliverable though. For example, how do you name your projects? Where do you put third party assemblies so they can be re-used? How do you set things up for an n-tier architecture? And the list goes on. I’ve given various talks throughout the US and it never fails that I end up in a conversation with multiple people on what are the best ways to organize projects within Visual Studio. This session should answer these questions and provide some proven guidance that works. In this session we’ll cover some best practices on how to organize your projects and solutions. We’ll also look at some tricks and guidance on how to map your folder structure to your namespaces. During the session we are going to build a new application from scratch and cover how to initially incorporate an n-tier design when initializing your project. Even if you are an experienced .Net developer this is one session you will not want to miss!

Hope to see you there!

The other day I mentioned I was working on an API to communicate with Sonic, an enterprise messaging system.  After getting the API to the point where I wanted to send it to some other developers I wanted to package everything up as on DLL.  ILMerge to the rescue!

Sonic provides their own DLLs that were ported from Java for .NET developers.  There are 7 DLLs you have to reference in order to communicate with Sonic.  Here’s how this can be done using ILMerge by simply modifying the project file using an after build target.

The following below does a few things.

1. It takes all of the assemblies that are outputted to the build directory and passes that to the ILMerge command.
2. Once they are merged the files are deleted.

   1: <Target Name="AfterBuild">

   2:     <CreateItem Include="@(ReferenceCopyLocalPaths)" Condition="'%(Extension)'=='.dll'">

   3:       <Output TaskParameter="Include" ItemName="IlmergeAssemblies" />

   4:     </CreateItem>

   5:     <Exec Command="&amp;quot;$(MSBuildProjectPath)..\..\ThirdPartyAssemblies\Ilmerge\Ilmerge.exe&amp;quot; /allowDup /log /keyfile:$(AssemblyOriginatorKeyFile) /out:@(MainAssembly) &amp;quot;@(IntermediateAssembly)&amp;quot; @(IlmergeAssemblies->'&quot;%(FullPath)&quot;', ' ')" />

   6:     <Delete Files="@(ReferenceCopyLocalPaths->'$(OutDir)%(DestinationSubDirectory)%(Filename)%(Extension)')" />

   7:   </Target>

Very easy as you can see although the syntax is a little finicky.  What does this mean? It means that developers can just add the one DLL to their project and everything works. 

Hopefully this will help someone as well.