Design patterns, object oriented design, and the employment thereof can be just as much a cure in search of a disease as they are a cure for a disease. I’ve recently run into this lock, stock, and barrel in a project with my unnamed employer.

There is this project, let’s call it FLEA, which is a relatively straightforward web forms application. Written as a front end to handle some day to day process for a particular customer, it was made out to be some kind of shimmering example of, well what the architect might have said “Stuff I read on the back cover of a book in the lavatory.”

It had “everything” from service locators, coding against interfaces, and perhaps most glaringly, a monstrous construct that was referred to as “The Repository Pattern on steroids.”

It’s one thing to permit reuse of code by decoupling interface from implementation, but it’s quite another to hobble the developer by only making available interfaces that operate on fully populated domain objects.

I saw places where they would set a flag in a table record by loading an object, all of it’s sub-objects, and sub-objects, with carefully constructed IF-THEN logic to subvert graph loops, then set ‘myPerson.Disabled = true; PersonRepository.Save(myPerson);’. Now its fine if you present that interface, then detect that only the Disabled flag changed, and update just that, but what I found was quite another beast: It proceeded to delete half of the sub-objects, re-add them, then ignore the rest of them, and do a multi-table update of the full object (without using a join, anywhere in the logic) creating a new database connection for every other call. Needless to say, this worked brilliantly when the thing only loaded 2 or 3 master objects but when it was scaled to include ten thousand users, disabling a user, and I use this phrase QUITE LITERALLY sent the server out to lunch. You could go to the corner store and back before it finished running a top-down depth loop of connection grabbing calls to update stuff.

It’s still not probably quite clear the degree of repository extremism I’m talking about here:

We had:
PersonRepository (with methods to GetAll, GetByLocation, GetById, Update, and Delete)
PersonLocationRepository (which was called repeatedly for every person loaded, even when displaying them in a dialog box, to avoid having to learn what a join is.)
PersonLocationActivityRepository
ActivityRepository
PlacesRepository
PersonEthnicityRepositiry
PersonJobCodeRepository
PersonHRRecordRepository
PersonMotherInLawRepository (Ok, I’m making that up.)
plus about 20 more (not exaggerating, I swear.)

That variety alone is not a problem. But calling LoadPerson or whatever the actual routine was called invoked a service locator, which loaded a bunch of other things, which called a service layer, which called a business logic layer, which then loaded a repository, which loaded a DAL object for each one of these things, DEEP. So that loading a person ended up instantiating a generic service loader factory, which created a service layer factory, and then proceeded to call a business logic layer factory, to create a DAL factory, and eventually instantiate a DAL object, to read the ethinicity of a person. Then all of those were destroyed, and the PersonHrRecord was loaded with a new chain of the same stuff.

Loading the user administration page literally kicked off 300,000 database connections, and called half a million stored procedures, loaded ALL of the data client side, and filtered out the results with fancy anonymous method calls to IList enumerators. And just because they realized what a hulking mess this was on the database, all of the results were chucked into ViewState, which to save time was chucked into the Session.

I, along with two others, retooled the entire project using Linq To SQL in about a month of half time working on this, but wow it was amazing.

If it would have been a well crafted joke, it would have been too meticulously executed to be even considered funny.

My conclusion is that it’s actually bad because of the way they made the database calls. Not the patterns themselves. There was also a demonstrable lack of understanding of what the implications of iteratively looping and loading are for performance.

The theory, I was told, was to make it reusable. From a design perspective, it looked like they might have been able to do that, but when I looked at what the actual layers were doing, apart from what they looked like in UML diagrams, MAN was it a mess.

Put another way, it’s one thing to design your application for reusablity, but quite another to implement it in the same spirit. At the outset of the project, it probably was originally, long ago, in a galaxy far away, a good idea to design a service layer separate from business logic layer, and a DAL layer, etc. But to just stop there and make the rest ‘work somehow’ without concern for the consequences of the ways the data actually got into the bottom bits.. and the way that it was unfiltered until the top bits, that was just silly. Of course I shouldn’t blame the pattern, but if they hadn’t been aiming for that methodology, as opposed to the operations that they were actually performing underneath it, it might have been a better ending.

Sure it’s common to say ‘That’s just an implementation detail’ when you’re designing, and scoff it at for a while until it comes time to implement it, but holy crap, when it comes time to implement it, all you HAVE are the implementation details. At that point they not only deserve SOME attention, but ALL of it.

