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: https://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.
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:
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.
Select Generate From Database
Next step is to chose what DB to generate from and give the enties a name space:
Then select what tables (or sprocs/views for that matter) should be included in the model.
Hit Finish and my result looked like this:
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).
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.
Ok, now we’re done, lets try it! F5 and hit the TaskStats.svc with a browser:
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”.
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.
Let’s fetch the (o)data by hitting the “From Data Feeds” button.
And the wizard kicks off. First step enter the service URL:
After that we decide what data to import.
The import succeeds (hopefully).
And we have the data in Excel.
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.
After adding some axis and slicers here’s what I got:
Summary
I think the points I want to drive with this post is:
- Show a way to access data in Outlook
- 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.
- 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.
Tags: .NET Development, Office, Productivity