Doodle Graphics

Don’t look now, but I think this might actually be a technical posting!

So, I’ve finally had an excuse to start playing around with LINQ – some data clean up tasks that I could have done by hand, but it’s always more fun to throw together a little app or two than spend your life in Enterprise Manager (or SQL Studio).

The key issues I came up against were with DataGrids, and picking and populating columns.

A fairly simple setup for editing the Taxonomy (remember, this is a developer application, so it works for me):

 
We have a ComboBox providing the Category, and then the DataGrid allows us to edit the Subjects. The “Subject ID” is an auto-generated GUID that is occasionally useful for the developer to know, so I wanted to display it, the data model looks like this:

 
As you can see there’s an additional column in the database that I’m not interested in for the DataGrid: “TAX_CATEGORY_ID” this is always powered by the Category selected in the combo box, so I didn’t want to have to set it every time.

Part 1: Designer Support for LINQ Database Models (DataContext)

When wiring up the DataGrid, I initially tried the following:

editSubjects.DataSource = from ts in db.TaxonomySubjects
   where ts.TAX_CATEGORY_ID == (Guid)chooseCategory.SelectedValue
   orderby ts.SORT_ORDER 
   select new { ts.TAX_SUBJECT_ID, ts.SORT_ORDER, ts.DISPLAY_NAME, ts.METADATA_NAME };

This almost worked – I had the columns I wanted, but I couldn’t edit the values at all – it turns out that if you use an anonymous type you lose the ability to edit it – the DataSource doesn’t know how to safely update the object, so disables editing.

On top of that, you have no way to change the column properties of the DataGrid in the designer.

I’d read about the ASP.Net LinqDataSource control, and so (naively) assumed there’d be a similar one for WinForms, but apparently not, and after hunting around and reading the readme of the samples that come with VS 2008 (namely the WinFormsDataBinding sample), I found the follow “solution”:

1. Add a BindingSource to your form
2. In the .Designer.cs class, add the following in the InitializeComponent() method:
   

   this.taxonomySubjectsBinding.DataSource = typeof(MetadataManager.TaxonomySubject);

   Replacing the binding name with your binding name, and the value in typeof() with the value from your “Linq To Sql” classes
3. You can now assign that BindingSource to your DataGrid and get access to the column builder:
   
   As you can just about see, the DataGrid doesn’t display the TAX_CATEGORY_ID column although it is there, and I’ve been able to supply nice names for the columns.

My data source wire up now looks like this:

editSubjects.DataSource = from ts in db.TaxonomySubjects
   where ts.TAX_CATEGORY_ID == (Guid)chooseCategory.SelectedValue
   orderby ts.SORT_ORDER 
   select ts

Which is just taking all the TaxonomySubject objects that match the query, and the DataSource is then filtering their properties into the correct columns without me having to worry about anything else.

Part 2: Populating hidden columns/Auto Generated Ids in Linq DataContexts

Having successfully cleaned up my DataGrid columns, I now had two remaining problems to solve around adding a new record:

1. Generating the new GUID for the subject.
2. Adding the TAX_CATEGORY_ID value to the hidden column.

In the database, the TAX_SUBJECT_ID field is defined as:

[TAX_SUBJECT_ID] [uniqueidentifier] NOT NULL   CONSTRAINT [DF_TAX_SUBJECTS_TAX_SUBJ_ID]
  DEFAULT (newid()),

So, it’s the primary key column, with a default value of a new GUID. This is where our problems begin.

LINQ understands that SQL can auto-generate the values for columns on a database, provided:

Primary keys that are designated IsDbGenerated=true should also have a DBType with the IDENTITY modifier.

Note that my column here is not marked as an Identity column – to use the IsDbGenerated property, anything that goes in that column must be an integer.

I noticed that my DataContext class contained the following methods in a region called “Extensibility Method Definitions”:

