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