This is the difficulty with GoF: Pretty Conceptual Pictures. People think they understand these, and it gives them the confidence to go implement this stuff without learning the other fundamentals. Like, well, SQL. (I’ve actually heard things like “I’m a C# developer, I don’t really DO SQL!” If I had my way, I’d put them in a corner with a command line OSQL.exe tool for the next three months and not let them come back until they’d gone through Books Online from top to bottom three times after that statement.) Pretty pictures like this also make it easy to gloss over an actual lack of planning on the bottom so that management doesn’t know they’ve bought into a dud until the delivered app kills the clients database farm.

The goal of an application is to have it work. Whatever abstractions you put on it to make it ‘easier to develop’ are not billable features to the client. You do not get extra line items on the proposal for Service Locator Patterns, and certainly that’s not a justification for screwing up the actual project.

Customer’s Boss: “This thing is a piece of crap, it kills our servers!”
Developer: “But it was completely decoupled from the implementation!”
Customer’s Boss: “Oh, right, sorry, I guess you did actually deliver what we ordered. I’m glad you know what you’re doing when it comes to object oriented design. So many people focus solely on the final product, without so much as a thought devoted to the design process. Clearly you have attended one of our finest higher educational institutions to know such things. And besides, I didn’t realize you were a SENIOR developer, I should be yelling at a grunt code right now. You shouldn’t be troubled with implementation details.”
Developers Boss: “I smell a RAISE for one of our brightest SENIOR developers!”
Developer: Nancy Kerrigan and I are going to Disney Land!

At first I thought I didn’t need parameters, but I was wrong. Everyone needs parameters, or else it’s not all that useful. Of course, that doesn’t mean you can’t still do parameterization correctly. So I took this lemon-like opportunity to demonstrate how to [make lemonade and...]

• Write Web Parts for WSS3
• Integrate a Parameterizable SQL Query (with a few security caveats I’ll readily admit, but injectibility isn’t likely one of them, more on this in a bit) with an in-page GridView and show the output
• How to hook it up to the Form web part using the standard SharePoint IWebPartRow interface.

First thing is first, to run a web part that hits a SQL server directly, you need Medium Trust, or to have modified your policy file to allow Sql Query permissions in your SharePoint environment.

Not far after first thing is the second thing, which I’ll discuss now (second.) I’m using the non-sharepoint style web part, otherwise known as ASP.NET web part (System.Web.UI.WebControls.WebParts.WebPart) as the base class for my part.

C#

Why have I done this? Becaue it’s the new recommended best practice from the kids at Microsoft who built it. If you need SharePoint, you can get it with a wink, a nod, and the SharePoint Object Model.

I have the Query string, the connection stuff, the authentication type, and probably a few other doodads I forgot to mention as web part parameters. (If you look at my code, the Sorting stuff is all commented out cause I kept screwing it up, and I felt that I didn’t really want to mess with it anymore for the time being. The web part works without it.) The reason I’m bothering to show you on the page here is to point out a few things, the Attributes with which we adorn the properties are slightly different than those of the SharePoint web parts variety of them you might know from WSS V2.

Personalizable means that it will be serialized and stored as a parameter and re-populated after the part is instantiated.

WebBrowsable means that it will genearate an editor field in the Default Editor Part (These were formerly known as ToolParts in the old model,) when you go in to configure the web part.

WebDisplayName just means “This is what we’ll label it in the Default Editor Part”.

Category is short for “The name of the collapsable section under which it appears in the default Editor Part.”

C#

Now, for the security caveats: I’ll tell you that it’s a little dangerous to put the Sql username and password in your web part instances configuration properties, cause if someone decides to export it, then you can see the username and password; but this does not apply if you use the Windows authentication mode. So, you figure out whether or not you can work around the issues.

As for the Parameterization:

I extract the expected parameters, as specified in the web part property, using a string-through, ie, I jump through the string one character at a time, looking for the parameters. This means I get them all in one pass, instead of a bunch of regexes or finds and splits, etc. They then go into a list, which is the list of expected ‘named paramters’ coming from the connected web parts. If I don’t get a match between the ones that are in your query and the ones connected to me, then I display a message.

C#

So, after I set the fact that I have some parameters, I have to know when to read the values, and after much tribulation, and reference perusal, I have discovered that the time to suck the data from your producing source is OnPreRender:

C#

This is a rough model of the web part, and in need of a bit of refactoring to remove some duplication, but you get the idea. I hope.

Attached is the code files/project for VS 2008. Click to Download it. I’ve decided, after considering it carefully that I will let you have the wsp already built. No warranty. If you don’t understand how it works, you can ask, and I’ll try to answer. And NO, I won’t customize it for your needs, nor will I change it to do something else — with one exception: if you figure out a way to fix the paging junk then I’ll put your fix in and give you credit. (Yes I could do it eventually, but I don’t want to mess with it right now!) So, here have at it!

Late Breaking edit: The code provided here has been fixed now that I got my real version from the source control. I’ve corrected the problem with the ‘default values’.

(PS: Use WSSOnVista from BambooSolutions.com to install SharePoint on your development Vista host and run/test/debug without remote desktop etc.)

I’m going to go over two things on this context lifetime issue: A) What the problem is, exactly, and B) what I did to fix it in my Webpage example.

