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