public override int DeleteProfiles(ProfileInfoCollection profiles)
{
var rowsAffected = 0;
try
{
if (profiles.Count < 1)
{
throw new ArgumentException("The collection parameter 'profiles' should not be empty.", "profiles");
}
var usernames = profiles
.Cast <ProfileInfo>()
.Select(x => x.UserName)
.ToArray();
rowsAffected = DeleteProfiles(usernames);
}
catch (SQLiteException ex)
{
if (!WriteExceptionsToEventLog)
{
throw;
}
EventLogger.WriteToEventLog(ex, "DeleteProfiles(ProfileInfoCollection)");
}
return(rowsAffected);
}
public override ProfileInfoCollection FindProfilesByUserName(ProfileAuthenticationOption authenticationOption,
string usernameToMatch,
int pageIndex, int pageSize, out int totalRecords)
{
var results = new ProfileInfoCollection();
totalRecords = 0;
try
{
using (var con = SQLiteUtils.GetConnection(_connectionString))
{
con.Open();
using (var cmd = con.CreateCommand())
{
cmd.CommandText =
"SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate, DATALENGTH(p.PropertyNames) + DATALENGTH(p.PropertyValuesString) + DATALENGTH(p.PropertyValuesBinary) " +
"FROM [aspnet_Users] u, [aspnet_Profile] p " +
"WHERE u.UserId = p.UserId AND ApplicationId = @ApplicationId AND (@ProfileAuthOptions = 2 OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1) OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0)) AND (@UserNameToMatch IS NULL OR LoweredUserName LIKE LOWER(@UserNameToMatch)) " +
"ORDER BY u.UserName Asc " +
"LIMIT @PageSize OFFSET @PageStart";
cmd.Parameters.AddRange(new[]
{
cmd.CreateParameter("@ApplicationId", _applicationId),
cmd.CreateParameter("@ProfileAuthOptions", (int)authenticationOption),
cmd.CreateParameter("@UserNameToMatch", usernameToMatch),
cmd.CreateParameter("@PageSize", pageSize),
cmd.CreateParameter("@PageStart", pageIndex * pageSize)
});
using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (reader.Read())
{
var username = reader.GetString(0);
var isAnonymous = reader.GetBoolean(1);
var lastActivityDate = DateTime.SpecifyKind(reader.GetDateTime(2), DateTimeKind.Utc);
var lastUpdatedDate = DateTime.SpecifyKind(reader.GetDateTime(3), DateTimeKind.Utc);
var size = reader.GetInt32(4);
results.Add(new ProfileInfo(username, isAnonymous, lastActivityDate, lastUpdatedDate, size));
}
}
}
}
totalRecords = results.Count;
}
catch (SQLiteException ex)
{
if (!WriteExceptionsToEventLog)
{
throw;
}
EventLogger.WriteToEventLog(ex, "FindProfilesByUserName");
}
return(results);
}
private string GetUserId(string userName)
{
try
{
using (var con = SQLiteUtils.GetConnection(_connectionString))
{
con.Open();
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT UserId " +
"FROM [aspnet_Users] " +
"WHERE LOWER(@UserName) = LoweredUserName AND ApplicationId = @ApplicationId";
cmd.Parameters.AddRange(new[]
{
cmd.CreateParameter("@UserName", userName),
cmd.CreateParameter("@ApplicationId", _applicationId)
});
return(cmd.ExecuteScalar() as string);
}
}
}
catch (SQLiteException ex)
{
if (!WriteExceptionsToEventLog)
{
throw;
}
EventLogger.WriteToEventLog(ex, "GetUserId");
}
return(Guid.Empty.ToString());
}
public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
{
try
{
if (!RoleExists(roleName))
{
throw new ProviderException("Role does not exist.");
}
if (throwOnPopulatedRole && GetUsersInRole(roleName).Length > 0)
{
throw new ProviderException("Cannot delete a populated role.");
}
var roleId = GetRoleId(roleName);
using (var con = SQLiteUtils.GetConnection(_connectionString))
{
using (var transaction = con.BeginTransaction())
{
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "DELETE FROM [aspnet_UsersInRoles] " +
"WHERE RoleId = @RoleId";
cmd.Parameters.Add(cmd.CreateParameter("@RoleId", roleId));
cmd.ExecuteNonQuery();
}
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "DELETE FROM [aspnet_Roles] " +
"WHERE RoleId = @RoleId AND ApplicationId = @ApplicationId";
cmd.Parameters.AddRange(new[]
{
cmd.CreateParameter("@RoleId", roleId),
cmd.CreateParameter("@AppliationId", _applicationId)
});
cmd.ExecuteNonQuery();
}
transaction.Commit();
}
}
return(true);
}
catch (SQLiteException ex)
{
if (!WriteExceptionsToEventLog)
{
throw;
}
EventLogger.WriteToEventLog(ex, "DeleteRole");
}
return(false);
}
public override int GetNumberOfInactiveProfiles(ProfileAuthenticationOption authenticationOption,
DateTime userInactiveSinceDate)
{
var numberOfInactiveProfiles = 0;
try
{
using (var con = SQLiteUtils.GetConnection(_connectionString))
{
con.Open();
var userIds = new List <string>();
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT UserId " +
"FROM [aspnet_Users] " +
"WHERE ApplicationId = @ApplicationId AND LastActivityDate <= @InactiveSinceDate AND (@ProfileAuthOptions = 2 OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1) OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0))";
cmd.Parameters.AddRange(new[]
{
cmd.CreateParameter("@ApplicationId", _applicationId),
cmd.CreateParameter("@ProfileAuthOptions", (int)authenticationOption),
cmd.CreateParameter("@InactiveSinceDate", userInactiveSinceDate.ToUniversalTime())
});
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
userIds.Add(reader.GetString(0));
}
}
}
using (var cmd = con.CreateCommand())
{
cmd.CommandText = string.Format("SELECT COUNT(UserId) " +
"FROM [aspnet_Profile] " +
"WHERE UserId IN ('{0}')", string.Join("','", userIds));
numberOfInactiveProfiles = (int)cmd.ExecuteScalar();
}
}
}
catch (SQLiteException ex)
{
if (!WriteExceptionsToEventLog)
{
throw;
}
EventLogger.WriteToEventLog(ex, "GetNumberOfInactiveProfiles");
}
return(numberOfInactiveProfiles);
}
public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
{
try
{
if (roleNames.Any(x => !RoleExists(x)))
{
throw new ProviderException("Role name not found.");
}
if (usernames.Any(username => roleNames.Any(roleName => !IsUserInRole(username, roleName))))
{
throw new ProviderException("User is not in role.");
}
using (var con = SQLiteUtils.GetConnection(_connectionString))
{
con.Open();
using (var transaction = con.BeginTransaction())
{
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "DELETE FROM [aspnet_UsersInRoles] " +
"WHERE UserId = @UserId AND RoleId = @RoleId";
foreach (var username in usernames)
{
foreach (var roleName in roleNames)
{
cmd.Parameters.Clear();
cmd.Parameters.AddRange(new[]
{
cmd.CreateParameter("@UserId", GetUserId(username)),
cmd.CreateParameter("@RoleId", GetRoleId(roleName))
});
cmd.ExecuteNonQuery();
}
}
}
transaction.Commit();
}
}
}
catch (SQLiteException ex)
{
if (!WriteExceptionsToEventLog)
{
throw;
}
EventLogger.WriteToEventLog(ex, "RemoveUsersFromRoles");
}
}
private void ParseDataFromDb(string[] names, string values, byte[] buf, SettingsPropertyValueCollection properties)
{
if (names == null || values == null || buf == null || properties == null)
{
return;
}
try
{
for (var i = 0; i < names.Length / 4; i++)
{
var name = names[i * 4];
var pp = properties[name];
if (pp == null) // property not found
{
continue;
}
var startPos = Int32.Parse(names[i * 4 + 2], CultureInfo.InvariantCulture);
var length = Int32.Parse(names[i * 4 + 3], CultureInfo.InvariantCulture);
if (length == -1 && !pp.Property.PropertyType.IsValueType) // Null Value
{
pp.PropertyValue = null;
pp.IsDirty = false;
pp.Deserialized = true;
}
if (names[i * 4 + 1] == "S" && startPos >= 0 && length > 0 && values.Length >= startPos + length)
{
pp.SerializedValue = values.Substring(startPos, length);
}
if (names[i * 4 + 1] == "B" && startPos >= 0 && length > 0 && buf.Length >= startPos + length)
{
var buf2 = new byte[length];
Buffer.BlockCopy(buf, startPos, buf2, 0, length);
pp.SerializedValue = buf2;
}
}
}
catch (Exception ex)
{
if (WriteExceptionsToEventLog)
{
EventLogger.WriteToEventLog(ex, "ParseDataFromDb");
}
}
}
public override string[] FindUsersInRole(string roleName, string usernameToMatch)
{
var users = new List <string>();
try
{
using (var con = SQLiteUtils.GetConnection(_connectionString))
{
con.Open();
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT u.UserName " +
"FROM [aspnet_Users] u, [aspnet_UsersInRoles] ur " +
"WHERE u.UserName LIKE @UserNameSearch AND ur.RoleName = @RoleName AND u.ApplicationId = @ApplicationId";
cmd.Parameters.AddRange(new []
{
cmd.CreateParameter("@UserNameSearch", usernameToMatch),
cmd.CreateParameter("@RoleName", roleName),
cmd.CreateParameter("@ApplicationId", _applicationId)
});
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
users.Add(reader.GetString(0));
}
}
}
}
}
catch (SQLiteException ex)
{
if (!WriteExceptionsToEventLog)
{
throw;
}
EventLogger.WriteToEventLog(ex, "FindUsersInRole");
}
return(users.ToArray());
}
public override string[] GetRolesForUser(string username)
{
var roles = new List <string>();
try
{
using (var con = SQLiteUtils.GetConnection(_connectionString))
{
con.Open();
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT RoleName " +
"FROM [aspnet_Roles] r, [aspnet_UsersInRoles] ur " +
"WHERE r.RoleId = ur.RoleId AND r.ApplicationId = @ApplicationId and ur.UserId = @UserId " +
"ORDER BY RoleName";
cmd.Parameters.AddRange(new []
{
cmd.CreateParameter("@UserId", GetUserId(username)),
cmd.CreateParameter("@ApplicationId", _applicationId)
});
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
roles.Add(reader.GetString(0));
}
}
}
}
}
catch (SQLiteException ex)
{
if (!WriteExceptionsToEventLog)
{
throw;
}
EventLogger.WriteToEventLog(ex, "GetRolesForUser");
}
return(roles.ToArray());
}
public override void CreateRole(string roleName)
{
try
{
if (roleName.IndexOf(',') > 0)
{
throw new ArgumentException("Role names cannot contain commas.");
}
if (RoleExists(roleName))
{
throw new ProviderException("Role name already exists");
}
using (var con = SQLiteUtils.GetConnection(_connectionString))
{
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "INSERT INTO [aspnet_Roles] (" +
"RoleName, LoweredRoleName, ApplicationId" +
") VALUES ( " +
"@Rolename, @LoweredRoleName, @ApplicationId" +
")";
cmd.Parameters.AddRange(new []
{
cmd.CreateParameter("@RoleName", roleName),
cmd.CreateParameter("@LoweredRoleName", roleName.ToLowerInvariant()),
cmd.CreateParameter("@ApplicationId", _applicationId)
});
cmd.ExecuteNonQuery();
}
}
}
catch (SQLiteException ex)
{
if (!WriteExceptionsToEventLog)
{
throw;
}
EventLogger.WriteToEventLog(ex, "CreateRole");
}
}
public override int DeleteProfiles(string[] usernames)
{
var rowsAffected = 0;
try
{
using (var con = SQLiteUtils.GetConnection(_connectionString))
{
con.Open();
using (var transaction = con.BeginTransaction())
{
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "DELETE FROM [aspnet_Profile] WHERE @UserId = UserId";
foreach (var username in usernames)
{
cmd.Parameters.Clear();
cmd.Parameters.Add(cmd.CreateParameter("@UserId", GetUserId(username)));
rowsAffected += cmd.ExecuteNonQuery();
}
}
transaction.Commit();
}
}
}
catch (SQLiteException ex)
{
if (!WriteExceptionsToEventLog)
{
throw;
}
EventLogger.WriteToEventLog(ex, "DeleteProfiles(String[])");
}
return(rowsAffected);
}
public override string[] GetAllRoles()
{
var roles = new List <string>();
try
{
using (var con = SQLiteUtils.GetConnection(_connectionString))
{
con.Open();
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT RoleName " +
"FROM [aspnet_Roles] " +
"WHERE ApplicationId = @ApplicationId";
cmd.Parameters.Add(cmd.CreateParameter("@ApplicationId", _applicationId));
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
roles.Add(reader.GetString(0));
}
}
}
}
}
catch (SQLiteException ex)
{
if (!WriteExceptionsToEventLog)
{
throw;
}
EventLogger.WriteToEventLog(ex, "GetAllRoles");
}
return(roles.ToArray());
}
public override bool IsUserInRole(string username, string roleName)
{
var userIsInRole = false;
try
{
using (var con = SQLiteUtils.GetConnection(_connectionString))
{
con.Open();
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT COUNT(*) " +
"FROM [aspnet_UsersInRoles] ur, [aspnet_Users] u, [aspnet_Roles] r " +
"WHERE ur.UserId = u.UserId AND ur.RoleId = ar.RoleId AND u.UserName = @UserName AND r.RoleName = @RoleName";
cmd.Parameters.AddRange(new []
{
cmd.CreateParameter("@UserName", username),
cmd.CreateParameter("@RoleName", roleName),
cmd.CreateParameter("@ApplicationId", _applicationId)
});
userIsInRole = (int)cmd.ExecuteScalar() > 0;
}
}
}
catch (SQLiteException ex)
{
if (!WriteExceptionsToEventLog)
{
throw;
}
EventLogger.WriteToEventLog(ex, "IsUserInRole");
}
return(userIsInRole);
}
public override bool RoleExists(string roleName)
{
var roleExists = false;
try
{
using (var con = SQLiteUtils.GetConnection(_connectionString))
{
con.Open();
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT COUNT(*) " +
"FROM [aspnet_Roles] " +
"WHERE RoleName = @RoleName AND ApplicationId = @ApplicationId";
cmd.Parameters.AddRange(new []
{
cmd.CreateParameter("@RoleName", roleName),
cmd.CreateParameter("@ApplicationId", _applicationId)
});
roleExists = (int)cmd.ExecuteScalar() > 0;
}
}
}
catch (SQLiteException ex)
{
if (!WriteExceptionsToEventLog)
{
throw;
}
EventLogger.WriteToEventLog(ex, "RoleExists");
}
return(roleExists);
}
private void PrepareDataForSaving(ref string allNames, ref string allValues, ref byte[] buf,
bool binarySupported, SettingsPropertyValueCollection properties, bool userIsAuthenticated)
{
try
{
var names = new StringBuilder();
var values = new StringBuilder();
using (var ms = (binarySupported ? new MemoryStream() : null))
{
var anyItemsToSave = false;
foreach (
var pp in properties.Cast <SettingsPropertyValue>().Where(pp => pp.IsDirty))
{
if (!userIsAuthenticated)
{
var allowAnonymous = (bool)pp.Property.Attributes["AllowAnonymous"];
if (!allowAnonymous)
{
continue;
}
}
anyItemsToSave = true;
break;
}
if (!anyItemsToSave)
{
return;
}
foreach (SettingsPropertyValue pp in properties)
{
if (!userIsAuthenticated)
{
var allowAnonymous = (bool)pp.Property.Attributes["AllowAnonymous"];
if (!allowAnonymous)
{
continue;
}
}
if (!pp.IsDirty && pp.UsingDefaultValue) // Not fetched from DB and not written to
{
continue;
}
int len;
var startPos = 0;
string propValue = null;
if (pp.Deserialized && pp.PropertyValue == null) // is value null?
{
len = -1;
}
else
{
var sVal = pp.SerializedValue;
if (sVal == null)
{
len = -1;
}
else
{
if (!(sVal is string) && !binarySupported)
{
sVal = Convert.ToBase64String((byte[])sVal);
}
if (sVal is string)
{
propValue = (string)sVal;
len = propValue.Length;
startPos = values.Length;
}
else
{
var b2 = (byte[])sVal;
startPos = (int)ms.Position;
ms.Write(b2, 0, b2.Length);
ms.Position = startPos + b2.Length;
len = b2.Length;
}
}
}
names.Append(pp.Name + ":" + ((propValue != null) ? "S" : "B") +
":" + startPos.ToString(CultureInfo.InvariantCulture) + ":" +
len.ToString(CultureInfo.InvariantCulture) + ":");
if (propValue != null)
{
values.Append(propValue);
}
}
if (binarySupported)
{
buf = ms.ToArray();
}
allNames = names.ToString();
allValues = values.ToString();
}
}
catch (Exception ex)
{
if (WriteExceptionsToEventLog)
{
EventLogger.WriteToEventLog(ex, "PrepareDataForSaving");
}
}
}
public override void AddUsersToRoles(string[] usernames, string[] roleNames)
{
try
{
if (roleNames.Any(roleName => !RoleExists(roleName)))
{
throw new ProviderException("Role name not found.");
}
foreach (var username in usernames)
{
if (username.IndexOf(',') > 0)
{
throw new ArgumentException("Usernames cannot contain commas.");
}
if (roleNames.Any(roleName => IsUserInRole(username, roleName)))
{
throw new ProviderException("User is already in role.");
}
}
using (var con = SQLiteUtils.GetConnection(_connectionString))
{
con.Open();
using (var transaction = con.BeginTransaction())
{
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "INSERT INTO [aspnet_UsersInRoles] (" +
"UserId, RoleId" +
") VALUES (" +
"@UserId, @RoleId" +
")";
foreach (var username in usernames)
{
foreach (var roleName in roleNames)
{
cmd.Parameters.Clear();
cmd.Parameters.AddRange(new []
{
cmd.CreateParameter("@UserId", GetUserId(username)),
cmd.CreateParameter("@RoleId", GetRoleId(roleName))
});
cmd.ExecuteNonQuery();
}
}
}
transaction.Commit();
}
}
}
catch (SQLiteException ex)
{
if (!WriteExceptionsToEventLog)
{
throw;
}
EventLogger.WriteToEventLog(ex, "AddUsersToRoles");
}
}
public override int DeleteInactiveProfiles(ProfileAuthenticationOption authenticationOption,
DateTime userInactiveSinceDate)
{
var rowsAffected = 0;
try
{
using (var con = SQLiteUtils.GetConnection(_connectionString))
{
con.Open();
var userIds = new List <string>();
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT UserId " +
"FROM [aspnet_Users] " +
"WHERE ApplicationId = @ApplicationId AND LastActivityDate <= @InactiveSinceDate AND (@ProfileAuthOptions = 2 OR (@ProfileAuthOptions = 0 AND IsAnonymous = 1) OR (@ProfileAuthOptions = 1 AND IsAnonymous = 0))";
cmd.Parameters.AddRange(new[]
{
cmd.CreateParameter("@ApplicationId", _applicationId),
cmd.CreateParameter("@ProfileAuthOptions", (int)authenticationOption),
cmd.CreateParameter("@InactiveSinceDate", userInactiveSinceDate.ToUniversalTime())
});
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
userIds.Add(reader.GetString(0));
}
}
}
using (var transaction = con.BeginTransaction())
{
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "DELETE FROM [aspnet_Profile] WHERE UserId = @UserId";
foreach (var userId in userIds)
{
cmd.Parameters.Clear();
cmd.Parameters.Add(cmd.CreateParameter("@UserId", userId));
rowsAffected += cmd.ExecuteNonQuery();
}
}
transaction.Commit();
}
}
}
catch (SQLiteException ex)
{
if (!WriteExceptionsToEventLog)
{
throw;
}
EventLogger.WriteToEventLog(ex, "DeleteInactiveProfiles");
}
return(rowsAffected);
}