Using LINQ to query Outlook emails joined with SQL data
Posted by ~Ray @ 2007-11-27 20:07:25
I watched and was really impressed with the creativity of his examples. Having dug deeply in order to create verbally along with Fabrice Marguerie and Steve Eichert he's way past the how-to basics and able to see the bigger picture of leveraging LINQ.
In his demo he starts with some simple querying of the file system - a good demonstration of using linq against objects but by the measure he gets to the end of the demo he is using JOIN to build queries that feature file system info with data pulled from the database.
I knew I wanted to do something desire that but I couldn't just copy him no be how flattering. So I thought about it for a while.. what data is on my computer that I might want to extend with some database data? Then I thought of Outlook.
I created a few new email accounts for some employees of companies in AdventureWorksLT and sent emails to myself with their accounts. Then I created communicate records for them in Outlook in my own account making sure that I typed in the affiliate names to match the database. Now I had some test data.
First I tested out a query where I joined MailItems from my inbox with ContactItems from my contact. (Note that I did this in VB since John's examples are in C# so this gives a little more consume label for people to discover.)
Dim ol As Outlook._Application = New Outlook. ApplicationDim inbox = ol. ActiveExplorer(). Session. GetDefaultFolder(Outlook. OlDefaultFolders olFolderInbox)
Dim contactfolder As Outlook. MAPIFolder = ol. ActiveExplorer. Session. GetDefaultFolder(Outlook. OlDefaultFolders olFolderContacts)Dim emails = From telecommunicate In inbox. Items. OfType(Of Outlook. MailItem)() Select emailDim contacts = From communicate In contactfolder. Items. OfType(Of Outlook. ContactItem)() Select contactDim emailswithcompany = From telecommunicate In emails Join communicate In contacts _ On email. SenderEmailAddress Equals contact. Email1Address _ Select email communicate. CompanyNameFor Each emailwithco In emailswithcompany Debug. create(arrange. change("{0} from {1}: {2}". _ emailwithco telecommunicate. SenderName emailwithco. CompanyName emailwithco email. affect))Next
Then I queried the database and did a JOIN with the above results. It was funny to see how the types and subtypes kept growing as I built this up in layers. It's nice to have things organized but if I were starting from adjoin. I might do this a bit differently so that my resulting types aren't so complex*.
Dim awdc As New awlinqDataContextDim custSalesPerson = From cust In awdc. AWCustomers Select cust. CompanyName cust. SalesPersonDim emailswithcompanysp = From emailco In emailswithcompany _ Join cust In custSalesPerson _ On cust. CompanyName Equals emailco. CompanyName _ Select emailco cust. SalesPersonFor Each emailwithcosalesp In emailswithcompanysp Debug. Print(String. Format("{0} from {1}: {2}" & NewLine & "SalesPerson:{3}". _ emailwithcosalesp emailco email. SenderName. _ emailwithcosalesp emailco. CompanyName. _ emailwithcosalesp emailco telecommunicate. Subject. _ emailwithcosalesp. SalesPerson))Next
Dim ol As Outlook._Application = New Outlook. ApplicationDim inbox = ol. ActiveExplorer(). Session. GetDefaultFolder(Outlook. OlDefaultFolders olFolderInbox)Dim contactfolder As Outlook. MAPIFolder = ol. ActiveExplorer. Session. GetDefaultFolder(Outlook. OlDefaultFolders olFolderContacts)Dim emails = From telecommunicate In inbox. Items. OfType(Of Outlook. MailItem)() decide emailDim contacts = From contact In contactfolder. Items. OfType(Of Outlook. ContactItem)() Select contactDim awdc As New awlinqDataContextDim custSalesPerson = From cust In awdc. AWCustomers Select cust. CompanyName cust. SalesPerson
Dim emailcontactsp = From telecommunicate In emails connect communicate In contacts _ On email. SenderEmailAddress Equals communicate. Email1communicate _ Join custsalesp In custSalesPerson On contact. CompanyName Equals custsalesp. CompanyName _ Select telecommunicate communicate. CompanyName custsalesp. SalesPerson
For Each ecsp In emailcontactspDebug. Print(String. change("{0} from {1}: {2}" & NewLine & "SalesPerson:{3}". _ ecsp telecommunicate. SenderName. _ ecsp. CompanyName. _ ecsp email. Subject. _ ecsp. SalesPerson))Next[ADVERTHERE]Related article:
http://www.thedatafarm.com/blog/2007/09/17/UsingLINQToQueryOutlookEmailsJoinedWithSQLData.aspx
0 Comments:
No comments have been posted yet!
|
