the excel syndrome

What is the excel syndrome?

It's an executive manager who uses Excel and is convinced he "knows" computers. You see it a lot now. And it's one of the big things that's changed in the last few years. Ten years ago, if you said "e-commerce" everyone's eyes glazed over and they said "just make something work".

Today, all the execs use Excel and buy things from Amazon. So when an IT professional says : "setting up this database correctly and adding accounts with the right security and tables with the right schemas will take... er... 2 weeks", the exec says "WHAT!?? that's insane! Why... I can do it in Excel in 10 minutes". Then he (or sometimes she, but that's pretty rare), slashes the project estimate.

Oh, I know I'm exaggerating for emphasis. But the syndrome exists. It really does. My CEO just said today that we weren't delivering fast enough. Fast enough compared to ... what? How does he have a feel for how long it *should* take?

I started noticing this a few years ago. Non-IT people started to think they understood IT well enough to second guess their IT staff. I see it among IT people too -- especially those who have worked in small environments and with older, more limited systems. Sure, it may be possible to export a file from DBase from a live system with a simple real-time query. But.. um... DBase isn't relational? Oh, and there's that little thing called "security"? And the DBase database that only held 2MB of data is a slightly different creature than a SqlServer database that holds 200GB.

It is part of a trend in the industry. IT has become big business. Consulting companies and vendors are big business. As the money that goes into IT grows, more and more people are trying to get their little part of it. Naturally, that includes the execs and the bean counters.

I'm not sure where IT will go in the future, but it's days of free-wheeling are done, I think. It will become more and more driven by people who have an opinion on how the money is being spent, even if they don't know what their opinion means. It's kind of like those Windows7 commercials where the people are saying the invented Windows7, because they sent an email to Microsoft telling them to make computers better.

There are a lot of those people.....

data access

there's lots of ways to do data access, but here's something I've been doing that seems kinda cool.

First, create a namespace (DataObjects, in my case) .

Next, a BaseDataObject this is the parent that all the data accessors will inherit from.

In the BaseDataObject go the common things. For example, I have a public property called "DatabaseName". Using this, the code will query for the correct connection string parameters when needed.

The BaseDataObject also owns the actual connection. This is interesting. The Connection goes into a thread static collection of connections (which is hard to say quickly :D ). When the connection is needed, the collection is searched. If the connection isn't there, it gets created and inserted for the life of the thread. Took a while to nail this, but this means that the connections are correctly cached by database name for the life of the thread and no longer.

In order for this to work, the connection is actually of type IDbConnection. Anything else will bind the data object to one database type.

All of the methods of the BaseDataObject follow that pattern -- each uses IDbCommand, IDataReader and the like.

The simplest subclasses only override the connection itself. For example, I've got a MySqlDataObject that allows connection to a MySql database. It overrides the "connectToDatabase" method to secretly append a mySql designation to the DatabaseName. This means the connection string resolution returns a MySql compatible connection string, not a SqlServer one. In addition, when it creates the database connection, it creates a "MySqlConnection", which gets implicitly cast upward to a IDBConnection on the return.

The more complex subclasses (the SqlServer one, for example) have some additional functionality. For example, I've added an ExecuteNonQueryWithParameters method to the BaseDataObject, which accepts a Dictionary of key-value parameters and then uses this to populate the Sql Statement. The BaseDataObject resolves all this by looping through the dictionary and ingloriously building the sql string. The SqlDataObject overrides this to use the Parameters.AddWithValue method on the SqlCommand, since this is available.

In addition, I've occasionally further subclassed the child data objects to allow versions of the objects that toss exceptions back to the caller and versions that don't, but log the exceptions and return a null result.

To wrap this all up, I added an object factory to the DataObjects namespace. Pass in the DatabaseName, and you get an appropriate subclass of BaseDataObject, based on whatever business rules are applied to decide this. The returning object is basically a live connection ready to be used.

So far, this has worked pretty well. My big frustration is the lookup on the database name -> connection string. I have been using a Microsoft "Settings" object for that, but, frankly, it sucks and doesn't provide any real benefit. The good news is that, since this is hidden by the objects in the namespace, I can change it anytime without issue. Moreover, the structure allows me to reference databases as objects. Not only do I like that, but the polymorphism and use of object factories allows that I can move data from one database to another with no changes to code.

Just thought it was cool enough to post.

today

just something to share that doesn't mean anything.

I spent about 3 hours today debugging a workflow. I was furious at Microsoft.

The issue was that the AutoResetEvent wasn't getting set, but timing out. So, after the flow was complete, the "oncomplete" delegate would fire, but then sit there and wait for the AutoResetEvent to timeout.

I checked the code. I checked the flow. I debugged. I searched the web. I was typing up a post on MSDN, when I thought I'd better double check my logic.

My flow was being called from a subclass of a util class that I had written. After some additional hair pulling, I realized that the subclass was hiding the AutoResetEvent with a variable named the same. What was really stupid was that in C#, you have to create the variable in the subclass with the "new" key word:

private new AutoResetEvent waitHandle = new AutoResetEvent(false);

Geeze. SO I *intentionally* was hiding the variable that I needed then wondering why it wasn't there.

My official thought: "DOH!"

So here's my takeaway. It's getting easier to build complex functionality with the new tools. They do a ton for you. Awesome.

But they hide things. Nothing in my debugging would show me which variable was getting updated or how.

I'm not blaming Microsoft. Well.. ok... I am, simply because I need someone to blame.

But it's a challenge that developers face now. Devs have always had to use code we didn't control. But the degree to which its needed is getting much higher. Whether we're using EJB or Windows something Foundation, you just have no clue what the code is really doing. If you make a simple mistake setting something up -- a hidden variable, a mistaken delegate, a copy/paste error in a config file -- can cost days in developer time to find. In the end, it could even be a bug in the toolset or a mistake in the docs.

It gives me pause for thought. I really wonder if this is the best way to approach things.

--kevin

Workflow and Exceptions

This guys says lots of cool, good, happy things about handing exceptions in Windows Workflow.

http://www.microsoft.com/uk/msdn/nuggets/nugget/60/Windows-Workflow-Foundation-Exceptions.aspx

AND he says it with a cool accent!

The basics are pretty simple, but there are some weird gotchas. Like you have to have a different Exception Handler for each kind of exception you want to handle, not just different code for each.

There are a couple additional things to keep in mind:

first, Studio (2008, anyway) doesn't seem to recognize that you have a workflow exception handler in place. So when you run the code in debug mode, it will stop at the exception and tell you that it's unhanded. If you hit F5, it'll will step into the handler. Seems like a disconnect between the development tool and the workflow foundation.

There are a bunch of things you can do with the exceptions, of course. But my real desire is usually to trap them, log them, then return them gracefully to the calling process.

The second thing to be aware of is how to do this. Lots of options, but if you create a public, read-only property to your workflows called, say "Exceptions", and defined as , say, a custom collection (or generic) to hold the exceptions, these will get auto-magically returned to the caller. However, they are cleverly hidden so that it's not obvious where they are.

Upon completion of the workflow, all the publics get serialized and passed as a parameter to the delegates registered for the workflowRuntime.WorkflowCompleted, workflowRuntime.WorkflowTerminated, workflowRuntime.WorkflowAborted events.

So somewhere in the code, there should be a something like this:

workflowRuntime.WorkflowCompleted += OnWorkflowCompleted;

OnWorkflowCompleted is the delegate defined by:

protected void OnWorkflowCompleted(object sender, WorkflowCompletedEventArgs e)

Typically, here is where you will set the wait handle:

waitHandle.Set();

where waitHandle is defined as :

protected AutoResetEvent waitHandle = new AutoResetEvent(false);

But the 2nd parameter (WorkflowCompletedEventArgs e) is really just a serialized representation of the workflow itself. So all the public properties are available as properties.

So, in my case, I just have my exception handlers add the exception to the public collection owned by the flow. This is really cool for capturing all the exceptions in one place. For example, say I'm persisting some data elements. It could be that many of the data elements are bad. If I throw an unhandled exception for the first one, I never see the rest until the data gets scrubbed and reposted. Then I bomb on the 2nd one. But with this approach, I can just add the exceptions to the output collection and de-serialize in the calling code.

SQL Server post Insert Trigger

I mostly want to document how to do this so I don't forget.

Here's the problem space: you've got a table (call it kjh) with some element (a). Whenever a new row is inserted into kjh, you want to insert a row into another table (kjh2), with some of the data (lets say column a) being duplicated.

This can be useful for a lot of things. You could use this to audit the values in a table, for example. In my case, I needed to copy off some of the data, so that the data could be modified, but the original data would be left alone for reporting purposes.

There 2 small issues. First, how do you get the data from the newly inserted row into the copy? Second, how do you trap and ignore the error that comes from trying to insert a duplicate row into the kjh2 table. This matters, in my case, because there are other things that can insert into kjh2, so there's a race condition. In the vast majority of cases, the trigger should insert cleanly. But it's possible that other processes could insert. Since there are no foreign key constraints here -- and since the other processes may legitimately have the right to do the insert to kjh2 before kjh -- I run into the possibility of this error.

One of the things that perplexed me is that this seems like it would be such a common problem. But multiple web searches basically lead no where. I finally found a kind soul on msdn to help.

On to some code. First the tables, just to show I have nothing up my sleeve.

CREATE TABLE [dbo].[kjh](

[a] [int] NULL,

[b] [int] NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[kjh2](

[c] [int] NULL

) ON [PRIMARY]

Now the trigger:

CREATE TRIGGER trinsert ON kjh

AFTER INSERT
AS

IF ((select a from inserted) between 2 and 19)

BEGIN TRY

INSERT INTO kjh2(a)
SELECT a
FROM Inserted ;

END TRY

BEGIN CATCH

DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; DECLARE @ErrorNumber INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorNumber = ERROR_NUMBER();

if (@ErrorNumber <> 2627)

-- raise the error

END CATCH

Just a couple notes.

"Inserted" is a temp table that SQL automatically creates which contains all the freshly inserted rows.

The error number mentioned is the error for a unique constraint violation.

and... *poof*... i've pulled a trigger out of a hat.

Missing Technical Phrases

Each season seems to bring a slew of new buzz words. Synergy, paradigm, core competencies and others have all graced our ears.

I'd like to officially propose some new, more down-to-earth ones. Please feel free to leave a comment with your own additions. Perhaps, we can create a list to present to those on high at Google and truly establish some meaningful phrases.

Inverse Redundancy:

Rather than putting one application on multiple servers in order to add stability and scalability, this is the practice of putting every application and service on the same server(s) in an apparent effort to increase clutter and decrease stability.

It has a related term...

Inverse Staff Redundancy

this is the condition of having the same person assigned to multiple roles on many projects. Typically, a project team or manager will present an org chart with many layers and lots of boxes. But looking closely, you'll see that most of the boxes below the management layer have the same resource. This is also known as "Office Space Syndrome", in a reference to the guy in the movie Office Space, who had 14 bosses.

Psychotic Optimism

This is the view that things are going well in spite of substantial evidence to the contrary. You've been on that project. I know I have. It's 4 days from delivery. You're 7 weeks behind schedule and have no resources actually working on it anyway. And your executive manager declares that the project is going well and will be delivered ahead of schedule.

Security By Annoyance

This is the practice of making security policies that serve no real purpose except to annoy the users, thereby creating the illusion of security. Technical examples include things like password restrictions that are so complex, MIT students can't understand them, sessions that conveniently expire right before work is committed, and locally-running software (hard drive encryption, virus checkers, etc.) that consume 60% of PC resources.

The primary value of this is three-fold. First and foremost, it safeguards the jobs of the security team, since their work is visible and seemingly important. Second, it drives off would-be hackers by making the common functionality too painful to bother hacking. And third, it decreases system load as common users simply give up and resort to pencil and paper.

It should be noted that this is not strictly an IT term, but can be seen in any airport security checkpoint.

Feel free to add your own.

bitlocker

I figured that since today is the official release date of Windows7 (I think?), I'd add an entry about one of its features.

Windows 7 now has drive encryption built in, in an application called bitlocker:

http://en.wikipedia.org/wiki/BitLocker_Drive_Encryption

I've been using it for a short time, and I have to say, it's pretty slick. It's just about what you'd want it to be. I have my sd card encrypted and it requires a password to view it the first time, then, once opened, allows free access until the computer restarts or the card is removed and re-inserted.

In general, I haven't really found anything with Windows 7 I don't like. I've got occasional annoyances with Microsoft because they seem to like to move things. You look for something in a menu or control panel section and it's not there. But you find it in a new section -- same functionality, same look and feel, just randomly moved.

I think one of the things about Vista was that Microsoft wanted to make it more userfriendly to people who weren't used to computers. At least, that's what they told me at the presentation I went to on it. I think there are 2 funny things about that. First, what is their target market? the 2 people in the mountains of Colorado who haven't looked at a computer in 12 years? I mean, computers are so key to our culture now that schoolkids grow up on them. Why target the computer illiterate when there are so few left. (Perhaps their real target is in China or other places where PCs aren't so common?)

The other thing about this is that, while a few things have become easier, most haven't. In fact, (to the point) Windows7 is more closely tied to Server 2008 than anything else. So, depending on what you do, you may end up having to mess with user roles and group policies. Personally, I think this is great. I love having it broken down like that. But for the computer nubie, they'll get lost.

Still, 7 is an awesome product. It's fast, clean, visually impressive, feature rich -- it's the best OS Microsoft has every marketed. The integrated virtual machine is good (not awesome, but good), the bitlocker feature is pretty cool, the integration with Vista apps is cool -- and it actually *runs* them without crashing! All in all, it rocks.