Having developed .NET web applications for about 5 years, I can safely say that one of the most useful tools provided by Microsoft since the emergence of ASP.NET 2 is Membership. Simply run an executable to unpack all of the tables and stored procedures to your SQL Server database, configure your users and roles through the nice configuration interface and you’re set. The amount of time it must have saved developers over the years is immense - quite simply, it’s the bee’s knees!
Membership and an SQL Server database are perfect partners, but what about those small projects where database hosting costs limit you to the dreaded Microsoft Access. Well, there’s a provider for that too... sort of. A collection of classes to support Membership with Access were made available some time ago – cool!
We’ve been implementing dual Data Layers and Interfaces recently for reusable components that could use either database type. Normally, an SQL Data Layer is created, then copied and customised for Microsoft Access. Sadly, I’ve encountered a few Gremlins which have slowed the Access conversions down;
The Membership.GetUser(object providerUserKey) facade
Works a charm with SQL the providers. Having tried the same thing using the Access Membership providers, I ran into a load of errors which lead me to a cracker of an explanation;
Awesome! By their own admission Microsoft had not yet fully implemented these classes, but I reckon throwing an Exception would have been more appropriate – it would have some time at least. Membership.GetUser(string userName) however is implemented – but all my tables reference users by their Id’s not their usernames – rather than change code in the project, we went with implementing the forgotten GetUser overload, which was pretty easy considering it was only a few lines different to the userName approach.
Guid vs. Int
SQL Server stores the MembershipUser Id’s as Uniqueidentifiers (Guid). Access doesn’t support these data types, so anywhere you’re passing a Guid around as a method parameter, be sure to pass it as an Object and then cast it back to a Guid in your SQL Data Layer, this way Access will be able to accept the same parameter and use it without casting.
Parameter weirdness
Another thing to look out for if you’re creating a bunch of queries in SQL, and later plan to copy these over to Access is the order in which you assign your parameters. SqlParameters can be assigned in any order, OleDbParameters can only be assigned in the order in which they are used. If OleDbParameters are declared in the wrong order, you’ll end up with incompatible data type errors or data in the wrong fields... bloody annoying!
So these are my findings, hopefully they may be of use!
Nick