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

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;

}

}

}


C# – Keep me from hurting myself

I just fixed a funny bug where it was behaving very odd and it was because I had a return statement where I intended to have a continue. I musta looked at that line a dozen times and did not think anything of it. Just went back to the basics of debugging and ran through the code line by line, iteration by iteration. What an embarassing relief!

[Upon further reflection]
What if there was new language feature that made this less likely to happen. Does it help readability and/or intent? Take the following loop.

foreach (var i in theCollection)
{
if (i.Visible == false)
continue;

// other code goes here
}

What if I could change the syntax to something like this…

foreach (var i in theCollection)
except i.Visible == false
{
// other code goes here
}

Upon futher thought what if I just used LINQ instead?

var iVisible = theCollection.FindAll( i => i.Visible)

foreach( var i in iVisible)
{
// other code goes here
}

Not to bad from a syntax point of view. I don’t like that I am making a shallow copy of the original list for the purposes of only iterating through it. The language extension above works on the original list.