A bit of fun with OData

So this post has really no big point. I started out by the fact that I’m a heavy user of Tasks in Outlook, as I have eluded to earlier: http://www.nnihlen.com/blog/archive/2009/05/22/adding-outlook-tasks-fast-using-launchy-with-a-little-programming.aspx

Much of my personality comes form being an athlete (and a geek) and as the mentality from that is to always improve you’re self. But that’s kinda hard if you don’t have any measurements. So on that note bundled up with a growing curiosity for for OData I sat down and came up with the following scheme.

OutLookTaskStats_01

So the idea is to import the task data from Outlook into a a database and then expose it as an OData service to for example Excel PowerPivot to play with the data and maybe try out the ASP.NET charting components.

Import

I created a simple database schema:

DB Design

The import is at this stage a very brute force thing. I figured you run it seldom, the amount of data isn’t that big and so on. So I went for the very ugly generate-SQL-statements-and-run-them-using-ADO.NET approach. I delete the data every time and recreate it. Not very elegant but works for the moment. I guess the next step here would be to use the C and U in the OData CRUD promise. But that will have to go into vNext.

So here it is in all its ugliness:

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using Microsoft.Office.Interop.Outlook;
using Exception = System.Exception;

namespace OutlookTaskStatistics.Importer
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            var categoriesHt = new Hashtable();

            Application olApp;
            NameSpace ns = null;
            SqlConnection conn = null;

            try
            {
                olApp = new Application();
                ns = olApp.GetNamespace("mapi");

                ns.Logon("Outlook", Missing.Value, false, true);

                MAPIFolder taskFolder =
                    olApp.Session.GetDefaultFolder(OlDefaultFolders.olFolderTasks);


                conn = new SqlConnection(ConfigurationManager.ConnectionStrings["OutlookStatsDB"].ConnectionString);
                conn.Open();

                using (SqlCommand truncateCmd = new SqlCommand("TRUNCATE TABLE TasksCategories; DELETE FROM Tasks; DELETE FROM TasksCategories", conn))
                {
                    truncateCmd.ExecuteNonQuery();
                    //truncateCmd.CommandText = "DELETE FROM Tasks";
                    //truncateCmd.ExecuteNonQuery();
                }


                var cmdGetCategories = new SqlCommand("SELECT * FROM Categories", conn);
                categoriesHt = GetCategoriesHashTable(cmdGetCategories);

                int i = 0;

                foreach (object taskItem in taskFolder.Items)
                {
                    var ti = taskItem as TaskItem;


                    if (ti.Categories != null)
                    {
                        string[] taskCategories;
                        taskCategories = GetTaskCategories(ti);

                        foreach (string taskCategory in taskCategories)
                        {
                            if (!categoriesHt.ContainsKey(taskCategory.Trim()))
                            {
                                var updateCategoriesCmd =
                                    new SqlCommand("INSERT INTO Categories (CategoryName) VALUES (@catName)", conn);
                                var catNameParameter = new SqlParameter("@catName", SqlDbType.VarChar, 50);
                                catNameParameter.Value = taskCategory.Trim();
                                updateCategoriesCmd.Parameters.Add(catNameParameter);
                                updateCategoriesCmd.ExecuteNonQuery();

                                categoriesHt = GetCategoriesHashTable(cmdGetCategories);
                            }
                        }
                    }

                    string insertTasksStatement =
                        "INSERT INTO Tasks (TaskId, Subject, CreateTime, StartDate, DueDate, CompletedDate, Complete, Importance) " +
                        "VALUES (@entryId, @subject, @createDate, @startDate, @dueDate, @completedDate, @completed, @importance)";

                    var cmd = new SqlCommand(insertTasksStatement, conn);

                    var paramTaskId = new SqlParameter("@entryId", SqlDbType.VarChar, 140);
                    paramTaskId.Value = ti.EntryID;

                    var subjrectParam = new SqlParameter("@subject", SqlDbType.VarChar);
                    subjrectParam.Value = ti.Subject;

                    var createaionParam = new SqlParameter("@createDate", SqlDbType.SmallDateTime);
                    cmd.Parameters.Add(createaionParam);
                    if (DateSeemsSane(ti.CreationTime))
                    {
                        createaionParam.Value = ti.CreationTime;
                    }
                    else
                    {
                        createaionParam.Value = DBNull.Value;
                    }

                    var startDateParam = new SqlParameter("@startDate", SqlDbType.SmallDateTime);
                    cmd.Parameters.Add(startDateParam);
                    if (DateSeemsSane(ti.StartDate))
                    {
                        startDateParam.Value = ti.StartDate;
                    }
                    else
                    {
                        startDateParam.Value = DBNull.Value;
                    }

                    var dueDateParam = new SqlParameter("@dueDate", SqlDbType.SmallDateTime);
                    cmd.Parameters.Add(dueDateParam);
                    if (DateSeemsSane(ti.DueDate))
                    {
                        dueDateParam.Value = ti.DueDate;
                    }
                    else
                    {
                        dueDateParam.Value = DBNull.Value;
                    }

                    var completeDateParam = new SqlParameter("@completedDate", SqlDbType.SmallDateTime);
                    cmd.Parameters.Add(completeDateParam);
                    if (DateSeemsSane(ti.DateCompleted))
                    {
                        completeDateParam.Value = ti.DateCompleted;
                    }
                    else
                    {
                        completeDateParam.Value = DBNull.Value;
                    }

                    var completedParam = new SqlParameter("@completed", SqlDbType.Bit);
                    completedParam.Value = ti.Complete;

                    var importanceParam = new SqlParameter("@importance", SqlDbType.VarChar);

                    switch (ti.Importance)
                    {
                        case OlImportance.olImportanceLow:
                            importanceParam.Value = "Low";
                            break;
                        case OlImportance.olImportanceHigh:
                            importanceParam.Value = "High";
                            break;
                        default:
                            importanceParam.Value = "Normal";
                            break;
                    }

                    cmd.Parameters.Add(paramTaskId);
                    cmd.Parameters.Add(subjrectParam);
                    cmd.Parameters.Add(completedParam);
                    cmd.Parameters.Add(importanceParam);

                    cmd.ExecuteNonQuery();

                    if (ti.Categories != null)
                    {
                        string[] taskCategories = GetTaskCategories(ti);

                        foreach (string taskCategory in taskCategories)
                        {
                            string stmt =
                                "INSERT INTO TasksCategories (TaskId, CategoryId) VALUES (@taskId, @catId)";

                            var categoryTaskCmd = new SqlCommand(stmt, conn);

                            categoryTaskCmd.Parameters.Add(new SqlParameter("@taskId", ti.EntryID));
                            categoryTaskCmd.Parameters.Add(new SqlParameter("@catId",
                                                                            (int) categoriesHt[taskCategory.Trim()]));
                            categoryTaskCmd.ExecuteNonQuery();
                        }
                    }

                    i++;
                    if(i % 100 == 0)
                    {
                        Console.Write(i.ToString());
                    }
                    else
                    {
                        Console.Write(".");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw;
            }
            finally
            {
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
                ns.Logoff();
            }
        }


        private static string[] GetTaskCategories(TaskItem ti)
        {
            string[] taskCategories;
            if (ti.Categories.IndexOf(';') > 0)
            {
                taskCategories = ti.Categories.Split(';');
            }
            else
            {
                taskCategories = ti.Categories.Split(',');
            }
            return taskCategories;
        }


        private static bool DateSeemsSane(DateTime dateToCheck)
        {
            if (dateToCheck.Year >= 1900 && dateToCheck.Year < 4501)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        private static Hashtable GetCategoriesHashTable(SqlCommand cmdGetCategories)
        {
            var categoriesHt = new Hashtable();
            SqlDataReader categoryReader = cmdGetCategories.ExecuteReader();
            while (categoryReader.Read())
            {
                categoriesHt.Add(categoryReader[categoryReader.GetOrdinal("CategoryName")],
                                 categoryReader[categoryReader.GetOrdinal("CategoryId")]);
            }
            categoryReader.Close();

            return categoriesHt;
        }
    }
}

