User Management
12 Jun 2009 @ 03:04PM
Updated: 26 Jan 2010 @ 03:05PM
In our previous tutorials, we've created a very large portion of the code needed to run this site. Next we're going to do the user management section. Once we've got that, we can do some more thorough testing of our blogging code to make sure it actually works the way it should. Then we can move into the final section: doing the comments. So let's begin by making some changes to our menu.
if (loggedIn && accessLevel == 255)
{
content += "<li><a href='default.aspx?option=userManagement'>User Management</a></li>";
}
This gets added to the
layout() method where we define our menu. I've changed the link to read User Management instead of Create Account. After all, this link will be for more than just creating accounts. We'll also be able to edit and delete users. I've then added a get variable for userManagement.
So now we need to add that to our
Page_Load() method.
int userUserid = 0;
...
case "userManagement":
case "editUser":
if (accessLevel < 255)
{
break;
}
try
{
userUserid = Convert.ToInt16(getVariable("userid", var.GET));
}
catch { }
showblogs = false;
content += userManagement(userUserid);
break;
So this is inside the GET case statement in our
Page_Load() method. I declared the userUserid variables outside of the case statement for scope purposes. There's nothing new here. We're checking to make sure you're a site admin, then run a method called
userManagement(). We're also checking for a
userid variable to handle edit and delete requests later on.
Now for the
userManagement() method.
private string userManagement(int userUserid)
{
string content = null;
string select = null;
select = @"SELECT userid, username, displayName, email, showEmail, createDate, accessLevel,
(SELECT count(*) FROM blogs WHERE userid = a.userid) as numBlogs,
(SELECT count(*) FROM blogs WHERE userid = a.userid and visible=1) as numVisibleBlogs,
(SELECT count(*) FROM comments WHERE userid = a.userid) as numComments,
(SELECT count(*) FROM comments WHERE userid = a.userid and visible=1) as numVisibleComments
FROM users a
ORDER BY username ASC";
SqlDataReader dr = query(select);
content += "<table class='userManagement'>" +
"<thead>" +
"<tr>" +
"<th>UserID</th>" +
"<th>Username</th>" +
"<th>DisplayName</th>" +
"<th>Email</th>" +
"<th>ShowEmail</th>" +
"<th>Created</th>" +
"<th>Access</th>" +
"<th>Blogs*</th>" +
"<th>Comments*</th>" +
"<th></th>" +
"</tr>" +
"</thead>" +
"<tbody>";
string lineClass = null;
while (dr.Read())
{
string userShowEmail = (Convert.ToInt16(dr["showEmail"]) == 1) ? "Yes" : "No";
string userAccess = null;
if (Convert.ToInt16(dr["accessLevel"]) == 255)
{
userAccess = "Admin";
}
else if (Convert.ToInt16(dr["accessLevel"]) >= 100)
{
userAccess = "Blogger";
}
else
{
userAccess = "User";
}
lineClass = (lineClass == "even") ? "odd" : "even";
content += "<tr class='" + lineClass + "'>" +
"<td>" + dr["userid"] + "</td>" +
"<td>" + dr["username"] + "</td>" +
"<td>" + dr["displayName"] + "</td>" +
"<td>" + dr["email"] + "</td>" +
"<td>" + userShowEmail + "</td>" +
"<td>" + dr["createDate"] + "</td>" +
"<td>" + userAccess + "</td>" +
"<td>" + dr["numBlogs"] + " (" + dr["numVisibleBlogs"] + ")</td>" +
"<td>" + dr["numComments"] + " (" + dr["numVisibleComments"] + ")</td>" +
"<td>" +
"<a href='default.aspx?option=editUser&userid=" + dr["userid"] + "'>Edit</a> / " +
"<a href='default.aspx?option=deleteUser&userid=" + dr["userid"] + "'>Delete</a>" +
"</td>" +
"</tr>";
}
content += @"</tbody>
<tfoot>
<tr>
<td colspan=10>* total (visible)</td>
</tr>
</tfoot>
</table>";
dr.Dispose();
return content;
}
So we begin by just querying the database for all the user stats we could care about. That's all their settings from the users table, plus some basic information as to how many comments or blogs they've written. We then build a table and loop through the database results until we output everything. If you notice, we turn the
showEmail into a more human-readable format... instead of 1 and 0 we have Yes and No. Similarly, we turn the
accessLevel into human readable format... either Admin, Blogger or User depending on where the number falls. We also set up our links to edit or delete the user.
Next comes the editing code. I'm just making some changes to the user loop. This is within the
userManagement() method where we're looping through all our users so we can display them.
if (userUserid == Convert.ToInt16(dr["userid"]))
{
string showEmailSelect = "<select name='showEmail'>" +
"<option value=0";
showEmailSelect += (userShowEmail == "No") ? " selected" : "";
showEmailSelect += ">No</option>" +
"<option value=1";
showEmailSelect += (userShowEmail == "Yes") ? " selected" : "";
showEmailSelect += ">Yes</option>" +
"</select>";
string accessLevelSelect = "<select name='accessLevel'>" +
"<option value='255'";
accessLevelSelect += (userAccess == "Admin") ? " selected" : "";
accessLevelSelect += ">Admin</option>" +
"<option value='100'";
accessLevelSelect += (userAccess == "Blogger") ? " selected" : "";
accessLevelSelect += ">Blogger</option>" +
"<option value='0'";
accessLevelSelect += (userAccess == "User") ? " selected" : "";
accessLevelSelect += ">User</option>" +
"</select>";
content += "<form method='POST' action='default.aspx'>" +
"<tr class='" + lineClass + "'>" +
"<td>" + dr["userid"] + "</td>" +
"<td><input name='username' value='" + dr["username"] + "'></td>" +
"<td><input name='displayName' value='" + dr["displayName"] + "'></td>" +
"<td><input name='email' value='" + dr["email"] + "'></td>" +
"<td>" + showEmailSelect + "</td>" +
"<td>" + dr["createDate"] + "</td>" +
"<td>" + accessLevelSelect + "</td>" +
"<td>" + dr["numBlogs"] + " (" + dr["numVisibleBlogs"] + ")</td>" +
"<td>" + dr["numComments"] + " (" + dr["numVisibleComments"] + ")</td>" +
"<td>" +
"<input type='hidden' name='userid' value='" + userUserid + "'>" +
"<input type='submit' name='option' value='Save User'></form>" +
"<form action='default.aspx' method='GET'>" +
"<input type='hidden' name='option' value='userManagement'>" +
"<input type='submit' value='Cancel'></form>" +
"</td>" +
"</tr>";
}
else
With this code in place, we continue to output the user management screen as normal unless there's a passed
userid matching one of the userids in the list. We then output a form instead. From here we need to add another option to our
Page_Load() method to catch an option of
Save User.
case "Save User":
if (accessLevel < 255)
{
break;
}
showblogs = false;
content += saveUser();
content += userManagement(0);
break;
This goes into the POST case statement since we're posting the form. As you can see I just execute a method called
saveUser(), then reexecute the
userManagement() method. I pass a 0 into
userManagement() because I know I'm just interested in getting a list of users without going into edit mode on any of them.
So next we take a look at the
saveUser() method.
private string saveUser()
{
string content = null;
//get variables
string userUsername = sanitize(getVariable("username", var.POST), clean.DB);
string userDisplayName = sanitize(getVariable("displayName", var.POST), clean.DB);
string userEmail = sanitize(getVariable("email", var.POST), clean.DB);
int userShowEmail = (getVariable("showEmail", var.POST) == "1")? 1 : 2;
int userAccessLevel = Convert.ToInt16(getVariable("accessLevel", var.POST));
int userUserid = Convert.ToInt16(getVariable("userid", var.POST));
//update user
string select = "UPDATE users " +
"SET username='" + userUsername + "'," +
" displayName='" + userDisplayName + "', " +
" email='" + userEmail + "', " +
" showEmail='" + userShowEmail + "', " +
" accessLevel='" + userAccessLevel + "' " +
"WHERE userid=" + userUserid;
SqlDataReader dr = query(select);
content += "<div class='error'>" + dr.RecordsAffected + " Record(s) Affected</div>";
dr.Dispose();
return content;
}
There's nothing too special going on here. I get all my POST variables, then update the SQL user using that info. Just for the heck of it I showed the
RecordAffected property of the SqlDataReader object. This shows how many rows were affected by the last SQL statement. In this case, it should always be 1 so it's probably a bit redundant.
While we're on a roll, let's create the option to delete a user.
case "deleteUser":
if (accessLevel < 255)
{
break;
}
try
{
userUserid = Convert.ToInt16(getVariable("userid", var.GET));
}
catch { }
showblogs = false;
content += deleteUser(userUserid);
content += userManagement(0);
break;
The above goes into our
Page_Load() method within the GET select statement.
And now for the
deleteUser() method.
private string deleteUser(int userUserid)
{
string content = null;
string select = "DELETE FROM users WHERE userid=" + userUserid;
SqlDataReader dr = query(select);
content += "<div class='error'>" + dr.RecordsAffected + " Record(s) Affected</div>";
dr.Dispose();
return content;
}
I'm keeping this section fairly simple for brevity's sake. I just take the
userid we passed in and delete the row. Bam, done. However, this is probably not the best thing we could do. What if the user we delete has a blog entry? Once we delete his user account, the select statement we use to retrieve blogs will no longer pick that blog up. It's still in the database, but because we're doing a join against the users table it's no longer going to be returned. We'd probably be better off with a deleted column so that we can mark a user as 'deleted', but not remove their user. Regardless, I'll leave this as is since this tutorial is gigantic enough.
Next we'll work on actually adding new users. I'll hold off on sharing the source code until we get the user management section completed.
