I manage my MP3 collection with my own hand-written tool because I already have a big database with information about the bands and songs.

One of the features of the playlist manager is to specify custom SQL code to select any of the 30,000+ songs in the database for the playlist.

This allows me to have playlists like:

• Any songs released in 1986
• Any songs by bands from Sweden before 1993
• Any songs released on "Sarah Records" on 10" vinyl.
• Songs by bands added to the database in the last 12 months

You get the idea.

For my next big trip I was thinking of which songs to take along. I only have about 40 Gig space for music on my player, which is enough for roughly 10,000 files.

One idea was to take songs from my all-time favourite bands, but the filter already returns over 10,000 songs and I wanted to add some other tracks as well. So I said, lets take just 50 songs from each of my 250 favourite bands. Which songs? I don't care, they can be random, but I don't want a song twice, regardless of the fact that I have duplicate MP3s because they have been released on multiple albums or compilations. The SQL to do this was not super simple, so that's why I documented here.

To keep things simpler, I first created two views, the first one filters out duplicate song titles:

CREATE VIEW UniqueSongs
AS
SELECT MIN(FileID) AS FileId, Title, BandKey
FROM SoundFiles
GROUP BY Title, BandKey

the second one returns my favourite bands:

CREATE VIEW FavouriteBands
AS
SELECT BandKey FROM Bands WHERE Rank > 8

Now to the main query, which uses a Common Table Expression:

WITH CTE AS
(
SELECT Row_Number() OVER (Partition BY b.BandKey ORDER BY ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))) As RowNo, 
u.FileId FROM UniqueSongs u INNER JOIN FavouriteBands b ON b.BandKey = u.BandKey
)
SELECT FileName, RootFolder + Location as Location, BandName, Title, Duration 
FROM SoundFiles 
WHERE FileId IN 
(
SELECT FileId FROM CTE WHERE RowNo <= 50
)

The first SELECT gets all the MP3s for my favourite bands,

ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)))

just returns a random number which we order by, so we get a different set of up to 50 songs each time we run the query.
The really cool part here is:

Row_Number() OVER (Partition BY b.BandKey ORDER BY

We partition our data by the BandKey in a random order and each row gets an new row number one higher than the previous one. For each new band, the row_number is reset to 1.

Here's how that looks for 3 songs per band:

The second SELECT gets the actual data required to build a playlist, the interesting part is in the sub-select in the WHERE clause:

SELECT FileId FROM CTE WHERE RowNo <= 50

Here we use our common table expression from above to get the first 50 fileIDs per band. Remember the RowNo is a continues increasing number, so filtering for anything less 51, gets us the first 50. Cool!

 

I have a classic many-to-many relationship, for example persons are members in clubs:


in the UI for a person I need to display the clubs the person is a member of and all the
other clubs to allow the person to join another club.

In T-SQL I would do this like this (assume 3 is my personId)

SELECT Name FROM Clubs
WHERE ClubId IN
  (SELECT ClubId FROM Members WHERE PersonId = 3)

SELECT Name FROM Clubs
WHERE ClubId NOT IN
  (SELECT ClubId FROM Members WHERE PersonId = 3)

 
then I moved to Linq To SQL

from c in db.Clubs
join m in db.Members
on c.ClubId equals m.ClubId
where m.PersonId == 3
select new { Name = c.Name };

from clubs in db.Clubs
where !(
        from members in db.Members
        where members.PersonId == 3
        select members.ClubId
       ).Contains(clubs.ClubId)
select new { Name = clubs.Name };

On to Linq to Entities, here the junction table 'members' is hidden
and I have use the persons collection on Clubs:

from c in context.Clubs
from p in c.Persons
where p.PersonId == 3
select new { Name = c.Name };

from c in context.Clubs
where !(from n in context.Clubs
        from p in n.Persons
        where p.PersonId == 3
        select n.ClubId
       ).Contains(c.ClubId)
select new {Name = c.Name};

The generated SQL for LinqToSql and LinqToEntities is similar but both are quite different from the hand written SQL. However the execution plans for all three are nearly the same.
 

For recent project I have to implement forms based authentication for SQL Server 2005 Reporting services. A great starting point is

‘Using SQL Reporting Services 2005 and Forms Authentication with the Whidbey/2.0 SQLMembershipProvider’ by Russell Christopher

http://blogs.msdn.com/bimusings/archive/2005/12/05/500195.aspx

It took me a while to get it all working but I succeeded in the end. The next problem was that this solution allows users who are logged on through the ASP.NET SQLMembershipProvider to be mapped to SQL reporting services users, but it doesn’t support the RoleManager in asp.net 2.0. If you have hundreds of users you would need to set security for each one them within Reporting services, not very practical. Instead I would like to assign them to asp.net roles and then set permissions for these roles within Reporting Services. Even though RS supports Windows Groups when using Windows Authentication I couldn’t figure out a supported way to do this with ASP.NET roles.

I first looked into the RS users, Policies and SecData tables but it didn’t help. However I could see that Windows groups are just another type of user in the users table. My idea was to try to get ASP.NET role names into this table and then pass in the name of the roles rather the name of the user when authenticating or authorizing a user.

The plan is to assign ASP.NET users to exactly one ASP.NET role and then use the name of that role rather the name of the user for reporting services.

I only needed four fixed roles for my application, so I started creating four dummy users ‘rsGuests, rsStandard, rsPremium, rsAdmins’ as ASP.NET users through the “ASP.NET Web Site Administration Tool”.

Over in Reporting Services I logged on as an admin user through forms authentication and in the properties of the home section I added four ‘new role assignments’, one for each user I just created. This way I got my four users into the RS database. I assigned all RS roles to the ‘rsAdmins’ user. This ensures that at least one user has admin access.

Back in the ‘Web Site Administration Tool’ I created four new roles with the same names as the four users. Then I added a bunch of real users to these roles.

So much for the preparation, the real change lies in the AuthenticationExtension class which is part of the Custom Security sample of Reporting Services. The key method here is GetUserInfo which is used by Reporting Services to get the identity of the currently logged on user. In the sample code the userIdentity returned as the HttpContext.Current.User.Identity, the Name Property of this class has the username which is used to look up permissions in RS.

I created a new class RSIdentity which I will use instead:

class RSIdentity: System.Security.Principal.IIdentity
{
    string _userName = string.Empty;

    public RSIdentity(string userName)
    {
        _userName = userName;
    }

    public string AuthenticationType
    {
        get { return "Forms"; }
    }

    public bool IsAuthenticated
    {
        get { return true; }
    }

    public string Name
    {
        get { return _userName; }
    }
}

And then changed to code in GetUserInfo as follows:

public void GetUserInfo(out IIdentity userIdentity, out IntPtr userId)
{
 if (HttpContext.Current != null
       && HttpContext.Current.User != null)
 {
     string[] rolesArray;

     rolesArray = Roles.GetRolesForUser();

     if (rolesArray.Length == 0)
     {
         userIdentity = null;
     }
     else
     {
         userIdentity = new RSIdentity(rolesArray[0]);
     }                        
 }
 else
       userIdentity = null;

 // initialize a pointer to the current user id to zero
 userId = IntPtr.Zero;
}

Here I am getting the first (and only) role the current user belongs to and pass that into my new RSIndentity class which is using it for it’s name property.

Now, when Reporting services is using GetUserInfo it gets the ‘identity’ for the role rather than the user.