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); }