New OLEDB Provider for Reading Excel 2007

I work for a financial company that uses alot of Excel. Many of the the business users here practically live in it. So we are constantly trying to figure out how to leverage Excel in our applications.

Do we just export data to Excel? If so, then is it a snapshot/copy of the data or do we build a connection to the backend data? What about importing data? Where is the boundry between using VBA and VSTO? Then if we pile SharePoint and Excel Services on this heap it starts to get really interesting.

One of our technical frustrations has been the OLEDB driver for reading Excel on the server was fairly lame. It made alot of assumptions about the data that made it nearly unusable except in the simpliest cases. Last week I found this updated Provider for Excel 2007 and I am looking forward to giving it a deeper look. What I can say is, that it did read in all my data rather easily. I just have not had time to play around the fringes much.

Download details: 2007 Office System Driver: Data Connectivity Components
href="http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

This made me laugh

A friend I have not heard from in a couple months sent me an email yesterday with this as the subject. Yeah the subject! After I stopped laughing, I opened the email. Thought I would share this one…

1. Ninjas are mammals.


2. Ninjas fight ALL the time.


3. The purpose of the ninja is to flip out and kill people.

UI Testing

I spent a bunch of time in the early Windows days trying to do UI testing the way this (UI Test Automation Tools are Snake Oil) blog entry talks about. Like him (or his clients) we used some really expensive tools and ended up not doing a very good job. I really like the thinking Michael is doing here. This is definately where my head is at. The problem is that I am struggling with creating MVC-style applications.

Where I work now we just don't build big applications. Instead we have lots of small applications that we deliver in weeks not months or years. I have not found this pattern of doing software very condusive for building applications with lots of design. Now hold on a minute - that does not mean we don't do design. We just don't do lots of design. When an application is very small how much design do you really need to do? Most of the applications tend to look like each other - read some data...munge it together...display it. We don't do much data entry; which is an exception in the pattern of apps I have built over the years.

That is not to say we don't have some big-ish applications. We do. Just that they are the exception. Could they do with more engineering? Absolutely! But we just don't have the infrastructure (staff, mindshare, experience, etc) to do it that way. Of course there are people doing a high level of engineering here. It's just that it's not everyone - it's not our default.

At first this was a hard pill to swallow (and it still makes me a little gassy at times). But it's the nature/culture of the way we do things. It's a model that works, but not in a scaleable way. Sharing anything in this model is very hard - maybe I will blog later about how we do that.

Old Problem – New Context