Part I: The problem

The idea of the DataContext in LINQ is that it tracks changes for you. Like a fancy living Dataset. You put objects into it, and holds state that represents whether or not you’ve changed something since it was read from the db, and when you call .SubmitChanges() you get them all committed. The problem with this is that the context object HOLDS A REFERNCE to all of the objects it’s ever touched since it’s instantiation. That’s an issue when you’re trying to say… change and object, or attach an object that you’ve constructed from serialization, but has the same primary key as another that’s already in there… So what to do? Squash the whole context after a quick call to SubmitChanges();

Part II: The solution (That I used.)

The last sentence of the last paragraph of ‘the problem’ alludes to the fact taht I squash the context; however, it doesn’t say how I hook on that in an aspx app. I read around and found a suggestion that I try out a Request-Scoped context. How might one do this?

Global.asax! You add two handlers:

So How do I Access this easily? Wrap it up!

Ok, so that was a minor ‘problem’ but really just a poor understanding of the technology on my part. I know better now, I think.

There’s something better that LINQ-to-SQL does, or rather, it’s a lot SEXIER in programming terms. Since you can ‘where’ expression your way through life with a lambda, it sure as heck makes it simple to Where your way to dynamic SQL queries generated by something simple in the UI (like a query builder interface.) I have a perfectly great example, but rather than try and write and claim it myself, I’ll give you the source that I got, and perhaps develop a more familiar and complete example later on in another post: C# 3.0 In a Nutshell (book website ‘excerpt’)

Why a Login script? Well, because this script affects the HKEY_CURRENT_USER registry hive which only exists for a particular user when that user is logged on. At least for all intents and purposes that’s true. I think it may be possible to somehow enumerate all users on the HKEY_USERS hive, but I didn’t go that far. I apologize up front for not going the extra mile on this one, but hey, look! A free script.

NOTE: If you copy and paste it, and get a compile error, check for abberant the new-line characters caused by the squishing of my code by the text rendering on the page.

After that runs it will generate in the output window (or the console if you use OSQL or some other command line tool to run it,) this form of output. I pipe it to another file, and change the last ‘union’ to a ‘go’ and we’re done. I probably should have also excluded the user dbo, but I didn’t you can if you want.

I have been preaching things like this for a long time, but Steve here is a bit more fluent about explaining it. I’ll not try to turn you off to the ideas presented here by summarizing it before you read it. Honestly, it’s the perfect expression of a relatively amorphous emotion I’ve had about compilers and their place in the world of CS for a long time, (but obviously I can’t take credit for the quality of Steve’s writing, of course.)

Make yourself read it though, please, if you never do another thing for me as long as you know me. (Halo_Four, if you’re reading this, this means you too.)

What is Dirty Data? According to IBM, and various other folks, Dirty Data is data that is extracted from the real world that cannot be nicely integrated into relational models because it’s not organized, or doesn’t fit a particular model. Luckily for us, there are various products out there to ‘clean it’ or techniques recommended by the various database manufactures to squeeze ‘clean’ over ‘dirty data.’

First of all, WHAT?! Do we work for computers or do they work for us? There is no such thing as dirty data, the fact of the matter is that if your system pukes because the data isn’t in the right format, then you’re using the wrong system to process it. The real world isn’t just ‘full of dirty data,’ it’s full of data, and computers are here to help us process it. It’s completely ridiculous for the database companies to say that my data is dirty.

