ERP员工入登记查询（六）

ERP员工入登记查询（六）

实现的功能：

视图的创建：

--两个表的查询select a.*,b.* from [dbo].[Department] as a inner join [dbo].[UserManager] as bon a.DepartmentId=b.DepartmentId--三个表的查询select a.*,b.*,c.* from [dbo].[Department] as a ,[dbo].[UserManager] as b, [dbo].[tbRose] as cwhere a.DepartmentId=b.DepartmentId and c.RoseID=b.RoleId--创建视图 查询中不能有相同的列CREATE VIEW UserInfoViewASselect a.[DepartmentName],b.*,c.[RoseName] from [dbo].[Department] as a ,[dbo].[UserManager] as b, [dbo].[tbRose] as cwhere a.DepartmentId=b.DepartmentId and c.RoseID=b.RoleIdselect * 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 StateNameFROM 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" %>

员工信息查询系统
		在职 离职 --请选择--

用的分页控件：（第三方组件结合自定分页存储过程实现）AspNetPager

属性：

RecordCount：总共条数

PageSize：每页显示的条数

CurrentPageIndex：当前页索引

事件：

PageChanged 页码改变后触发事件

指定条件查询总条数的存储过程：

-- Description: 根据指定表，指定条件查询总共条数-- =============================================ALTER PROCEDURE [dbo].[getDataCountByCondition] @tableName nvarchar(500), @condition nvarchar(1000)= 'and 1=1' --查询条件ASBEGIN SET NOCOUNT ON; DECLARE @Sql nvarchar(2000) SET @Sql='select count(*) from '+@tableName+' where 1=1 '+@condition EXEC(@Sql)END

在common层封装根据指定表，指定的条件，查询返回总条数

///

/// 根据指定表，指定条件，查询返回总条数 ///

/// 指定表 /// 指定条件 /// object 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 = "";

查询所有的员工的信息：

///

/// 查询所有员工信息 ///

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(); } }

搜索的页面拼装条件：

///

/// 搜索的业务 ///

/// /// 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 ASBEGIN -- 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=@useridEND

修改的业务代码：

///

/// 根据指定ID返回其对象 ///

/// userid /// UserManager 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(""); 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"); } } }

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。