public override void Initialize(string name, NameValueCollection config)
{
if (config == null)
{
throw new ArgumentNullException("config");
}
if (name == null || name.Length < 1)
{
name = "SqlCeProfileProvider";
}
if (string.IsNullOrEmpty(config["description"]))
{
config.Remove("description");
config.Add("description", "SqlCeProfileProvider");
}
base.Initialize(name, config);
//
// Initialize SqlCeConnection.
//
ConnectionStringSettings ConnectionStringSettings =
ConfigurationManager.ConnectionStrings[config["connectionStringName"]];
if (ConnectionStringSettings == null || string.IsNullOrWhiteSpace(ConnectionStringSettings.ConnectionString))
{
throw new ProviderException("Connection string cannot be blank.");
}
connectionString = ConnectionStringSettings.ConnectionString;
ApplicationName = GetConfigValue(config["applicationName"], System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);
Util.ConnectionStringName = ConnectionStringSettings.Name;
Util.ConnectionString = connectionString;
SqlCeMembershipUtils.CreateDatabaseIfRequired(connectionString, ApplicationName);
pApplicationId = SqlCeMembershipUtils.GetApplicationId(connectionString, ApplicationName);
config.Remove("connectionStringName");
config.Remove("applicationName");
if (config.Count > 0)
{
string attribUnrecognized = config.GetKey(0);
if (!String.IsNullOrEmpty(attribUnrecognized))
{
throw new ProviderException(SR.GetString(SR.Provider_unrecognized_attribute, attribUnrecognized));
}
}
}
//
// System.Configuration.Provider.ProviderBase.Initialize Method
//
public override void Initialize(string name, NameValueCollection config)
{
//
// Initialize values from web.config.
//
if (config == null)
{
throw new ArgumentNullException("config");
}
if (name == null || name.Length == 0)
{
name = "SqlCeRoleProvider";
}
if (String.IsNullOrEmpty(config["description"]))
{
config.Remove("description");
config.Add("description", "SqlCe Role provider");
}
// Initialize the abstract base class.
base.Initialize(name, config);
if (string.IsNullOrWhiteSpace(config["applicationName"]))
{
pApplicationName = System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath;
}
else
{
pApplicationName = config["applicationName"];
}
if (config["writeExceptionsToEventLog"] != null)
{
if (config["writeExceptionsToEventLog"].ToUpperInvariant() == "TRUE")
{
pWriteExceptionsToEventLog = true;
}
}
//
// Initialize SqlCeConnection.
//
pConnectionStringSettings = ConfigurationManager.
ConnectionStrings[config["connectionStringName"]];
if (pConnectionStringSettings == null || string.IsNullOrWhiteSpace(pConnectionStringSettings.ConnectionString))
{
throw new ProviderException("Connection string cannot be blank.");
}
connectionString = pConnectionStringSettings.ConnectionString;
Util.ConnectionStringName = pConnectionStringSettings.Name;
Util.ConnectionString = connectionString;
SqlCeMembershipUtils.CreateDatabaseIfRequired(connectionString, ApplicationName);
pApplicationId = SqlCeMembershipUtils.GetApplicationId(connectionString, pApplicationName);
}
private void GetPropertyValuesFromDatabase(string userName, SettingsPropertyValueCollection svc)
{
HttpContext context = HttpContext.Current;
string[] names = null;
string values = null;
byte[] buf = null;
string sName = null;
if (context != null)
{
sName = (context.Request.IsAuthenticated ? context.User.Identity.Name : context.Request.AnonymousID);
}
using (SqlCeConnection conn = new SqlCeConnection(connectionString))
{
Guid userId;
using (SqlCeCommand cmd = new SqlCeCommand("SELECT ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName", conn))
{
conn.Open();
cmd.Parameters.Clear();
cmd.Parameters.Add(CreateInputParam("@ApplicationId", SqlDbType.UniqueIdentifier, SqlCeMembershipUtils.GetApplicationId(conn.ConnectionString, ApplicationName)));
cmd.Parameters.Add(CreateInputParam("@UserName", SqlDbType.NVarChar, userName));
cmd.CommandText = "SELECT UserId FROM aspnet_Users WHERE ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)";
object o = cmd.ExecuteScalar();
if (o != null && o is Guid)
{
userId = (Guid)o;
}
else
{
return;
}
cmd.Parameters.Clear();
cmd.Parameters.Add(CreateInputParam("@UserId", SqlDbType.UniqueIdentifier, userId));
cmd.CommandText = "SELECT PropertyNames, PropertyValuesString, PropertyValuesBinary FROM aspnet_Profile WHERE UserId = @UserId";
using (SqlCeDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
cmd.Parameters.Clear();
cmd.Parameters.Add(CreateInputParam("@CurrentTimeUtc", SqlDbType.DateTime, DateTime.UtcNow));
cmd.Parameters.Add(CreateInputParam("@UserId", SqlDbType.UniqueIdentifier, userId));
cmd.CommandText = "UPDATE aspnet_Users SET LastActivityDate = @CurrentTimeUtc WHERE UserId = @UserId";
cmd.ExecuteNonQuery();
if (reader.Read())
{
names = reader.GetString(0).Split(':');
values = reader.GetString(1);
int size = (int)reader.GetBytes(2, 0, null, 0, 0);
buf = new byte[size];
reader.GetBytes(2, 0, buf, 0, size);
}
ParseDataFromDB(names, values, buf, svc);
}
}
}
}
private ProfileInfoCollection GetProfilesForQuery(SqlCeParameter[] args, ProfileAuthenticationOption authenticationOption, int pageIndex, int pageSize, out int totalRecords)
{
if (pageIndex < 0)
{
throw new ArgumentException("PageIndex must be greater than -1", "pageIndex");
}
if (pageSize < 1)
{
throw new ArgumentException("PageSize must be greater than 0", "pageSize");
}
long lowerBound = pageIndex * pageSize;
long upperBound = (long)pageSize - 1 + lowerBound;
if (upperBound > Int32.MaxValue)
{
throw new ArgumentException("The combination of pageIndex and pageSize cannot exceed the maximum value of System.Int32.", "pageIndex and pageSize");
}
SqlCeDataReader reader = null;
totalRecords = 0;
try
{
using (SqlCeConnection conn = new SqlCeConnection(connectionString))
{
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand(
@"
SELECT
aspnet_Users.UserName,
aspnet_Users.IsAnonymous,
aspnet_Users.LastActivityDate,
aspnet_Profile.LastUpdatedDate,
DATALENGTH(aspnet_Profile.PropertyNames) + DATALENGTH(aspnet_Profile.PropertyValuesString) + DATALENGTH(aspnet_Profile.PropertyValuesBinary)
FROM
aspnet_Users,
aspnet_Profile
WHERE
aspnet_Users.UserId = aspnet_Profile.UserId
AND ApplicationId = @ApplicationId
AND
(
@ProfileAuthOptions = 2
OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1)
OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)
)"
, conn))
{
cmd.Parameters.Add(CreateInputParam("@ApplicationId", SqlDbType.UniqueIdentifier, SqlCeMembershipUtils.GetApplicationId(conn.ConnectionString, ApplicationName)));
cmd.Parameters.Add(CreateInputParam("@ProfileAuthOptions", SqlDbType.Int, (int)authenticationOption));
cmd.Parameters.Add(CreateInputParam("@PageLowerBound", SqlDbType.Int, lowerBound));
cmd.Parameters.Add(CreateInputParam("@PageSize", SqlDbType.Int, pageSize));
foreach (SqlCeParameter arg in args)
{
cmd.Parameters.Add(arg);
switch (arg.ParameterName)
{
case "@InactiveSinceDate":
cmd.CommandText += " AND (@InactiveSinceDate IS NULL OR aspnet_Users.LastActivityDate <= @InactiveSinceDate)";
break;
case "@UserNameToMatch":
cmd.CommandText += " AND (@UserNameToMatch IS NULL OR LoweredUserName LIKE LOWER(@UserNameToMatch))";
break;
}
}
// append paging
cmd.CommandText += " ORDER BY aspnet_Users.Username OFFSET @PageLowerBound ROWS FETCH NEXT @PageSize ROWS ONLY";
reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
ProfileInfoCollection profiles = new ProfileInfoCollection();
while (reader.Read())
{
string username;
DateTime dtLastActivity, dtLastUpdated;
bool isAnon;
username = reader.GetString(0);
isAnon = reader.GetBoolean(1);
dtLastActivity = DateTime.SpecifyKind(reader.GetDateTime(2), DateTimeKind.Utc);
dtLastUpdated = DateTime.SpecifyKind(reader.GetDateTime(3), DateTimeKind.Utc);
int size = reader.GetInt32(4);
profiles.Add(new ProfileInfo(username, isAnon, dtLastActivity, dtLastUpdated, size));
}
totalRecords = profiles.Count;
if (reader.NextResult())
{
if (reader.Read())
{
totalRecords = reader.GetInt32(0);
}
}
return(profiles);
}
}
}
catch
{
throw;
}
}
public override void SetPropertyValues(SettingsContext sc, SettingsPropertyValueCollection properties)
{
string username = (string)sc["UserName"];
bool userIsAuthenticated = (bool)sc["IsAuthenticated"];
if (username == null || username.Length < 1 || properties.Count < 1)
{
return;
}
string names = String.Empty;
string values = String.Empty;
byte[] buf = null;
PrepareDataForSaving(ref names, ref values, ref buf, true, properties, userIsAuthenticated);
if (names.Length == 0)
{
return;
}
Guid userId = Guid.Empty;
using (SqlCeConnection conn = new SqlCeConnection(connectionString))
{
using (SqlCeCommand cmd = new SqlCeCommand("SELECT Count(UserId) FROM aspnet_Users WHERE ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)", conn))
{
conn.Open();
cmd.Parameters.Add(CreateInputParam("@ApplicationId", SqlDbType.UniqueIdentifier, SqlCeMembershipUtils.GetApplicationId(conn.ConnectionString, ApplicationName)));
cmd.Parameters.Add(CreateInputParam("@UserName", SqlDbType.NVarChar, username));
int userCount = (int)cmd.ExecuteScalar();
if (userCount.Equals(0))
{
// create user: pinched from SqlCeMembershipProvider.CreateUser method
userId = Guid.NewGuid();
using (SqlCeCommand cmd2 = new SqlCeCommand(
@"INSERT INTO [aspnet_Users]
([ApplicationId]
,[UserId]
,[UserName]
,[LoweredUserName]
,[IsAnonymous]
,[LastActivityDate])
VALUES
(@ApplicationId
,@UserId
,@UserName
,@LoweredUserName
,@IsAnonymous
,@LastActivityDate)
" , conn))
{
cmd2.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = SqlCeMembershipUtils.GetApplicationId(conn.ConnectionString, ApplicationName);
cmd2.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier).Value = (Guid)userId;
cmd2.Parameters.Add("@UserName", SqlDbType.NVarChar, 256).Value = username;
cmd2.Parameters.Add("@LoweredUserName", SqlDbType.NVarChar, 256).Value = username.ToLowerInvariant();
cmd2.Parameters.Add("@IsAnonymous", SqlDbType.Bit).Value = !userIsAuthenticated;
cmd2.Parameters.Add("@LastActivityDate", SqlDbType.DateTime).Value = DateTime.UtcNow;
int rowsAffected = cmd2.ExecuteNonQuery();
if (!rowsAffected.Equals(1))
{
// oops!
}
}
}
else if (userCount > 1)
{
throw new Exception(String.Format(CultureInfo.CurrentCulture, "Duplicate user records found for username '{0}' and application '{1}'", username, ApplicationName));
}
cmd.CommandText = "SELECT UserId FROM aspnet_Users WHERE ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)";
userId = (Guid)cmd.ExecuteScalar();
}
using (SqlCeCommand cmd = new SqlCeCommand("UPDATE aspnet_Users SET LastActivityDate=@CurrentTimeUtc WHERE UserId = @UserId", conn))
{
cmd.Parameters.Add(CreateInputParam("@UserId", SqlDbType.UniqueIdentifier, userId));
cmd.Parameters.Add(CreateInputParam("@CurrentTimeUtc", SqlDbType.DateTime, DateTime.UtcNow));
cmd.ExecuteNonQuery();
}
int profileCount = 0;
using (SqlCeCommand cmd = new SqlCeCommand("SELECT COUNT(UserId) FROM aspnet_Profile WHERE UserId = @UserId", conn))
{
cmd.Parameters.Add(CreateInputParam("@UserId", SqlDbType.UniqueIdentifier, userId));
object o = cmd.ExecuteScalar();
profileCount = (int)o;
}
switch (profileCount)
{
case 0:
using (SqlCeCommand cmd = new SqlCeCommand("INSERT INTO aspnet_Profile(UserId, PropertyNames, PropertyValuesString, PropertyValuesBinary, LastUpdatedDate) VALUES (@UserId, @PropertyNames, @PropertyValuesString, @PropertyValuesBinary, @CurrentTimeUtc)", conn))
{
cmd.Parameters.Add(CreateInputParam("@UserId", SqlDbType.UniqueIdentifier, userId));
cmd.Parameters.Add(CreateInputParam("@PropertyNames", SqlDbType.NText, names));
cmd.Parameters.Add(CreateInputParam("@PropertyValuesString", SqlDbType.NText, values));
cmd.Parameters.Add(CreateInputParam("@PropertyValuesBinary", SqlDbType.Image, buf));
cmd.Parameters.Add(CreateInputParam("@CurrentTimeUtc", SqlDbType.DateTime, DateTime.UtcNow));
cmd.ExecuteNonQuery();
}
break;
case 1:
using (SqlCeCommand cmd = new SqlCeCommand("UPDATE aspnet_Profile SET PropertyNames=@PropertyNames, PropertyValuesString = @PropertyValuesString, PropertyValuesBinary = @PropertyValuesBinary, LastUpdatedDate=@CurrentTimeUtc WHERE UserId = @UserId", conn))
{
cmd.Parameters.Add(CreateInputParam("@UserId", SqlDbType.UniqueIdentifier, userId));
cmd.Parameters.Add(CreateInputParam("@PropertyNames", SqlDbType.NText, names));
cmd.Parameters.Add(CreateInputParam("@PropertyValuesString", SqlDbType.NText, values));
cmd.Parameters.Add(CreateInputParam("@PropertyValuesBinary", SqlDbType.Image, buf));
cmd.Parameters.Add(CreateInputParam("@CurrentTimeUtc", SqlDbType.DateTime, DateTime.UtcNow));
cmd.ExecuteNonQuery();
}
break;
}
}
}
public override int GetNumberOfInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate)
{
using (SqlCeConnection conn = new SqlCeConnection(connectionString))
{
conn.Open();
SqlCeDataReader reader;
List <string> userids = new List <string>();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT UserId FROM aspnet_Users u WHERE ApplicationId = @ApplicationId AND LastActivityDate <= @InactiveSinceDate AND (@ProfileAuthOptions = 2 OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1) OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0))", conn))
{
cmd.Parameters.Add(CreateInputParam("@ApplicationId", SqlDbType.UniqueIdentifier, SqlCeMembershipUtils.GetApplicationId(conn.ConnectionString, ApplicationName)));
cmd.Parameters.Add(CreateInputParam("@ProfileAuthOptions", SqlDbType.Int, (int)authenticationOption));
cmd.Parameters.Add(CreateInputParam("@InactiveSinceDate", SqlDbType.DateTime, userInactiveSinceDate.ToUniversalTime()));
reader = cmd.ExecuteReader();
while (reader.Read())
{
userids.Add("'" + ((Guid)reader["UserId"]).ToString() + "'");
}
}
using (SqlCeCommand cmd = new SqlCeCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SELECT COUNT(UserId) FROM aspnet_Profile WHERE UserId IN (" + string.Join(",", userids.ToArray()) + ")";
object o = cmd.ExecuteScalar();
if (o == null || !(o is int))
{
return(0);
}
return((int)o);
}
}
}
