Skip to main content

DAL Ponderings ...

Here's something I have just posted to the NZDNUG (NZ .NET User Group) sql-server mailing list that I thought might make for a useful post:

"Hi all,

I have just completed a largish project designing and building a back-end system that is implemented across a series of processes, some of which are multithreaded. I have effectively designed and built this system from database design through to external interface implementation. A relatively complex system by requirement, but it has all come together fairly nicely (pats self on back ;-)).

What I have come to realise however in looking back at this experience, and in stepping back and looking at the 'complete' system, is that the part where things get a little twisted is in my sproc implementation. Although it all works nicely, I find that development of TSQL and SQL scripts in general, when compared to .NET (and OO) development tends to get a little 'hairy'. I find that TSQL and SQL don't really lend themselves to structured software design techniques, so I just tend to develop scripts (using as much foresight and caution as is practical of course) as the need develops.

I've studied databases to stage III at university, and have worked closely with databases throughout my IT career, but have found that there is not much documentation out there (or perhaps I've just not come across much) that goes into in-depth theoretical detail as to how sprocs can be organised in a structured manner.

I'd like to open a discussion about this to investigate what peoples thoughts are in this regard.



And another one:

"…further to my previous email…

For example – you have then need to access/update/insert lots of small bits and pieces of seemingly unrelated data – some scalar, some that would return/update/insert only a few 'small' records. Lump them all together in a single API type sproc ( i.e. pass thru a parameter, and receive back the appropriate data/confirmation), or make a separate sproc for each?

Another scenario - you have large chunks of data that are being pushed and pulled through a system. The data has potentially been modified as it is shifted around. The database code needs to ( i.e. it is appropriate for the db code to) analyse it to be able to determine if a change has occurred, then marshal the data accordingly. The data may be passed in this way though several different marshalling 'gates' before it finds it's way to it's home in the db table(s). Is it best to generate a tree-like series of sprocs that the data percolates though before it reaches the tables, or try to lump the whole thing into a single script?

What I'd like to be able to develop I think is a more mature way of managing the problem of data manipulation through the sproc 'layer'. Ideally I'd like to be able to apply some methodology - in the same way I can organise my .NET code into manageable portions by employing OO methodologies.

After some development experience it does seem to become clearer to distinguish which section of a data manipulation procedure should be performed in db-side code and which shouldn't. Some discussion on this topic would also be warranted though I think, especially with the onslaught of technologies such as DLINQ which are set to dissolve these db-side/custom-app-side data manipulation boundaries even further.


Edit - 20101016:

When I originally made this post on the NZ .NET user group mailing list, several senior developers came back to me and said bluntly "don't put that business logic in your database" and/or "the RDBMS is designed for data, not code"...all I can say is, they were so, so right...


Popular posts from this blog

HOW-TO: Apply a “baseless merge” in Team Foundation Server 2010 (and 2012)

Another purely technical post on TFS...
The scenario We wish to migrate code between branches that do not have a branch/merge relationship, in order to expedite urgent changes being made by a project team, without disrupting on-going BAU development work. Sample branch hierachy/strategy Imagine the following branching strategy in TFS (visible by connecting to TFS via Visual Studio 2010 or 2012):

Essentially you have a "DEV" branch, which has a "QA" branch, which in turn has a "PROD" branch. DEV is the branch that you would be using for BAU development. As a piece of development matures, you move it into QA, where it is tested by your internal QA team. There may be further changes made in DEV that are moved into the QA branch as the QA team pick up issues. Once the QA team are happy with a packaged of changes, they will move them into PROD, which is essentially the hand-over to the customer. The PROD branch represents the software that the customer has.


HOW-TO: Add/edit a field in Team Foundation Server 2012 using Visual Studio 2012

It's been a while since I made a purely technical post...

So, today I wanted to make a change to a Microsoft Team Foundation Server 2012 (TFS2012) instance that I am working with to reflect "Actual" time spent on a task - mainly for reporting purposes, and because I have found in the past that making this minor process adjustment yields a relatively useful metric over the long-term.

I am using the Microsoft Scrum 2.1 Process Template ( for a project that I am working with. So that I don't forget how to do this (again!) I will blog-post the procedure I've used to add this field to the template as a screen-shot-based tutorial, as follows...
Before Assuming you are familiar with the Scrum Process Template (2.1-ish) - open a task and take a look at the "Details" section, as follows:

 This is where I want my "Actual" field to show up.
Get the Power Tools Download and install the latest v…

Eclipse/Android error: "Multiple dex files define [...]"

Wow, I am really going nuts blogging this-evening - 2nd post in less than an hour. 

Anyway this is a particularly nasty error that I keep running into with Eclipse/Android when starting the emulator after I have not run it for a little while. Since I run the risk of permanently forgetting the solution to the problem every time I walk away from my Android project (and thus having to spend a painful hour-or-so digging up the procedure again), I will blog it here, for my benefit, and for the benefit of anyone who may also suffer the same problem.

The gist is that when you start the emulator in debug mode (that is, you hit the button in the following image), you get the following error message come out on the console and a nasty popup telling you nothing more than there is an error with your program and you need to fix it:

[2012-04-06 23:20:57 - Dex Loader] Unable to execute dex: Multiple dex files define Lcom/google/gson/ExclusionStrategy;
[2012-04-06 23:20:57 - SimpleList] Conversion to Dal…