/// <summary>
/// Updates all fields for a user in the database.
/// This will create the user and roles if they do not already exist.
/// </summary>
/// <param name="user">the user to save</param>
/// <returns></returns>
public bool UpdateUser(WebPortalUser user)
{
string sql = "UPDATE appuser SET ";
sql += "UserName = "******"'" + dbEncode(user.UserName) + "'" + ", ";
sql += "Password = "******"'" + dbEncode(user.Password) + "'" + ", ";
sql += "FullName = " + "'" + dbEncode(user.FullName) + "'" + ", ";
sql += "EmailAddress = " + "'" + dbEncode(user.EmailAddress) + "'" + ", ";
sql += "LastLoginDateTime = " + DBDialect.currentDateTime + " ";
sql += " WHERE AppUserId = " + user.uid.ToString();
int numUpdated = this.RunUpdateQuery(sql);
if (numUpdated >= 0)
{
// -- user has been updated, let's update role associations
RemoveUserFromAllRoles(user);
bool b = AddRolesToExistingUser(user, user.userRoles);
if (b)
{
RemoveUserFromAllPermissions(user);
return(AddPermissionsToExistingUser(user, user.Permissions));
}
}
return(false);
} // UpdateUser
} // getWebPortalUsersFromStandardDataSet
/// <summary>
/// sets a user's Extended Information for a given key to the val
/// you must have a valid user id before setting extended infos!
/// </summary>
/// <param name="user">the user to set the info for</param>
/// <param name="key">the data key to set the data for (case insensitive)</param>
/// <param name="val">the value to associate with the user's key</param>
public bool setExtendedInfo(WebPortalUser user, string key, string val)
{
val = this.dbEncode(val);
if (user.uid > -1)
{
key = key.ToLower();
int eid = ExtendedInfoExists(user, key);
if (eid != -1)
{ // update
string sql = "UPDATE appuserextendedinfo ";
sql = sql + "SET appuserextendedinfo.val = '" + dbEncode(val) + "' ";
sql = sql + "WHERE appuserextendedinfo.UserId = " + user.uid.ToString() + " ";
sql = sql + "AND appuserextendedinfo.key = '" + dbEncode(key) + "';";
int numAffected = this.RunUpdateQuery(sql);
return(numAffected > 0);
}
else
{ // insert
string sql = "INSERT INTO appuserextendedinfo ";
sql = sql + "(appuserextendedinfo.UserId, appuserextendedinfo.Key, appuserextendedinfo.Value) ";
sql = sql + " VALUES (";
sql = sql + user.uid.ToString() + " , ";
sql = sql + "'" + dbEncode(key) + "', ";
sql = sql + "'" + dbEncode(val) + "') ";
this.RunInsertQuery(sql);
return(true);
}
}
return(false);
} // setExtendedInfo
/// <summary>
/// returns NULL if no user is logged in.
/// </summary>
/// <returns></returns>
public static WebPortalUser GetCurrentWebPortalUser(PortalApplication portalApp)
{
try
{
// -- we cache the currentWebPortal user so that we don't go to the database
string cacheKey = "WebPortalUser.currentWebPortalUser";
if (PerRequestCache.CacheContains(cacheKey))
{
return(PerRequestCache.GetFromCache(cacheKey, null) as WebPortalUser);
}
if (System.Web.HttpContext.Current != null && System.Web.HttpContext.Current.User != null &&
System.Web.HttpContext.Current.User.Identity != null && System.Web.HttpContext.Current.User.Identity.IsAuthenticated)
{
WebPortalUser u = WebPortalUser.FetchUser(System.Web.HttpContext.Current.User.Identity.Name, portalApp);
PerRequestCache.AddToCache(cacheKey, u);
return(u);
}
else
{
return(null);
}
}
catch
{ }
return(null);
}
} // checkLogin
public static bool DeleteUser(WebPortalUser user)
{
if (user.uid > -1)
{
WebPortalUserDB db = new WebPortalUserDB();
return(db.DeleteUser(user));
}
return(false);
}
/// <summary>
/// Checks to see if the username and password match a user in the system.
/// Returns true if the username exists, and the password matches, otherwise returns false.
/// </summary>
/// <param name="username"></param>
/// <param name="password"></param>
/// <returns></returns>
public static bool CheckLogin(string username, string password, PortalApplication portalApp)
{
WebPortalUser user = (new WebPortalUserDB()).FetchWebPortalUser(username, portalApp);
if (user != null && String.Compare(user.Password, password) == 0)
{
return(true);
}
return(false);
} // checkLogin
/// <summary>
/// removes all the roles for the given user
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public bool RemoveUserFromAllRoles(WebPortalUser user)
{
if (user.uid > -1)
{
string sql = "DELETE from appuserroles where appuserid = " + user.uid.ToString() + "; ";
int numAffected = RunUpdateQuery(sql);
return(numAffected >= 0);
}
return(false);
}
/// <summary>
/// Delete a user with a given user ID from the system
/// </summary>
/// <param name="userID"></param>
/// <returns>true if deleted successfully, false if not</returns>
public bool DeleteUser(WebPortalUser userToDelete)
{
if (userToDelete.uid > -1)
{
string sql = "UPDATE appuser set DELETED = " + DBDialect.currentDateTime + " WHERE appuserid = " + userToDelete.uid.ToString() + " ";
int numAffected = RunUpdateQuery(sql);
return(numAffected > 0);
}
return(false);
} // DeleteUser
public static bool UsernameExists(string username, PortalApplication portalApp)
{
WebPortalUser user = (new WebPortalUserDB()).FetchWebPortalUser(username, portalApp);
if (user == null || user.uid < 0)
{
return(false);
}
return(true);
}
} // UpdateUser
public bool SetLastLoginInDatabaseToNow(WebPortalUser user)
{
string sql = "UPDATE appuser SET ";
sql += "LastLoginDateTime = " + DBDialect.currentDateTime + " ";
sql += " WHERE AppUserId = " + user.uid.ToString();
int numUpdated = this.RunUpdateQuery(sql);
if (numUpdated >= 0)
{
return(true);
}
return(false);
}
} // DeleteUser
public bool InsertUser(WebPortalUser user)
{
string sql = "INSERT INTO appuser (UserName, Password, FullName, EmailAddress, LastLoginDateTime) VALUES ('" + dbEncode(user.UserName) + "', '" + dbEncode(user.Password) + "', '" + dbEncode(user.FullName) + "', '" + dbEncode(user.EmailAddress) + "', " + DBDialect.currentDateTime + ") ";
int newId = this.RunInsertQuery(sql);
if (newId >= 0)
{
user.uid = newId;
bool b = AddRolesToExistingUser(user, user.userRoles);
if (b)
{
return(AddPermissionsToExistingUser(user, user.Permissions));
}
}
return(false);
}
public bool AddPermissionsToExistingUser(WebPortalUser user, PortalApplicationPermission[] permissions)
{
if (permissions.Length > 0 && user.uid > -1)
{
StringBuilder sql = new StringBuilder();
sql.Append("INSERT INTO appuserpermissions ");
sql.Append("(AppUserId, PermissionsId)");
sql.Append(" VALUES ");
foreach (PortalApplicationPermission perm in permissions)
{
sql.Append(" ( ");
if (user.uid < 0)
{
sql.Append("NULL, ");
}
else
{
sql.Append(user.uid.ToString() + ", ");
}
if (perm.ID < 0)
{
sql.Append("NULL ");
}
else
{
sql.Append(perm.ID.ToString() + " ");
}
sql.Append(" ),");
} // foreach
// remove trailing comma
string s = sql.ToString().Substring(0, sql.ToString().Length - 1);
int numInserted = this.RunUpdateQuery(s); // do not use RunInsertQuery
if (numInserted == permissions.Length)
{
return(true);
}
}
else if (permissions.Length == 0)
{
return(true);
}
return(false);
}
} // AddRole
public bool AddRolesToExistingUser(WebPortalUser user, WebPortalUserRole[] roles)
{
if (roles.Length > 0 && user.uid > -1)
{
StringBuilder sql = new StringBuilder();
sql.Append("INSERT INTO appuserroles ");
sql.Append("(AppUserId, RoleId)");
sql.Append(" VALUES ");
foreach (WebPortalUserRole role in roles)
{
sql.Append(" ( ");
if (user.uid < 0)
{
sql.Append("NULL, ");
}
else
{
sql.Append(user.uid.ToString() + ", ");
}
if (role.RoleID < 0)
{
sql.Append("NULL, ");
}
else
{
sql.Append(role.RoleID.ToString() + " ");
}
sql.Append(" ),");
} // foreach
// remove trailing comma
string s = sql.ToString().Substring(0, sql.ToString().Length - 1);
int numInserted = this.RunUpdateQuery(s); // do not use RunInsertQuery
if (numInserted == roles.Length)
{
return(true);
}
}
else if (roles.Length == 0)
{
return(true);
}
return(false);
}
public string getExtendedInfo(WebPortalUser user, string key, string notFoundValue)
{
if (user.uid > -1)
{
key = key.ToLower();
string sql = "SELECT value from appuserextendedinfo a WHERE ";
sql = sql + " a.userid = " + user.uid.ToString();
sql = sql + " AND a.key = '" + key + "' ";
sql = sql + " AND " + DBDialect.isNull("a.Deleted") + " ";
DataSet ds = this.RunSelectQuery(sql);
if (ds.Tables[0] != null & ds.Tables[0].Rows.Count == 1)
{
return(ds.Tables[0].Rows[0]["value"].ToString());
}
}
return(notFoundValue);
}
} // removeExtendedInfo
private int ExtendedInfoExists(WebPortalUser user, string key)
{
if (user.uid > -1)
{
key = key.ToLower();
string sql = "SELECT ExtendedInfoId from appuserextendedinfo a where ";
sql = sql + " a.userid = " + user.uid.ToString();
sql = sql + " AND a.key = '" + dbEncode(key) + "' ";
sql = sql + " AND " + DBDialect.isNull("a.Deleted") + " ";
DataSet ds = this.RunSelectQuery(sql);
if (ds.Tables[0] != null & ds.Tables[0].Rows.Count == 1)
{
return(Convert.ToInt32(ds.Tables[0].Rows[0]["ExtendedInfoId"].ToString()));
}
}
return(-1);
}
public NameValueCollection getAllExtendedInfo(WebPortalUser user)
{
NameValueCollection ret = new NameValueCollection();
string sql = "select a.key, a.value from appuserextendedinfo a WHERE ";
sql += " a.userid = " + user.uid.ToString();
sql += " AND " + DBDialect.isNull("a.Deleted") + " ";
DataSet ds = this.RunSelectQuery(sql);
if (ds.Tables[0] != null & ds.Tables[0].Rows.Count >= 1)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
ret.Add((dr["key"].ToString()), (dr["value"].ToString()));
} // foreach row
}
return(ret);
}
} // setExtendedInfo
public bool removeExtendedInfo(WebPortalUser user, string key)
{
key = key.Trim().ToLower();
int keyId = ExtendedInfoExists(user, key);
if (user.uid > -1 && key != "" && keyId > -1)
{
string sql = "UPDATE appuserextendedinfo a set a.deleted = " + DBDialect.currentDateTime + " ";
sql += "WHERE a.ExtendedInfoId = " + keyId.ToString() + "; ";
try
{
int numAffected = RunUpdateQuery(sql);
return(numAffected > 0);
}
catch
{
return(false);
}
// return true;
}
return(false);
} // removeExtendedInfo
} // getAllWebPortalUsers
/// <summary>
/// returns an ArrayList of WebPortalUser objects. The passed in DataSet MUST have been created
/// using getStandardUserSQL()
/// </summary>
/// <param name="ds">The DataSet returned from the query</param>
/// <returns>an ArrayList of WebPortalUser objects</returns>
private WebPortalUser[] getWebPortalUsersFromStandardDataSet(DataSet ds)
{
Dictionary <string, WebPortalUser> storage = new Dictionary <string, WebPortalUser>();
if (hasRows(ds))
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
string key = dr["AppUserId"].ToString();
// -- get the user
WebPortalUser user;
if (!storage.ContainsKey(key))
{
user = new WebPortalUser(Convert.ToInt32(dr["AppUserId"]), dr["username"].ToString(), dr["password"].ToString());
user.FullName = dr["FullName"].ToString();
user.EmailAddress = dr["EmailAddress"].ToString();
user.LastLogin = getPossiblyNullValue(dr, "LastLoginDateTime", DateTime.MinValue);
}
else
{
user = storage[key];
}
// -- roles
if (dr["roleid"] != System.DBNull.Value)
{
WebPortalUserRole role = new WebPortalUserRole(Convert.ToInt32(dr["roleid"]), dr["RoleName"].ToString(), dr["RoleDesc"].ToString());
user.AddUserRole(role);
}
// x.`key` as exKey, x.`value` as exVal,
// -- Permissions
// p.PermissionsId, p.ApplicationName, p.Action, p.Description
if (dr["PermissionsId"] != System.DBNull.Value)
{
PortalApplicationPermission p = new PortalApplicationPermission();
p.ID = Convert.ToInt32(dr["PermissionsId"]);
p.ApplicationName = dr["ApplicationName"].ToString().Trim();
p.Action = dr["Action"].ToString().Trim();
p.Description = dr["Description"].ToString();
user.AddUserPermission(p);
}
if (!storage.ContainsKey(key))
{
storage.Add(key, user);
}
} // foreach row
// ---- copy the storage NameValueCollection to the ArrayList
} // if there is data
List <WebPortalUser> ret = new List <WebPortalUser>();
foreach (string k in storage.Keys)
{
ret.Add(storage[k]);
}
return(ret.ToArray());
} // getWebPortalUsersFromStandardDataSet
