public List <UserDto> GetAll()
{
var users = new List <UserDto>();
using (_dbCon.Open())
{
string query = "SELECT * FROM [Dbo].[User]";
using SqlCommand command = new SqlCommand(query, _dbCon.connection);
var reader = command.ExecuteReader();
while (reader.Read())
{
UserDto dto = new UserDto()
{
UserId = reader.GetInt32(0),
FirstName = reader.GetString(1),
LastName = reader.GetString(2),
Email = reader.GetString(3),
Password = reader.GetString(4),
Role = reader.GetString(5)
};
users.Add(dto);
}
_dbCon.Close();
}
return(users);
}
public List <ArticleDto> GetAll()
{
var articles = new List <ArticleDto>();
using (_dbCon.Open())
{
string query =
"SELECT Articles.ArticleID, Articles.AccountID, Articles.Title, Articles.ArticleText, Articles.DateCreated, Articles.Draft, Articles.LastEdited, Articles.Images, a1.TagID, t.TagName " +
"FROM Articles " +
"INNER JOIN ArticleTag a1 on Articles.ArticleID = a1.ArticleID " +
"INNER JOIN Tags t on a1.TagID = t.TagID;";
using (SqlCommand command = new SqlCommand(query, _dbCon.connection))
{
var reader = command.ExecuteReader();
while (reader.Read())
{
ArticleDto ArticleDTO = new ArticleDto
{
ArticleID = reader.GetInt32(0),
AccountID = reader.GetInt32(1),
Title = reader.GetString(2),
ArticleText = reader.GetString(3),
DateCreated = reader.GetDateTime(4),
Draft = reader.GetBoolean(5),
LastEdited = reader.GetDateTime(6),
Images = reader.GetString(7),
TagID = reader.GetInt32(8),
TagName = reader.GetString(9)
};
articles.Add(ArticleDTO);
}
_dbCon.Close();
}
}
return(articles);
}
public List <RoleDto> GetAll()
{
List <RoleDto> dtos = new List <RoleDto>();
using (_dbCon.Open())
{
string query = "SELECT * FROM Role";
using (SqlCommand getAll = new SqlCommand(query, _dbCon.connection))
{
var reader = getAll.ExecuteReader();
while (reader.Read())
{
RoleDto dto = new RoleDto()
{
Id = reader.GetInt32(0),
Name = reader.GetString(1)
};
dtos.Add(dto);
}
_dbCon.Close();
return(dtos);
}
}
}
