public IEnumerable <Semester> Handle()
{
string query = "sp_GetActiveStudentDetails";
IEnumerable <Semester> students = sqlQueryExecutor.Execute(query, GetDetails);
return(students);
}
public Professor Handle(ProfessorByIdQuery query)
{
string spQuery = "sp_GetProfessorDetails";
professorIdParameter = new SqlParameter("@professorId", query.Id);
Professor professor = sqlQueryExecutor.Execute(spQuery, Get);
return(professor);
}
public DataTable GetDMVeTinh(string dlu, string host)
{
string sql = String.Format("SELECT * FROM QuanLyVT WHERE UR = '{0}' AND Host = '{1}'", dlu, host);
sqlQueryExecutor = new SqlServerSqlQueryExecutor(Properties.Settings.Default.Server, 1433, Properties.Settings.Default.Database, Properties.Settings.Default.User, Properties.Settings.Default.Pass);
DataTable lsAlarm = new DataTable();
lsAlarm = sqlQueryExecutor.Execute(sql);
return(lsAlarm);
}
public DataTable GetMaxTimeEvent(string host)
{
string sql = "SELECT MAX(DateEvent) AS MAXDATE FROM ALARM WHERE HOST = '" + host + "'";
sqlQueryExecutor = new SqlServerSqlQueryExecutor(Properties.Settings.Default.Server, 1433, Properties.Settings.Default.Database, Properties.Settings.Default.User, Properties.Settings.Default.Pass);
DataTable result = new DataTable();
result = sqlQueryExecutor.Execute(sql);
return(result);
}
public DataTable GetDMAlarm(string maAlarm, string host, string loai)
{
string sql = String.Format("SELECT * FROM DM_ALARM WHERE MaAlarm = '{0}' AND HOST = '{1}' AND Loai = '{2}'",
maAlarm, host, loai);
sqlQueryExecutor = new SqlServerSqlQueryExecutor(Properties.Settings.Default.Server, 1433, Properties.Settings.Default.Database, Properties.Settings.Default.User, Properties.Settings.Default.Pass);
DataTable lsAlarm = new DataTable();
lsAlarm = sqlQueryExecutor.Execute(sql);
return(lsAlarm);
}
public DataTable GetAlarmBeginCCSM(ALARM alarm)
{
string sql = String.Format("SELECT * FROM PSTNAlarmTapTrung WHERE NAlarm like '{0}%' AND TrangThaiEnd = 0 ORDER BY MaSend desc",
alarm.HOST + "_" + alarm.DLU + "_" + alarm.ERL + "_");
sqlQueryExecutor = new SqlServerSqlQueryExecutor(Properties.Settings.Default.Server, 1433, Properties.Settings.Default.Database, Properties.Settings.Default.User, Properties.Settings.Default.Pass);
DataTable lsAlarm = new DataTable();
lsAlarm = sqlQueryExecutor.Execute(sql);
return(lsAlarm);
}
public DataTable GetAlarmBegin(ALARM alarm)
{
string sql = String.Format("SELECT * FROM ALARM WHERE DLU = '{0}' AND ERL = '{1}' AND HOST = '{2}' AND IsSuccess = 0 ORDER BY ID desc",
alarm.DLU, alarm.ERL, alarm.HOST);
sqlQueryExecutor = new SqlServerSqlQueryExecutor(Properties.Settings.Default.Server, 1433, Properties.Settings.Default.Database, Properties.Settings.Default.User, Properties.Settings.Default.Pass);
DataTable lsAlarm = new DataTable();
lsAlarm = sqlQueryExecutor.Execute(sql);
return(lsAlarm);
}
public IEnumerable <TEntity> Handle(AllEntitiesQuery <TEntity> query)
{
string sqlQuery = $@"SELECT * FROM {query.Table} WHERE IsDeleted = @withDeleted";
sqlParameters = new SqlParameter[]
{
new SqlParameter("@withDeleted", $"{query.WithDeleted}")
};
IEnumerable <TEntity> entities = sqlQueryExecutor.Execute(sqlQuery, GetAll);
return(entities);
}
public TEntity Handle(EntityByIdQuery <TEntity> query)
{
string sqlQuery = $@"SELECT * FROM {query.Table} WHERE Id = @id";
sqlParameters = new SqlParameter[]
{
new SqlParameter("@id", $"{query.Id}")
};
TEntity entity = sqlQueryExecutor.Execute(sqlQuery, GetById);
return(entity);
}
public void LoadDLUCombobox(string host)
{
string sql = String.Format("SELECT UR, TenVT FROM QuanLyVT WHERE Host = '{0}' UNION SELECT 0 MaDLU, N'Tất cả' Name", host);
sqlQueryExecutor = new SqlServerSqlQueryExecutor(Properties.Settings.Default.Server, 1433, Properties.Settings.Default.Database, Properties.Settings.Default.User, Properties.Settings.Default.Pass);
DataTable dt = new DataTable();
dt = sqlQueryExecutor.Execute(sql);
cbDLU.ValueMember = "UR";
cbDLU.DisplayMember = "TenVT";
cbDLU.DataSource = dt;
}
public bool Handle(DeleteEntityCommand command)
{
string query = $@"UPDATE {command.Table}
SET IsDeleted = @isDeleted, DeletedOn = @deletedOn
WHERE Id = @id";
sqlParameters = new SqlParameter[]
{
new SqlParameter("@id", command.Id),
new SqlParameter("@isDeleted", true),
new SqlParameter("@deletedOn", DateTime.UtcNow)
};
bool isDeleted = sqlQueryExecutor.Execute(query, Delete);
return(isDeleted);
}
public bool Handle(UpdateEntityCommand command)
{
string setQuery = string.Join(", ", command.Columns.Keys.Select(x => x + " = @" + x));
string query = $@"UPDATE {command.Table}
SET {setQuery}
WHERE Id = @id";
sqlParameters = new List <SqlParameter>();
sqlParameters.Add(new SqlParameter("@id", command.Id));
foreach (var column in command.Columns)
{
sqlParameters.Add(new SqlParameter($"@{column.Key}", column.Value));
}
bool isUpdated = sqlQueryExecutor.Execute(query, Update);
return(isUpdated);
}
public int Handle(EntityCommand command)
{
string columnKeys = string.Join(", ", command.Columns.Keys);
string columnVariables = string.Join(", ", command.Columns.Keys.Select(x => "@" + x));
string query = $@"INSERT INTO {command.Table}({columnKeys})
OUTPUT INSERTED.ID
VALUES({columnVariables})";
sqlParameters = new List <SqlParameter>();
foreach (var column in command.Columns)
{
sqlParameters.Add(new SqlParameter($"@{column.Key}", column.Value));
}
int id = sqlQueryExecutor.Execute(query, Create);
return(id);
}