Archive for March, 2010

How to query your iTunes library

March 14, 2010 2 comments

As much as I hate iTunes its pretty much a necessary evil if you have a iPod or iPhone. One of the biggest problems I have is when a track is moved, there is no way to automatically locate the track, its just marked as missing and there is not much you can do about it other than locate it manually.

I had a browse today and came across a suggestion on Digg for using the WSH and the COM components to find tracks in the library and delete them. I decided to port this over to c# so we could really query the library. Here’s how I did it.

In a Visual Studio project, add a reference to the iTunes type library then create a class and add a method with the following code

public IEnumerable<IITFileOrCDTrack> GetITunesTracks()
    foreach (IITFileOrCDTrack track in new iTunesAppClass().LibraryPlaylist.Tracks)
        if (track.Kind == ITTrackKind.ITTrackKindFile)
            yield return track;

we now have a query object holding the entire media library. This gives us the ability to find all the tracks in the iTunes library which are missing by writing a simple method such as

public IEnumerable<IITFileOrCDTrack> GetITunesTracksWithoutALocation()
    return from track in GetITunesTracks()
    where string.IsNullOrEmpty(track.Location)
    select track;

the track object has a delete method which means we can just call delete if we wanted to remove all the missing items from your media library.

This is a quick and easy way to delete lost items, but could also be used for deleting podcasts older than a certain period of time, and probably many other things, hope you find it useful. I can make the source available if anyone needs it.

Categories: Development Tags:

A easier way to create stored procedures from a schema

March 13, 2010 Leave a comment

Unfortunately, we don’t always get complete autonomy when writing software, the most typical example of this is having to use stored procedures for all data access. I am not going to bother with the whole debate about performance and security it’s a simple fact that many of us have to use sprocs at work despite our personal preference.

We used to use CSLA ( I just shuddered while I typed that) for our business objects but we are now evaluating other options, on a recent project I spent some time using stored procedures and LINQ2SQL to create a winforms solution and was pleasantly surprised by how easily the two co-existed. After a false start regarding relationships between entities we got the solution up and running completely using sprocs for all data access while still being able to use the built in LINQ2SQL Unit of work.

For the stored procedures however, I had forgotten how tedious it was to write simple data access code which could easily be generated, I evaluated a few different options (T4 templates etc) then chose to roll my own not because I suffer from Not Invented Here syndrome but as an excuse to learn some new technology, which segues us onto a new open source project i create Ubersprogen. while this is  possibly the stupidest names open source project in history. I needed a way of generating CRUD and select by primary key and foreign key which i could then use for my dbml file in linq, giving me much of the power of Linq2Sql while still keeping our DBA’s happy.

While its not going to change the world it has made my life easier and if your in a similar position, possibly yours too.

The elevator pitch

Ubersprogen will generate all the CRUD sprocs you will usually need from Sql Server 2005/2008  including dropping existing procs and headers if required. This is done using a templating engine called nvelocity from the castle project. This allows you to change the layout and logic (to a certain extent) of the sprocs as and when you need. It should be able to ensure the the sprocs follow your companies coding standards too.

A bit more detail

I used WPF for writing the front end, then LINQ2SQL for the data access and nvelocity for the templates, its not finished, but it is in a position where it will generate stored procedures from a db, so it seems about the right time to announce it to the world. It pretty much my attempts at WPF and I have attempted to implement MV-VM so there may well be some horrid anti patterns in there. I would hope that everyone should be able to make it work easily. The UI consists of one form.

Ubersprogen Ui

As you can probably gather it allows you to select which procs to generate, how many files to generate, whether to use NoCount and what isolation level. the gold key depicts a Primary Key and the silver key a Foreign Key.

you can download the binaries here and a link to the source is here

If you have any other ideas of functionality, please leave a comment. If you think its crap, let me know too!