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); }