Home > MOSS 2007 > FBA: Combining Stored Procedures and Power of CTEs

FBA: Combining Stored Procedures and Power of CTEs

January 26th, 2008 Leave a comment Go to comments

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!

  1. CREATE PROCEDURE [dbo].[GetUsers]
  2. (
  3. @email AS VARCHAR(50) = NULL,
  4. @username AS VARCHAR(50) = NULL,
  5. @pageIndex AS INTEGER,
  6. @pageSize AS INTEGER
  7. )
  8. AS
  9.  
  10. SET NOCOUNT ON
  11. --Set the page bounds
  12. DECLARE @PageLowerBound int
  13. DECLARE @PageUpperBound int
  14. DECLARE @TotalRecords   int
  15. SET @PageLowerBound = @PageSize * @PageIndex
  16. SET @PageUpperBound = @PageSize - 1 + @PageLowerBound
  17.  
  18. --Create a temp table for pager logic
  19. CREATE TABLE #PageIndexForContacts
  20. (
  21. IndexId INT IDENTITY (0, 1) NOT NULL,
  22. ContactID UNIQUEIDENTIFIER
  23. )
  24.  
  25. --Insert into temp table
  26. IF( @email IS NOT NULL )
  27.  
  28. INSERT INTO #PageIndexForContacts(ContactID) SELECT ContactID FROM Contacts
  29. WHERE  LOWER(Email) LIKE LOWER(@email)
  30. ORDER BY Email
  31.  
  32. ELSE IF(@username IS NOT NULL)
  33.  
  34. INSERT INTO #PageIndexForContacts(ContactID) SELECT ContactID FROM Contacts
  35. WHERE  LOWER(UserName) LIKE LOWER(UserName)
  36. ORDER BY UserName
  37.  
  38. ELSE
  39. INSERT INTO #PageIndexForContacts(ContactID) SELECT ContactID FROM Contacts
  40. ORDER BY UserName
  41.  
  42. SELECT @TotalRecords = @@ROWCOUNT
  43.  
  44. SELECT
  45. Comment,
  46. CreationDate,
  47. Email,
  48. IsApproved,
  49. IsLockedOut,
  50. IsOnline,
  51. LastActivityDate,
  52. LastLockoutDate,
  53. LastLoginDate,
  54. LastPasswordChangedDate,
  55. PasswordQuestion,
  56. ProviderName,
  57. ProviderUserKey,
  58. UserName
  59.  
  60. FROM
  61. Contacts con,#PageIndexForContacts conp
  62. WHERE
  63. con.ContactID = conp.ContactID AND
  64. conp.IndexId <= @PageLowerBound AND conp.IndexId >= @PageUpperBound
  65.  
  66. RETURN @TotalRecords
  67.  
  68. -- Drop the temp table
  69. DROP TABLE #PageIndexForContacts
  70.  
  71. SET NOCOUNT OFF

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:

  1. with UsersCTE as
  2. (
  3. SELECT
  4.  
  5. ROW_NUMBER() over (order by con.UserName, con.CreatedOn, con. Email) as RowNum,
  6. con.UserName,
  7. con.CreatedOn,
  8. con.Email
  9. FROM
  10. dbo.Contacts con
  11. )
  12. select
  13. UsersCTE.*
  14. from
  15. UsersCTE
  16. where
  17. RowNum between @PageLowerBound and @PageUpperBound

Retrieving Users in 10 records chunks

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:

  1. with UsersCTE as
  2. (
  3. SELECT
  4. ROW_NUMBER() over (order by con.ContactID,con.UserName,con.CreatedOn,con.Email) as RowNum,
  5. con.ContactID,
  6. con.UserName,
  7. con.CreatedOn,
  8. con.Email
  9. FROM
  10. dbo.Contacts con
  11. )
  12.  
  13. SELECT
  14. UsersCTE.RowNum,
  15. conm.ContactID,
  16. conm.CreatedOn,
  17. conm.Email,
  18. conm.UserName
  19. FROM
  20. dbo.Contacts conm INNER JOIN UsersCTE ON conm.ContactID = UsersCTE.ContactID
  21.  
  22. WHERE        UsersCTE.RowNum IN (SELECT MIN (UsersCTE.RowNum)
  23. FROM UsersCTE
  24. GROUP BY UserName
  25. HAVING Count(*) = 1)
Categories: MOSS 2007 Tags: