public IEnumerable <LessonSignalDto> ShowSignals()
{
_msqlConnection.Open();
MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM lesson_signal", _msqlConnection);
DataSet dataset = new DataSet();
adapter.Fill(dataset, "lesson_signal");
var lst = new List <LessonSignalDto>();
foreach (DataRow row in dataset.Tables[0].Rows)
{
var signalDto = new LessonSignalDto
{
Id = (int)row["id"],
Timestamp = (DateTime)row["timestamp_"],
Type = (LessonSignalType)Convert.ToInt32(row["signal_type"]),
UserId = (string)row["user_id"]
};
lst.Add(signalDto);
}
_msqlConnection.Close();
return(lst);
}
public LessonSignalDto ShowSignal(long id)
{
_msqlConnection.Open();
var command = new MySqlCommand("SELECT * FROM lesson_signal WHERE id = @id", _msqlConnection);
command.Parameters.AddWithValue("id", id);
MySqlDataAdapter adapter = new MySqlDataAdapter(command);
DataSet dataset = new DataSet();
adapter.Fill(dataset, "lesson_signal");
if (dataset.Tables[0].Rows.Count < 1)
{
return(null);
}
var row = dataset.Tables[0].Rows[0];
var signalDto = new LessonSignalDto
{
Id = (int)row["id"],
Timestamp = (DateTime)row["timestamp_"],
Type = (LessonSignalType)Convert.ToInt32(row["signal_type"]),
UserId = (string)row["user_id"]
};
_msqlConnection.Close();
return(signalDto);
}
public IEnumerable <LessonSignalDto> ShowSignals()
{
var connectionString = _configuration.GetConnectionString("BotDatabase");
var query = "SELECT * FROM lesson_signal;";
var conn = new MySqlConnection(connectionString);
var dataTable = new DataTable();
var cmd = new MySqlCommand(query, conn);
var da = new MySqlDataAdapter(cmd);
try
{
conn.Open();
da.Fill(dataTable);
conn.Close();
}
catch
{
return(null);
}
var enumerable = new List <LessonSignalDto>();
foreach (DataRow row in dataTable.Rows)
{
var obj = new LessonSignalDto
{
Id = (int)row["id"],
UserId = (string)row["user_id"],
Type = (LessonSignalType)row["signal_type"],
Timestamp = (DateTime)row["time_stamp"]
};
enumerable.Add(obj);
}
return(enumerable);
}
public LessonSignalDto ShowSignal(long id)
{
using (var conn = new MySqlConnection(_configuration.GetConnectionString("BotDatabase")))
{
conn.Open();
var command = new MySqlCommand("SELECT * FROM lesson_signal WHERE id = @id", conn);
command.Parameters.AddWithValue("id", id);
var adapter = new MySqlDataAdapter(command);
var dataset = new DataSet();
adapter.Fill(dataset, "lesson_signal");
if (dataset.Tables[0].Rows.Count < 1)
{
return(null);
}
var row = dataset.Tables[0].Rows[0];
var signalDto = new LessonSignalDto
{
Timestamp = (DateTime)row["timestamp_"],
Type = (LessonSignalType)row["signal_type"],
UserId = (string)row["user_id"]
};
return(signalDto);
}
}
public IEnumerable <LessonSignalDto> ShowSignals()
{
var connectionString = _configuration.GetConnectionString("BotDatabase");
using (var conn = new MySqlConnection(connectionString))
{
conn.Open();
var adapter = new MySqlDataAdapter("SELECT * FROM lesson_signal", conn);
var dataset = new DataSet();
adapter.Fill(dataset, "lesson_signal");
foreach (DataRow row in dataset.Tables[0].Rows)
{
var signalDto = new LessonSignalDto
{
Id = (int)row["id"],
Timestamp = (DateTime)row["timestamp_"],
Type = (LessonSignalType)Convert.ToInt32(row["signal_type"]),
UserId = (string)row["user_id"]
};
yield return(signalDto);
}
}
}
public LessonSignalDto ShowSignal(long id)
{
LessonSignalDto signal = _repository.ShowSignal(id);
if (signal == null)
{
Response.StatusCode = 404;
}
return(signal);
}
public LessonSignalDto ShowSignal(long id)
{
var connectionString = _configuration.GetConnectionString("BotDatabase");
using (var conn = new MySqlConnection(connectionString))
{
conn.Open();
var command = "SELECT lesson_signal.Id, lesson_signal.SignalType AS Type, " +
"lesson_signal.Timestamp, student.user_id AS UserId FROM lesson_signal " +
"INNER JOIN student ON lesson_signal.student_id=student.id " +
"WHERE lesson_signal.Id=@Id;";
LessonSignalDto signal = conn.Query <LessonSignalDto>(command, new { Id = id }).SingleOrDefault();
if (signal == null)
{
return(null);
}
return(signal);
}
}