Another sore point with me is the fact that they say that performance problems can stem from ‘unreasonable relationship tracking.’ I want to know too many facets of my data, and it’s not ‘database correct’ of me to want my data and to be able to eat it too, from every angle, in real time. Pardon me, but the fact that YOUR relational database doesn’t handle this kind of scenario well doesn’t mean that my request is unreasonable. If I want to be able to efficiently track back to the original record by any one of any number of my many to one or many to many, or one to many relationships, that’s my requirement, not your place to tell me that it’s unreasonable of me to expect that it can be done.

The problem is that relational database proponents, and in particular the big three commercial implementors, are all engaging in this ‘you can’t do that, in real life’ and ‘its your fault our apps are slow,’ propaganda, and I won’t stand for it.

Just as an example, and I’m sure it’s not the only way, I’m using Relavance, an associative model engine which has none of these limitations.

Even Relavance aside, or even the entire associative model aside, it’s still really stupid of any real computer scientist to think, and especially to decree that certain problems are just not to be solved using computers because they are not well suited to modern technology. If that were the attitude that people had throughout history, then we’d still be toggling in code on the front panels of computers the size of Olympus Mons. Microsoft may be the market leader, and Oracle and IBM may sell a lot of bits and bytes, and tow very heavy loads in the marketplace, but they are not here to tell me what is and is not possible in computer science based on their own models of the universe.

</rant>

Rather than try to start by comparing this method to what you already know how to do, I’m going to just pretend that you’ve always wondered how to store data, and never have done it before.

Applications acquire data. They also retrieve data and display it for users, or do something otherwise useful with it. Sometimes it’s the users who give the data to the application directly. Actually that happens quite a lot.

We store different bits of information in different places, and we do so for the purpose of organizing things that go together. We go to all of this bother because we’re likely to want to get them back out together. What that means to you and me as developers is that we spend quite a lot of time thinking about the kinds of things we want to get out of the system before we go putting them in. Arguably we try to think of everything we’d like to get out of it beforehand, and that’s how we’ll know we’re done with our model – when it provides us with a place and method to put everything we need to know into the storage and get it back out on demand. We’d use this information of what we need to get out later as the basis for a ’schema.’ A layout of how all the data looks when it’s just sitting around waiting for someone to get it back out again.

Great! That sounds simple enough! Ah, but life isn’t so forgiving, and neither is programming. Today we think we know what we want, and that’s it, but tomorrow, that all might change. In fact, if you ask enough pointy haired bosses about what they want from the data they’re paying you to store in a database, then you’re likely to get a few things in common, and a bunch of special requests. Some of what they want is ’stuff’ that needs to be in there, but also, they want ‘ways to organize things’ on the way out especially. Some people would call these differing requirements views, or reports.

Fine, so we can’t possibly think of all of the kinds of views and reports, so we might as well just not think about them ahead of time, right? We’ll just store everything as generally as possible while leaving some things together that obviously go together in pretty much all cases, and that way, we can let the person getting things out tell us what they want at that time, and never worry about it again. Especially not that would push the application delivery date back! Right? WRONG!

When we store data, each thing we’d like to know about is called a Concept. And all of the different aspects of the concepts are different sorts of ‘lights in which we’d like to view these concepts,’ which we might term a Context. So in life we have concepts, or things or ideas of interest, and contexts, which are the glasses through which we view these concepts.

When we want to store a concept in our knowledge store, we put it in there, and designate it to be accessible in a particular context. Of course it’s not really friendly to the user to just pull every concept out with every time we want to retrieve information, so the idea of limiting a concept to a particular context seems to make a lot of sense. We do this in our everyday conversation, even sometimes a single word can mean many things! Surely that’s an idea we’d have difficulty describing in a file… Or is it?

