public void Delete(ADOCategory cat)
{
if (cat != null)
{
using (SqlCommand com = new SqlCommand(
"DELETE Categories WHERE CategoryId = @CatId",
db.Connection))
{
com.Parameters.AddWithValue("CatId", cat.CategoryId);
com.ExecuteNonQuery();
}
}
}
public void Create(ADOCategory cat)
{
if (cat != null)
{
using (SqlCommand com = new SqlCommand(
"INSERT INTO Categories (Name) VALUES (@CName)",
db.Connection))
{
com.Parameters.AddWithValue("CName", cat.Name);
com.ExecuteNonQuery();
}
}
}
public void Update(ADOCategory cat)
{
if (cat != null)
{
using (SqlCommand com = new SqlCommand(
"UPDATE Categories SET Name = @CName WHERE CategoryId = @CatId",
db.Connection))
{
com.Parameters.AddWithValue("CName", cat.Name);
com.Parameters.AddWithValue("CatId", cat.CategoryId);
com.ExecuteNonQuery();
}
}
}
public ADOCategory GetById(int id)
{
ADOCategory cat = null;
using (SqlCommand com = new SqlCommand(
"SELECT * FROM Categories WHERE CategoryId = @CId",
db.Connection))
{
com.Parameters.AddWithValue("CId", id);
using (SqlDataReader reader = com.ExecuteReader())
{
if (reader.Read())
{
cat = new ADOCategory()
{
CategoryId = (int)reader[0],
Name = (string)reader[1]
}
}
;
}
}
return(cat);
}
public List <ADOArticle> GetCurrentUsersSnippets(int id)
{
List <ADOArticle> articles = new List <ADOArticle>();
ADOUserProfile userProfile = new ADOUserProfile();
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT
a.Id,
a.UserProfileId, a.Author, a.Published, a.CurrentsId, a.Title,
a.[Description], a.[Url], a.UserTitle, a.Content, a.CreateDateTime,
a.[Image], a.[Language], a.Publisher, a.Objectivity, a.Sentimentality,
up.FirebaseUserId, up.FirstName, up.LastName, up.DisplayName, up.CreateDateTime,
up.ImageLocation, up.CreateDateTime, up.Email, ar.ArticleId, ar.ReferenceArticleId
FROM Article a
JOIN UserProfile up ON up.Id = a.UserProfileId
LEFT JOIN ArticleReference ar ON ar.ReferenceArticleId = a.Id
WHERE a.UserProfileId = @id AND ReferenceArticleId IS NULL
ORDER BY a.CreateDateTime DESC
";
cmd.Parameters.AddWithValue("@id", id);
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
userProfile.Id = reader.GetInt32(reader.GetOrdinal("UserProfileId"));
userProfile.FirebaseUserId = reader.GetString(reader.GetOrdinal("FirebaseUserId"));
userProfile.FirstName = reader.GetString(reader.GetOrdinal("FirstName"));
userProfile.LastName = reader.GetString(reader.GetOrdinal("LastName"));
userProfile.DisplayName = reader.GetString(reader.GetOrdinal("DisplayName"));
userProfile.Email = reader.GetString(reader.GetOrdinal("Email"));
userProfile.CreateDateTime = reader.GetDateTime(reader.GetOrdinal("CreateDateTime"));
userProfile.ImageLocation = ReaderHelpers.GetNullableString(reader, "ImageLocation");
ADOArticle article = new ADOArticle
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
UserProfileId = reader.GetInt32(reader.GetOrdinal("UserProfileId")),
Author = reader.GetString(reader.GetOrdinal("Author")),
Publisher = reader.GetString(reader.GetOrdinal("Publisher")),
CurrentsId = reader.GetString(reader.GetOrdinal("CurrentsId")),
Title = reader.GetString(reader.GetOrdinal("Title")),
Description = ReaderHelpers.GetNullableString(reader, "Description"),
Url = reader.GetString(reader.GetOrdinal("Url")),
UserTitle = ReaderHelpers.GetNullableString(reader, "UserTitle"),
Content = ReaderHelpers.GetNullableString(reader, "Content"),
CreateDateTime = ReaderHelpers.GetNullableDateTime(reader, "CreateDateTime"),
Image = ReaderHelpers.GetNullableString(reader, "Image"),
Language = ReaderHelpers.GetNullableString(reader, "Language"),
Published = ReaderHelpers.GetNullableDateTime(reader, "Published"),
Objectivity = ReaderHelpers.GetNullableDouble(reader, "Objectivity"),
Sentimentality = ReaderHelpers.GetNullableDouble(reader, "Sentimentality"),
Categories = new List <ADOCategory>(),
ArticleReferences = new List <ADOArticleReference>(),
UserProfile = userProfile,
};
articles.Add(article);
}
reader.Close();
}
else
{
return(null);
}
}
}
}
foreach (var article in articles)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT
ac.Id AS ArticleCategoryId, c.Id AS CategoryId, c.[Name]
FROM Article a
LEFT JOIN ArticleCategory ac ON ac.ArticleId = a.Id
JOIN Category c ON c.Id = ac.CategoryID
WHERE a.Id = @id
";
cmd.Parameters.AddWithValue("@id", article.Id);
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
ADOCategory category = new ADOCategory
{
Id = reader.GetInt32(reader.GetOrdinal("CategoryId")),
Name = reader.GetString(reader.GetOrdinal("Name")),
};
article.Categories.Add(category);
}
}
}
}
}
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT ar.Id AS ArticleReferenceId,
ar.ReferenceArticleId
FROM ArticleReference ar
LEFT JOIN Article a ON ar.ArticleId = a.Id
WHERE a.Id = @id
";
cmd.Parameters.AddWithValue("@id", article.Id);
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
ADOArticleReference articleReference = new ADOArticleReference
{
Id = reader.GetInt32(reader.GetOrdinal("ArticleReferenceId")),
ArticleId = article.Id,
ReferenceArticleId = reader.GetInt32(reader.GetOrdinal("ReferenceArticleId")),
};
article.ArticleReferences.Add(articleReference);
}
}
}
}
}
if (article.ArticleReferences != null)
{
foreach (var articleReference in article.ArticleReferences)
{
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT
a.Id, a.Author, a.[Image],
a.Content, a.CreateDateTime,
a.CurrentsId, a.[Description],
a.[Language], a.Objectivity,
a.Publisher, a.Sentimentality,
a.[Url], a.UserTitle, a.Title,
a.Published
FROM Article a
RIGHT JOIN ArticleReference ar ON ar.ReferenceArticleId = a.Id
WHERE a.Id = @id
";
cmd.Parameters.AddWithValue("@id", articleReference.ReferenceArticleId);
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
ADOArticle referenceArticle = new ADOArticle
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
UserProfileId = article.UserProfileId,
Author = ReaderHelpers.GetNullableString(reader, "Author"),
Publisher = ReaderHelpers.GetNullableString(reader, "Publisher"),
CurrentsId = ReaderHelpers.GetNullableString(reader, "CurrentsId"),
Title = ReaderHelpers.GetNullableString(reader, "Title"),
Description = ReaderHelpers.GetNullableString(reader, "Description"),
Url = ReaderHelpers.GetNullableString(reader, "Url"),
UserTitle = ReaderHelpers.GetNullableString(reader, "UserTitle"),
Content = ReaderHelpers.GetNullableString(reader, "Content"),
CreateDateTime = ReaderHelpers.GetNullableDateTime(reader, "CreateDateTime"),
Image = ReaderHelpers.GetNullableString(reader, "Image"),
Language = ReaderHelpers.GetNullableString(reader, "Language"),
Published = ReaderHelpers.GetNullableDateTime(reader, "Published"),
Objectivity = ReaderHelpers.GetNullableDouble(reader, "Objectivity"),
Sentimentality = ReaderHelpers.GetNullableDouble(reader, "Sentimentality"),
Categories = new List <ADOCategory>(),
UserProfile = userProfile,
};
articleReference.ReferenceArticle = referenceArticle;
}
}
}
}
}
using (SqlConnection conn = Connection)
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT
ac.Id AS ArticleCategoryId, c.Id AS CategoryId, c.[Name]
FROM Article a
LEFT JOIN ArticleCategory ac ON ac.ArticleId = a.Id
JOIN Category c ON c.Id = ac.CategoryID
WHERE a.Id = @id
";
cmd.Parameters.AddWithValue("@id", articleReference.ReferenceArticle.Id);
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
ADOCategory category = new ADOCategory
{
Id = reader.GetInt32(reader.GetOrdinal("CategoryId")),
Name = reader.GetString(reader.GetOrdinal("Name")),
};
articleReference.ReferenceArticle.Categories.Add(category);
}
}
}
}
}
}
}
}
return(articles);
}
