public List <AuditEmployeeInfo> GetAuditDefaultMember(SqlConnection connection)
{
List <AuditEmployeeInfo> ret = new List <AuditEmployeeInfo>();
using (var command = new SqlCommand(@"select * from tbl_Employee where IsAuditMem = 'true'
order by OrderAuditMem
", connection))
{
WriteLogExecutingCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var item = new AuditEmployeeInfo();
item.EmployeeID = GetDbReaderValue <int>(reader["EmployeeID"]);
item.Title = GetDbReaderValue <string>(reader["Title"]);
item.RoleName = GetDbReaderValue <string>(reader["RoleName"]);
item.Name = GetDbReaderValue <string>(reader["Name"]);
item.Generic = GetDbReaderValue <int>(reader["Generic"]);
item.DepartmentID = GetDbReaderValue <string>(reader["DepartmentID"]);
ret.Add(item);
}
}
}
return(ret);
}
public List <AuditEmployeeInfo> GetAuditEmployeesByIds(SqlConnection connection, string ids)
{
List <AuditEmployeeInfo> ret = new List <AuditEmployeeInfo>();
using (var command = new SqlCommand(@"select AE.AutoID, AE.AuditID, AE.EmployeeID, AE.Comment, E.Title, E.RoleName, E.Name, E.Generic , E.DepartmentID , E.UserID from
(select * from tbl_AuditEmployee where AuditID in (" + ids + @")) AE
left join tbl_Employee as E on E.EmployeeID = AE.EmployeeID
left join tbl_EmployeeRoles as ER on ER.RoleID = E.Role
", connection))
{
WriteLogExecutingCommand(command);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var item = new AuditEmployeeInfo();
item.AutoID = GetDbReaderValue <int>(reader["AutoID"]);
item.AuditID = GetDbReaderValue <int>(reader["AuditID"]);
item.EmployeeID = GetDbReaderValue <int>(reader["EmployeeID"]);
item.Comment = GetDbReaderValue <string>(reader["Comment"]);
item.Title = GetDbReaderValue <string>(reader["Title"]);
item.RoleName = GetDbReaderValue <string>(reader["RoleName"]);
item.Name = GetDbReaderValue <string>(reader["Name"]);
item.Generic = GetDbReaderValue <int>(reader["Generic"]);
item.DepartmentID = GetDbReaderValue <string>(reader["DepartmentID"]);
item.UserID = GetDbReaderValue <string>(reader["UserID"]);
ret.Add(item);
}
}
}
return(ret);
}
public void InsertAuditEmployee(SqlConnection connection, int _AuditID, AuditEmployeeInfo item)
{
using (var command = new SqlCommand("Insert into [dbo].[tbl_AuditEmployee] ([AuditID], [EmployeeID],[Comment])" +
"VALUES(@AuditID,@EmployeeID,@Comment)", connection))
{
AddSqlParameter(command, "@AuditID", _AuditID, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@EmployeeID", item.EmployeeID, System.Data.SqlDbType.Int);
AddSqlParameter(command, "@Comment", item.Comment, System.Data.SqlDbType.NVarChar);
WriteLogExecutingCommand(command);
command.ExecuteScalar();
}
}
