Reza Alirezaei’s Blog

Since I am a lazy developer I normally don’t like to author multiple stored procedures if I can afford to aggregate all the logic in one place without causing too much complexity. For example when working with custom authentication providers you are often required to write separate stored procedures for GetAllUsers, FindUsersByName, FindUsersByEmail that can be called from their counterpart overridden methods in your custom authentication provider’s code. Following example demonstrates how you can have one stored procedure that can handle GetAllUsers, FindUsersByName, FindUsersByEmail logic in one stored procedure. As shown below, @email and @username parameters can be null which means you can ignore these two filters and get all the users instead. One important thing to note is the table that holds all the user information in this example. It is called Contacts with ContactID as the primary key, but yours might be different!

One new feature in SQL Server 2005 that comes handy a lot when dealing with paging and recursions is Common Table Expressions (a.k.a CTEs) . Common Table Expressions are much nicer way to work with derived tables and specially when you need recursion functionality. Let’s say , you want to retrieve all your users and show them on <wssawc:SPGridView> control with pagination and row numbers. It’s ideal if you deal with records in chunks (specified by @pageIndex parameter) to save bandwidth and time instead of retrieving all users at once. Then in your code (MemberGrid_ClickNext), what you need to do is to send the right @pageIndex parameter to the stored procedure which in turn takes care of paging the requested records and return the right chunk to the client .Here is how CTEs can be your friend:

It is not uncommon that you find yourself in a situation that your identity store (for which you are writing your custom authentication provider) allows duplicate usernames, WOW that’s scary isn’t it? For example in the Users table structure I provided above , I am extracting user information from a table that is indexed on ContactID and not on username. Picture a system (Like a CRM instance) that allows a non-member becomes a member and upon this transformation users can keep their username. Reza (username= rezaa) becomes a member but Reza is still the same person with the same username, billing address, credit card information and bluh bluh . From CRM standpoint Reza is no longer old Reza but a new identity, therefore a new ContactID is issued for him and some of his associated information is copied to the new identity including his username. This leads us to a situation in which we have to deal with duplicate usernames ! Your stored procedure returns two records (with different ContactIDs but same username) and in your code when you construct MembershipUserCollection you get an exception that two keys with the same value cannot be added to a hashtable (which makes sense!). You need to delete the duplicate rows right in your stored procedure before returning them to your custom authentication provider. Alternatively, you can handle avoiding duplications in your code by putting your code in a try-catch block and move on to the next record should adding the current record throws an exception , but question is why do you need to take this approach when you can easily handle it in your stored procedure? So how exactly does this all work? I am so glad you asked! Here you go: