public static int Profile_GetProperties(DbConnection connection, string applicationName, string username, DateTime currentTimeUtc, out DbDataReader reader) { reader = null; string appId = DerbyApplicationsHelper.GetApplicationId(connection, applicationName); if (appId == null) { return(-1); } string userId = GetUserId(connection, null, appId, username); if (userId == null) { return(-1); } string queryUpdUser = @"UPDATE aspnet_Users SET LastActivityDate = ? WHERE UserId = ?"; OleDbCommand cmdUpdUser = new OleDbCommand(queryUpdUser, (OleDbConnection)connection); AddParameter(cmdUpdUser, "LastActivityDate", currentTimeUtc); AddParameter(cmdUpdUser, "UserId", userId); cmdUpdUser.ExecuteNonQuery(); string querySelect = @"SELECT PropertyNames, PropertyValuesString, PropertyValuesBinary FROM aspnet_Profile WHERE UserId = ?"; OleDbCommand cmdSelect = new OleDbCommand(querySelect, (OleDbConnection)connection); AddParameter(cmdSelect, "UserId", userId); reader = cmdSelect.ExecuteReader(); return(0); }
public static int Profile_GetProfiles(DbConnection connection, string applicationName, int profileAuthOptions, int pageIndex, int pageSize, string userNameToMatch, out DbDataReader reader) { reader = null; string appId = DerbyApplicationsHelper.GetApplicationId(connection, applicationName); if (appId == null) { return(-1); } string querySelect = @"SELECT usr.UserName, usr.IsAnonymous, usr.LastActivityDate, prf.LastUpdatedDate, " + "LENGTH(prf.PropertyNames) + LENGTH(prf.PropertyValuesString) + LENGTH(prf.PropertyValuesBinary)" + "FROM aspnet_Users usr, aspnet_Profile prf WHERE ApplicationId = ? AND usr.UserId = prf.UserId " + GetProfileAuthOptions(profileAuthOptions) + (string.IsNullOrEmpty(userNameToMatch) ? "" : " AND usr.LoweredUserName LIKE ?"); OleDbCommand cmdSelect = new OleDbCommand(querySelect, (OleDbConnection)connection); AddParameter(cmdSelect, "ApplicationId", appId); if (!string.IsNullOrEmpty(userNameToMatch)) { AddParameter(cmdSelect, "LoweredUserName", userNameToMatch.ToLowerInvariant()); } reader = cmdSelect.ExecuteReader(); return(0); }
public static int Profile_DeleteInactiveProfiles(DbConnection connection, string applicationName, int profileAuthOptions, DateTime inactiveSinceDate) { string appId = DerbyApplicationsHelper.GetApplicationId(connection, applicationName); if (appId == null) { return(0); } string queryDelete = @"DELETE FROM aspnet_Profile WHERE UserId IN ( " + "SELECT UserId FROM aspnet_Users WHERE ApplicationId = ? AND LastActivityDate <= ? " + GetProfileAuthOptions(profileAuthOptions) + ")"; OleDbCommand cmdDelete = new OleDbCommand(queryDelete, (OleDbConnection)connection); AddParameter(cmdDelete, "ApplicationId", appId); AddParameter(cmdDelete, "LastActivityDate", inactiveSinceDate); return(cmdDelete.ExecuteNonQuery()); }
public static int Profile_DeleteProfiles(DbConnection connection, string applicationName, string [] userNames) { int deletedUsers = 0; string appId = DerbyApplicationsHelper.GetApplicationId(connection, applicationName); if (appId == null) { return(0); } OleDbTransaction trans = (OleDbTransaction)connection.BeginTransaction(); try { foreach (string username in userNames) { string userId = GetUserId(connection, trans, appId, username); if (userId == null) { continue; } string queryDelete = "DELETE FROM aspnet_Profile WHERE UserId = ?"; OleDbCommand cmdDelete = new OleDbCommand(queryDelete, (OleDbConnection)connection); cmdDelete.Transaction = trans; AddParameter(cmdDelete, "UserId", userId); cmdDelete.Transaction = trans; deletedUsers += cmdDelete.ExecuteNonQuery(); } trans.Commit(); return(deletedUsers); } catch (Exception e) { trans.Rollback(); throw e; } }
public static int Profile_GetNumberOfInactiveProfiles(DbConnection connection, string applicationName, int profileAuthOptions, DateTime inactiveSinceDate) { string appId = DerbyApplicationsHelper.GetApplicationId(connection, applicationName); if (appId == null) { return(0); } string querySelect = @"SELECT COUNT(*) FROM aspnet_Users usr, aspnet_Profile prf WHERE ApplicationId = ? " + "AND usr.UserId = prf.UserId AND LastActivityDate <= ? " + GetProfileAuthOptions(profileAuthOptions); OleDbCommand cmdSelect = new OleDbCommand(querySelect, (OleDbConnection)connection); AddParameter(cmdSelect, "ApplicationId", appId); AddParameter(cmdSelect, "LastActivityDate", inactiveSinceDate); using (OleDbDataReader reader = cmdSelect.ExecuteReader()) { if (reader.Read()) { return(reader.GetInt32(0)); } } return(0); }
public static int Profile_SetProperties(DbConnection connection, string applicationName, string propertyNames, string propertyValuesString, byte [] propertyValuesBinary, string username, bool isUserAnonymous, DateTime currentTimeUtc) { string appId = DerbyApplicationsHelper.GetApplicationId(connection, applicationName); if (appId == null) { object newAppId = DerbyApplicationsHelper.Applications_CreateApplication(connection, applicationName); appId = newAppId as string; if (appId == null) { return(-1); } } OleDbTransaction trans = (OleDbTransaction)connection.BeginTransaction(); try { string userId = GetUserId(connection, trans, appId, username); if (userId == null) { object newUserId = null; DerbyMembershipHelper.Users_CreateUser(connection, trans, appId, username, true, currentTimeUtc, ref newUserId); userId = newUserId as string; if (userId == null) { trans.Rollback(); return(-1); } } string queryUpdUser = @"UPDATE aspnet_Users SET LastActivityDate=? WHERE UserId = ?"; OleDbCommand cmdUpdUser = new OleDbCommand(queryUpdUser, (OleDbConnection)connection); cmdUpdUser.Transaction = trans; AddParameter(cmdUpdUser, "LastActivityDate", currentTimeUtc); AddParameter(cmdUpdUser, "UserId", userId); cmdUpdUser.ExecuteNonQuery(); string querySelect = @"SELECT * FROM aspnet_Profile WHERE UserId = ?"; OleDbCommand cmdSelect = new OleDbCommand(querySelect, (OleDbConnection)connection); cmdSelect.Transaction = trans; AddParameter(cmdSelect, "UserId", userId); bool userHasRecords = false; using (OleDbDataReader reader = cmdSelect.ExecuteReader()) { userHasRecords = reader.HasRows; } if (userHasRecords) { string queryUpdate = @"UPDATE aspnet_Profile SET PropertyNames = ?, PropertyValuesString = ?, " + "PropertyValuesBinary = ?, LastUpdatedDate = ? WHERE UserId = ?"; OleDbCommand cmdUpdate = new OleDbCommand(queryUpdate, (OleDbConnection)connection); cmdUpdate.Transaction = trans; AddParameter(cmdUpdate, "PropertyNames", propertyNames); AddParameter(cmdUpdate, "PropertyValuesString", propertyValuesString); AddParameter(cmdUpdate, "PropertyValuesBinary", propertyValuesBinary); AddParameter(cmdUpdate, "LastUpdatedDate", currentTimeUtc); AddParameter(cmdUpdate, "UserId", userId); cmdUpdate.ExecuteNonQuery(); } else { string queryInsert = @"INSERT INTO aspnet_Profile(UserId, PropertyNames, PropertyValuesString, " + "PropertyValuesBinary, LastUpdatedDate) VALUES (?, ?, ?, ?, ?)"; OleDbCommand cmdInsert = new OleDbCommand(queryInsert, (OleDbConnection)connection); cmdInsert.Transaction = trans; AddParameter(cmdInsert, "UserId", userId); AddParameter(cmdInsert, "PropertyNames", propertyNames); AddParameter(cmdInsert, "PropertyValuesString", propertyValuesString); AddParameter(cmdInsert, "PropertyValuesBinary", propertyValuesBinary); AddParameter(cmdInsert, "LastUpdatedDate", currentTimeUtc); cmdInsert.ExecuteNonQuery(); } trans.Commit(); } catch (Exception e) { trans.Rollback(); throw e; } return(0); }