partial void InsertTaxonomyCategory(TaxonomyCategory instance);
partial void UpdateTaxonomyCategory(TaxonomyCategory instance);
partial void DeleteTaxonomyCategory(TaxonomyCategory instance);
partial void InsertTaxonomySubject(TaxonomySubject instance);
partial void UpdateTaxonomySubject(TaxonomySubject instance);
partial void DeleteTaxonomySubject(TaxonomySubject instance);

So, let’s implement an insert in a new class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MetadataManager
{
  public partial class MetadataDatabaseDataContext   {     partial void InsertTaxonomySubject(TaxonomySubject instance)
    {
      if (Guid.Empty == instance.TAX_SUBJECT_ID)
        instance.TAX_SUBJECT_ID = Guid.NewGuid();

      this.ExecuteDynamicInsert(instance);
    }
  }
}

A really key thing to note here – if you create these methods, even as empty methods, you must call this.ExecuteDynamicInsert(instance); otherwise any changes you try to submit will be lost.

Basically, before the changes are inserted into the database, this method fires for each one, and I’m creating a new GUID if the column is empty.

When adding just one new row this seems to work ok, but as soon as I try and add more rows, or two at a time, I get exceptions like:

"Cannot add an entity with a key that is already in use."

Hardly ideal – the key’s not in use, but I guess there’s something else I’m missing – any thoughts? On top of that, there’s no way to insert the Category ID into the foreign key column in this method.

So the final solution I came up with to resolve this was to hook into the RowEntered event on the DataGrid:

private void editSubjects_RowEnter(object sender, DataGridViewCellEventArgs e)
{
  int rowIndex = e.RowIndex;
  if (null == editSubjects["taxSubjectId", rowIndex].Value)
  {
    editSubjects["taxSubjectId", rowIndex].Value = Guid.NewGuid();
  }
  if (null == editSubjects["taxCategoryId", rowIndex].Value)
  {
    editSubjects["taxCategoryId", rowIndex].Value = (Guid)chooseCategory.SelectedValue;
  }
}

This lets me generate a new GUID for the row, and adds the Category’s ID to the hidden column.

Which feels to me like an awful cludgy hack (but then so does a lot of the LINQ stuff as well – it’s all inline SQL really isn’t it? Isn’t that supposed to be bad?), but then I read the rest of ScottGu’s blog post – to get a custom query hooked up to a LinqDataSource you have to hook into the Selecting event and replace the result property with your new query…

Any thoughts, let me know.

Well, as many other people have pointed out, you can import your blog into facebook as notes - it's quite clever an puts them all in the right place based on date.

Last time I tried this, I ended up with duplicate notes when I wrote a new blog post, so this is mostly to prove that it does that, or that something's changed and facebook no longer duplicates them.

I think it might be something to do with the fact that I'm not using a permalink as the posting guid, but the id (GUID) of the posting in the database, but who knows.

Anyone else having the same problem? Let me know.

And not just the facebook one.

I mean, come on, I fit the requirements - I have broadband, that supposedly runs fairly close to 8Meg, I've got IE installed somewhere, and I have Windows XP still - I know, luddite.

So, I missed Tribe last night, and a quick scan of the EPG on my freeview box implied that it wasn't repeated - I'd not looked that hard, it is repeated at 12:10am on Sunday.

So I finally get around to checking out the iPlayer site that I got a login for last month, find the episode of Tribe I'm after, and click Download. No, wait, click Install, and then come back, and click Download. No, wait, register again with a new user name and password that I choose, even though I still need to use the rubbish one they supplied to get to the listings.

So, finally, start downloading Tribe... 800+Meg. Now, from other sites I can easily pull down 1G in about an hour or so, so I think this shouldn't be a major headache...

Oh, should I mention I'll only have 5 days to view this?

So, while I wait, we watch last weeks episode of Heros on the Freeview box, no time limits to when I can watch that, I just watch it.

Go back 60 minutes later, and we're at 100Meg downloaded - I was expecting atleast half of it. Meh. Whatever. I've set the freeview box to record it on Sunday morning, and I'll watch it on the sofa in the lounge

Ben removed the BBC iPlayer application and Kontiki P2P service.