ERP员工入登记查询（六）
实现的功能：
视图的创建：
--两个表的查询 select a.*,b.* from [dbo].[Department] as a inner join [dbo].[UserManager] as b on a.DepartmentId=b.DepartmentId --三个表的查询 select a.*,b.*,c.* from [dbo].[Department] as a ,[dbo].[UserManager] as b, [dbo].[tbRose] as c where a.DepartmentId=b.DepartmentId and c.RoseID=b.RoleId --创建视图 查询中不能有相同的列 CREATE VIEW UserInfoView AS select a.[DepartmentName],b.*,c.[RoseName] from [dbo].[Department] as a ,[dbo].[UserManager] as b, [dbo].[tbRose] as c where a.DepartmentId=b.DepartmentId and c.RoseID=b.RoleId select * from [dbo].[UserInfoView]
直接在视图中修改员工状态：
SELECT a.DepartmentName, b.UserId, b.LoginName, b.UserName, b.Password, b.DepartmentId, b.RoleId, b.Birthday, b.Mobile,
b.Email, b.Photo, b.Address, b.LastLoginDate, b.Sex, b.DisplayOrder, b.Sate, c.RoseName,
CASE b.sate WHEN 'True' THEN '正式员工' ELSE '试用期员工' END AS StateName
FROM dbo.Department AS a INNER JOIN
dbo.UserManager AS b ON a.DepartmentId = b.DepartmentId INNER JOIN
dbo.tbRose AS c ON b.RoleId = c.RoseID
前台页面显示的代码：
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="UserListShow.aspx.cs" Inherits="BioErpWeb.HRSystem.UserListShow" %>
<%@ Register assembly="AspNetPager" namespace="Wuqi.Webdiyer" tagprefix="webdiyer" %>
<!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>
<link href="../Styles/ERPBaseStyle.css" rel="stylesheet" type="text/css" />
<link href="../Styles/AspNetPagerStyle.css" rel="stylesheet" type="text/css" />
<style type="text/css">
td{ text-align:center;}
.tdsearch{ line-height:30px;}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<table class="maintable">
<tr>
<td colspan="4" class="titlebar">
<span>员工信息查询系统</span>
</td>
</tr>
<tr>
<td class="tdsearch">
<asp:Label ID="Label1" runat="server" Text="员工姓名"></asp:Label>
<asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>
</td>
<td class="tdsearch">
<asp:Label ID="Label2" runat="server" Text="部门"></asp:Label>
<asp:DropDownList ID="ddlDepartMent" Width="150" runat="server">
</asp:DropDownList>
</td>
<td class="tdsearch">
<asp:Label ID="Label3" runat="server" Text="员工的状态"></asp:Label>
<asp:DropDownList ID="ddlState" Width="150" runat="server">
<asp:ListItem Value="1">在职</asp:ListItem>
<asp:ListItem Value="2">离职</asp:ListItem>
<asp:ListItem Selected="True" Value="0">--请选择--</asp:ListItem>
</asp:DropDownList>
</td>
<td class="tdsearch">
<asp:ImageButton ID="imgbutnSearch" Width="46" Height="22" runat="server"
ImageUrl="~/Web/images/Btnsearch.gif" onclick="imgbutnSearch_Click" />
</td>
</tr>
<tr>
<td colspan="4" class="bottomtd">
<asp:GridView ID="GridView1" Width="100%" runat="server" AutoGenerateColumns="False" DataKeyNames="UserId">
<Columns>
<asp:TemplateField HeaderText="员工编号" HeaderStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:Label ID="Label4" runat="server" Text='<%# Eval("UserID") %>'></asp:Label>
</ItemTemplate>
<HeaderStyle HorizontalAlign="Center"></HeaderStyle>
<ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
<asp:TemplateField HeaderText="员工姓名" HeaderStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:Label ID="Label5" runat="server" Text='<%# Eval("UserName") %>'></asp:Label>
</ItemTemplate>
<HeaderStyle HorizontalAlign="Center"></HeaderStyle>
<ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
<asp:TemplateField HeaderText="所属部门" HeaderStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:Label ID="Label6" runat="server" Text='<%# Eval("DepartmentName") %>'></asp:Label>
</ItemTemplate>
<HeaderStyle HorizontalAlign="Center"></HeaderStyle>
<ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
<asp:TemplateField HeaderText="角色" HeaderStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:Label ID="Label7" runat="server" Text='<%# Eval("RoseName") %>'></asp:Label>
</ItemTemplate>
<HeaderStyle HorizontalAlign="Center"></HeaderStyle>
<ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
<asp:TemplateField HeaderText="手机号码" HeaderStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:Label ID="Label8" runat="server" Text='<%# Eval("Mobile") %>'></asp:Label>
</ItemTemplate>
<HeaderStyle HorizontalAlign="Center"></HeaderStyle>
<ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Email" HeaderStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:Label ID="Label9" runat="server" Text='<%# Eval("Email") %>'></asp:Label>
</ItemTemplate>
<HeaderStyle HorizontalAlign="Center"></HeaderStyle>
<ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
<asp:TemplateField HeaderText="状态" HeaderStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:Label ID="Label10" runat="server" Text='<%# Eval("StateName") %>'></asp:Label>
</ItemTemplate>
<HeaderStyle HorizontalAlign="Center"></HeaderStyle>
<ItemStyle HorizontalAlign="Center" />
</asp:TemplateField>
<asp:HyperLinkField DataNavigateUrlFields="UserId"
DataNavigateUrlFormatString="WorkerAdd.aspx?ID={0}" HeaderText="操作"
Text="修改员工信息">
<HeaderStyle HorizontalAlign="Center" />
<ItemStyle HorizontalAlign="Center" />
</asp:HyperLinkField>
</Columns>
</asp:GridView>
</td>
</tr>
<tr>
<td colspan="4">
<webdiyer:AspNetPager ID="AspNetPager1" runat="server" CssClass="paginator" CurrentPageButtonClass="cpb"
onpagechanged="AspNetPager1_PageChanged">
</webdiyer:AspNetPager>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
用的分页控件：（第三方组件结合自定分页存储过程实现）AspNetPager
属性：
RecordCount：总共条数
PageSize：每页显示的条数
CurrentPageIndex：当前页索引
事件：
PageChanged 页码改变后触发事件
指定条件查询总条数的存储过程：
-- Description: 根据指定表，指定条件查询总共条数 -- ============================================= ALTER PROCEDURE [dbo].[getDataCountByCondition] @tableName nvarchar(500), @condition nvarchar(1000)= 'and 1=1' --查询条件 AS BEGIN SET NOCOUNT ON; DECLARE @Sql nvarchar(2000) SET @Sql='select count(*) from '+@tableName+' where 1=1 '+@condition EXEC(@Sql) END
在common层封装根据指定表，指定的条件，查询返回总条数
/// <summary>
/// 根据指定表，指定条件，查询返回总条数
/// </summary>
/// <param name="tableName">指定表</param>
/// <param name="condition">指定条件</param>
/// <returns>object</returns>
public static int getDataCountByCondition(string tableName, string condition)
{
SqlParameter[] pars = new SqlParameter[]{
new SqlParameter("@tableName",tableName),
new SqlParameter("@condition",condition)
};
object obj=DataBaseHelper.SelectSQLReturnObject("getDataCountByCondition", CommandType.StoredProcedure, pars) ;
if (obj != null)
{
return int.Parse(obj.ToString());
}
return 0;
}
注册一个分页控件事件：
放到工具箱中：
定义全局变量：
public static int pageindex = 0;
public static int pagesize = 10;
public static string condition = "";
查询所有的员工的信息：
/// <summary>
/// 查询所有员工信息
/// </summary>
private void getallUsersList()
{
//获取总共的条数
this.AspNetPager1.RecordCount = SqlComm.getDataCountByCondition("UserInfoView", condition);
this.AspNetPager1.PageSize = pagesize;
this.GridView1.DataSource= SqlComm.getDataByPageIndex("UserInfoView", "*", "Userid", condition, pageindex, pagesize);
this.GridView1.DataBind();
}
注册的事件：
protected void AspNetPager1_PageChanged(object sender, EventArgs e)
{
pageindex = this.AspNetPager1.CurrentPageIndex - 1;
getallUsersList();
}
加载的时候：
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DepartMentList();
getallUsersList();
}
}
搜索的页面拼装条件：
/// <summary>
/// 搜索的业务
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void imgbutnSearch_Click(object sender, ImageClickEventArgs e)
{
pageindex = 0;
condition = "";
if (txtUserName.Text.Trim() != null && this.txtUserName.Text.Trim().Length!=0)
{
condition = condition + " and Username like '" + txtUserName.Text + "%'";
}
if (this.ddlDepartMent.SelectedValue != "0")
{
condition = condition + " and DepartmentId ='"+ddlDepartMent.SelectedValue.ToString()+"'";
}
if (this.ddlState.SelectedValue != "0")
{
if (this.ddlState.SelectedValue == "1")
{
condition = condition + " and Sate ='True'";
}
else
{
condition = condition + " and Sate ='False'";
}
}
getallUsersList();
}
分页的CSS样式：
.paginator { font: 11px Arial, Helvetica, sans-serif;padding:10px 20px 10px 0; margin: 0px;}
.paginator a {padding: 1px 6px; border: solid 1px #ddd; background: #fff; text-decoration: none;margin-right:2px}
.paginator a:visited {padding: 1px 6px; border: solid 1px #ddd; background: #fff; text-decoration: none;}
.paginator .cpb {padding: 1px 6px;font-weight: bold; font-size: 13px;border:none}
.paginator a:hover {color: #fff; background: #ffa501;border-color:#ffa501;text-decoration: none;}
效果图：
员工修改的步骤：
在gridview中设置超链接：
绑定修改的列：
绑定的列：
另一种方式通过操作：
-- Description:根据用户编号获取用户信息
-- =============================================
ALTER PROCEDURE [dbo].[getUserByid]
@userid int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT
UserId,
LoginName,
UserName,
Password,
DepartmentId,
RoleId,
Birthday,
Mobile,
Email,
Photo,
Address,
LastLoginDate,
Sex,
DisplayOrder,
Sate
FROM
UserManager
WHERE UserManager.UserId=@userid
END
修改的业务代码：
/// <summary>
/// 根据指定ID返回其对象
/// </summary>
/// <param name="id">userid</param>
/// <returns>UserManager</returns>
public UserManager getuserbyId(string id)
{
UserManager user = new UserManager();
SqlParameter[] pars = new SqlParameter[]{
new SqlParameter("@userid",id)
};
SqlDataReader reader = DataBaseHelper.SelectSQLReturnReader("getUserByid", CommandType.StoredProcedure, pars);
while (reader.Read())
{
user.LoginName = reader["LoginName"].ToString();
user.UserName = reader["UserName"].ToString();
user.DepartmentId =int.Parse(reader["DepartmentId"].ToString());
user.RoleId = int.Parse(reader["RoleId"].ToString());
user.Birthday =Convert.ToDateTime(reader["Birthday"].ToString());
user.Mobile = reader["Mobile"].ToString();
user.Email = reader["Email"].ToString();
user.Photo = reader["Photo"].ToString();
user.Address = reader["Address"].ToString();
user.LastLoginDate = Convert.ToDateTime(reader["LastLoginDate"].ToString());
user.Sex = reader["Sex"].ToString() == "True" ? true : false;
user.DisplayOrder =int.Parse( reader["DisplayOrder"].ToString());
user.Sate = reader["Sate"].ToString() == "True" ? true : false;
}
reader.Close();
return user;
}
后台绑定数据的代码：
UserManager user=new UserManager();
UserManagerBLL userbll;
static bool isadd = true;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
DepartMentBand();
UserRoseList();
PageInfoBind();
if (Request.QueryString["ID"] != null && Request.QueryString["ID"].ToString().Length > 0)
{
isadd = false;
}
}
}
private void PageInfoBind()
{
if (Request.QueryString["ID"] != null)
{
string userid = Request.QueryString["ID"].ToString();
userbll = new UserManagerBLL();
user = userbll.getuserbyId(userid);
this.txtUserName.Text = user.UserName;
this.txtLoginName.Text = user.LoginName;
this.txtBirthday.Text = user.Birthday.ToString();
this.txtMobile.Text = user.Mobile;
this.txtEmail.Text = user.Email;
this.Userimg.ImageUrl = @"\Files\Usersphoto\" + user.Photo;
this.txtAddress.Text = user.Address;
this.txtDisplayOrder.Text = user.DisplayOrder.ToString();
this.ddlDepartMent.SelectedValue = user.DepartmentId.ToString();
this.ddlRose.SelectedValue = user.RoleId.ToString();
if (user.Sex == true)
{
this.ddlSex.SelectedValue = "0";
}
if (user.Sate == false)
{
this.ddlState.SelectedValue = "0";
}
}
}
增加和修改：
protected void btnSubmit_Click(object sender, EventArgs e)
{
user.LoginName = this.txtLoginName.Text;
user.UserName = this.txtUserName.Text;
if (ddlDepartMent.SelectedValue == "0")
{
//Response.Write("<script>alert('请选择部门')</script>");
ScriptManager.RegisterStartupScript(this, this.GetType(), "test", "alert('请选择部门');", true);
return;
}
else
{
user.DepartmentId = int.Parse(ddlDepartMent.SelectedValue.ToString());
}
if (ddlRose.SelectedValue == "0")
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "test", "alert('请选择角色');", true);
return;
}
else
{
user.RoleId =int.Parse(ddlRose.SelectedValue.ToString());
}
user.Mobile = this.txtMobile.Text;
user.Birthday = Convert.ToDateTime(this.txtBirthday.Text);
user.Email = this.txtEmail.Text;
user.Address = this.txtAddress.Text;
//0：男，1,女
user.Sex = this.ddlSex.SelectedValue == "0" ? true : false;
user.DisplayOrder = Convert.ToInt32(this.txtDisplayOrder.Text);
//0：离职，1：在职
user.Sate = this.ddlState.SelectedValue == "0" ? false : true;
user.Password =Comm.MD5("123456");
if (ImgName != "")
{
user.Photo = ImgName;
}
user.LastLoginDate =Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd"));
userbll = new UserManagerBLL();
if (isadd)
{
int count = userbll.UserMangerAdd(user);
if (count == 0)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "test", "alert('数据提交失败');", true);
return;
}
Server.Transfer("UserListShow.aspx");
}
else
{
user.UserId =int.Parse( Request.QueryString["ID"].ToString());
int count = userbll.UserManagerUpdate(user);
if (count == 0)
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "test", "alert('数据提交失败');", true);
return;
}
else
{
Server.Transfer("UserListShow.aspx");
}
}
}
