MSDE? (1 Viewer)

[kev160967]

Hi,
I've just been having a play with the music database sources (I want to add support for the album artist tag). It seems straightforward enough, but one thing I find myself doing a lot is comparing the SQLlite stuff with MS SQL Server. Now I've got to admit that I have something of a bias here, as I work with MS's product all the time in my day job (and I really like it). Having said that, I do keep on coming across things which strike me as being compromises based on SQLLite limitations. So, finally, to my question. I've no feeling for how good SQLLite is in terms of performance, but would people think there'd be any mileage in doing an alternative (optional) MSDE back end database implementation? I don't want to waste my time writing something that nobody else would touch with a bargepole, so I thought I'd see what people thought about the idea first. Any performace improvements aside, I certainly think it could make for cleaner code in media portal itself.

Kev

 

[samuel337]

I think the idea is to move the music database on to the TVEngine3's DB structure (i.e. MSSQL2005) once the TV phase is completed.

Otherwise, I think its not worth it installing MSDE/SQL2005Express on all client machines, the gain is minimal. As for cleaner code, what do you mean?

Would MSSQL CE make it better?

Sam

 

[kev160967]

I think the idea is to move the music database on to the TVEngine3's DB structure (i.e. MSSQL2005) once the TV phase is completed.

That's interesting - I must take a look at the TV engine's code at some point

Otherwise, I think its not worth it installing MSDE/SQL2005Express on all client machines, the gain is minimal. As for cleaner code, what do you mean?

Having read up on SQLLite a bit more, it's not as cut and dried - it implements more language features than I anticipated initially. However there are a few SELECT/ACT/SELECT/ACT operations taking place in the C# at the moment, and I'd be inclined to implement them as stored procedures. Apart from simplifying the relevant code, we've also seen big performance improvements in our own apps by reducing the amount of round tripping to the database.

Also, I've added an idAlbumArtist column to the song table. I obviously need to load Song objects with this data when they're fetched from the database. If you take a look in MusicDatabase.cs you'll see there are dozens of effectively identical (in terms of what they retrieve) select statements pulling back song data for different purposes, some using explicit joins, some joining via the where clause. With the current code I'd have to alter every one of these (instead I've rewritten the code so all the methods call a single method, just specifying a different where clase). If I was writing against SQL Server I could write a view for this, and then always select against the view, reducing the number of places I'd need to change my select clause to 1.

Anyway, that's probably enough waffling about it,
Kev

 

[samuel337]

Ah ok, I know what you're talking about now. IIRC, there were some cartesian joins in there too.

It's just that it still seems like overkill to install SQLServer 2005 on each client as well. The only other lightweight database that has sprocs is MS Access as far as I know (not sure about SQL Server CE).

Sam