Exposing OData

Next step is to expose the data as a OData service.

Entity Framework Model

First of all we got to put a layer on top of the database that WCF Data Services (previously known as Astoria) understands, namely Linq to SQL or Entity Frame. I decided to go with EF here, so I right clicked in the solution tree and selected Add –> New Item… And then selected ADO.NET Entity Data Model.

Create Entity Model

Select Generate From Database

Generate From DB

Next step is to chose what DB to generate from and give the enties a name space:

Create Entity Model 02

Then select what tables (or sprocs/views for that matter) should be included in the model.

Create Entity Model 03

Hit Finish and my result looked like this:

Entity Model

OData Service

Next thing to do is to expose the model as OData. First up we create an OData Service or as Visual Studio (and Microsoft Marketing) likes to put it – WCF Data Service. (I you bingle that you probably wanna put “Astoria” in the search to).

Add OData Service 

When it’s created the xxx.svc.cs file will pop up in Visual Studio, here you’ll need to make two changes (or more if you want to lock down the data a little more then “ReadAll”). As I tried to show in the image below I tell the DataService to be of OutlookTaskStatsEntities and I set the Entity Access Rule to apply to “*”, that is all entites.

Service Config

Ok, now we’re done, lets try it! F5 and hit the TaskStats.svc with a browser:

OData Service Started

Looks good, now we can drilldown using the URL if we want to. For example http://localhost:36277/TaskStats.svc/Categories(2) shows me the category “Personal”.

Category XML

 

Excel PowerPivot

Ok, now we have the data out of Outlook and exposed as OData. Next step in my plan is to get it into Excel and I’m going to use PowerPivot for excel for that. If you don’t have the plug-in installed go get it from www.powerpoivot.com.

Fire up Excel and start PowerPivot.

PowerPivot Start

Let’s fetch the (o)data by hitting the “From Data Feeds” button.

PowerPivot Open Feed

And the wizard kicks off. First step enter the service URL:

PowerPivot Open Feed 2

After that we decide what data to import.

PowerPivot Open Feed 3

The import succeeds (hopefully).

PowerPivot Open Feed 4

And we have the data in Excel.

PowerPivot Imported Data

Here is where my current skillset fails me and I really have to get better at massaging this data in Excel, maybe I should ask some of my BI centric customers for help :). Anyway here’s a little example of a first view of the data. I chose to create a Pivot diagram.

Create Pivot Diagram 01

After adding some axis and slicers here’s what I got:

PowerPivot Data

 

Summary

I think the points I want to drive with this post is:

  1. Show a way to access data in Outlook
  2. I hope I’ve shown in this post that it is relatively simple to expose you model as OData. This is of course a greatly simplified example where I don’t really care about security.
  3. Have a look at PowerPivot

Next up I’m going to consume the OData from Linq and see if I can do some fun statistics and graphics, but that’s another post.