Lucky for us, Everyone in the Universe uses Associative Knowledgebases, and everyone knows that in an associative knowledgebase, you add concepts to the base, associated with a particular context, and from there you are able to retrieve concepts relative to the particular context of reference. You can have a single concept be availabe in as many contexts as are needed, and you don’t need to keep putting multiple representations of the same concept in over and over again to get the job done. Each concept has a bit of data that goes with it, and it can be associated to other concepts to represent what sorts of ideas it shares with its associates. Now an association isn’t just a one type deal, you can associate on different ‘axes’ of association to represent different aspects of what the two concepts have in common. Who knows, two concept items may also be associated to the same context, so when you go to retrieve information relative to a context, you’d get them both anyway! As you may have picked up, each ‘concept’ can be represented by a single ‘item’ or a set of ‘items’, which are tiny units of free-floating information. Every item is associated to at least one context, and some associated to thousands of contexts, just depending on what you need from it. Each item is universally addressable by it’s own unique four ordinal vector. Each item stores its own value, as well as the vectors of each of its associates, grouped by which axis they happen to be associated on. The items can be associated to other items, or to entire contexts, it’s completely up to us! We can associate multiple items on the same axis or split them up, and there is virtually no limit to the number of associates we can have in any particular axis. (Could be anywhere from zero to several million or even sometimes several billion other concepts.) Since we have this wildy variable method of associating one uniquely addressable item with any number of uniquely addressible items, on any number of axes, it’s lucky for us that we don’t need to know how many of what kind of associates we can have right up front. Even items in the same context can have different numbers of associates, and there is no negative consequence to the other items. It’s cool that nobody decided that we’d have to allocate null associates to represent items without associates, because when we got over a hundred, well that would just be ridiculous! We certainly can’t easily convert items into something that fits completely into an Excel spreadsheet! That’s quite alright, because we have a lot at our disposal now. (And if we REALLY like Excel spreadsheets, then we can limit the information coming out in such a way that it fits, complete all of the totally unnatural quirkiness of any spreadsheet, some empty columns in some rows, and flat tuples that are all but meaningless without some formal explanation of what they are and what they mean — like any Excel spreadsheet, pointy haired bosses love those.)

Of course we can predetermine some of the ways in which we’ll have to establish associations based on the context we wish to use to represent our particular concept, and some of them will be common to all items in the same context. These we’ll call ‘explicit mappings’ of information. For example if you’re adding the concept of an Apple to the Fruit context, you’ll likely want to also associate it to a member of the Plants context to indicate what it grows on (we can even say that this association exists on the ‘Fruit Grows On’ axis.) These are simple, and the rules are always the same.

We also have the ability to examine concepts we wish to associate with a context AS THEY ARE BEING ADDED TO THE BASE and apply a set of rules to them to determine if, while we’re at it, we ought to associate them with any other concepts or contexts. In this way, we can define a list of rules which dynamically determine to which other contexts and concepts we would like to associate with this concept, based on well… what it is. Contexts and concepts don’t have a set structure. They can be anything we like them to be, and represent any aspect we’d like them to. For example, sure we have a context called Fruit which is associated to all of the things that you and I call “Fruit” like Apples, Pears, and Bananas (a personal favorite of mine!) but you can also talk about a context that’s more abstract like ‘Found in Africa’. So all of the things we know about that are ‘found in Africa’ can be associated to the Found in Africa context. That means we can have some of the Fruits, and some of the Plants all be associated to the same context of ‘Found in Africa’, so when we ask the Knowledge base for what sort of concepts it knows about in the context of ‘found in Africa’ we get both some Fruits and some Plants. Isn’t data storage great!?!

Well, pointy haired bosses are never satisfied, so when they see you can do such wonderful cartwheels with individually simple data, they still want more.

“I’d like to be able to find what sort of Fruits and Plants we can find in Africa that are legal to export to the United States.”

Just when we thought we’d thought of everything, we now have to add something else!

“Ok,” we tell the boss, “No Problem!” It’s a good thing that we don’t have to completely redesign the knowledge base just to add a new context for some concepts we already have! We can just create a new context called “Exportable To the US” and associate the proper concepts with that context as well. Great!

So, it’s also relatively simple now to construct ‘queries’ for this kind of data store. When we write the application to show the boss what sorts of Fruits and Plants are Exportable to Africa, then we just have to retrieve all of the items associated with the context of Exportable to the US and also Found in Africa, and then make sure that the results are either Fruits or Plants. It’s rather like drawing a venn diagram. Since we used rules to make the associations on the way into the storage system, we don’t have to do anything but grab the ready and waiting list of associates we need on the way out! Sure it takes a tiny bit of effort when the concepts are inserted into the store for the rules to figure out where they are to be associated, but that all runs asynchronously, and we didn’t care about it when we added it. Only now when the boss comes to get his answers does the effort really matter! So, with almost no effort at all, we’ve given the boss what he wants. We can only hope that there aren’t too many more bosses to satisfy today, because we were supposed to go out for a drink after work. It’s a good thing everyone uses Associative Knowledgebases, or who knows how long we’d be here?!?

A Post Facto I forgot to mention, this is a description of a working system. It’s called Relavance, and it really does all of the wonderful things I just described as if they were hypothetical.