Hi all,

 I'm really digging Hao Kung's Table Profile Provider samples in the Sandbox:

http://www.asp.net/sandbox/samp_profiles.aspx?tabindex=0&tabid=1

However, the samples are in C# and I'm more of a VB guy.  Has anyone had success converting this to VB?  I've tried using converters and then cleaning up the left-overs, but I'm getting an infinite loop somewhere and am not sure how to proceed.

 Thanks,

 MJ

Here is the converted SqlTableProfileProvider.vb file SO FAR...openly admitting that I have messed something up.

1    Imports System.Web
2    Imports System.Web.UI
3    Imports System
4    Imports System.Web.Profile
5    Imports System.Web.Configuration
6    Imports System.Security.Principal
7    Imports System.Security.Permissions
8    Imports System.Globalization
9    Imports System.Runtime.Serialization
10   Imports System.Collections
11   Imports System.Collections.Generic
12   Imports System.Collections.Specialized
13   Imports System.Data
14   Imports System.Data.SqlClient
15   Imports System.Data.SqlTypes
16   Imports System.Runtime.Serialization.Formatters.Binary
17   Imports System.IO
18   Imports System.Reflection
19   Imports System.Xml.Serialization
20   Imports System.Text
21   Imports System.Configuration.Provider
22   Imports System.Configuration
23   Imports System.Web.Hosting
24   Imports System.Web.Util
25   
26   Namespace Microsoft.Samples
27   
28       Public Class SqlTableProfileProvider
29           Inherits ProfileProvider
30           Private _appName As String
31           Private _appId As Guid
32           Private _appIdSet As Boolean
33           Private _sqlConnectionString As String
34           Private _commandTimeout As Integer
35           Private _table As String
36   
37           Public Overrides Sub Initialize(ByVal name As String, ByVal config As NameValueCollection)
38               If config Is Nothing Then
39                   Throw New ArgumentNullException("config")
40               End If
41               If [String].IsNullOrEmpty(name) Then
42                   name = "SqlTableProfileProvider"
43               End If
44               If String.IsNullOrEmpty(config("description")) Then
45                   config.Remove("description")
46                   config.Add("description", "SqlTableProfileProvider")
47               End If
48               MyBase.Initialize(name, config)
49   
50               Dim temp As String = config("connectionStringName")
51               If [String].IsNullOrEmpty(temp) Then
52                   Throw New ProviderException("connectionStringName not specified")
53               End If
54               _sqlConnectionString = SqlStoredProcedureProfileProvider.GetConnectionString(temp)
55               If [String].IsNullOrEmpty(_sqlConnectionString) Then
56                   Throw New ProviderException("connectionStringName not specified")
57               End If
58   
59               _appName = config("applicationName")
60               If String.IsNullOrEmpty(_appName) Then
61                   _appName = SqlStoredProcedureProfileProvider.GetDefaultAppName()
62               End If
63               If _appName.Length > 256 Then
64                   Throw New ProviderException("Application name too long")
65               End If
66   
67               _table = config("table")
68               If String.IsNullOrEmpty(_table) Then
69                   Throw New ProviderException("No table specified")
70               End If
71               EnsureValidTableOrColumnName(_table)
72   
73               Dim timeout As String = config("commandTimeout")
74               If String.IsNullOrEmpty(timeout) Or Not Int32.TryParse(timeout, _commandTimeout) Then
75                   _commandTimeout = 30
76               End If
77   
78               config.Remove("commandTimeout")
79               config.Remove("connectionStringName")
80               config.Remove("applicationName")
81               config.Remove("table")
82               If config.Count > 0 Then
83                   Dim attribUnrecognized As String = config.GetKey(0)
84                   If Not [String].IsNullOrEmpty(attribUnrecognized) Then
85                       Throw New ProviderException("Unrecognized config attribute:" + attribUnrecognized)
86                   End If
87               End If
88           End Sub 'Initialize 
89   
90           Public Overrides Property ApplicationName() As String
91               Get
92                   Return _appName
93               End Get
94               Set(ByVal value As String)
95                   If value Is Nothing Then
96                       Throw New ArgumentNullException("ApplicationName")
97                   End If
98                   If value.Length > 256 Then
99                       Throw New ProviderException("Application name too long")
100                  End If
101                  _appName = value
102                  _appIdSet = False
103              End Set
104          End Property
105  
106  
107          Private ReadOnly Property AppId() As Guid
108              Get
109                  If Not _appIdSet Then
110                      Dim conn As SqlConnection = Nothing
111                      Try
112                          conn = New SqlConnection(_sqlConnectionString)
113                          conn.Open()
114  
115                          Dim cmd As New SqlCommand("dbo.aspnet_Applications_CreateApplication", conn)
116                          cmd.CommandType = CommandType.StoredProcedure
117                          cmd.Parameters.AddWithValue("@applicationname", ApplicationName)
118                          cmd.Parameters.Add(CreateOutputParam("@ApplicationId", SqlDbType.UniqueIdentifier, 0))
119  
120                          cmd.ExecuteNonQuery()
121                          _appId = CType(cmd.Parameters("@ApplicationId").Value, Guid)
122                          _appIdSet = True
123                      Finally
124                          If Not (conn Is Nothing) Then
125                              conn.Close()
126                          End If
127                      End Try
128                  End If
129                  Return _appId
130              End Get
131          End Property
132  
133  
134          Private ReadOnly Property CommandTimeout() As Integer
135              Get
136                  Return _commandTimeout
137              End Get
138          End Property
139          '//////////////////////////////////////////////////////////
140          '//////////////////////////////////////////////////////////
141          '//////////////////////////////////////////////////////////
142          Private Shared s_legalChars As String = "_@#$"
143  
144          Private Shared Sub EnsureValidTableOrColumnName(ByVal name As String)
145              Dim i As Integer
146  
147              While i < name.Length
148                  If Not [Char].IsLetterOrDigit(name(i)) And s_legalChars.IndexOf(name(i)) = -1 Then
149                      Throw New ProviderException("Table and column names cannot contain: " + name(i))
150                  End If
151              End While
152          End Sub 'EnsureValidTableOrColumnName
153  
154          Private Sub GetProfileDataFromTable(ByVal properties As SettingsPropertyCollection, ByVal svc As SettingsPropertyValueCollection, ByVal username As String, ByVal conn As SqlConnection)
155              Dim columnData As New ArrayList
156  
157              'List(<ProfileColumnData>, columnData) = New List(<ProfileColumnData>, properties.Count) '
158              Dim commandText As New StringBuilder("SELECT u.UserID")
159              Dim cmd As New SqlCommand([String].Empty, conn)
160  
161              Dim columnCount As Integer = 0
162              Dim prop As SettingsProperty
163              For Each prop In properties
164                  Dim value As New SettingsPropertyValue(prop)
165                  svc.Add(value)
166  
167                  Dim persistenceData As String = prop.Attributes("CustomProviderData") '
168  
169                  ' If we can't find the table/column info we will ignore this data
170                  If [String].IsNullOrEmpty(persistenceData) Then
171                      ' REVIEW: Perhaps we should throw instead?
172                      GoTo ContinueForEach1
173                  End If
174                  Dim chunk As String() = persistenceData.Split(New Char() {";"c})
175                  If chunk.Length <> 2 Then
176                      ' REVIEW: Perhaps we should throw instead?
177                      GoTo ContinueForEach1
178                  End If
179                  Dim columnName As String = chunk(0)
180                  ' REVIEW: Should we ignore case?
181                  Dim datatype As SqlDbType = CType([Enum].Parse(GetType(SqlDbType), chunk(1), True), SqlDbType)
182  
183                  columnData.Add(New ProfileColumnData(columnName, value, Nothing, datatype)) ' not needed for get 
184                  commandText.Append(", ")
185                  commandText.Append(("t." + columnName))
186  ContinueForEach1:
187              Next prop
188  
189              commandText.Append((" FROM " + _table + " t, vw_aspnet_Users u WHERE u.ApplicationId = '")).Append(AppId)
190              commandText.Append("' AND u.UserName = LOWER(@Username) AND t.UserID = u.UserID")
191              cmd.CommandText = commandText.ToString()
192              cmd.CommandType = CommandType.Text
193              cmd.Parameters.AddWithValue("@Username", username)
194              Dim reader As SqlDataReader = Nothing
195  
196              Try
197                  reader = cmd.ExecuteReader()
198                  'If no row exists in the database, then the default Profile values
199                  'from configuration are used.
200                  If reader.Read() Then
201                      Dim userId As Guid = reader.GetGuid(0)
202                      Dim i As Integer = 0
203  
204                      While i < columnData.Count
205                          Dim val As Object = reader.GetValue((i + 1))
206                          Dim colData As ProfileColumnData = columnData(i)
207                          Dim propValue As SettingsPropertyValue = colData.PropertyValue
208  
209                          'Only initialize a SettingsPropertyValue for non-null values
210                          If Not (TypeOf val Is DBNull Or val Is Nothing) Then
211                              propValue.PropertyValue = val
212                              propValue.IsDirty = False
213                              propValue.Deserialized = True
214                          End If
215                          i += 1
216                      End While
217  
218                      ' need to close reader before we try to update the user
219                      If Not (reader Is Nothing) Then
220                          reader.Close()
221                          reader = Nothing
222                      End If
223  
224                      UpdateLastActivityDate(conn, userId)
225                  End If
226              Finally
227                  If Not (reader Is Nothing) Then
228                      reader.Close()
229                  End If
230              End Try
231          End Sub 'GetProfileDataFromTable
232  
233          Private Shared Sub UpdateLastActivityDate(ByVal conn As SqlConnection, ByVal userId As Guid)
234              Dim cmd As New SqlCommand("UPDATE aspnet_Users SET LastActivityDate = @LastUpdatedDate WHERE UserId = '" & userId.ToString & "'", conn)
235              cmd.CommandType = CommandType.Text
236              cmd.Parameters.AddWithValue("@LastUpdatedDate", DateTime.UtcNow)
237              Try
238                  cmd.ExecuteNonQuery()
239              Finally
240                  cmd.Dispose()
241              End Try
242          End Sub 'UpdateLastActivityDate
243  
244          Public Overrides Function GetPropertyValues(ByVal context As SettingsContext, ByVal collection As SettingsPropertyCollection) As SettingsPropertyValueCollection
245              Dim svc As New SettingsPropertyValueCollection()
246  
247              If collection Is Nothing Or collection.Count < 1 Or context Is Nothing Then
248                  Return svc
249              End If
250              Dim username As String = CStr(context("UserName"))
251              If [String].IsNullOrEmpty(username) Then
252                  Return svc
253              End If
254              Dim conn As SqlConnection = Nothing
255              Try
256                  conn = New SqlConnection(_sqlConnectionString)
257                  conn.Open()
258  
259                  GetProfileDataFromTable(collection, svc, username, conn)
260              Finally
261                  If Not (conn Is Nothing) Then
262                      conn.Close()
263                  End If
264              End Try
265  
266              Return svc
267          End Function 'GetPropertyValues
268         _
269  
270          '//////////////////////////////////////////////////////////
271          '//////////////////////////////////////////////////////////
272          '//////////////////////////////////////////////////////////
273          ' Container struct for use in aggregating columns for queries
274          Private Structure ProfileColumnData
275              Public ColumnName As String
276              Public PropertyValue As SettingsPropertyValue
277              Public Value As Object
278              Public DataType As SqlDbType
279  
280  
281              Public Sub New(ByVal col As String, ByVal pv As SettingsPropertyValue, ByVal val As Object, ByVal type As SqlDbType)
282                  EnsureValidTableOrColumnName(col)
283                  ColumnName = col
284                  PropertyValue = pv
285                  Value = val
286                  DataType = type
287              End Sub 'New
288          End Structure 'ProfileColumnData
289  
290  
291          Public Overrides Sub SetPropertyValues(ByVal context As SettingsContext, ByVal collection As SettingsPropertyValueCollection)
292              Dim username As String = CStr(context("UserName"))
293              Dim userIsAuthenticated As Boolean = CBool(context("IsAuthenticated"))
294  
295              If username Is Nothing Or username.Length < 1 Or collection.Count < 1 Then
296                  Return
297              End If
298              Dim conn As SqlConnection = Nothing
299              Dim reader As SqlDataReader = Nothing
300              Dim cmd As SqlCommand = Nothing
301              Try
302                  Dim anyItemsToSave As Boolean = False
303  
304                  ' First make sure we have at least one item to save
305                  Dim pp As SettingsPropertyValue
306                  For Each pp In collection
307                      If pp.IsDirty Then
308                          If Not userIsAuthenticated Then
309                              Dim allowAnonymous As Boolean = CBool(pp.Property.Attributes("AllowAnonymous"))
310                              If Not allowAnonymous Then
311                                  GoTo ContinueForEach1
312                              End If
313                          End If
314                          anyItemsToSave = True
315                          Exit For
316                      End If
317  ContinueForEach1:
318                  Next pp
319  
320                  If Not anyItemsToSave Then
321                      Return
322                  End If
323                  conn = New SqlConnection(_sqlConnectionString)
324                  conn.Open()
325  
326                  'List < ProfileColumnData > columnData = New List(__unknown < ProfileColumnData > collection.Count) '
327  
328                  Dim columnData As New ArrayList
329  
330                  'Dim ap As SettingsPropertyValue
331                  For Each pp In collection
332                      If Not userIsAuthenticated Then
333                          Dim allowAnonymous As Boolean = CBool(pp.Property.Attributes("AllowAnonymous"))
334                          If Not allowAnonymous Then
335                              GoTo ContinueForEach2
336                          End If
337                      End If
338                      'Normal logic for original SQL provider
339                      'if (!ap.IsDirty && ap.UsingDefaultValue) // Not fetched from DB and not written to
340                      'Can eliminate unnecessary updates since we are using a table though
341                      If Not pp.IsDirty Then
342                          GoTo ContinueForEach2
343                      End If
344                      Dim persistenceData As String = pp.Property.Attributes("CustomProviderData") '
345  
346                      ' If we can't find the table/column info we will ignore this data
347                      If [String].IsNullOrEmpty(persistenceData) Then
348                          ' REVIEW: Perhaps we should throw instead?
349                          GoTo ContinueForEach2
350                      End If
351                      Dim chunk As String() = persistenceData.Split(New Char() {";"c})
352                      If chunk.Length <> 2 Then
353                          ' REVIEW: Perhaps we should throw instead?
354                          GoTo ContinueForEach2
355                      End If
356                      Dim columnName As String = chunk(0)
357                      ' REVIEW: Should we ignore case?
358                      Dim datatype As SqlDbType = CType([Enum].Parse(GetType(SqlDbType), chunk(1), True), SqlDbType)
359  
360                      Dim value As Object = Nothing
361  
362                      ' REVIEW: Is this handling null case correctly?
363                      If pp.Deserialized And pp.PropertyValue Is Nothing Then ' is value null?
364                          value = DBNull.Value
365                      Else
366                          value = pp.PropertyValue
367                      End If
368  
369                      ' REVIEW: Might be able to ditch datatype
370                      columnData.Add(New ProfileColumnData(columnName, pp, value, datatype))
371  ContinueForEach2:
372                  Next pp
373  
374                  ' Figure out userid, if we don't find a userid, go ahead and create a user in the aspnetUsers table
375                  Dim userId As Guid = Guid.Empty
376                  cmd = New SqlCommand("SELECT u.UserId FROM vw_aspnet_Users u WHERE u.ApplicationId = '" & AppId.ToString & "' AND u.UserName = LOWER(@Username)", conn)
377                  cmd.CommandType = CommandType.Text
378                  cmd.Parameters.AddWithValue("@Username", username)
379                  Try
380                      reader = cmd.ExecuteReader()
381                      If reader.Read() Then
382                          userId = reader.GetGuid(0)
383                      Else
384                          reader.Close()
385                          cmd.Dispose()
386                          reader = Nothing
387  
388                          cmd = New SqlCommand("dbo.aspnet_Users_CreateUser", conn)
389                          cmd.CommandType = CommandType.StoredProcedure
390                          cmd.Parameters.AddWithValue("@ApplicationId", AppId)
391                          cmd.Parameters.AddWithValue("@UserName", username)
392                          cmd.Parameters.AddWithValue("@IsUserAnonymous", Not userIsAuthenticated)
393                          cmd.Parameters.AddWithValue("@LastActivityDate", DateTime.UtcNow)
394                          cmd.Parameters.Add(CreateOutputParam("@UserId", SqlDbType.UniqueIdentifier, 16))
395  
396                          cmd.ExecuteNonQuery()
397                          userId = CType(cmd.Parameters("@userid").Value, Guid)
398                      End If
399                  Finally
400                      If Not (reader Is Nothing) Then
401                          reader.Close()
402                          reader = Nothing
403                      End If
404                      cmd.Dispose()
405                  End Try
406  
407                  ' Figure out if the row already exists in the table and use appropriate SELECT/UPDATE
408                  cmd = New SqlCommand([String].Empty, conn)
409                  Dim sqlCommand As StringBuilder = New StringBuilder("IF EXISTS (SELECT 1 FROM ").Append(_table)
410                  sqlCommand.Append(" WHERE UserId = @UserId) ")
411                  cmd.Parameters.AddWithValue("@UserId", userId)
412  
413                  ' Build up strings used in the query
414                  Dim columnStr As New StringBuilder()
415                  Dim valueStr As New StringBuilder()
416                  Dim setStr As New StringBuilder()
417                  Dim count As Integer = 0
418                  Dim data As ProfileColumnData
419                  For Each data In columnData
420                      columnStr.Append(", ")
421                      valueStr.Append(", ")
422                      columnStr.Append(data.ColumnName)
423                      Dim valueParam As String = "@Value" + count
424                      valueStr.Append(valueParam)
425                      cmd.Parameters.AddWithValue(valueParam, data.Value)
426  
427                      ' REVIEW: Can't update Timestamps?
428                      If data.DataType <> SqlDbType.Timestamp Then
429                          If count > 0 Then
430                              setStr.Append(",")
431                          End If
432                          setStr.Append(data.ColumnName)
433                          setStr.Append("=")
434                          setStr.Append(valueParam)
435                      End If
436                  Next data
437                  columnStr.Append(",LastUpdatedDate ")
438                  valueStr.Append(",@LastUpdatedDate")
439                  setStr.Append(",LastUpdatedDate=@LastUpdatedDate")
440                  cmd.Parameters.AddWithValue("@LastUpdatedDate", DateTime.UtcNow)
441  
442                  sqlCommand.Append("BEGIN UPDATE ").Append(_table).Append(" SET ").Append(setStr.ToString())
443                  sqlCommand.Append(" WHERE UserId = '").Append(userId).Append("'")
444  
445                  sqlCommand.Append("END ELSE BEGIN INSERT ").Append(_table).Append(" (UserId").Append(columnStr.ToString())
446                  sqlCommand.Append(") VALUES ('").Append(userId).Append("'").Append(valueStr.ToString()).Append(") END")
447  
448                  cmd.CommandText = sqlCommand.ToString()
449                  cmd.CommandType = CommandType.Text
450  
451                  cmd.ExecuteNonQuery()
452  
453                  ' Need to close reader before we try to update
454                  If Not (reader Is Nothing) Then
455                      reader.Close()
456                      reader = Nothing
457                  End If
458  
459                  UpdateLastActivityDate(conn, userId)
460              Finally
461                  If Not (reader Is Nothing) Then
462                      reader.Close()
463                  End If
464                  If Not (cmd Is Nothing) Then
465                      cmd.Dispose()
466                  End If
467                  If Not (conn Is Nothing) Then
468                      conn.Close()
469                  End If
470              End Try
471          End Sub 'SetPropertyValues
472  
473          '//////////////////////////////////////////////////////////
474          '//////////////////////////////////////////////////////////
475          '//////////////////////////////////////////////////////////
476          Private Shared Function CreateInputParam(ByVal paramName As String, ByVal dbType As SqlDbType, ByVal objValue As Object) As SqlParameter
477              Dim param As New SqlParameter(paramName, dbType)
478              If objValue Is Nothing Then
479                  objValue = [String].Empty
480              End If
481              param.Value = objValue
482              Return param
483          End Function 'CreateInputParam
484  
485  
486          Private Shared Function CreateOutputParam(ByVal paramName As String, ByVal dbType As SqlDbType, ByVal size As Integer) As SqlParameter
487              Dim param As New SqlParameter(paramName, dbType)
488              param.Direction = ParameterDirection.Output
489              param.Size = size
490              Return param
491          End Function 'CreateOutputParam
492  
493  
494          '///////////////////////////////////////////////////////////////////////////
495          '///////////////////////////////////////////////////////////////////////////
496          '///////////////////////////////////////////////////////////////////////////
497          '///////////////////////////////////////////////////////////////////////////
498          ' Mangement APIs from ProfileProvider class
499          Public Overloads Overrides Function DeleteProfiles(ByVal profiles As ProfileInfoCollection) As Integer
500              If profiles Is Nothing Then
501                  Throw New ArgumentNullException("profiles")
502              End If
503  
504              If profiles.Count < 1 Then
505                  Throw New ArgumentException("Profiles collection is empty")
506              End If
507  
508              Dim usernames(profiles.Count) As String
509  
510              Dim iter As Integer = 0
511              Dim profile As ProfileInfo
512              For Each profile In profiles
513                  usernames(iter) = profile.UserName
514                  iter += 1
515              Next profile
516  
517              Return DeleteProfiles(usernames)
518          End Function 'DeleteProfiles
519  
520          '///////////////////////////////////////////////////////////////////////////
521          '///////////////////////////////////////////////////////////////////////////
522          Public Overloads Overrides Function DeleteProfiles(ByVal usernames() As String) As Integer
523              If usernames Is Nothing Or usernames.Length < 1 Then
524                  Return 0
525              End If
526  
527              Dim numProfilesDeleted As Integer = 0
528              Dim beginTranCalled As Boolean = False
529              Try
530                  Dim conn As SqlConnection = Nothing
531                  Try
532                      conn = New SqlConnection(_sqlConnectionString)
533                      conn.Open()
534  
535                      Dim cmd As SqlCommand
536                      Dim numUsersRemaining As Integer = usernames.Length
537                      While numUsersRemaining > 0
538                          cmd = New SqlCommand([String].Empty, conn)
539                          cmd.Parameters.AddWithValue("@UserName0", usernames((usernames.Length - numUsersRemaining)))
540                          Dim allUsers As New StringBuilder("@UserName0")
541                          numUsersRemaining -= 1
542  
543                          Dim userIndex As Integer = 1
544                          Dim iter As Integer
545                          For iter = usernames.Length - numUsersRemaining To usernames.Length - 1
546                              ' REVIEW: Should we check length of command string instead of parameter lengths?
547                              If allUsers.Length + usernames(iter).Length + 3 >= 4000 Then
548                                  Exit For
549                              End If
550                              Dim userNameParam As String = "@UserName" + userIndex
551                              allUsers.Append(",")
552                              allUsers.Append(userNameParam)
553                              cmd.Parameters.AddWithValue(userNameParam, usernames(iter))
554                              numUsersRemaining -= 1
555                          Next iter
556  
557                          ' We don't need to start a transaction if we can finish this in one sql command
558                          If Not beginTranCalled And numUsersRemaining > 0 Then
559                              Dim beginCmd As New SqlCommand("BEGIN TRANSACTION", conn)
560                              beginCmd.ExecuteNonQuery()
561                              beginTranCalled = True
562                          End If
563  
564  
565                          cmd.CommandText = "DELETE FROM " + _table + " WHERE UserId IN ( SELECT u.UserId FROM vw_aspnet_Users u WHERE u.ApplicationId = '" + AppId.ToString + "' AND u.UserName IN (" + allUsers.ToString() + "))"
566                          cmd.CommandTimeout = CommandTimeout
567                          numProfilesDeleted += cmd.ExecuteNonQuery()
568                      End While
569  
570                      If beginTranCalled Then
571                          cmd = New SqlCommand("COMMIT TRANSACTION", conn)
572                          cmd.ExecuteNonQuery()
573                          beginTranCalled = False
574                      End If
575                  Catch
576                  Finally
577                      If Not (conn Is Nothing) Then
578                          conn.Close()
579                          conn = Nothing
580                      End If
581                  End Try
582              Catch
583              End Try
584              Return numProfilesDeleted
585          End Function 'DeleteProfiles
586  
587  
588          Private Function GenerateQuery(ByVal delete As Boolean, ByVal authenticationOption As ProfileAuthenticationOption) As String
589              Dim cmdStr As New StringBuilder(200)
590              If delete Then
591                  cmdStr.Append("DELETE FROM ")
592              Else
593                  cmdStr.Append("SELECT COUNT(*) FROM ")
594              End If
595              cmdStr.Append(_table)
596              cmdStr.Append(" WHERE UserId IN (SELECT u.UserId FROM vw_aspnet_Users u WHERE u.ApplicationId = '").Append(AppId)
597              cmdStr.Append("' AND (u.LastActivityDate <= @InactiveSinceDate)")
598              Select Case authenticationOption
599                  Case ProfileAuthenticationOption.Anonymous
600                      cmdStr.Append(" AND u.IsAnonymous = 1")
601                  Case ProfileAuthenticationOption.Authenticated
602                      cmdStr.Append(" AND u.IsAnonymous = 0")
603                  Case ProfileAuthenticationOption.All
604              End Select ' Want to delete all profiles here, so nothing more needed
605              cmdStr.Append(")")
606              Return cmdStr.ToString()
607          End Function 'GenerateQuery
608  
609  
610          '///////////////////////////////////////////////////////////////////////////
611          '///////////////////////////////////////////////////////////////////////////
612          Public Overrides Function DeleteInactiveProfiles(ByVal authenticationOption As ProfileAuthenticationOption, ByVal userInactiveSinceDate As DateTime) As Integer
613              Try
614                  Dim conn As SqlConnection = Nothing
615                  Dim cmd As SqlCommand = Nothing
616                  Try
617                      conn = New SqlConnection(_sqlConnectionString)
618                      conn.Open()
619  
620                      cmd = New SqlCommand(GenerateQuery(True, authenticationOption), conn)
621                      cmd.CommandTimeout = CommandTimeout
622                      cmd.Parameters.Add(CreateInputParam("@InactiveSinceDate", SqlDbType.DateTime, userInactiveSinceDate.ToUniversalTime()))
623  
624                      Return cmd.ExecuteNonQuery()
625                  Finally
626                      If Not (cmd Is Nothing) Then
627                          cmd.Dispose()
628                      End If
629                      If Not (conn Is Nothing) Then
630                          conn.Close()
631                          conn = Nothing
632                      End If
633                  End Try
634              Catch
635              End Try
636          End Function 'DeleteInactiveProfiles
637  
638  
639          '///////////////////////////////////////////////////////////////////////////
640          '///////////////////////////////////////////////////////////////////////////
641          Public Overrides Function GetNumberOfInactiveProfiles(ByVal authenticationOption As ProfileAuthenticationOption, ByVal userInactiveSinceDate As DateTime) As Integer
642              Dim conn As SqlConnection = Nothing
643              Dim cmd As SqlCommand = Nothing
644              Try
645                  conn = New SqlConnection(_sqlConnectionString)
646                  conn.Open()
647  
648                  cmd = New SqlCommand(GenerateQuery(False, authenticationOption), conn)
649                  cmd.CommandTimeout = CommandTimeout
650                  cmd.Parameters.Add(CreateInputParam("@InactiveSinceDate", SqlDbType.DateTime, userInactiveSinceDate.ToUniversalTime()))
651  
652                  Dim o As Object = cmd.ExecuteScalar()
653                  If o Is Nothing Or Not TypeOf o Is Integer Then
654                      Return 0
655                  End If
656                  Return CInt(o)
657              Finally
658                  If Not (cmd Is Nothing) Then
659                      cmd.Dispose()
660                  End If
661                  If Not (conn Is Nothing) Then
662                      conn.Close()
663                      conn = Nothing
664                  End If
665              End Try
666          End Function 'GetNumberOfInactiveProfiles
667  
668          Private Function GenerateTempInsertQueryForGetProfiles(ByVal authenticationOption As ProfileAuthenticationOption) As StringBuilder
669              Dim cmdStr As New StringBuilder(200)
670              cmdStr.Append("INSERT INTO #PageIndexForProfileUsers (UserId) ")
671              cmdStr.Append("SELECT u.UserId FROM vw_aspnet_Users u, ").Append(_table)
672              cmdStr.Append(" p WHERE ApplicationId = '").Append(AppId)
673              cmdStr.Append("' AND u.UserId = p.UserId")
674              Select Case authenticationOption
675                  Case ProfileAuthenticationOption.Anonymous
676                      cmdStr.Append(" AND u.IsAnonymous = 1")
677                  Case ProfileAuthenticationOption.Authenticated
678                      cmdStr.Append(" AND u.IsAnonymous = 0")
679                  Case ProfileAuthenticationOption.All
680              End Select ' Want to delete all profiles here, so nothing more needed
681              Return cmdStr
682          End Function 'GenerateTempInsertQueryForGetProfiles
683  
684  
685          '///////////////////////////////////////////////////////////////////////////
686          '///////////////////////////////////////////////////////////////////////////
687          Public Overrides Function GetAllProfiles(ByVal authenticationOption As ProfileAuthenticationOption, ByVal pageIndex As Integer, ByVal pageSize As Integer, ByRef totalRecords As Integer) As ProfileInfoCollection
688              Dim insertQuery As StringBuilder = GenerateTempInsertQueryForGetProfiles(authenticationOption)
689              Return GetProfilesForQuery(Nothing, pageIndex, pageSize, insertQuery, totalRecords)
690          End Function 'GetAllProfiles
691  
692  
693          '///////////////////////////////////////////////////////////////////////////
694          '///////////////////////////////////////////////////////////////////////////
695          Public Overrides Function GetAllInactiveProfiles(ByVal authenticationOption As ProfileAuthenticationOption, ByVal userInactiveSinceDate As DateTime, ByVal pageIndex As Integer, ByVal pageSize As Integer, ByRef totalRecords As Integer) As ProfileInfoCollection
696              Dim insertQuery As StringBuilder = GenerateTempInsertQueryForGetProfiles(authenticationOption)
697              insertQuery.Append(" AND u.LastActivityDate <= @InactiveSinceDate")
698              Dim args(1) As SqlParameter
699              args(0) = CreateInputParam("@InactiveSinceDate", SqlDbType.DateTime, userInactiveSinceDate.ToUniversalTime())
700              Return GetProfilesForQuery(args, pageIndex, pageSize, insertQuery, totalRecords)
701          End Function 'GetAllInactiveProfiles
702  
703  
704          '///////////////////////////////////////////////////////////////////////////
705          '///////////////////////////////////////////////////////////////////////////
706          Public Overrides Function FindProfilesByUserName(ByVal authenticationOption As ProfileAuthenticationOption, ByVal usernameToMatch As String, ByVal pageIndex As Integer, ByVal pageSize As Integer, ByRef totalRecords As Integer) As ProfileInfoCollection
707              Dim insertQuery As StringBuilder = GenerateTempInsertQueryForGetProfiles(authenticationOption)
708              insertQuery.Append(" AND u.UserName LIKE LOWER(@UserName)")
709              Dim args(1) As SqlParameter
710              args(0) = CreateInputParam("@UserName", SqlDbType.NVarChar, usernameToMatch)
711              Return GetProfilesForQuery(args, pageIndex, pageSize, insertQuery, totalRecords)
712          End Function 'FindProfilesByUserName
713  
714  
715          '///////////////////////////////////////////////////////////////////////////
716          '///////////////////////////////////////////////////////////////////////////
717          Public Overrides Function FindInactiveProfilesByUserName(ByVal authenticationOption As ProfileAuthenticationOption, ByVal usernameToMatch As String, ByVal userInactiveSinceDate As DateTime, ByVal pageIndex As Integer, ByVal pageSize As Integer, ByRef totalRecords As Integer) As ProfileInfoCollection
718              Dim insertQuery As StringBuilder = GenerateTempInsertQueryForGetProfiles(authenticationOption)
719              insertQuery.Append(" AND u.UserName LIKE LOWER(@UserName) AND u.LastActivityDate <= @InactiveSinceDate")
720              Dim args(2) As SqlParameter
721              args(0) = CreateInputParam("@InactiveSinceDate", SqlDbType.DateTime, userInactiveSinceDate.ToUniversalTime())
722              args(1) = CreateInputParam("@UserName", SqlDbType.NVarChar, usernameToMatch)
723              Return GetProfilesForQuery(args, pageIndex, pageSize, insertQuery, totalRecords)
724          End Function 'FindInactiveProfilesByUserName
725  
726  
727          '///////////////////////////////////////////////////////////////////////////
728          '///////////////////////////////////////////////////////////////////////////
729          ' Private methods
730          '///////////////////////////////////////////////////////////////////////////
731          '///////////////////////////////////////////////////////////////////////////
732          Private Function GetProfilesForQuery(ByVal insertArgs() As SqlParameter, ByVal pageIndex As Integer, ByVal pageSize As Integer, ByVal insertQuery As StringBuilder, ByRef totalRecords As Integer) As ProfileInfoCollection
733              If pageIndex < 0 Then
734                  Throw New ArgumentException("pageIndex")
735              End If
736              If pageSize < 1 Then
737                  Throw New ArgumentException("pageSize")
738              End If
739              Dim lowerBound As Long = CLng(pageIndex) * pageSize
740              Dim upperBound As Long = lowerBound + pageSize - 1
741              If upperBound > Int32.MaxValue Then
742                  Throw New ArgumentException("pageIndex and pageSize")
743              End If
744  
745              Dim conn As SqlConnection = Nothing
746              Dim reader As SqlDataReader = Nothing
747              Dim cmd As SqlCommand = Nothing
748              Try
749                  conn = New SqlConnection(_sqlConnectionString)
750                  conn.Open()
751  
752                  Dim cmdStr As New StringBuilder(200)
753                  ' Create a temp table TO store the select results
754                  cmd = New SqlCommand("CREATE TABLE #PageIndexForProfileUsers(IndexId int IDENTITY (0, 1) NOT NULL, UserId uniqueidentifier)", conn)
755                  cmd.CommandTimeout = CommandTimeout
756                  cmd.ExecuteNonQuery()
757                  cmd.Dispose()
758  
759                  insertQuery.Append(" ORDER BY UserName")
760                  cmd = New SqlCommand(insertQuery.ToString(), conn)
761                  cmd.CommandTimeout = CommandTimeout
762                  If Not (insertArgs Is Nothing) Then
763                      Dim arg As SqlParameter
764                      For Each arg In insertArgs
765                          cmd.Parameters.Add(arg)
766                      Next arg
767                  End If
768                  cmd.ExecuteNonQuery()
769                  cmd.Dispose()
770  
771                  cmdStr = New StringBuilder(200)
772                  cmdStr.Append("SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate FROM vw_aspnet_Users u, ").Append(_table)
773                  cmdStr.Append(" p, #PageIndexForProfileUsers i WHERE u.UserId = p.UserId AND p.UserId = i.UserId AND i.IndexId >= ")
774                  cmdStr.Append(lowerBound).Append(" AND i.IndexId <= ").Append(upperBound)
775                  cmd = New SqlCommand(cmdStr.ToString(), conn)
776                  cmd.CommandTimeout = CommandTimeout
777  
778                  reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
779                  Dim profiles As New ProfileInfoCollection()
780                  While reader.Read()
781                      Dim username As String
782                      Dim dtLastUpdated As DateTime = DateTime.UtcNow
783                      Dim dtLastActivity As DateTime
784                      Dim isAnon As Boolean
785  
786                      username = reader.GetString(0)
787                      isAnon = reader.GetBoolean(1)
788                      dtLastActivity = DateTime.SpecifyKind(reader.GetDateTime(2), DateTimeKind.Utc)
789                      dtLastUpdated = DateTime.SpecifyKind(reader.GetDateTime(3), DateTimeKind.Utc)
790                      profiles.Add(New ProfileInfo(username, isAnon, dtLastActivity, dtLastUpdated, 0))
791                  End While
792                  totalRecords = profiles.Count
793  
794                  If Not (reader Is Nothing) Then
795                      reader.Close()
796                      reader = Nothing
797                  End If
798  
799                  cmd.Dispose()
800  
801                  ' Cleanup, REVIEW: should move to finally?
802                  cmd = New SqlCommand("DROP TABLE #PageIndexForProfileUsers", conn)
803                  cmd.ExecuteNonQuery()
804  
805                  Return profiles
806              Finally
807                  If Not (reader Is Nothing) Then
808                      reader.Close()
809                  End If
810                  If Not (cmd Is Nothing) Then
811                      cmd.Dispose()
812                  End If
813                  If Not (conn Is Nothing) Then
814                      conn.Close()
815                      conn = Nothing
816                  End If
817              End Try
818          End Function 'GetProfilesForQuery
819      End Class 'SqlTableProfileProvider
820  End Namespace 'Microsoft.Samples

I think I've got it figured out and working. :)