ERP员工入登记查询（六）
生活随笔
收集整理的這篇文章主要介紹了
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.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 StateName FROM dbo.Department AS a INNER JOINdbo.UserManager AS b ON a.DepartmentId = b.DepartmentId INNER JOINdbo.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" οnclick="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 BEGINSET NOCOUNT ON;DECLARE @Sql nvarchar(2000)SET @Sql='select count(*) from '+@tableName+' where 1=1 '+@conditionEXEC(@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;SELECTUserId,LoginName,UserName,Password,DepartmentId,RoleId,Birthday,Mobile,Email,Photo,Address,LastLoginDate,Sex,DisplayOrder,SateFROMUserManagerWHERE 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");}}}?
轉載于:https://www.cnblogs.com/sunliyuan/p/5936943.html
總結
以上是生活随笔為你收集整理的ERP员工入登记查询（六）的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 第三次作业总结
- 下一篇： webpack学习笔记--安装