Linq To SQL – A bit of fun – Cataloguing my CD Collection


I’ve been having a bit of fun with Linq to SQL and building some programs which will generate a catalogue of the CD’s I own. This journey has provided a couple of interesting lessons along the way. The most prominent I’ll share here.

Data Model

image I’ve settled on a very simple data model for the relational schema, which I’m storing in SQL Server Express (which seems to work without problems). The relational data model maps into the following Objects when using the ORM (Object Relational Mapping) features in Visual Studio.

The data model has one big deficiency, it is designed to cater for my musical tastes which are modern (Artists do Albums). If one wanted a “classical” focused, or accommodating, data model, then there are a bunch of fields one would add to the Artist (Conductor maybe as a synonym for FullName in my model). To tell the truth, I’ve no idea what would need to be added. I would need to do some research, and talk with a classical music audiophile.

Building the data model in Visual Studio is a snack. The relational (SQL) project gives all the functionality to build tables, define keys, define the referential integrity constraints (foreign keys). The Linq to SQL class object, is a snack to to work with, just drop the tables from the Server Explorer onto the design surface (the links get sucked up from the relational database tables.

Loading the Data

image

I’ve explored beforehand how Windows Media Player stores albums in the file system. You have a structure as shown. Where the “Shared Music” is the root directory from which all Artists (as sub directories), then Albums (as sub directories) below the Artist, are stored.

The major functional code in this part of the process is in the following two C# methods.

        private List<DirDetail> ReadDirectory(string rootDir)
        {
            List<DirDetail> retVal = new List<DirDetail>();
            foreach (string artistDir in Directory.GetDirectories(rootDir))
            {
                retVal.AddRange(ReadAlbums(rootDir, artistDir)); 
            }
            return retVal;
        }

        private List<DirDetail> ReadAlbums(string rootDir, string artistDir)
        {
            List<DirDetail> retVal = new List<DirDetail>();
            foreach (string albumDir in Directory.GetDirectories(artistDir))
            {
                DateTime created = Directory.GetCreationTime(albumDir);
                ArtistAlbum Details = new ArtistAlbum(albumDir, rootDir);
                retVal.Add(new DirDetail(Details.Artist, Details.Album, created)); 
            }
            return retVal;
        }

The rest of the support object are pretty simple data container object. The use of the List<DirDetails> and AddRange is a particularly elegant way of acuminating, in a type safe manner, a result list of things which need to be squirted into the SQL data tables.

The following two methods show how Linq to SQL makes loading to the database tables so simple.

        internal void Update(List<DirDetail> loadValues)
        {
            Guid unfiledLocation = GetUnfiledLocation();
            foreach (DirDetail LoadAlbum in loadValues)
            {
                Guid ArtistID = GetArtistID(LoadAlbum.Artist);
                InsertAlbum(unfiledLocation, ArtistID, LoadAlbum.Album, LoadAlbum.Loaded);
            }
        }

        private void InsertAlbum(Guid unfiledLocation, Guid ArtistID, string LoadAlbum, DateTime LoadDateTime)
        {
            var AlbumQuery = from Albums in ctx.Albums
                                 where Albums.Title == LoadAlbum 
                                 && Albums.ArtistID == ArtistID
                                 select Albums.AlbumID;
            foreach (var LoadedAlbumID in AlbumQuery)
                return;
            Album newAlbum = new Album();
            newAlbum.AlbumID = Guid.NewGuid();
            newAlbum.ArtistID = ArtistID;
            newAlbum.DateLoadedToLibrary = LoadDateTime;
            newAlbum.LocationID = unfiledLocation;
            newAlbum.Title = LoadAlbum;
            ctx.Albums.InsertOnSubmit(newAlbum);
            ctx.SubmitChanges();
        }

The “foreach/return” is a lazy way of  checking that the Album has not been loaded previously. Propagating the insert by just filling up the properties on the generated class and calling the “InsertOnSubmit”, “SubmitChanges” methods is just too simple. This old programmer appreciates the simplicity. No more building SQL statement, building parameters, getting the data binding right, many lines of code I did not have to write.

 

Next in this series – Using Linq to SQL in a Winforms UI (I promise I’ll write it, there are a couple things which took a bit of figuring out in this end of Linq to SQL)

Digg This
Advertisements

, , , , ,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: