Profile Provider Implementation Example
The code example in this topic illustrates an ASP.NET profile provider implementation. For information about how to compile this code and use the provider, see How to: Build and Run the Profile Provider Example.
Example
Code
Imports System.Web.Profile
Imports System.Configuration.Provider
Imports System.Collections.Specialized
Imports System
Imports System.Data
Imports System.Data.Odbc
Imports System.Configuration
Imports System.Diagnostics
Imports System.Web
Imports System.Collections
Imports Microsoft.VisualBasic
''' This provider works with the following schema for the table of user data.' ' CREATE TABLE Profiles' (' UniqueID AutoIncrement NOT NULL PRIMARY KEY,' Username Text (255) NOT NULL,' ApplicationName Text (255) NOT NULL,' IsAnonymous YesNo, ' LastActivityDate DateTime,' LastUpdatedDate DateTime,' CONSTRAINT PKProfiles UNIQUE (Username, ApplicationName)' )' ' CREATE TABLE StockSymbols' (' UniqueID Integer,' StockSymbol Text (10),' CONSTRAINT FKProfiles1 FOREIGN KEY (UniqueID)' REFERENCES Profiles' )' ' CREATE TABLE ProfileData' (' UniqueID Integer,' ZipCode Text (10),' CONSTRAINT FKProfiles2 FOREIGN KEY (UniqueID)' REFERENCES Profiles' )' ' Namespace Samples.AspNet.Profile
PublicNotInheritableClass OdbcProfileProvider
Inherits ProfileProvider
' ' Global connection string, generic exception message, event log info. 'Private eventSource AsString = "OdbcProfileProvider"Private eventLog AsString = "Application"Private exceptionMessage AsString = "An exception occurred. Please check the event log."Private connectionString AsString
' ' If false, exceptions are thrown to the caller. If true, ' exceptions are written to the event log. 'Private pWriteExceptionsToEventLog AsBooleanPublicProperty WriteExceptionsToEventLog AsBooleanGetReturn pWriteExceptionsToEventLog
EndGetSet
pWriteExceptionsToEventLog = value
EndSetEndProperty
' ' System.Configuration.Provider.ProviderBase.Initialize Method 'PublicOverridesSub Initialize(name AsString, config As NameValueCollection)
' ' Initialize values from web.config. 'If config IsNothingThen _
ThrowNew ArgumentNullException("config")
If name IsNothingOrElse name.Length = 0 Then _
name = "OdbcProfileProvider"IfString.IsNullOrEmpty(config("description")) Then
config.Remove("description")
config.Add("description", "Sample ODBC Profile provider")
EndIf
' Initialize the abstract base class.MyBase.Initialize(name, config)
If config("applicationName") IsNothingOrElse config("applicationName").Trim() = ""Then
pApplicationName = System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath
Else
pApplicationName = config("applicationName")
EndIf
' ' Initialize connection string. 'Dim pConnectionStringSettings As ConnectionStringSettings = _
ConfigurationManager.ConnectionStrings(config("connectionStringName"))
If pConnectionStringSettings IsNothingOrElse _
pConnectionStringSettings.ConnectionString.Trim() = "" _
ThenThrowNew ProviderException("Connection String cannot be blank.")
EndIf
connectionString = pConnectionStringSettings.ConnectionString
EndSub
' ' System.Configuration.SettingsProvider.ApplicationName 'Private pApplicationName AsStringPublicOverridesProperty ApplicationName AsStringGetReturn pApplicationName
EndGetSet
pApplicationName = value
EndSetEndProperty
' ' System.Configuration.SettingsProvider methods. '
' ' SettingsProvider.GetPropertyValues 'PublicOverridesFunction GetPropertyValues(context As SettingsContext, _
ppc As SettingsPropertyCollection) _
As SettingsPropertyValueCollection
' The serializeAs attribute is ignored in this provider implementation.Dim username AsString = CType(context("UserName"), String)
Dim isAuthenticated AsBoolean = CType(context("IsAuthenticated"), Boolean)
Dim svc As SettingsPropertyValueCollection = New SettingsPropertyValueCollection()
ForEach prop As SettingsProperty In ppc
Dim pv As SettingsPropertyValue = New SettingsPropertyValue(prop)
SelectCase prop.Name
Case"StockSymbols"
pv.PropertyValue = GetStockSymbols(username, isAuthenticated)
Case"ZipCode"
pv.PropertyValue = GetZipCode(username, isAuthenticated)
CaseElseThrowNew ProviderException("Unsupported property.")
EndSelect
svc.Add(pv)
Next
UpdateActivityDates(username, isAuthenticated, True)
Return svc
EndFunction
' ' SettingsProvider.SetPropertyValues 'PublicOverridesSub SetPropertyValues(context As SettingsContext, _
ppvc As SettingsPropertyValueCollection)
' The serializeAs attribute is ignored in this provider implementation.Dim username AsString = CType(context("UserName"), String)
Dim isAuthenticated AsBoolean = CType(context("IsAuthenticated"), Boolean)
Dim uniqueID AsInteger = GetUniqueID(username, isAuthenticated, False)
If uniqueID = 0 Then uniqueID = CreateProfileForUser(username, isAuthenticated)
ForEach pv As SettingsPropertyValue In ppvc
SelectCase pv.Property.Name
Case"StockSymbols"
SetStockSymbols(uniqueID, CType(pv.PropertyValue, ArrayList))
Case"ZipCode"
SetZipCode(uniqueID, CType(pv.PropertyValue, String))
CaseElseThrowNew ProviderException("Unsupported property.")
EndSelectNext
UpdateActivityDates(username, isAuthenticated, False)
EndSub
' ' UpdateActivityDates ' Updates LastActivityDate and LastUpdatedDate when profile properties are accessed ' by GetPropertyValues and SetPropertyValues. Specifying activityOnly as true will update ' only the LastActivityDate. 'PrivateSub UpdateActivityDates(username AsString, isAuthenticated AsBoolean, activityOnly AsBoolean)
Dim activityDate As DateTime = DateTime.Now
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand()
cmd.Connection = conn
If activityOnly Then
cmd.CommandText = "UPDATE Profiles Set LastActivityDate = ? " & _
"WHERE Username = ? AND ApplicationName = ? AND IsAnonymous = ?"
cmd.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = activityDate
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName
cmd.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = Not isAuthenticated
Else
cmd.CommandText = "UPDATE Profiles Set LastActivityDate = ?, LastUpdatedDate = ? " & _
"WHERE Username = ? AND ApplicationName = ? AND IsAnonymous = ?"
cmd.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = activityDate
cmd.Parameters.Add("@LastUpdatedDate", OdbcType.DateTime).Value = activityDate
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName
cmd.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = Not isAuthenticated
EndIfTry
conn.Open()
cmd.ExecuteNonQuery()
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "UpdateActivityDates")
ThrowNew ProviderException(exceptionMessage)
ElseThrow e
EndIfFinally
conn.Close()
EndTryEndSub
' ' GetStockSymbols ' Retrieves stock symbols from the database during the ' call to GetPropertyValues. 'PrivateFunction GetStockSymbols(username AsString, isAuthenticated AsBoolean) As ArrayList
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT StockSymbol FROM Profiles " & _
"INNER JOIN StockSymbols ON Profiles.UniqueID = StockSymbols.UniqueID " & _
"WHERE Username = ? AND ApplicationName = ? And IsAnonymous = ?", conn)
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName
cmd.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = Not isAuthenticated
Dim outList As ArrayList = New ArrayList()
Dim reader As OdbcDataReader = NothingTry
conn.Open()
reader = cmd.ExecuteReader()
DoWhile reader.Read()
outList.Add(reader.GetString(0))
LoopCatch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "GetStockSymbols")
ThrowNew ProviderException(exceptionMessage)
ElseThrow e
EndIfFinallyIfNot reader IsNothingThen reader.Close()
conn.Close()
EndTryReturn outList
EndFunction
' ' SetStockSymbols ' Inserts stock symbol values into the database during ' the call to SetPropertyValues. 'PrivateSub SetStockSymbols(uniqueID AsInteger, stocks As ArrayList)
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("DELETE FROM StockSymbols WHERE UniqueID = ?", conn)
cmd.Parameters.Add("@UniqueID", OdbcType.Int).Value = uniqueID
Dim cmd2 As OdbcCommand = New OdbcCommand("INSERT INTO StockSymbols (UniqueID, StockSymbol) " & _
"Values(?, ?)", conn)
cmd2.Parameters.Add("@UniqueID", OdbcType.Int).Value = uniqueID
cmd2.Parameters.Add("@StockSymbol", OdbcType.VarChar, 10)
Dim tran As OdbcTransaction = NothingTry
conn.Open()
tran = conn.BeginTransaction()
cmd.Transaction = tran
cmd2.Transaction = tran
' Delete any existing values.
cmd.ExecuteNonQuery()
ForEach o AsObjectIn Stocks
cmd2.Parameters("@StockSymbol").Value = o.ToString()
cmd2.ExecuteNonQuery()
Next
tran.Commit()
Catch e As OdbcException
Try
tran.Rollback()
CatchEndTryIf WriteExceptionsToEventLog Then
WriteToEventLog(e, "SetStockSymbols")
ThrowNew ProviderException(exceptionMessage)
ElseThrow e
EndIfFinally
conn.Close()
EndTryEndSub
' ' GetZipCode ' Retrieves ZipCode value from the database during ' the call to GetPropertyValues. 'PrivateFunction GetZipCode(username AsString, isAuthenticated AsBoolean) AsStringDim zipCode AsString = ""Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT ZipCode FROM Profiles " & _
"INNER JOIN ProfileData ON Profiles.UniqueID = ProfileData.UniqueID " & _
"WHERE Username = ? AND ApplicationName = ? And IsAnonymous = ?", conn)
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName
cmd.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = Not isAuthenticated
Try
conn.Open()
zipCode = CType(cmd.ExecuteScalar(), String)
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "GetZipCode")
ThrowNew ProviderException(exceptionMessage)
ElseThrow e
EndIfFinally
conn.Close()
EndTryReturn zipCode
EndFunction
' ' SetZipCode ' Inserts the zip code value into the database during ' the call to SetPropertyValues. 'PrivateSub SetZipCode(uniqueID AsInteger, zipCode AsString)
If zipCode IsNothingThen zipCode = String.Empty
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("DELETE FROM ProfileData WHERE UniqueID = ?", conn)
cmd.Parameters.Add("@UniqueID", OdbcType.Int).Value = uniqueID
Dim cmd2 As OdbcCommand= New OdbcCommand("INSERT INTO ProfileData (UniqueID, ZipCode) " & _
"Values(?, ?)", conn)
cmd2.Parameters.Add("@UniqueID", OdbcType.Int).Value = uniqueID
cmd2.Parameters.Add("@ZipCode", OdbcType.VarChar, 10).Value = zipCode
Dim tran As OdbcTransaction = NothingTry
conn.Open()
tran = conn.BeginTransaction()
cmd.Transaction = tran
cmd2.Transaction = tran
' Delete any existing values.
cmd.ExecuteNonQuery()
cmd2.ExecuteNonQuery()
tran.Commit()
Catch e As OdbcException
Try
tran.Rollback()
CatchEndTryIf WriteExceptionsToEventLog Then
WriteToEventLog(e, "SetZipCode")
ThrowNew ProviderException(exceptionMessage)
ElseThrow e
EndIfFinally
conn.Close()
EndTryEndSub
' ' GetUniqueID ' Retrieves the uniqueID from the database for ' the current user and application. 'PrivateFunction GetUniqueID(username AsString, isAuthenticated AsBoolean, _
ignoreAuthenticationType AsBoolean) AsIntegerDim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT UniqueID FROM Profiles " & _
"WHERE Username = ? AND ApplicationName = ?", conn)
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName
IfNot ignoreAuthenticationType Then
cmd.CommandText &= " AND IsAnonymous = ?"
cmd.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = Not isAuthenticated
EndIfDim uniqueID AsInteger = 0
Dim reader As OdbcDataReader = NothingTry
conn.Open()
reader = cmd.ExecuteReader(CommandBehavior.SingleRow)
If reader.HasRows Then _
uniqueID = reader.GetInt32(0)
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "GetUniqueID")
ThrowNew ProviderException(exceptionMessage)
ElseThrow e
EndIfFinallyIf reader IsNotNothingThen reader.Close()
conn.Close()
EndTryReturn uniqueID
EndFunction
' ' CreateProfileForUser ' If no user currently exists in the database, ' a user record is created during the call to the ' GetUniqueID Private method. 'PrivateFunction CreateProfileForUser(username AsString, isAuthenticated AsBoolean) AsInteger ' Check for valid user name.If username IsNothingThen _
ThrowNew ArgumentNullException("username")
If username.Length > 255 Then _
ThrowNew ArgumentException("User name exceeds 255 characters.")
If username.Contains(",") Then _
ThrowNew ArgumentException("User name cannot contain a comma (,).")
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("INSERT INTO Profiles (Username, " & _
"ApplicationName, LastActivityDate, LastUpdatedDate, " & _
"IsAnonymous) Values(?, ?, ?, ?, ?)", conn)
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName
cmd.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = DateTime.Now
cmd.Parameters.Add("@LastUpdatedDate", OdbcType.VarChar).Value = DateTime.Now
cmd.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = Not isAuthenticated
Dim cmd2 As OdbcCommand = New OdbcCommand("SELECT @@IDENTITY", conn)
Dim uniqueID AsInteger = 0
Try
conn.Open()
cmd.ExecuteNonQuery()
uniqueID = CType(cmd2.ExecuteScalar(), Integer)
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "CreateProfileForUser")
ThrowNew HttpException(exceptionMessage)
ElseThrow e
EndIfFinally
conn.Close()
EndTryReturn uniqueID
EndFunction
' ' ProfileProvider.DeleteProfiles(ProfileInfoCollection) 'PublicOverridesFunction DeleteProfiles(profiles As ProfileInfoCollection) AsIntegerDim deleteCount AsInteger = 0
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim tran As OdbcTransaction = NothingTry
conn.Open()
tran = conn.BeginTransaction()
ForEach p As ProfileInfo In profiles
If DeleteProfile(p.UserName, conn, tran) Then deleteCount += 1
Next
tran.Commit()
Catch e As Exception
Try
tran.Rollback()
CatchEndTryIf WriteExceptionsToEventLog Then
WriteToEventLog(e, "DeleteProfiles(String())")
ThrowNew ProviderException(exceptionMessage)
ElseThrow e
EndIfFinally
conn.Close()
EndTryReturn deleteCount
EndFunction
' ' ProfileProvider.DeleteProfiles(string()) 'PublicOverridesFunction DeleteProfiles(usernames AsString()) AsIntegerDim deleteCount AsInteger = 0
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim tran As OdbcTransaction = NothingTry
conn.Open()
tran = conn.BeginTransaction()
ForEach user AsStringIn usernames
If (DeleteProfile(user, conn, tran)) Then deleteCount += 1
Next
tran.Commit()
Catch e As Exception
Try
tran.Rollback()
CatchEndTryIf WriteExceptionsToEventLog Then
WriteToEventLog(e, "DeleteProfiles(String())")
ThrowNew ProviderException(exceptionMessage)
ElseThrow e
EndIfFinally
conn.Close()
EndTryReturn deleteCount
EndFunction
' ' ProfileProvider.DeleteInactiveProfiles 'PublicOverridesFunction DeleteInactiveProfiles( _
authenticationOption As ProfileAuthenticationOption, _
userInactiveSinceDate As DateTime) AsIntegerDim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT Username FROM Profiles " & _
"WHERE ApplicationName = ? AND " & _
" LastActivityDate <= ?", conn)
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName
cmd.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = userInactiveSinceDate
SelectCase authenticationOption
Case ProfileAuthenticationOption.Anonymous
cmd.CommandText &= " AND IsAnonymous = ?"
cmd.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = TrueCase ProfileAuthenticationOption.Authenticated
cmd.CommandText &= " AND IsAnonymous = ?"
cmd.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = FalseEndSelectDim reader As OdbcDataReader = NothingDim usernames AsString = ""Try
conn.Open()
reader = cmd.ExecuteReader()
DoWhile reader.Read()
usernames &= reader.GetString(0) + ","LoopCatch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "DeleteInactiveProfiles")
ThrowNew ProviderException(exceptionMessage)
ElseThrow e
EndIfFinallyIfNot reader IsNothingThen reader.Close()
conn.Close()
EndTryIf usernames.Length > 0 Then ' Remove trailing comma.
usernames = usernames.Substring(0, usernames.Length - 1)
EndIf
' Delete profiles.Return DeleteProfiles(usernames.Split(CChar(",")))
EndFunction
' ' ' DeleteProfile ' Deletes profile data from the database for the specified user name. Expects an OdbcConnection and ' an OdbcTransaction as it supports deleting multiple profiles in a transaction. 'PrivateFunction DeleteProfile(username AsString, conn As OdbcConnection, tran As OdbcTransaction) AsBoolean ' Check for valid user name.If username IsNothingThen _
ThrowNew ArgumentNullException("username")
If username.Length > 255 Then _
ThrowNew ArgumentException("User name exceeds 255 characters.")
If username.Contains(",") Then _
ThrowNew ArgumentException("User name cannot contain a comma (,).")
Dim uniqueID AsInteger = GetUniqueID(username, False, True)
Dim cmd1 As OdbcCommand = New OdbcCommand("DELETE * FROM ProfileData WHERE UniqueID = ?", conn)
cmd1.Parameters.Add("@UniqueID", OdbcType.Int).Value = uniqueID
Dim cmd2 As OdbcCommand = New OdbcCommand("DELETE * FROM StockSymbols WHERE UniqueID = ?", conn)
cmd2.Parameters.Add("@UniqueID", OdbcType.Int).Value = uniqueID
Dim cmd3 As OdbcCommand = New OdbcCommand("DELETE * FROM Profiles WHERE UniqueID = ?", conn)
cmd3.Parameters.Add("@UniqueID", OdbcType.Int).Value = uniqueID
cmd1.Transaction = tran
cmd2.Transaction = tran
cmd3.Transaction = tran
Dim numDeleted AsInteger = 0
' Exceptions will be caught by the calling method.
numDeleted += cmd1.ExecuteNonQuery()
numDeleted += cmd2.ExecuteNonQuery()
numDeleted += cmd3.ExecuteNonQuery()
If numDeleted = 0 ThenReturnFalseElseReturnTrueEndIfEndFunction
' ' ProfileProvider.FindProfilesByUserName 'PublicOverridesFunction FindProfilesByUserName( _
authenticationOption As ProfileAuthenticationOption, _
usernameToMatch AsString, _
pageIndex AsInteger, _
pageSize AsInteger, _
ByRef totalRecords AsInteger) As ProfileInfoCollection
CheckParameters(pageIndex, pageSize)
Return GetProfileInfo(authenticationOption, usernameToMatch, Nothing, _
pageIndex, pageSize, totalRecords)
EndFunction
' ' ProfileProvider.FindInactiveProfilesByUserName 'PublicOverridesFunction FindInactiveProfilesByUserName( _
authenticationOption As ProfileAuthenticationOption, _
usernameToMatch AsString, _
userInactiveSinceDate As DateTime, _
pageIndex AsInteger, _
pageSize AsInteger, _
ByRef totalRecords AsInteger) As ProfileInfoCollection
CheckParameters(pageIndex, pageSize)
Return GetProfileInfo(authenticationOption, usernameToMatch, userInactiveSinceDate, _
pageIndex, pageSize, totalRecords)
EndFunction
' ' ProfileProvider.GetAllProfiles 'PublicOverridesFunction GetAllProfiles( _
authenticationOption As ProfileAuthenticationOption, _
pageIndex AsInteger, _
pageSize AsInteger, _
ByRef totalRecords AsInteger) As ProfileInfoCollection
CheckParameters(pageIndex, pageSize)
Return GetProfileInfo(authenticationOption, Nothing, Nothing, _
pageIndex, pageSize, totalRecords)
EndFunction
' ' ProfileProvider.GetAllInactiveProfiles 'PublicOverridesFunction GetAllInactiveProfiles( _
authenticationOption As ProfileAuthenticationOption, _
userInactiveSinceDate As DateTime, _
pageIndex AsInteger, _
pageSize AsInteger, _
ByRef totalRecords AsInteger) As ProfileInfoCollection
CheckParameters(pageIndex, pageSize)
Return GetProfileInfo(authenticationOption, Nothing, userInactiveSinceDate, _
pageIndex, pageSize, totalRecords)
EndFunction
' ' ProfileProvider.GetNumberOfInactiveProfiles 'PublicOverridesFunction GetNumberOfInactiveProfiles( _
authenticationOption As ProfileAuthenticationOption, _
userInactiveSinceDate As DateTime) AsIntegerDim inactiveProfiles AsInteger = 0
Dim profiles As ProfileInfoCollection = _
GetProfileInfo(authenticationOption, Nothing, userInactiveSinceDate, _
0, 0, inactiveProfiles)
Return inactiveProfiles
EndFunction
' ' CheckParameters ' Verifies input parameters for page size and page index. ' Called by GetAllProfiles, GetAllInactiveProfiles, ' FindProfilesByUserName, and FindInactiveProfilesByUserName. 'PrivateSub CheckParameters(pageIndex AsInteger, pageSize AsInteger)
If pageIndex < 0 Then _
ThrowNew ArgumentException("Page index must 0 or greater.")
If pageSize < 1 Then _
ThrowNew ArgumentException("Page size must be greater than 0.")
EndSub
' ' GetProfileInfo ' Retrieves a count of profiles and creates a ' ProfileInfoCollection from the profile data in the database. ' Called by GetAllProfiles, GetAllInactiveProfiles, ' FindProfilesByUserName, FindInactiveProfilesByUserName, ' and GetNumberOfInactiveProfiles. ' Specifying a pageIndex of 0 retrieves a count of the results only. 'PrivateFunction GetProfileInfo( _
authenticationOption As ProfileAuthenticationOption, _
usernameToMatch AsString, _
userInactiveSinceDate AsObject, _
pageIndex AsInteger, _
pageSize AsInteger, _
ByRef totalRecords AsInteger) As ProfileInfoCollection
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
' Command to retrieve the total count.Dim cmd As OdbcCommand = New OdbcCommand("SELECT COUNT(*) FROM Profiles WHERE ApplicationName = ? ", conn)
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName
' Command to retrieve the profile data.Dim cmd2 As OdbcCommand = New OdbcCommand("SELECT Username, LastActivityDate, LastUpdatedDate, " & _
"IsAnonymous FROM Profiles WHERE ApplicationName = ? ", conn)
cmd2.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName
' If searching for a user name to match, ' add the command text and parameters.IfNot usernameToMatch IsNothingThen
cmd.CommandText &= " AND Username LIKE ? "
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = usernameToMatch
cmd2.CommandText &= " AND Username LIKE ? "
cmd2.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = usernameToMatch
EndIf
' If searching for inactive profiles, ' add the command text and parameters.IfNot userInactiveSinceDate IsNothingThen
cmd.CommandText &= " AND LastActivityDate <= ? "
cmd.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = _
CType(userInactiveSinceDate, DateTime)
cmd2.CommandText &= " AND LastActivityDate <= ? "
cmd2.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = _
CType(userInactiveSinceDate, DateTime)
EndIf
' If searching for a anonymous or authenticated profiles, add the command text ' and parameters.SelectCase authenticationOption
Case ProfileAuthenticationOption.Anonymous
cmd.CommandText &= " AND IsAnonymous = ?"
cmd.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = True
cmd2.CommandText &= " AND IsAnonymous = ?"
cmd2.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = TrueCase ProfileAuthenticationOption.Authenticated
cmd.CommandText &= " AND IsAnonymous = ?"
cmd.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = False
cmd2.CommandText &= " AND IsAnonymous = ?"
cmd2.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = FalseEndSelect
' Get the data.Dim reader As OdbcDataReader = NothingDim profiles As ProfileInfoCollection = New ProfileInfoCollection()
Try
conn.Open()
' Get the profile count.
totalRecords = CType(cmd.ExecuteScalar(), Integer)
' No profiles found.If totalRecords <= 0 ThenReturn profiles
' Count profiles only.If pageSize = 0 ThenReturn profiles
reader = cmd2.ExecuteReader()
Dim counter AsInteger = 0
Dim startIndex AsInteger = pageSize * (pageIndex - 1)
Dim endIndex AsInteger = startIndex + pageSize - 1
DoWhile reader.Read()
If counter >= startIndex ThenDim p As ProfileInfo = GetProfileInfoFromReader(reader)
profiles.Add(p)
EndIfIf counter >= endIndex Then cmd.Cancel()
counter += 1
LoopCatch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "GetProfileInfo")
ThrowNew ProviderException(exceptionMessage)
ElseThrow e
EndIfFinallyIfNot reader IsNothingThen reader.Close()
conn.Close()
EndTryReturn profiles
EndFunction
' ' GetProfileInfoFromReader ' Takes the current row from the OdbcDataReader ' and populates a ProfileInfo object from the values. 'PrivateFunction GetProfileInfoFromReader(reader As OdbcDataReader) As ProfileInfo
Dim username AsString = reader.GetString(0)
Dim lastActivityDate As DateTime = New DateTime()
IfNot reader.GetValue(1) Is DBNull.Value Then _
lastActivityDate = reader.GetDateTime(1)
Dim lastUpdatedDate As DateTime = New DateTime()
IfNot reader.GetValue(2) Is DBNull.Value Then _
lastUpdatedDate = reader.GetDateTime(2)
Dim isAnonymous AsBoolean = reader.GetBoolean(3)
' ProfileInfo.Size not currently implemented.Dim p As ProfileInfo = New ProfileInfo(username, _
isAnonymous,lastActivityDate,lastUpdatedDate, 0)
Return p
EndFunction
' ' WriteToEventLog ' A helper function that writes exception detail to the ' event log. Exceptions are written to the event log as ' a security measure to prevent Private database details ' from being returned to the browser. If a method does not ' return a status or Boolean value indicating whether the action ' succeeded or failed, the caller also throws a generic exception. 'PrivateSub WriteToEventLog(e As Exception, action AsString)
Dim log As EventLog = New EventLog()
log.Source = eventSource
log.Log = eventLog
Dim message AsString = "An exception occurred communicating with the data source." & vbCrLf & vbCrLf
message &= "Action: " & action & vbCrLf & vbCrLf
message &= "Exception: " & e.ToString()
log.WriteEnTry(message)
EndSubEndClassEndNamespace
using System.Web.Profile;
using System.Configuration.Provider;
using System.Collections.Specialized;
using System;
using System.Data;
using System.Data.Odbc;
using System.Configuration;
using System.Diagnostics;
using System.Web;
using System.Collections;
/*
This provider works with the following schema for the table of user data.
CREATE TABLE Profiles
(
UniqueID AutoIncrement NOT NULL PRIMARY KEY,
Username Text (255) NOT NULL,
ApplicationName Text (255) NOT NULL,
IsAnonymous YesNo,
LastActivityDate DateTime,
LastUpdatedDate DateTime,
CONSTRAINT PKProfiles UNIQUE (Username, ApplicationName)
)
CREATE TABLE StockSymbols
(
UniqueID Integer,
StockSymbol Text (10),
CONSTRAINT FKProfiles1 FOREIGN KEY (UniqueID)
REFERENCES Profiles
)
CREATE TABLE ProfileData
(
UniqueID Integer,
ZipCode Text (10),
CONSTRAINT FKProfiles2 FOREIGN KEY (UniqueID)
REFERENCES Profiles
)
*/namespace Samples.AspNet.Profile
{
publicsealedclass OdbcProfileProvider: ProfileProvider
{
//// Global connection string, generic exception message, event log info.//privatestring eventSource = "OdbcProfileProvider";
privatestring eventLog = "Application";
privatestring exceptionMessage = "An exception occurred. Please check the event log.";
privatestring connectionString;
//// If false, exceptions are thrown to the caller. If true,// exceptions are written to the event log.//privatebool pWriteExceptionsToEventLog;
publicbool WriteExceptionsToEventLog
{
get { return pWriteExceptionsToEventLog; }
set { pWriteExceptionsToEventLog = value; }
}
//// System.Configuration.Provider.ProviderBase.Initialize Method//publicoverridevoid Initialize(string name, NameValueCollection config)
{
//// Initialize values from web.config.//if (config == null)
thrownew ArgumentNullException("config");
if (name == null || name.Length == 0)
name = "OdbcProfileProvider";
if (String.IsNullOrEmpty(config["description"]))
{
config.Remove("description");
config.Add("description", "Sample ODBC Profile provider");
}
// Initialize the abstract base class.base.Initialize(name, config);
if (config["applicationName"] == null || config["applicationName"].Trim() == "")
{
pApplicationName = System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath;
}
else
{
pApplicationName = config["applicationName"];
}
//// Initialize connection string.//
ConnectionStringSettings pConnectionStringSettings = ConfigurationManager.
ConnectionStrings[config["connectionStringName"]];
if (pConnectionStringSettings == null ||
pConnectionStringSettings.ConnectionString.Trim() == "")
{
thrownew ProviderException("Connection string cannot be blank.");
}
connectionString = pConnectionStringSettings.ConnectionString;
}
//// System.Configuration.SettingsProvider.ApplicationName//privatestring pApplicationName;
publicoverridestring ApplicationName
{
get { return pApplicationName; }
set { pApplicationName = value; }
}
//// System.Configuration.SettingsProvider methods.////// SettingsProvider.GetPropertyValues//publicoverride SettingsPropertyValueCollection
GetPropertyValues(SettingsContext context,
SettingsPropertyCollection ppc)
{
string username = (string)context["UserName"];
bool isAuthenticated = (bool)context["IsAuthenticated"];
// The serializeAs attribute is ignored in this provider implementation.
SettingsPropertyValueCollection svc =
new SettingsPropertyValueCollection();
foreach (SettingsProperty prop in ppc)
{
SettingsPropertyValue pv = new SettingsPropertyValue(prop);
switch (prop.Name)
{
case"StockSymbols":
pv.PropertyValue = GetStockSymbols(username, isAuthenticated);
break;
case"ZipCode":
pv.PropertyValue = GetZipCode(username, isAuthenticated);
break;
default:
thrownew ProviderException("Unsupported property.");
}
svc.Add(pv);
}
UpdateActivityDates(username, isAuthenticated, true);
return svc;
}
//// SettingsProvider.SetPropertyValues//publicoverridevoid SetPropertyValues(SettingsContext context,
SettingsPropertyValueCollection ppvc)
{
// The serializeAs attribute is ignored in this provider implementation.string username = (string)context["UserName"];
bool isAuthenticated = (bool)context["IsAuthenticated"];
int uniqueID = GetUniqueID(username, isAuthenticated, false);
if (uniqueID == 0)
uniqueID = CreateProfileForUser(username, isAuthenticated);
foreach (SettingsPropertyValue pv in ppvc)
{
switch (pv.Property.Name)
{
case"StockSymbols":
SetStockSymbols(uniqueID, (ArrayList)pv.PropertyValue);
break;
case"ZipCode":
SetZipCode(uniqueID, (string)pv.PropertyValue);
break;
default:
thrownew ProviderException("Unsupported property.");
}
}
UpdateActivityDates(username, isAuthenticated, false);
}
//// UpdateActivityDates// Updates the LastActivityDate and LastUpdatedDate values // when profile properties are accessed by the// GetPropertyValues and SetPropertyValues methods. // Passing true as the activityOnly parameter will update// only the LastActivityDate.//privatevoid UpdateActivityDates(string username, bool isAuthenticated, bool activityOnly)
{
DateTime activityDate = DateTime.Now;
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand();
cmd.Connection = conn;
if (activityOnly)
{
cmd.CommandText = "UPDATE Profiles Set LastActivityDate = ? " +
"WHERE Username = ? AND ApplicationName = ? AND IsAnonymous = ?";
cmd.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = activityDate;
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
cmd.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = !isAuthenticated;
}
else
{
cmd.CommandText = "UPDATE Profiles Set LastActivityDate = ?, LastUpdatedDate = ? " +
"WHERE Username = ? AND ApplicationName = ? AND IsAnonymous = ?";
cmd.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = activityDate;
cmd.Parameters.Add("@LastUpdatedDate", OdbcType.DateTime).Value = activityDate;
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
cmd.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = !isAuthenticated;
}
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "UpdateActivityDates");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
}
//// GetStockSymbols// Retrieves stock symbols from the database during the call to GetPropertyValues.//private ArrayList GetStockSymbols(string username, bool isAuthenticated)
{
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new
OdbcCommand("SELECT StockSymbol FROM Profiles " +
"INNER JOIN StockSymbols ON Profiles.UniqueID = StockSymbols.UniqueID " +
"WHERE Username = ? AND ApplicationName = ? And IsAnonymous = ?", conn);
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
cmd.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = !isAuthenticated;
ArrayList outList = new ArrayList();
OdbcDataReader reader = null;
try
{
conn.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
outList.Add(reader.GetString(0));
}
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetStockSymbols");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
return outList;
}
//// SetStockSymbols// Inserts stock symbol values into the database during // the call to SetPropertyValues.//privatevoid SetStockSymbols(int uniqueID, ArrayList stocks)
{
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("DELETE FROM StockSymbols WHERE UniqueID = ?", conn);
cmd.Parameters.Add("@UniqueID", OdbcType.Int).Value = uniqueID;
OdbcCommand cmd2 = new OdbcCommand("INSERT INTO StockSymbols (UniqueID, StockSymbol) " +
"Values(?, ?)", conn);
cmd2.Parameters.Add("@UniqueID", OdbcType.Int).Value = uniqueID;
cmd2.Parameters.Add("@StockSymbol", OdbcType.VarChar, 10);
OdbcTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
cmd2.Transaction = tran;
// Delete any existing values;
cmd.ExecuteNonQuery();
foreach (object o in stocks)
{
cmd2.Parameters["@StockSymbol"].Value = o.ToString();
cmd2.ExecuteNonQuery();
}
tran.Commit();
}
catch (OdbcException e)
{
try
{
tran.Rollback();
}
catch
{
}
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "SetStockSymbols");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
}
//// GetZipCode// Retrieves ZipCode value from the database during // the call to GetPropertyValues.//privatestring GetZipCode(string username, bool isAuthenticated)
{
string zipCode = "";
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("SELECT ZipCode FROM Profiles " +
"INNER JOIN ProfileData ON Profiles.UniqueID = ProfileData.UniqueID " +
"WHERE Username = ? AND ApplicationName = ? And IsAnonymous = ?", conn);
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
cmd.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = !isAuthenticated;
try
{
conn.Open();
zipCode = (string)cmd.ExecuteScalar();
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetZipCode");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
return zipCode;
}
//// SetZipCode// Inserts the zip code value into the database during // the call to SetPropertyValues.//privatevoid SetZipCode(int uniqueID, string zipCode)
{
if (zipCode == null) { zipCode = String.Empty; }
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("DELETE FROM ProfileData WHERE UniqueID = ?", conn);
cmd.Parameters.Add("@UniqueID", OdbcType.Int).Value = uniqueID;
OdbcCommand cmd2 = new OdbcCommand("INSERT INTO ProfileData (UniqueID, ZipCode) " +
"Values(?, ?)", conn);
cmd2.Parameters.Add("@UniqueID", OdbcType.Int).Value = uniqueID;
cmd2.Parameters.Add("@ZipCode", OdbcType.VarChar, 10).Value = zipCode;
OdbcTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
cmd2.Transaction = tran;
// Delete any existing values.
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
tran.Commit();
}
catch (OdbcException e)
{
try
{
tran.Rollback();
}
catch
{
}
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "SetZipCode");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
}
//// GetUniqueID// Retrieves the uniqueID from the database for the current user and application.//privateint GetUniqueID(string username, bool isAuthenticated, bool ignoreAuthenticationType)
{
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("SELECT UniqueID FROM Profiles " +
"WHERE Username = ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
if (!ignoreAuthenticationType)
{
cmd.CommandText += " AND IsAnonymous = ?";
cmd.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = !isAuthenticated;
}
int uniqueID = 0;
OdbcDataReader reader = null;
try
{
conn.Open();
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (reader.HasRows)
uniqueID = reader.GetInt32(0);
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetUniqueID");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
return uniqueID;
}
//// CreateProfileForUser// If no user currently exists in the database, // a user record is created during// the call to the GetUniqueID private method.//privateint CreateProfileForUser(string username, bool isAuthenticated)
{
// Check for valid user name.if (username == null)
thrownew ArgumentNullException("User name cannot be null.");
if (username.Length > 255)
thrownew ArgumentException("User name exceeds 255 characters.");
if (username.Contains(","))
thrownew ArgumentException("User name cannot contain a comma (,).");
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("INSERT INTO Profiles (Username, " +
"ApplicationName, LastActivityDate, LastUpdatedDate, " +
"IsAnonymous) Values(?, ?, ?, ?, ?)", conn);
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
cmd.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = DateTime.Now;
cmd.Parameters.Add("@LastUpdatedDate", OdbcType.VarChar).Value = DateTime.Now;
cmd.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = !isAuthenticated;
OdbcCommand cmd2 = new OdbcCommand("SELECT @@IDENTITY", conn);
int uniqueID = 0;
try
{
conn.Open();
cmd.ExecuteNonQuery();
uniqueID = (int)cmd2.ExecuteScalar();
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "CreateProfileForUser");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
return uniqueID;
}
//// ProfileProvider.DeleteProfiles(ProfileInfoCollection)//publicoverrideint DeleteProfiles(ProfileInfoCollection profiles)
{
int deleteCount = 0;
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
foreach (ProfileInfo p in profiles)
{
if (DeleteProfile(p.UserName, conn, tran))
deleteCount++;
}
tran.Commit();
}
catch (Exception e)
{
try
{
tran.Rollback();
}
catch
{
}
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "DeleteProfiles(ProfileInfoCollection)");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
return deleteCount;
}
//// ProfileProvider.DeleteProfiles(string[])//publicoverrideint DeleteProfiles(string[] usernames)
{
int deleteCount = 0;
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
foreach (string user in usernames)
{
if (DeleteProfile(user, conn, tran))
deleteCount++;
}
tran.Commit();
}
catch (Exception e)
{
try
{
tran.Rollback();
}
catch
{
}
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "DeleteProfiles(String())");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
return deleteCount;
}
//// ProfileProvider.DeleteInactiveProfiles//publicoverrideint DeleteInactiveProfiles(
ProfileAuthenticationOption authenticationOption,
DateTime userInactiveSinceDate)
{
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("SELECT Username FROM Profiles " +
"WHERE ApplicationName = ? AND " +
" LastActivityDate <= ?", conn);
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
cmd.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = userInactiveSinceDate;
switch (authenticationOption)
{
case ProfileAuthenticationOption.Anonymous:
cmd.CommandText += " AND IsAnonymous = ?";
cmd.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = true;
break;
case ProfileAuthenticationOption.Authenticated:
cmd.CommandText += " AND IsAnonymous = ?";
cmd.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = false;
break;
default:
break;
}
OdbcDataReader reader = null;
string usernames = "";
try
{
conn.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
usernames += reader.GetString(0) + ",";
}
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "DeleteInactiveProfiles");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
if (usernames.Length > 0)
{
// Remove trailing comma.
usernames = usernames.Substring(0, usernames.Length - 1);
}
// Delete profiles.return DeleteProfiles(usernames.Split(','));
}
//// DeleteProfile// Deletes profile data from the database for the // specified user name.//privatebool DeleteProfile(string username, OdbcConnection conn, OdbcTransaction tran)
{
// Check for valid user name.if (username == null)
thrownew ArgumentNullException("User name cannot be null.");
if (username.Length > 255)
thrownew ArgumentException("User name exceeds 255 characters.");
if (username.Contains(","))
thrownew ArgumentException("User name cannot contain a comma (,).");
int uniqueID = GetUniqueID(username, false, true);
OdbcCommand cmd1 = new OdbcCommand("DELETE * FROM ProfileData WHERE UniqueID = ?", conn);
cmd1.Parameters.Add("@UniqueID", OdbcType.Int).Value = uniqueID;
OdbcCommand cmd2 = new OdbcCommand("DELETE * FROM StockSymbols WHERE UniqueID = ?", conn);
cmd2.Parameters.Add("@UniqueID", OdbcType.Int).Value = uniqueID;
OdbcCommand cmd3 = new OdbcCommand("DELETE * FROM Profiles WHERE UniqueID = ?", conn);
cmd3.Parameters.Add("@UniqueID", OdbcType.Int).Value = uniqueID;
cmd1.Transaction = tran;
cmd2.Transaction = tran;
cmd3.Transaction = tran;
int numDeleted = 0;
// Exceptions will be caught by the calling method.
numDeleted += cmd1.ExecuteNonQuery();
numDeleted += cmd2.ExecuteNonQuery();
numDeleted += cmd3.ExecuteNonQuery();
if (numDeleted == 0)
returnfalse;
elsereturntrue;
}
//// ProfileProvider.FindProfilesByUserName//publicoverride ProfileInfoCollection FindProfilesByUserName(
ProfileAuthenticationOption authenticationOption,
string usernameToMatch,
int pageIndex,
int pageSize,
outint totalRecords)
{
CheckParameters(pageIndex, pageSize);
return GetProfileInfo(authenticationOption, usernameToMatch,
null, pageIndex, pageSize, out totalRecords);
}
//// ProfileProvider.FindInactiveProfilesByUserName//publicoverride ProfileInfoCollection FindInactiveProfilesByUserName(
ProfileAuthenticationOption authenticationOption,
string usernameToMatch,
DateTime userInactiveSinceDate,
int pageIndex,
int pageSize,
outint totalRecords)
{
CheckParameters(pageIndex, pageSize);
return GetProfileInfo(authenticationOption, usernameToMatch, userInactiveSinceDate,
pageIndex, pageSize, out totalRecords);
}
//// ProfileProvider.GetAllProfiles//publicoverride ProfileInfoCollection GetAllProfiles(
ProfileAuthenticationOption authenticationOption,
int pageIndex,
int pageSize,
outint totalRecords)
{
CheckParameters(pageIndex, pageSize);
return GetProfileInfo(authenticationOption, null, null,
pageIndex, pageSize, out totalRecords);
}
//// ProfileProvider.GetAllInactiveProfiles//publicoverride ProfileInfoCollection GetAllInactiveProfiles(
ProfileAuthenticationOption authenticationOption,
DateTime userInactiveSinceDate,
int pageIndex,
int pageSize,
outint totalRecords)
{
CheckParameters(pageIndex, pageSize);
return GetProfileInfo(authenticationOption, null, userInactiveSinceDate,
pageIndex, pageSize, out totalRecords);
}
//// ProfileProvider.GetNumberOfInactiveProfiles//publicoverrideint GetNumberOfInactiveProfiles(
ProfileAuthenticationOption authenticationOption,
DateTime userInactiveSinceDate)
{
int inactiveProfiles = 0;
ProfileInfoCollection profiles =
GetProfileInfo(authenticationOption, null, userInactiveSinceDate,
0, 0, out inactiveProfiles);
return inactiveProfiles;
}
//// CheckParameters// Verifies input parameters for page size and page index. // Called by GetAllProfiles, GetAllInactiveProfiles, // FindProfilesByUserName, and FindInactiveProfilesByUserName.//privatevoid CheckParameters(int pageIndex, int pageSize)
{
if (pageIndex < 0)
thrownew ArgumentException("Page index must 0 or greater.");
if (pageSize < 1)
thrownew ArgumentException("Page size must be greater than 0.");
}
//// GetProfileInfo// Retrieves a count of profiles and creates a // ProfileInfoCollection from the profile data in the // database. Called by GetAllProfiles, GetAllInactiveProfiles,// FindProfilesByUserName, FindInactiveProfilesByUserName, // and GetNumberOfInactiveProfiles.// Specifying a pageIndex of 0 retrieves a count of the results only.//private ProfileInfoCollection GetProfileInfo(
ProfileAuthenticationOption authenticationOption,
string usernameToMatch,
object userInactiveSinceDate,
int pageIndex,
int pageSize,
outint totalRecords)
{
OdbcConnection conn = new OdbcConnection(connectionString);
// Command to retrieve the total count.
OdbcCommand cmd = new OdbcCommand("SELECT COUNT(*) FROM Profiles WHERE ApplicationName = ? ", conn);
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
// Command to retrieve the profile data.
OdbcCommand cmd2 = new OdbcCommand("SELECT Username, LastActivityDate, LastUpdatedDate, " +
"IsAnonymous FROM Profiles WHERE ApplicationName = ? ", conn);
cmd2.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
// If searching for a user name to match, add the command text and parameters.if (usernameToMatch != null)
{
cmd.CommandText += " AND Username LIKE ? ";
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = usernameToMatch;
cmd2.CommandText += " AND Username LIKE ? ";
cmd2.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = usernameToMatch;
}
// If searching for inactive profiles, // add the command text and parameters.if (userInactiveSinceDate != null)
{
cmd.CommandText += " AND LastActivityDate <= ? ";
cmd.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = (DateTime)userInactiveSinceDate;
cmd2.CommandText += " AND LastActivityDate <= ? ";
cmd2.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = (DateTime)userInactiveSinceDate;
}
// If searching for a anonymous or authenticated profiles, // add the command text and parameters.switch (authenticationOption)
{
case ProfileAuthenticationOption.Anonymous:
cmd.CommandText += " AND IsAnonymous = ?";
cmd.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = true;
cmd2.CommandText += " AND IsAnonymous = ?";
cmd2.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = true;
break;
case ProfileAuthenticationOption.Authenticated:
cmd.CommandText += " AND IsAnonymous = ?";
cmd.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = false;
cmd2.CommandText += " AND IsAnonymous = ?";
cmd2.Parameters.Add("@IsAnonymous", OdbcType.Bit).Value = false;
break;
default:
break;
}
// Get the data.
OdbcDataReader reader = null;
ProfileInfoCollection profiles = new ProfileInfoCollection();
try
{
conn.Open();
// Get the profile count.
totalRecords = (int)cmd.ExecuteScalar();
// No profiles found.if (totalRecords <= 0) { return profiles; }
// Count profiles only.if (pageSize == 0) { return profiles; }
reader = cmd2.ExecuteReader();
int counter = 0;
int startIndex = pageSize * (pageIndex - 1);
int endIndex = startIndex + pageSize - 1;
while (reader.Read())
{
if (counter >= startIndex)
{
ProfileInfo p = GetProfileInfoFromReader(reader);
profiles.Add(p);
}
if (counter >= endIndex)
{
cmd.Cancel();
break;
}
counter++;
}
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetProfileInfo");
thrownew ProviderException(exceptionMessage);
}
else
{
throw e;
}
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
return profiles;
}
//// GetProfileInfoFromReader// Takes the current row from the OdbcDataReader// and populates a ProfileInfo object from the values. //private ProfileInfo GetProfileInfoFromReader(OdbcDataReader reader)
{
string username = reader.GetString(0);
DateTime lastActivityDate = new DateTime();
if (reader.GetValue(1) != DBNull.Value)
lastActivityDate = reader.GetDateTime(1);
DateTime lastUpdatedDate = new DateTime();
if (reader.GetValue(2) != DBNull.Value)
lastUpdatedDate = reader.GetDateTime(2);
bool isAnonymous = reader.GetBoolean(3);
// ProfileInfo.Size not currently implemented.
ProfileInfo p = new ProfileInfo(username,
isAnonymous, lastActivityDate, lastUpdatedDate,0);
return p;
}
//// WriteToEventLog// A helper function that writes exception detail to the event // log. Exceptions are written to the event log as a security // measure to prevent private database details from being // returned to the browser. If a method does not return a // status or Boolean value indicating whether the action succeeded // or failed, the caller also throws a generic exception.//privatevoid WriteToEventLog(Exception e, string action)
{
EventLog log = new EventLog();
log.Source = eventSource;
log.Log = eventLog;
string message = "An exception occurred while communicating with the data source.\n\n";
message += "Action: " + action + "\n\n";
message += "Exception: " + e.ToString();
log.WriteEntry(message);
}
}
}
See Also
Tasks
How to: Build and Run the Profile Provider Example