public static List <HrEmploryee> SelectEmployeeNameAndDepartmentNameByEmailAddress(SqlConnection conn, List <string> emailAddressList) { var hrEmploryeeList = new List <HrEmploryee>(); var emailAddressTable = new DataTable(); emailAddressTable.Columns.Add("EmailAddress", Type.GetType("System.String")); emailAddressList.ForEach(item => { emailAddressTable.Rows.Add(emailAddressTable.NewRow()[0] = item); }); var parm = new[] { new SqlParameter("@EmailAddressList", emailAddressTable) }; using (var dataReader = SqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, "HrEmploryee_SelectEmployeeNameAndDepartmentNameByEmailAddress", parm)) { while (dataReader.Read()) { var hrEmploryee = new HrEmploryee(); hrEmploryee.EmployeeName = dataReader.GetTuhuString(0); hrEmploryee.DepartmentName = dataReader.GetTuhuString(1); hrEmploryee.EmailAddress = dataReader.GetTuhuString(2); hrEmploryeeList.Add(hrEmploryee); } } return(hrEmploryeeList); }
/// <summary> /// 新增员工信息 /// </summary> /// <param name="connection"></param> /// <param name="he"></param> public static void AddTheHrEmployee(SqlConnection connection, HrEmploryee he) { const string sql = @" INSERT INTO dbo.HrEmployee ( EmployeeID, EmployeeName, Department, DepartmentName, Mobile, Tel, EmailAddress, Remark, IsActive, Roles, RolesName ) VALUES ( @EmployeeID, -- EmployeeID - nvarchar(20) @EmployeeName, -- EmployeeName - nvarchar(50) @Department, -- Department - nvarchar(500) @DepartmentName, -- DepartmentName - nvarchar(50) @Mobile, -- Mobile - varchar(50) @Tel, -- Tel - varchar(50) @EmailAddress, -- EmailAddress - varchar(50) @Remark, -- Remark - nvarchar(50) @IsActive, -- IsActive - bit @Roles, -- Roles - nvarchar(500) @RolesName -- RolesName - nvarchar(500) )" ; var sqlParameters = new[] { new SqlParameter("@EmployeeID", he.EmployeeID), new SqlParameter("@EmployeeName", he.EmployeeName), new SqlParameter("@Department", he.Department), new SqlParameter("@DepartmentName", he.DepartmentName), new SqlParameter("@Mobile", he.Mobile), new SqlParameter("@Tel", he.Tel), new SqlParameter("@EmailAddress", he.EmailAddress), new SqlParameter("@Remark", he.Remark), new SqlParameter("@IsActive", he.IsActive), new SqlParameter("@Roles", he.Roles), new SqlParameter("@RolesName", he.RolesName) }; SqlHelper.ExecuteNonQuery(connection, CommandType.Text, sql, sqlParameters); }
/// <summary> /// 根据邮箱地址查询员工 /// </summary> public static HrEmploryee GetEmployeeByEmailAddress(SqlConnection connection, string emailAddress) { var employee = new HrEmploryee(); string sql = "SELECT PKID,EmployeeName,Mobile,EmailAddress FROM dbo.HrEmployee WITH(NOLOCK) WHERE EmailAddress=@EmailAddress"; var parameters = new[] { new SqlParameter("@EmailAddress", emailAddress) }; using (var reader = SqlHelper.ExecuteReaderV2(connection, CommandType.Text, sql, parameters)) { if (reader.Read()) { employee.PKID = reader.GetTuhuValue <int>(0); employee.EmployeeName = reader.GetTuhuString(1); employee.Mobile = reader.GetTuhuString(2); employee.EmailAddress = reader.GetTuhuString(3); } } return(employee); }
/// <summary> /// 编辑员工信息 /// </summary> /// <param name="connection"></param> /// <param name="he"></param> public static void EditTheHrEmployee(SqlConnection connection, HrEmploryee he) { const string sql = @" UPDATE dbo.HrEmployee SET EmployeeID = @EmployeeID, EmployeeName = @EmployeeName, Department = @Department, DepartmentName = @DepartmentName, Mobile = @Mobile, Tel = @Tel, EmailAddress = @EmailAddress, Remark = @Remark, IsActive = @IsActive, Roles = @Roles, RolesName = @RolesName WHERE PKID = @PKID" ; var sqlParameters = new[] { new SqlParameter("@EmployeeID", he.EmployeeID), new SqlParameter("@EmployeeName", he.EmployeeName), new SqlParameter("@Department", he.Department), new SqlParameter("@DepartmentName", he.DepartmentName), new SqlParameter("@Mobile", he.Mobile), new SqlParameter("@Tel", he.Tel), new SqlParameter("@EmailAddress", he.EmailAddress), new SqlParameter("@Remark", he.Remark), new SqlParameter("@IsActive", he.IsActive), new SqlParameter("@Roles", he.Roles), new SqlParameter("@RolesName", he.RolesName), new SqlParameter("@PKID", he.PKID) }; var result = SqlHelper.ExecuteNonQuery(connection, CommandType.Text, sql, sqlParameters); if (result > 0) { var editSql = @"UPDATE Tuhu_profiles..UserObject SET u_tel_number=@Tel,u_mobile_number=@Mobile WHERE u_email_address=@EmailAddress"; SqlHelper.ExecuteNonQuery(connection, CommandType.Text, editSql, sqlParameters); } }
/// <summary> /// 员工信息表 /// </summary> /// <param name="connection"></param> /// <param name="pageIndex"></param> /// <param name="conditions"></param> /// <param name="paramList"></param> /// <returns></returns> public static List <HrEmploryee> SelectHrEmployeeList(SqlConnection connection, int pageIndex, string conditions, List <SqlParameter> paramList) { string startPage = Convert.ToString(20 * (pageIndex - 1)); string endPage = Convert.ToString(20 * pageIndex + 1); List <SqlParameter> sqlParams = new List <SqlParameter>(); var sqlParameters = new[] { new SqlParameter("@StartPage", startPage), new SqlParameter("@EndPage", endPage) }; var list = new List <HrEmploryee>(); sqlParams.AddRange(sqlParameters); sqlParams.AddRange(paramList); // using (var reader = SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, "HrEmployee_SelectList", sqlParameters)) //{ string sql = @"SELECT Tb.PKID, Tb.EmployeeID, Tb.EmployeeName, Tb.Department, Tb.DepartmentName, Tb.Mobile, Tb.Tel, Tb.EmailAddress, Tb.Remark, Tb.IsActive, Tb.Roles, Tb.RolesName FROM ( SELECT HE.PKID, HE.EmployeeID, HE.EmployeeName, HE.Department, HE.DepartmentName, HE.Mobile, HE.Tel, HE.EmailAddress, HE.Remark, HE.IsActive, HE.Roles, HE.RolesName, ROW_NUMBER() OVER ( ORDER BY HE.PKID ) AS RowNum FROM dbo.HrEmployee AS HE WITH ( NOLOCK ) WHERE 1 = 1 " + conditions + ") AS Tb WHERE Tb.RowNum < @EndPage AND Tb.RowNum > @StartPage"; using (var reader = SqlHelper.ExecuteReader(connection, CommandType.Text, sql, sqlParams.ToArray())) { while (reader.Read()) { var model = new HrEmploryee { PKID = reader.IsDBNull(0) ? 0 : reader.GetInt32(0), EmployeeID = reader.IsDBNull(1) ? string.Empty : reader.GetString(1), EmployeeName = reader.IsDBNull(2) ? string.Empty : reader.GetString(2), Department = reader.IsDBNull(3) ? string.Empty : reader.GetString(3), DepartmentName = reader.IsDBNull(4) ? string.Empty : reader.GetString(4), Mobile = reader.IsDBNull(5) ? string.Empty : reader.GetString(5), Tel = reader.IsDBNull(6) ? string.Empty : reader.GetString(6), EmailAddress = reader.IsDBNull(7) ? string.Empty : reader.GetString(7), Remark = reader.IsDBNull(8) ? string.Empty : reader.GetString(8), IsActive = reader.IsDBNull(9) ? false : reader.GetBoolean(9), Roles = reader.IsDBNull(10) ? string.Empty : reader.GetString(10), RolesName = reader.IsDBNull(11) ? string.Empty : reader.GetString(11) }; list.Add(model); } } return(list); }