I'm now writing stored procedures in C#, on the SQL CLR, as I have some complex data mangling to do, which I've already solved in C# elsewhere. Instead of converting the code to a long T-SQL stored procedure, I decided to stick it in as is.

Anyway, I got an error message "System.InvalidOperationException: The context connection is already in use." when calling the stored procedure from a trigger. In the trigger, I defined a new connection, did some stuff and called the stored procedure:

using (SqlConnection cn = new SqlConnection("context connection=true"))
{
    int stuffID = GetStuffIdFromDatabase(cn);
    StoredProcedures.usp_GetMyStuff(stuffID);
}

and in the stored procedure, I ran an update statement:

    SqlContext.Pipe.ExecuteAndSend(new SqlCommand("UPDATE Stuff SET [Description] = 'Updated from trigger' WHERE ID = " + stuffID));

I googled around for a while without much luck, but eventually realised that the SqlContext.Pipe.ExecuteAndSend must be creating its own connection. I moved the stored procedure call out of the using block, and it all worked fine.

n.b. I posted this for the benefit of other people who will, in future, google for the error message. If you are one of these people, I hope it helps you, because my regular readers have undoubtedly moved on by now...

If you've opened a table in SSMS, and you want to clear an existing value to NULL, click on the value, and press Ctrl+0 (zero).

Hurrah! I get to use DLinq, therefore making me a DLinquent.

[n.b. I googled for "dlinquent dlinq" to see if anyone else has thought of that pun before, and there were no results, which means I thought of it first, hence I am officially witty. Others have used the term dlinquent, but in a different context, thus they cannot justifiably diminish my wittiness. If anyone else tries to pass off "DLinquent" as their own, please refer them to this blog post. Thanks.]

OK, here goes. Let me select a couple of hundred rows from a table and do something with them:

var musicians = from t in DB.Musicians
                select t

foreach (Musician musician in musicians)
{
    // Do a thing ...
}

Cool. Now lets do some other stuff:

foreach (Musician musician in musicians)
{
    foreach (Album album in musician.Albums)
    {
        // Do a thing ...
    }
}

Wow. Even more cool. Actually, hang on, that took ages. Let's try that again with a stopwatch. Hmm. 4 seconds, and there are only 200 musicians, and the database is on the local machine.

Time for SQL profiler, and stepping through the code...

OK, so the select doesn't actually run the select against the database, it just defines it. The foreach runs a select statement. So does the next foreach. Back to the top foreach again. Yup, another select against the database. And another, and another, and, er, hang on, it's fetching data one row at a time. I think it would be quicker to get it all at the beginning, and do the stuff when it's all been fetched.

var musicians = from t in DB.Musicians
                select t
Array<Musician> musiciansArray = musicians.ToArray();

foreach (Musician musician in musiciansArray)
{
    foreach (Album album in musician.Albums)
    {
        // Do a thing ...
    }
}

That's a bit faster. It's fetched all the musicians when it built the array. The albums are still being fetched one at a time though. [Please excuse me while I google...]

Aha! Right, try again.

DataLoadOptions loadOptions = new DataLoadOptions();
loadOptions.LoadWith(m => m.Albums);
DB.LoadOptions = loadOptions;

var musicians = from t in DB.Musicians
                select t
Array<Musician> musiciansArray = musicians.ToArray();

foreach (Musician musician in musiciansArray)
{
    foreach (Album album in musician.Albums)
    {
        // Do a thing ...
    }
}

Brilliant. Now the albums are loaded at the same time as the musicians, right up front. Down to two hundredths of a second.

OK, so I've just had a great idea.

Consider Cyril, who I've been in sporadic touch with for the last 10 years or so. We'd like to think that we were friends, but as we live so far apart, we only see each other every 10 years or so, and the rest of the time we phone or email.

Cyril has lots of photos (3Gb) that he'll never want to lose. He's backed them all up to an external drive, so if his main hard drive breaks, he'll still have the photos. However, if a Cessna 172 falls onto Cyril's house, and breaks both drives, he'll have no photos. He might just get round to uploading them all to an ftp site somewhere, but in the mean time, he needs a backup buddy.

What Cyril does is put all his photos in a password-protected zip file (so I can't sneak a peek), burns the zip file onto a DVD*, and sends it to me in the post. When I get the DVD, I'll check that I can see the zip file, and then put the DVD in a safe place. As I live 218 miles away from Cyril, when the plane lands in his lounge, the DVD won't be damaged, so I'll send him a copy for when he's found a new computer (and house. And plane).

Clearly it's not the most robust solution, but it's a quick and cheap way for storing data that won't change.

As it's my idea, I'm prepared to be a backup buddy for up to 5 people. If you'd like me to keep a DVD or two safe for you in Uckfield, East Sussex, UK, then send me an email.

* Or a pen drive, or whatever.

Let us suppose you have a customer, who has a website, which has a database. Let us also suppose that you have a local version of this website and database that you use for development and support. Let us also suppose that you periodically update your database with data from the live site.

Let us suppose (alright, that's the last time I'll say it) that since the last update, you have added rows to a table with an auto-incrementing ID column on your database, and the customer has done the same with her database. When you next come to synchronise the databases, you have two different rows with ID 3948. What to do?

In SQL Server, the identity seed and identity increment both default to 1. These can be changed to get around this problem. Changing them both to 10 on the live database will mean that all rows will end in zero (10, 20, 30 ...). On the development database, changing the seed to 5 and the increment to 10 will mean that all new rows will end in 5 (15, 25, 35 ...).

When merging the two databases, the rows should slot nicely between each other. A nice side effect is that you can tell which site a row was added from by the last number in the ID.

I'm not sure if this is just my setup, but I've noticed a little buglet. You can try this at home too. You just need Visual Studio 2008 and a .NET 3.5 C# project:

1. Using the mouse, position the cursor on a line of code (e.g. line 38)
2. Without changing the mouse position, click in the breakpoint margin on a different line of code (e.g. line 51).
3. Notice that the breakpoint appears on line 38, instead of the expected line 51.
4. Clicking again in line 51's margin removes the breakpoint on line 38.

A little annoying, as I tend to use the mouse for breakpoints more than the F9 key.

[Tags: visualstudio2008 bug]

Yesterday I started a new software consultancy contract for a championship football club. They are already using MatchMatix, my own micro-ISV software product, for analysing matches and players (in real time). For the next three months I'll be integrating this with a new database and intranet.

As I have full control of the development process, I can choose my own development practices, including test-driven development. It's been a while since I worked anywhere that encouraged TDD, and I'm really enjoying writing a unit test, and then making it pass, and then running the whole suite of tests, and checking they still pass.

I'm also pleased to be using .NET 3.5, so I've rustled up some database access classes with sqlmetal, and I'm using DLinq to call them. I'm planning some WPF action soon too.

You'll be happy to know, that although I will be launching MatchMatix at some point in the near future, this blog won't start going all Joel on Software, and talking lots about the product. If I do mention it, the focus will be on the related technology. When it's appropriate, I'll start a blog on the MatchMatix site where I can trumpet its virtues.