[Here is an old post I found that I had never published. I think it's because I could not get the formatting of the code to look "right". This time I published from Word and the formatting was still not preserved - rats! Also, the article is not as complete as I would like it but I wanted to get the code into the blogosphere.]

[There are a few technical things wrong with this post. The first is that using Thread Local Storage is risk (at best) in an ASP.NET application. See http://www.hanselman.com/blog/CommentView.aspx?guid=320. The second is that I would prefer to use a different pattern than the one below; something a little more elegant. Lastly, after using LINQ much more I don't know that this code is even a best practice; I need to noodle on this more.]

I keep thinking that there are like a dozen or so abstract problems that I solve and everything else is just another derivation from those. This is one of those dozen problems – what is context and why do we need it so often?

We have been using a lot of LINQ lately. While using LINQ to SQL I ran into a problem a few weeks ago and created a little hack to work around it. Well we are starting to roll LINQ out to the rest of the developers we need more than a hack for this issue. What is the issue, well it's the same we have had in the last 4 ADO stacks - how do I reuse a connection when I have a transaction open. The only time I have not had to work around this issue was in MTS/MTX and when we used the Microsoft Enterprise Library (EntLib)

The way MTX and EntLib get around this is that they are a "layer" in between me (you) and the underlying infrastructure. I remember Bernie's old saying - "Interesting things can happen when you can get in between things". Another way of saying that layers or interception are something that are a key part of any design.

So here is what I want the client/consuming code to look like.

const
string CN = "Server=localhost;Database=AdventureWorks2008;Trusted_Connection=true";

using (var aTrx = new TransactionScope())

{

var aCtx = new AdventureWorksDataContext(CN);

var x = from p in aCtx.Persons

where p.FirstName.StartsWith("A")

select p;

var y = x.First();

var aCtx2 = new AdventureWorksDataContext(CN);

var x2 = from p in aCtx2.Persons

where p.FirstName.StartsWith("A")

select p;

var y2 = x2.First();

}

So I wrote a little class that caches the connections so that they can be reused across LINQ statements. If we don't do it this way then we are invoking the Distributed Transaction Coordinator (DTC). We only want to do this when the statements need to be in the same transaction.

public
static
class
TransactionalDataContext

{

[ThreadStatic]

private
static Hashtable mConnections;

public
static IDbConnection DataContext(string pCnStr)

{

var aTrx = Transaction.Current;

if (aTrx == null)

{

return CreateConnection(pCnStr);

}

if (mConnections == null)

{

mConnections = new Hashtable();

}

if (mConnections.ContainsKey(pCnStr))

{

return (IDbConnection)mConnections[pCnStr];

}

var aCn = CreateConnection(pCnStr);

mConnections[pCnStr] = aCn;

aTrx.TransactionCompleted += TransactionCompleted;

return aCn;

}

private
static IDbConnection CreateConnection(string pCnStr)

{

// cn str is name value pairs with '=' and ';' separating each pair

// get each of the pairs

var aCnStrPairs = pCnStr.Split(new[] { ';' });

// find the pair with 'Provider'

var aProvPairEnum = aCnStrPairs.Where(i => string.Compare(i, "Provider", true) == 0);

IDbConnection aCn;

if (aProvPairEnum.Count() == 0)

{

// no provider specified so assume MS SQL Server

aCn = new SqlConnection(pCnStr);

}

else

{

// get the provider name

var aProvPair = aProvPairEnum.First();

var aProvNvp = aProvPair.Split(new[] { '=' });

// create a connection

var aFactory = System.Data.Common.DbProviderFactories.GetFactory(aProvNvp[1]);

aCn = aFactory.CreateConnection();

aCn.ConnectionString = pCnStr;

}

// we open the cn so that LINQ does not - this will also signal to LINQ not to close the cn

aCn.Open();

return aCn;

}

// handler for when the trx is complete

private
static
void TransactionCompleted(object sender, TransactionEventArgs e)

{

try

{

// dispose of each connection

foreach (DictionaryEntry aEntry in mConnections)

{

try

{

((IDbConnection)aEntry.Value).Dispose();

}

catch { }

}

}

finally

{

// reset the collection, in case doing another on this thread

mConnections.Clear;

}

}

}

Outlook Rules

After reading the title to this entry I had to laugh. So am I stating an opinion on how I feel about Outlook (it rules!) or something else? When it the electrons from my brain told my finger tips to type this title I was definitely thinking something else. Hey man – words do mean something.

So after reading Hanselman's blog entry (http://www.hanselman.com/blog/TheThreeMostImportantOutlookRulesForProcessingMail.aspx) over the Holiday about processing Outlook rules; I was inspired to revisit my own set of rules. When I was working at Microsoft (as Scott now does) I was totally overwhelmed with the amount of internal information flowing around the company; and at the time most of it was through email distribution lists. Between the stuff directed at me as part of my job (Microsoft Consulting) and the internal chatter; I was getting hundreds of emails a day. Without a good set of rules it would have been impossible to get through everything in my inbox.

That practice has stayed with me ever since and has served me well over the years. Two things have happened recently that I wanted to write about with regard to Outlook.

First I wanted to record this (http://office.microsoft.com/en-us/outlook/HA103869131033.aspx?pid=CH102499821033) article called "Outlook Meeting Requests: Essential Do's and Don'ts". As a long time Outlook user; I already do many of the things in this article. But there were some other nuggets in there that I found useful; especially around using rules on meeting requests. One thing I have been looking all over for and found on this page was how to turn off the acknowledgement to a meeting invite. There are a few meetings that I invite 20+ people to and I don't need to know who has accepted and who has not. I had a rule just to catch all of these responses and move them off to a folder.

Secondly, I wanted to mention that about 4 months ago I became a Crackberry user for my work email. There were many things happening at work that I just needed to stay apprised of during the time I was not logged in. Well this changed the way that I wrote many of my rules. Suddenly I was not just filtering things so that my Inbox was less cluttered (and relatively prioritized). Now I needed more because the Blackberry (Bb) is synchronizing with my Inbox and my hip was buzzing for every email.

Side note – I cannot tell you how annoying it is that the Bb buzzes about 7 seconds before an email arrives in my Inbox. As if the little bubble pop-up isn't enough of a distraction!!

So now I have two Inboxes. I have a series of rules that move everything EXCEPT the "hey a server is down" type of emails to my secondary inbox. The trick is to do this given the fact that the Bb service (on the Exchange server) sees only the emails that fall off of the server side rules waterfall. So I have been using the "Move" and "Stop Processing More Rules" combination much more. There are also some rules that are order dependent; which I am not happy with.

But the whole thing works relatively well now. It's funny how refactoring my rules for the Bb may have actually made them better. Kind of like my code/designs; sometimes it's not until a new requirement comes along that I realize a better way. As Hannibal Smith used to say (The A-Team) – "I love it when a plan comes together".

What I would really love is for the Blackberry guys to give me a way to interact with the Exchange service running on the server. Let me write some rules separately, just for the device. I would not be at all surprised if such a thing exists and we are just not "with it" enough to have deployed it. It's probably for the same reason that we deploy WindowsXP with the old Start Menu configuration (no MRU etc). Because someone felt that it would be just too hard and confusing to the users to change anything. Now that we have rolled out Office 2007 I better not hear that excuse ever again!!

Brick Wall - Bang Head

I had 17 (the number is not important except that it is more than one) Excel files I needed to get into a database and since wee happen to use SQL Server - I thought of SSIS. I was going to leave my trusty C# hammer in the tool bag for a more specialized tool. I was confident that even though I had not used SSIS for much (mostly trivial imports from SSMS) that I could get something running pretty quickly.

Well I could not have been more wrong.

I spent the entire day working through a series of SSIS issues specific to the problem I was trying to solve . The final issue was that a couple of the cells in Excel have more than 255 chars of data; what I nightmare trying to get the Excel driver to read more. And now that it is - its in an NTEXT data type which is practically useless when I really want a String. What a mess! Not to mention that I enlisted (wasted?) some help from other people (at least 4) who are much more knowledgeable on this technology than I.

The question I am asking myself is not whether to use a different tool (my trusty C# knife); but when I should have "cut bait and run"?

Every time I ran into an issue yesterday it felt like I was getting closer and closer to being done. Problem is that now feels like there was an infinate distance to travel; so closer being relative - i was never going to be done. At what point do I should I have realized this? Is one day too long to have been doing this? Is this just a case of arrogance and/or stuborness?

Just one of those things that makes me say Hmmmm.

It's hard to explain to people who don't do what I (we) do for a living this type of situation. I wonder how much time is spent / wasted doing just this sort of thing; using the wrong tool for the job. How many pople just keep banging a bent nail? Sure it works, but it's so britile you can never change it. I guess I could have just dropped a script component on the SSIS design surface and written the entire thing in C#. Too bad the debugger does not work. I am just too old to debug something using MessageBox.Show(). Geez! I am so anal about debuggers that a friend and I (he did most of the heavy lifting) wrote our own debugger for a Basic compiler we were using back in the early 90's!

I think I gave SSIS a fair chance. Now it's time to get this thing done.

IIS 401.5 Error

Had a really weird problem today where I was getting a IIS 401.5 error on a subweb today. The problem shows up as a 401.2 error in the browser, but when you look at the IIS log it's reported as a 401.5.

I was running through all my usual ways of debugging these types of issues and non of the usual things worked. Asked a collegue for help because I must be missing something obvious. Then I was verbally walking him through the configuration and I mentioned that the problem was only happening on this subweb. Something clicked on both our faces - it's a subweb so there is more than one web.config in play here. I checked the web.config file and found that it was busted (the root site did not work either - but no one cares). I renamed the web.config file and bang, the site started working again.

Blogging this on the off chance it helps someone and to get it to stick in my own memory for next time.