Microsoft CRM Caller ID Lookup for TrixBox Part 3
In part 1 we set the scene and created our web application project. In part 2 we began creating the HTTP Generic Handler, retrieved the CLID string from the URL's query string and knocked it into the correct canonical format ready for looking in the CRM database. In part 3, we are going to use LINQ - a new feature in Visual Studio 2008 and the .NET Framework 3.5 - to perform the lookup in the CRM database and return the response from our HTTP handler.
LINQ to SQL
One thing to get out of the way. Normally, it wouldn't be the done thing to access the CRM database directly. You'd want to use the CRM Web Service. However, I'm allowing myself to take this shortcut for the following reasons:
- We are only reading a very limited amount of data and will not be modifying the database.
- I want to bypass the normal CRM security mechanisms so that our web application has access to all of the contact records.
- Scalability will not be an issue because TrixBox performs its own caching.
Nevertheless, you should know that what I'm doing here isn't really best practice. Maybe in a future version I'll address that and do things properly.
Now, the next thing to do is to add the LINQ to SQL classes to our project. To do this, right click the project, click Add, New Item... then select LINQ to SQL Classes. Call them CrmContacts.dbml as shown here.
You'll see the LINQ to SQL design surface. You now need to create a connection to your CRM SQL database using Server Explorer. Expand the Views folder and drag the Contact view onto the design surface. It should look like this (click the image for a full size view):
While we're here, we need to grab a copy of the SQL connection string. Right-click on your connection in Server Explorer, select Properties... and in the Properties pane, copy the value of the connection string.
Go to your projects Settings and paste the value in, replacing the value already there, if any.
The reason for doing this is to decouple the code from the database. Putting the connection string in the Web.config file enables the connection to be changed later without having to rebuild the code. For example, if you deployed this on your web server against a test database, then later you can switch to the production database simply by changing the connection string.
Now, save everything and build. There should be no errors or warnings.
Define the LINQ Query
This is the LINQ query that will lookup our CLID in the CRM database. Replace the line
//ToDo: Lookup in CRM database
with the following code
string clidQueryString = String.Format("+{0} ({1}) {2}", clidCountry, clidArea, clidNumber);
Diagnostics.TraceInfo("Searching CRM contacts for {0}", clidQueryString);
CrmContactsDataContext crmContext = new CrmContactsDataContext(Properties.Settings.Default.CrmConnectionString);
var queryCLID = from contact in crmContext.Contacts
where contact.Address1_Telephone1 == clidQueryString
|| contact.Address1_Telephone2 == clidQueryString
|| contact.Address1_Telephone3 == clidQueryString
|| contact.Address2_Telephone1 == clidQueryString
|| contact.Address2_Telephone2 == clidQueryString
|| contact.Address2_Telephone3 == clidQueryString
|| contact.Telephone1 == clidQueryString
|| contact.Telephone2 == clidQueryString
|| contact.Telephone3 == clidQueryString
select new
{
Account = contact.AccountIdName,
FullName = contact.FullName
};
We're looking for a match in any of the phone number fields in a CRM Contact record. If your contact entity has been customised or you want to be more selective, then edit the code accordingly.
One of the problems with this technique is that it can return multiple results, for example if you have two contacts within the same company who share the same phone number. One strategy for dealing with that is to only return the first match, but then you might not actually be returning the right person. Another alternative would be to just return the company name. What I decided to do was to return all the matches, concatenated together in one long result string, like this:
StringBuilder caller = new StringBuilder();
int count = 0;
foreach (var contact in queryCLID)
{
if (count > 0) caller.Append(", ");
caller.AppendFormat("{0} ({1}) ", contact.FullName, contact.Account);
++count;
}
if (count == 0) caller.Append("unknown");
string response = caller.ToString();
So, we have our response. All we need to do now is send it back to the client who made the original request. This is very simple:
Diagnostics.TraceInfo("Matched {0} records, returning: {1}", count, response);
context.Response.ContentType = "text/plain";
context.Response.Write(response);
Delete the redundant last two lines:
//ToDo: Format & return the result string
context.Response.Write("Hello World");
And we're good to go. Build and run the project. If the compiler complains about StringBuilder, add a Using System.Text; at the top of the code. When the web browser opens, click on the hyperlinks on your default.aspx page and, provided the query strings correspond to numbers in your CRM database, you should see the contact's name and company returned to your web browser. You should also be able to look up numbers by editing the URL in your browser's Address bar. If you've been adding the diagnostics and running DebugVw, you'll see output like this:
[7860] TiGra.TrixBox[Info]: Received request: /Lookup.ashx?CLID=01443208678
[7860] TiGra.TrixBox[Verb]: CLID query string Raw=01443208678 Trimmed=1443208678
[7860] TiGra.TrixBox[Verb]: Recognised local number and area code.
[7860] TiGra.TrixBox[Info]: Searching CRM contacts for +44 (1443) 208678
[7860] TiGra.TrixBox[Info]: Matched 1 records, returning: Timothy Long (TiGra Networks)
OK, so that's the code done. In the next installment, we'll look at configuring TrixBox to use all this stuff.