1、关于使用的几张表的说明
2、关于PowerDesigher :上一篇介绍了初次在数据库生成表的过程,实际上还可以实现更新和逆向操作(根据数据库中的表在PD中生成表模型)。
3、模拟数据
(1)表Module
(2)表Permissions
(3)表ModulePermissions
4、角色分页显示和删除
这里实现功能的代码基本都是最初级的代码,后面另一个版本会在架构、UI、代码等方面进行较大的改进;另外这里的代码纯粹是演示,不会考虑注入、异常等方面的处理。
(1)分页显示数据直接使用GridView了
(2)在列表页面可以全选本页数据一次性删除,全选/全取消使用JQuery实现,选中时将值(这里是角色编码)保存在一个隐藏域中,隐藏域中的值的格式:'值1','值2',...(数据库中的RoleCode列的数据类型是nvarchar);取消选中时将隐藏域中的值清空;单行CheckBox选中/取消时,将当前行的值追加到隐藏域的值或从隐藏域的值中移除当前行的值 。删除时先判断隐藏域的值是否为空,如果为空提示”选中数据“,如果不为空提示”确定删除吗?“,确认删除后,根据隐藏域中的值构造delete语句(使用in子句)将数据删除。
(3)代码
Index.aspx
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>角色管理</title>
<script type="text/javascript" src="../Scripts/jquery-1.4.1.min.js"></script>
<script type="text/javascript">
//全选/取消CheckBox
function CheckAll(cbAllId) {
var keys = $("#txtKeys").val("").val();
//遍历页面中所有CheckBox,
//根据GridView头中CheckBox的checked值设置所有CheckBox的checked值,
//同时,如果Header中CheckBox为选中则获取所有其他CheckBox的值(角色编码),
//构造:'值','值',...格式的字符串,方便in查询,
//如果Header中CheckBox的Checked值为false则清空txtKeys中的值
$(":checkbox").each(function () {
//设置当前遍历到的CheckBox的选中状态跟“全选”CheckBox一样
$(this).attr("checked", $("#cbAll").attr("checked"));
//如果当前CheckBox选中,将值追加到隐藏域的value
var cbValue = "'" + $(this).val() + "',";
if ($("#cbAll").attr("checked") && $(this).attr("id") != "cbAll") {
keys += cbValue;
}
});
$("#txtKeys").val(keys);
}
//单行选择
function CheckRow(cb) {
var keys = $("#txtKeys").val();
var cbValue = "'" + $(cb).val() + "',";
//移除
if (!$(cb).attr("checked")) {
keys = keys.replace(cbValue, "");
} else if (keys.indexOf(cbValue) == -1) {//追加
keys += cbValue;
}
$("#txtKeys").val(keys);
}
//删除时检查是否选中任何记录,如果是让用户确认删除操作
function ConfirmDel() {
if ($("#txtKeys").val().length == 0) {
alert("请先选择要删除的数据");
return false;
} else {
return confirm("确定要删除选中的数据吗?");
}
}
</script>
</head>
<body>
<form id="form1" runat="server">
请输入角色名称:<asp:TextBox ID="txtKeyword" runat="server" Width="301px"></asp:TextBox>
<asp:Button ID="btnQuery" runat="server" Text="查询" onclick="btnQuery_Click" />
<!--存储选中项的值列表,以“,”间隔,删除时直接取值构造SQL-->
<asp:HiddenField ID="txtKeys" runat="server" EnableViewState="false" />
<div>
<asp:GridView ID="gvRole" runat="server" AutoGenerateColumns="False" EnableModelValidation="True"
Width="100%" AllowPaging="True" DataKeyNames="RoleCode" OnPageIndexChanging="gvRole_PageIndexChanging"
OnRowDataBound="gvRole_RowDataBound" PageSize="4" >
<Columns>
<asp:TemplateField>
<HeaderTemplate>
<input id="cbAll" type="checkbox" onclick="CheckAll();" />
</HeaderTemplate>
<ItemTemplate>
<input id="cbRow" value='<%#Eval("RoleCode") %>' type="checkbox" onclick="CheckRow(this);" />
</ItemTemplate>
<ItemStyle Width="30px" />
</asp:TemplateField>
<asp:BoundField HeaderText="序号" HeaderStyle-Width="50px" ItemStyle-Width="50px">
<HeaderStyle Width="50px"></HeaderStyle>
<ItemStyle Width="50px"></ItemStyle>
</asp:BoundField>
<asp:BoundField DataField="RoleName" HeaderText="角色名称" />
<asp:BoundField DataField="RoleCode" HeaderText="角色编码" />
<asp:HyperLinkField Text="编辑" HeaderStyle-Width="50px" ItemStyle-Width="50px" DataNavigateUrlFields="RoleCode,RoleName"
DataNavigateUrlFormatString="Edit.aspx?RoleCode={0}&RoleName={1}">
<HeaderStyle Width="50px"></HeaderStyle>
<ItemStyle Width="50px"></ItemStyle>
</asp:HyperLinkField>
</Columns>
<EmptyDataTemplate>
没有数据。
</EmptyDataTemplate>
<PagerSettings Position="TopAndBottom" />
</asp:GridView>
</div>
<asp:HyperLink ID="hlNew" runat="server" NavigateUrl="~/Role/Edit.aspx">新增</asp:HyperLink> |
<asp:LinkButton ID="lbDelete" runat="server"
OnClientClick="return ConfirmDel();" onclick="lbDelete_Click">删除</asp:LinkButton>
</form>
</body>
</html>
说明:
$("#txtKeys") :JQuery得到ID为txtKeys的元素
$("#txtKeys").val() ;:获得ID为txtKeys的元素的值
$("#txtKeys").val("");:设置ID为txtKeys的元素的值,这里是空字符串
$(":checkbox").each(function () { });:遍历当前文档中CheckBox类型的元素,并在function中进行处理
$(this).attr("checked");:获得当前遍历到的CheckBox的checked属性的值
$(this).attr("checked", $("#cbAll").attr("checked"));:设置当前CheckBox的选中状态跟“全选”CheckBox一样,实际上就是通过这种方式给当前CheckBox的checked属性
注意所有CheckBox都有客户端onclick事件,触发时会调用不同JavaScript函数执行
用到的Web服务器控件:TextBox 、Button、HiddenField、GridView、HyperLink、LinkButton
Index.aspx.cs
using System.Web.UI.WebControls;
using System.Data;
public partial class Role_Index : System.Web.UI.Page
{
private void BindRoles()
{
string keyWord = this.txtKeyword.Text.Trim();
string querySql =
string.Format("select * from Roles where RoleVisible=1 and RoleName Like '%{0}%'", keyWord);
SqlHelper dbHelper = new SqlHelper();
DataTable dtRole = new DataTable();
dbHelper.FillDataTable(querySql, dtRole);
gvRole.DataSource = dtRole;
gvRole.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindRoles();
}
}
//翻页
protected void gvRole_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gvRole.PageIndex = e.NewPageIndex;
BindRoles();
}
protected void gvRole_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
//行编号
e.Row.Cells[1].Text =
Convert.ToString(gvRole.PageSize * gvRole.PageIndex + e.Row.RowIndex + 1);
}
}
//查询
protected void btnQuery_Click(object sender, EventArgs e)
{
BindRoles();
}
//删除
protected void lbDelete_Click(object sender, EventArgs e)
{
string keys = txtKeys.Value.Trim();
string deleteSql = string.Format("delete from Roles where RoleCode in ({0})",
keys.TrimEnd(','));
SqlHelper dbHelper = new SqlHelper();
if (dbHelper.ExecuteNonQueryWithTran(deleteSql))
{
BindRoles();
txtKeys.Value = "";
}
else
{
ClientScript.RegisterStartupScript(this.GetType(),"Infomation", "alert('删除失败!');",true);
}
}
}
说明:
SqlHelper:是数据访问类,包含一些方法提供对数据库的访问和操作
ExecuteNonQueryWithTran:调用SqlCommand的ExecuteNonQuery方法执行insert、update、delete语句,添加了事务处理
5、角色新增和修改
(1)新建角色
这儿的关键是绑定权限、全选或全取消及获取选中项的值、构造insert语句,先看一下页面运行后的结果
(1.1)绑定权限
使用两个GridView嵌套,最外面的(gvParent)显示顶级模块名称;嵌套在内的(gvChild)在gvParent的RowDataBound中进行数据绑定以显示二级模块名称(根据gvParent当前行的模块编码Moudle表中查询数据; 在gvChild的RowDataBound中绑定权限复选框列表,数据根据gvChild当前行的模块编码从下面视图(V_ModulePermissions)中查询,这里的复选框用的是<input type="checkbox" />,首先跟权限的数量构造CheckBox的html代码字符串,然后用Label显示出来,由于Label在客户端生成的代码中将变为所有这些CheckBox的父元素,所以也方便将来实现单模块的全选或者全取消,具体内容看后面代码。
视图V_ModulePermissions:
SQL:
FROM dbo.ModulePermissions INNER JOIN
dbo.Permissions ON dbo.ModulePermissions.PermissionCode = dbo.Permissions.PermissionCode INNER JOIN
dbo.Module ON dbo.ModulePermissions.ModuleCode = dbo.Module.ModuleCode
WHERE (dbo.Module.ModuleVisible = 1) AND (dbo.Permissions.PermissionVisible = 1)
(1.2)全选或全取消及获取选中项的值
每一个CheckBox的Click事件触发时都会调用一个JavaScript函数执行,这些函数不仅是完成选中或者取消选中,还要完成获得并追加或移除相应的CheckBox的值,最终构造的字符串是这种格式:,1,,2,,3,,4,...,并存储在隐藏域中。代码后面附上 。
(1.3)构造insert语句
获取隐藏域的值进行字符串替换和拆分,遍历拆分出来的数据,构造insert语句,最后执行。
(2)修改角色
关键是设置当前角色已有权限项选中,先看页面结果
这里的处理是根据URL参数RoleCode的值从视图V_RolePermissions查询该角色的所有权限,然后构造一个字符串,在构造<input type="checkbox" />时判断该字符串是否包含此权限的标识,如果包含,就添加 checked属性。
SQL:
FROM dbo.V_ModulePermissions INNER JOIN
dbo.RolePermissions ON dbo.V_ModulePermissions.ID = dbo.RolePermissions.ModulePermissionID
(3)新建、修改全部代码
Edit.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Edit.aspx.cs" Inherits="Role_Edit" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>编辑角色</title>
<script type="text/javascript" src="../Scripts/jquery-1.4.1.min.js"></script>
<script type="text/javascript">
//全部模块全选或全取消
function cbAllCheck(obj) {
$("#txtPermissions").val("");
$(":checkbox").each(function () {
$(this).attr("checked", $(obj).attr("checked"));
if ($(obj).attr("checked")) {
if ($(this).val() != "on") {
$("#txtPermissions").val($("#txtPermissions").val() + "," + $(this).val() + ",");
}
}
});
}
//当前模块全选或全取消
function cbModuleCheck(obj, cblID) {
$("#" + cblID).find(":input").each(function () {
$(this).attr("checked", $(obj).attr("checked"));
if ($(obj).attr("checked")) {
if ($("#txtPermissions").val().indexOf("," + $(this).val() + ",") == -1) {
$("#txtPermissions").val($("#txtPermissions").val() + "," + $(this).val() + ",");
}
} else {
$("#txtPermissions").val(
$("#txtPermissions").val().replace("," + $(this).val() + ",", ""));
}
});
}
//当前权限选中或取消
function cbPermissionCheck(obj) {
if ($(obj).attr("checked")) {
$("#txtPermissions").val($("#txtPermissions").val() + "," + $(obj).val() + ",");
} else {
$("#txtPermissions").val(
$("#txtPermissions").val().replace("," + $(obj).val() + ",", ""));
}
}
</script>
</head>
<body>
<form id="form1" runat="server">
<asp:HiddenField ID="txtPermissions" runat="server" />
<div>
角色名称:<asp:TextBox ID="txtRoleName" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="txtRoleName" Display="Dynamic" ErrorMessage="<---请填写角色名称"></asp:RequiredFieldValidator>
</div>
<div>
角色编码:<asp:TextBox ID="txtRoleCode" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
ControlToValidate="txtRoleCode" Display="Dynamic" ErrorMessage="<---请填写角色编码"></asp:RequiredFieldValidator>
</div>
<div><input id="cbAll" type="checkbox" onclick="cbAllCheck(this)" />全选</div>
<div>
<asp:GridView runat="server" ID="gvParent" AutoGenerateColumns="False" EnableModelValidation="True"
DataKeyNames="ModuleCode" ShowHeader="false"
OnRowDataBound="gvParent_RowDataBound" Width="100%">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:Label ID="lblTopModule" runat="server" Font-Bold="true" Text='<%# Eval("ModuleName") %>'></asp:Label>
<br />
<asp:GridView runat="server" ID="gvChild" AutoGenerateColumns="False" EnableModelValidation="True"
DataKeyNames="ModuleCode" ShowHeader="false"
OnRowDataBound="gvChild_RowDataBound" Width="100%">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:Label ID="lblMoudle" runat="server" Text='<%# Eval("ModuleName") %>'></asp:Label>
<asp:CheckBox id="cbModule" runat="server"/>全选
<br />
<asp:Label ID="lblPermissions" runat="server"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
<div>
<asp:Button ID="btnSubmit" runat="server" Text="提交" onclick="btnSubmit_Click" />
</div>
</form>
</body>
</html>
Edit.aspx.cs
using System.Web.UI.WebControls;
using System.Data;
using System.Text;
public partial class Role_Edit : System.Web.UI.Page
{
private DataSet ds = new DataSet();
private StringBuilder RolePermission { get; set; }
private void BindTopModule()
{
if (ds != null && ds.Tables.Count > 0)
{
//筛选顶级模块
DataRow[] drs = ds.Tables[0].Select("ParentCode='0'");
//克隆表结构
DataTable dt = ds.Tables[0].Clone();
foreach (DataRow dr in drs)
{
dt.ImportRow(dr);
}
gvParent.DataSource = dt;
gvParent.DataBind();
}
}
private void SetRolePermission()
{
string querySql = string.Format("select * from V_RolePermissions where RoleCode='{0}'",
ViewState["RoleCode"]);
DataTable dt = new DataTable();
SqlHelper helper = new SqlHelper();
helper.FillDataTable(querySql, dt);
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
RolePermission.Append("," + dr["ID"].ToString() + ",");
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
txtRoleName.Text = Request.QueryString["RoleName"];
ViewState["RoleCode"] = txtRoleCode.Text = Request.QueryString["RoleCode"];
//修改时根据RoleCode获得当前Role的所有权限ID
if (ViewState["RoleCode"]!=null)
{
RolePermission = new StringBuilder(); ;
SetRolePermission();
txtPermissions.Value = RolePermission.ToString();
}
SqlHelper helper = new SqlHelper();
String querySql = "select * from Module where ModuleVisible=1;select * from V_ModulePermissions";
helper.FillDataSet(querySql, ds) ;
BindTopModule();
}
}
protected void gvParent_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
GridView gv = (GridView)e.Row.FindControl("gvChild");
if (ds != null && ds.Tables.Count > 0)
{
DataRow[] drs = ds.Tables[0].Select("ParentCode='" + gvParent.DataKeys[e.Row.RowIndex].Value + "'");
DataTable dt = ds.Tables[0].Clone();
foreach (DataRow dr in drs)
{
dt.ImportRow(dr);
}
gv.DataSource = dt;
gv.DataBind();
}
}
}
protected void gvChild_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
if (ds != null && ds.Tables.Count > 1)
{
Label lblPermissions = (Label)e.Row.FindControl("lblPermissions");
StringBuilder appendCheckBox = new StringBuilder();
DataRow[] drs = ds.Tables[1].Select("ModuleCode='" + ((GridView)sender).DataKeys[e.Row.RowIndex].Value + "'");
for (int index = 0; index < drs.Length;index++ )
{
DataRow dr = drs[index];
appendCheckBox.Append(
string.Format("<input type='checkbox' id='{0}' value='{1}' onclick='cbPermissionCheck(this)' {3} />{2} ",
"cb" + e.Row.RowIndex.ToString("00") + index.ToString("00"),
dr["ID"].ToString(),
dr["PermissionName"].ToString(),
RolePermission != null && RolePermission.ToString().IndexOf("," + dr["ID"].ToString() + ",") >= 0 ? "checked" : ""
)
);
//(delegate(string id){return id==dr["id"].ToString();})>=0
}
lblPermissions.Text = appendCheckBox.ToString();
((CheckBox)e.Row.FindControl("cbModule")).Attributes.Add("onclick", "cbModuleCheck(this,'" + lblPermissions.ClientID+ "')");
}
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
if (IsValid)
{
string roleName = txtRoleName.Text.Trim();
string roleCode = txtRoleCode.Text.Trim();
string[] permissions = txtPermissions.Value
.TrimStart(',')
.TrimEnd(',')
.Replace(",,", ",")
.Split(',');
StringBuilder appendSql = new StringBuilder();
if (ViewState["RoleCode"] != null)
{
appendSql.Append(string.Format("delete from RolePermissions where RoleCode='{0}';", ViewState["RoleCode"]));
appendSql.Append(string.Format("update Roles set RoleCode='{0}',RoleName='{1}' where RoleCode='{2}'",
roleCode, roleName, ViewState["RoleCode"]));
}
else
{
appendSql.Append(string.Format("insert into Roles(RoleCode,RoleName) values('{0}','{1}')",
roleCode, roleName));
}
foreach (string s in permissions)
{
if (!string.IsNullOrEmpty(s))
{
appendSql.Append(
string.Format(";insert into dbo.RolePermissions values('{0}',{1})",
roleCode, s));
}
}
txtPermissions.Value = "";
//Response.Write(appendSql.ToString());
SqlHelper helper = new SqlHelper();
if (helper.ExecuteNonQueryWithTran(appendSql.ToString()))
{
Response.Redirect("../Redirect.html?Role/Index.aspx");
}
else
{
ClientScript.RegisterStartupScript(this.GetType(), "Infomation", "alert('操作失败!');", true);
}
}
}
}
(4)操作成功后的提示及页面跳转
Redirect.html
使用setInterval函数倒计时
获取传递过来的路径(传递的是相对路径),倒计时到0秒时,使用window.clearInterval(intervalId)清除setInterval事件,并指定window.location.href=路径,使得由当前页跳转到目的页
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>继续</title>
<script type="text/javascript">
//秒数
var count = 3;
//setIntervalId
var setIntervalId;
var url = location.href;
url = url.substring(url.indexOf("?") + 1, url.length);
function Redirect() {
var displayOjb = document.getElementById("showTime")
if (count == 0) {
//清除setInterval
window.clearInterval(setIntervalId);
displayOjb.innerHTML = "网页正在自动跳转";
location.href = url;
}
else {
displayOjb.innerHTML = "操作成功!" + count + "秒后网页自动跳转。";
count--;
}
}
setIntervalId = setInterval("Redirect()", 1000);
</script>
</head>
<body>
<div id="showTime"></div>
</body>
</html>
OK,到此全部完成,感觉说的太多了,所以有些内容没介绍,看代码吧。