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

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

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)
del.icio.us Tags: C#,linq,sql,Windows Media Player,Linq to SQL,Visual Studio 2010

Rhetorical Questions to Ask Yourself at Work

As alluded to in a previous post, this post repackages some of the thoughts which I put into a recent job application.

I’ve put this up as a separate post because I suspect that the grains of wisdom which are contained here could of user for others.

Question	Reasoning
What don’t I know about this?	The answer to this question frequently prompts me to conduct further research into a topic. My role as an Enterprise Architect frequently has me asking this question.
Do I need to know more about this?	I am the first to admit that my knowledge of a subject is not complete. The positive answer to the question frequently prompts me to conduct further research into a topic, request training in the topic, or seek advice from others (internal to the ASB or externally).
What could I have done better?	This form of self-assessment is something that I frequently apply to my own performance at work.
What need doing?	The capacity to determine what needs doing, and by whom, is one which I exercise frequently. This one is applied with only one eye on the project plan; the other eye is on the broader picture.
What are the broader consequences of this?	Working in an Enterprise Architect Practice, has me asking this type of question frequently. There is strong need to assess choices, and decisions in a broader, enterprise wide context. This perspective allows to one to see the strategic directions which should be taken, rather than the tactical (or point solutions) choices which are made.

 

del.icio.us Tags: work performance,self-assessment,performance

Writing Job Applications

This post is slightly different one.

I’ve been ruminating about why I dislike writing job applications. Something which I should be doing rather than writing this! But, I’ve a couple of insights into the process which are pertinent.

The first is a piece of poetry from which the Quote “the hand that rock the cradle rules the world”. The poem is a homage to motherhood, but also describes one of the aspects of my dislike of writing job applications.

The original poem by William Ross Wallace “What rules the world”:

Blessings on the hand of women! Angels guard its strength and grace, In the palace, cottage, hovel, Oh, no matter where the place; Would that never storms assailed it, Rainbows ever gently curled; For the hand that rocks the cradle Is the hand that rules the world. Infancy’s the tender fountain, Power may with beauty flow, Mother’s first to guide the streamlets, From them souls unresting grow— Grow on for the good or evil, Sunshine streamed or evil hurled; For the hand that rocks the cradle Is the hand that rules the world.

Woman, how divine your mission Here upon our natal sod! Keep, oh, keep the young heart open Always to the breath of God! All true trophies of the ages Are from mother-love impearled; For the hand that rocks the cradle Is the hand that rules the world. Blessings on the hand of women! Fathers, sons, and daughters cry, And the sacred song is mingled With the worship in the sky— Mingles where no tempest darkens, Rainbows evermore are hurled; For the hand that rocks the cradle Is the hand that rules the world.

 

Why does this resonate with my dislike of writing job applications? Well, I’ve an attitude that a “gentle hand” gets more mileage, than any other approach. This belief has its roots in year of experience of on the job training. Being a leader, and providing leadership, in the IT profession often requires one to teach.  I frequently have the occasion to explain new concepts, system designs, or systems architectures a process which is didactic

Writing a job application feels to me a bit like a bit of a “heavy handed” approach to stating ones worth.

Well, I’ve completed the application now, and submitted it. So, I’m free of that burden for now.

I’ve another couple of thought which I’ll blog in another post. These are reflections on the way I approach managing my performance at work. These condensed from the processes of answering one of the selection criteria in just completed application.

del.icio.us Tags: job applications,William Ross Wallace,What rules the world