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 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);
}
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 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");
}
}
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 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 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 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);
}
public static string GetApplicationId(string connectionString, string applicationName)
{
using (var con = SQLiteUtils.GetConnection(connectionString))
{
con.Open();
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT [ApplicationId] " +
"FROM [aspnet_Applications] " +
"WHERE [ApplicationName]=@ApplicationName";
cmd.Parameters.Add(cmd.CreateParameter("@ApplicationName", applicationName));
var applicationId = cmd.ExecuteScalar();
if (applicationId == null)
{
throw new System.Configuration.Provider.ProviderException("Unable to find application id for provided application name: " + applicationName);
}
return(applicationId as string);
}
}
}
private void GetPropertyValuesFromDatabase(string userName, SettingsPropertyValueCollection svc)
{
string[] names = null;
string values = null;
byte[] buf = null;
using (var con = SQLiteUtils.GetConnection(_connectionString))
{
con.Open();
string userId;
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT UserId " +
"FROM [aspnet_Users] " +
"WHERE ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)";
cmd.Parameters.AddRange(new[]
{
cmd.CreateParameter("@ApplicationId", _applicationId),
cmd.CreateParameter("@UserName", userName)
});
userId = cmd.ExecuteScalar() as string;
if (userId == null)
{
return;
}
}
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT PropertyNames, PropertyValuesString, PropertyValuesBinary " +
"FROM [aspnet_Profile]" +
"WHERE UserId = @UserId";
cmd.Parameters.Add(cmd.CreateParameter("@UserId", userId));
using (var reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (reader.Read())
{
names = reader.GetString(0).Split(':');
values = reader.GetString(1);
var 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);
}
}
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "UPDATE [aspnet_Users] " +
"SET LastActivityDate = @CurrentTimeUtc " +
"WHERE UserId = @UserId";
cmd.Parameters.AddRange(new[]
{
cmd.CreateParameter("@CurrentTimeUtc", DateTime.UtcNow),
cmd.CreateParameter("@UserId", userId)
});
cmd.ExecuteNonQuery();
}
}
}
public override void SetPropertyValues(SettingsContext context, SettingsPropertyValueCollection collection)
{
var username = (string)context["UserName"];
var isAuthenticated = (bool)context["IsAuthenticated"];
if (string.IsNullOrEmpty(username) || collection.Count < 1)
{
return;
}
var names = string.Empty;
var values = string.Empty;
byte[] buffer = null;
PrepareDataForSaving(ref names, ref values, ref buffer, true, collection, isAuthenticated);
if (names.Length == 0)
{
return;
}
using (var con = SQLiteUtils.GetConnection(_connectionString))
{
con.Open();
int userCount;
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT Count(UserId) " +
"FROM [aspnet_Users]" +
"WHERE ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)";
cmd.Parameters.AddRange(new[]
{
cmd.CreateParameter("@ApplicationId", _applicationId),
cmd.CreateParameter("@UserName", username)
});
userCount = (int)cmd.ExecuteScalar();
}
string userId;
if (userCount.Equals(0))
{
userId = Guid.NewGuid().ToString();
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "INSERT INTO [aspnet_Users] ( " +
"[ApplicationId], " +
"[UserId], " +
"[UserName], " +
"[LoweredUserName], " +
"[IsAnonymous], " +
"[LastActivityDate] " +
") VALUES (" +
"@ApplicationId, " +
"@UserId, " +
"@UserName, " +
"@LoweredUserName, " +
"@IsAnonymous, " +
"@LastActivityDate)";
cmd.Parameters.AddRange(new[]
{
cmd.CreateParameter("@ApplicationId", _applicationId),
cmd.CreateParameter("@UserId", userId),
cmd.CreateParameter("@UserName", username),
cmd.CreateParameter("@LoweredUserName", username.ToLowerInvariant()),
cmd.CreateParameter("@IsAnonymous", !isAuthenticated),
cmd.CreateParameter("@LastActivityDate", DateTime.UtcNow)
});
cmd.ExecuteNonQuery();
}
}
else if (userCount > 1)
{
throw new Exception(
string.Format("Duplicate user records found for username '{0}' and application '{1}'",
username,
ApplicationName));
}
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT UserId " +
"FROM [aspnet_Users] " +
"WHERE ApplicationId = @ApplicationId AND LoweredUserName = LOWER(@UserName)";
userId = cmd.ExecuteScalar() as string;
}
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "UPDATE [aspnet_Users] " +
"SET LastActivityDate=@CurrentTimeUtc " +
"WHERE UserId = @UserId";
cmd.Parameters.AddRange(new[]
{
cmd.CreateParameter("@CurrentTimeUtc", DateTime.UtcNow),
cmd.CreateParameter("@UserId", userId)
});
}
int profileCount;
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT COUNT(UserId) " +
"FROM [aspnet_Profile]" +
"WHERE UserId = @UserId";
cmd.Parameters.Add(cmd.CreateParameter("@UserId", userId));
profileCount = (int)cmd.ExecuteScalar();
}
using (var cmd = con.CreateCommand())
{
if (profileCount == 0)
{
cmd.CommandText = "INSERT INTO [aspnet_Profile] (" +
"UserId, " +
"PropertyNames, " +
"PropertyValuesString, " +
"PropertyValuesBinary, " +
"LastUpdatedDate" +
") VALUES ( " +
"@UserId, " +
"@PropertyNames, " +
"@PropertyValuesString, " +
"@PropertyValuesBinary, " +
"@CurrentTimeUtc)";
}
else
{
cmd.CommandText = "UPDATE [aspnet_Profile] " +
"SET " +
"PropertyNames = @PropertyNames, " +
"PropertyValuesString = @PropertyValuesString, " +
"PropertyValuesBinary = @PropertyValuesBinary, " +
"LastUpdatedDate = @CurrentTimeUtc " +
"WHERE UserId = @UserId";
}
cmd.Parameters.AddRange(new[]
{
cmd.CreateParameter("@PropertyNames", names),
cmd.CreateParameter("@PropertyValuesString", values),
cmd.CreateParameter("@PropertyValuesBinary", buffer),
cmd.CreateParameter("@CurrentTimeUtc", DateTime.UtcNow),
cmd.CreateParameter("@UserId", userId)
});
cmd.ExecuteNonQuery();
}
}
}
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);
}
