public List<ProgramLogDTO> GetLogsByProgram(int programId)
{
var logList = new List<ProgramLogDTO>();
using (var sqlConnection = new SqlConnection(connectionString))
{
var sqlCommand = new SqlCommand
{
Connection = sqlConnection,
CommandText = @"SELECT *
FROM ProgramLogs pl
JOIN Programs p
ON pl.ProgramId = p.ProgramId
WHERE pl.ProgramId = @ProgramId
ORDER BY TimeStamp DESC"
};
sqlConnection.Open();
sqlCommand.Parameters.AddWithValue("@ProgramId", programId);
var reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
var dto = new ProgramLogDTO()
{
LogId = reader["LogId"] != DBNull.Value ? Convert.ToInt32(reader["LogId"]) : 0,
ProgramId = reader["ProgramId"] != DBNull.Value ? Convert.ToInt32(reader["ProgramId"]) : 0,
ProgramName = reader["ProgramName"].ToString(),
TimeStamp = reader["TimeStamp"] != DBNull.Value ? Convert.ToDateTime(reader["TimeStamp"]) : DateTime.MinValue,
ComputerName = reader["ComputerName"].ToString(),
UserName = reader["UserName"].ToString(),
UserDomainName = reader["UserDomainName"].ToString(),
UserAction = reader["UserAction"].ToString(),
Information = reader["Information"].ToString(),
SQLCommand = reader["SQLCommand"].ToString()
};
logList.Add(dto);
}
}
return logList;
}
public List<ProgramLogDTO> GetLogs()
{
List<ProgramLogDTO> logList = new List<ProgramLogDTO>();
using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = @"SELECT *
FROM Logs l
JOIN Programs p
ON l.ProgramId = p.ProgramId
ORDER BY TimeStamp DESC";
sqlConnection.Open();
SqlDataReader reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
var dto = new ProgramLogDTO()
{
LogId = reader["LogId"] != DBNull.Value ? Convert.ToInt32(reader["LogId"]) : 0,
ProgramId = reader["ProgramId"] != DBNull.Value ? Convert.ToInt32(reader["ProgramId"]) : 0,
ProgramName = reader["ProgramName"].ToString(),
TimeStamp = reader["TimeStamp"] != DBNull.Value ? Convert.ToDateTime(reader["TimeStamp"]) : DateTime.MinValue,
ComputerName = reader["ComputerName"].ToString(),
UserName = reader["UserName"].ToString(),
UserDomainName = reader["UserDomainName"].ToString(),
UserAction = reader["UserAction"].ToString(),
Information = reader["Information"].ToString(),
SQLCommand = reader["SQLCommand"].ToString()
};
logList.Add(dto);
}
}
return logList;
}
public List<ProgramLogDTO> GetLogsByDate(DateTime startDate, DateTime endDate)
{
endDate = new DateTime(endDate.Year, endDate.Month, endDate.Day, 23, 59, 59);
var logList = new List<ProgramLogDTO>();
using (var sqlConnection = new SqlConnection(connectionString))
{
var sqlCommand = new SqlCommand
{
Connection = sqlConnection,
CommandText = @"SELECT *
FROM ProgramLogs pl
JOIN Programs p
ON pl.ProgramId = p.ProgramId
WHERE pl.TimeStamp BETWEEN @StartDate AND @EndDate
ORDER BY TimeStamp DESC"
};
sqlConnection.Open();
sqlCommand.Parameters.AddWithValue("@StartDate", startDate);
sqlCommand.Parameters.AddWithValue("@EndDate", endDate);
var reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
var dto = new ProgramLogDTO()
{
LogId = reader["LogId"] != DBNull.Value ? Convert.ToInt32(reader["LogId"]) : 0,
ProgramId = reader["ProgramId"] != DBNull.Value ? Convert.ToInt32(reader["ProgramId"]) : 0,
ProgramName = reader["ProgramName"].ToString(),
TimeStamp = reader["TimeStamp"] != DBNull.Value ? Convert.ToDateTime(reader["TimeStamp"]) : DateTime.MinValue,
ComputerName = reader["ComputerName"].ToString(),
UserName = reader["UserName"].ToString(),
UserDomainName = reader["UserDomainName"].ToString(),
UserAction = reader["UserAction"].ToString(),
Information = reader["Information"].ToString(),
SQLCommand = reader["SQLCommand"].ToString()
};
logList.Add(dto);
}
}
return logList;
}