public bool InsertComment(ArticleComment comment) { string queryString = "INSERT INTO [MLB].[dbo].[ArticleComment] " + " ([ArticleId],[PosterName],[PosterEmail],[PosterWebSite],[CommentText],[RepyToCommentId]) " + " VALUES (@ArticleId,@PosterName,@PosterEmail,@PosterWebSite,@CommentText,@RepyToCommentId)"; using (SqlConnection connection = new SqlConnection(Base.conn)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(queryString, connection); command.Parameters.AddWithValue("@ArticleId", comment.ArticleId); command.Parameters.AddWithValue("@PosterName", comment.PosterName); command.Parameters.AddWithValue("@PosterEmail", comment.PosterEmail); command.Parameters.AddWithValue("@PosterWebSite", comment.PosterWebSite); command.Parameters.AddWithValue("@CommentText", comment.Text); command.Parameters.AddWithValue("@RepyToCommentId", comment.RepyToCommentId); try { connection.Open(); command.ExecuteNonQuery(); } catch (Exception ex) { DIYError sError = new DIYError(ex); } } return(true); }
public void LoadArticle(int articleId) { string queryString = "SELECT [ArticleId],[CategoryId],[SecondLevCategoryId],[ThirdLevCategoryId],[Name] " + ",[Title],[Description],[Author],[Keywords],[MainContent],[SideContent] " + ",[ListItemContent],[ViewRequests],[URLLink] " + "FROM [MLB].[dbo].[Article] " + "WHERE ArticleId = " + articleId; using (SqlConnection connection = new SqlConnection(Base.conn)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(queryString, connection); try { connection.Open(); SqlDataReader reader = command.ExecuteReader(); LoadArticle(reader); reader.Close(); } catch (Exception ex) { DIYError sError = new DIYError(ex); } } }
public void LoadArticle(string articleName, int articleId) { string queryString = "sp_LoadArticle"; using (SqlConnection connection = new SqlConnection(Base.conn)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(queryString, connection); command.CommandType = CommandType.StoredProcedure; if (!String.IsNullOrEmpty(articleName)) { command.Parameters.Add(new SqlParameter("@articleName", articleName)); } if (articleId > 0) { command.Parameters.Add(new SqlParameter("@articleId", articleName)); } try { connection.Open(); SqlDataReader reader = command.ExecuteReader(); LoadArticle(reader); reader.Close(); } catch (Exception ex) { DIYError sError = new DIYError(ex); } } }
public bool InsertComment(ArticleComment comment) { string queryString = "INSERT INTO [MLB].[dbo].[ArticleComment] " + " ([ArticleId],[PosterName],[PosterEmail],[PosterWebSite],[CommentText],[RepyToCommentId]) " + " VALUES (@ArticleId,@PosterName,@PosterEmail,@PosterWebSite,@CommentText,@RepyToCommentId)"; using (SqlConnection connection = new SqlConnection(Base.conn)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(queryString, connection); command.Parameters.AddWithValue("@ArticleId", comment.ArticleId); command.Parameters.AddWithValue("@PosterName", comment.PosterName); command.Parameters.AddWithValue("@PosterEmail", comment.PosterEmail); command.Parameters.AddWithValue("@PosterWebSite", comment.PosterWebSite); command.Parameters.AddWithValue("@CommentText", comment.Text); command.Parameters.AddWithValue("@RepyToCommentId", comment.RepyToCommentId); try { connection.Open(); command.ExecuteNonQuery(); } catch (Exception ex) { DIYError sError = new DIYError(ex); } } return true; }
public ArticleList PostList(int categoryRowId, int currentPage, int numberPerPage, int categoryLev) { ArticleList al = new ArticleList(); al.ListItems = new List <Article>(); al.CurrentPage = currentPage; string queryString = "sp_LoadPostList"; using (SqlConnection connection = new SqlConnection(Base.conn)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(queryString, connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add(new SqlParameter("@CategoryRowId", categoryRowId)); try { connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Article a = new Article(); a.ArticleId = reader.GetInt32(0); a.Name = reader.GetString(1); a.Title = reader.GetStringSafe(2); a.Description = reader.GetStringSafe(3); a.ListItemContent = reader.GetStringSafe(4); a.ViewRequests = reader.GetInt32(5); a.URLLink = reader.GetString(6); a.NameId = reader.GetString(7); a.CategoryRowId = reader.GetInt32(8); a.CategoryId = reader.GetInt32Safe(9); a.SecondLevCategoryId = reader.GetInt32Safe(10); a.ThirdLevCategoryId = reader.GetInt32Safe(11); a.CategoryName = reader.GetString(12); a.CategoryUrl = reader.GetString(13); a.SecondLevCategoryName = reader.GetStringSafe(14); a.SecondLevCategoryUrl = reader.GetStringSafe(15); a.ThirdLevCategoryName = reader.GetStringSafe(16); a.ThirdLevCategoryUrl = reader.GetStringSafe(17); a.CreatedDate = reader.GetDateTime(18); al.ListItems.Add(a); } reader.Close(); } catch (Exception ex) { DIYError sError = new DIYError(ex); } } al.TotalItems = 25; return(al); }
public List <Article> CategoryArticles(int categoryId, int categoryLevel) { List <Article> articles = new List <Article>(); string queryString = "SELECT * FROM [Article]"; string whereSql = ""; switch (categoryLevel) { case 1: whereSql = "WHERE CategoryId = " + categoryId; break; case 2: whereSql = "WHERE SecondLevCategoryId = " + categoryId; break; case 3: whereSql = "WHERE ThirdLevCategoryId = " + categoryId; break; case 4: whereSql = "WHERE CategoryId = " + categoryId; break; default: break; } queryString += whereSql; using (SqlConnection connection = new SqlConnection(Base.conn)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(queryString, connection); //command.Parameters.AddWithValue("leagueId", leagueId); try { connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Article article = new Article(); articles.Add(article); } reader.Close(); } catch (Exception ex) { DIYError sError = new DIYError(ex); } } return(articles); }
public List <CustomHtmlLink> MostViewed(int categoryId, int returnAmount) { List <CustomHtmlLink> viewList = new List <CustomHtmlLink>(); string queryString = "MostViewed"; using (SqlConnection connection = new SqlConnection(Base.conn)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(queryString, connection); command.CommandType = System.Data.CommandType.StoredProcedure; command.Parameters.AddWithValue("@categoryId", categoryId); command.Parameters.AddWithValue("@returnAmount", returnAmount); try { connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { CustomHtmlLink link = new CustomHtmlLink(); link.Href = "/" + reader.GetStringSafe(0); if (!reader.IsDBNull(1)) { link.Href += reader.GetStringSafe(1); } if (!reader.IsDBNull(2)) { link.Href += reader.GetStringSafe(2); } link.LinkText = reader.GetStringSafe(3); link.Title = reader.GetStringSafe(4); viewList.Add(link); } reader.Close(); } catch (Exception ex) { DIYError sError = new DIYError(ex); } } return(viewList); }
//TODO: THIS NEEDS TO BE UPDATED WHEN sp IS CALLED TO PULL ARTICLE //THIS ENITY FRAME WORK STUFF IS SLOW AND LAME, NEEDS BE REMOVE ONCE SITE IS RUNNING //DON'T FORGET TO REMOVE ALL INSTANCES CALLING public static void InsertArticleViewRequest(int articleId) { string queryCheckGame = "UPDATE [dbo].[Article] SET [ViewRequests] = [ViewRequests]+1 WHERE ArticleId = @ArticleId"; using (SqlConnection connection = new SqlConnection(Base.conn)) { SqlCommand command = new SqlCommand(queryCheckGame, connection); command.Parameters.AddWithValue("@ArticleId", articleId); try { connection.Open(); command.ExecuteNonQuery(); } catch (Exception ex) { DIYError sError = new DIYError(ex); } } }
public List<Category> AllCategory() { List<Category> categoryList = new List<Category>(); string queryString = "SELECT * FROM Category"; using (SqlConnection connection = new SqlConnection(Base.conn)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(queryString, connection); //command.Parameters.AddWithValue("leagueId", leagueId); try { connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Category category = new Category(); category.CategoryRowId = reader.GetInt32Safe(0); category.TopNavIndex = reader.GetInt32Safe(1); category.CategoryId = reader.GetInt32Safe(2); category.SecondLevCategoryId = reader.GetInt32Safe(3); category.ThirdLevCategoryId = reader.GetInt32Safe(4); category.CategoryName = reader.GetStringSafe(5); category.CategoryUrl = reader.GetStringSafe(6); category.SecondLevCategoryName = reader.GetStringSafe(7); category.SecondLevCategoryUrl = reader.GetStringSafe(8); category.ThirdLevCategoryName = reader.GetStringSafe(9); category.ThirdLevCategoryUrl = reader.GetStringSafe(10); categoryList.Add(category); } reader.Close(); } catch (Exception ex) { DIYError sError = new DIYError(ex); } } return categoryList; }
public List <Category> AllCategory() { List <Category> categoryList = new List <Category>(); string queryString = "SELECT * FROM Category"; using (SqlConnection connection = new SqlConnection(Base.conn)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(queryString, connection); //command.Parameters.AddWithValue("leagueId", leagueId); try { connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Category category = new Category(); category.CategoryRowId = reader.GetInt32Safe(0); category.TopNavIndex = reader.GetInt32Safe(1); category.CategoryId = reader.GetInt32Safe(2); category.SecondLevCategoryId = reader.GetInt32Safe(3); category.ThirdLevCategoryId = reader.GetInt32Safe(4); category.CategoryName = reader.GetStringSafe(5); category.CategoryUrl = reader.GetStringSafe(6); category.SecondLevCategoryName = reader.GetStringSafe(7); category.SecondLevCategoryUrl = reader.GetStringSafe(8); category.ThirdLevCategoryName = reader.GetStringSafe(9); category.ThirdLevCategoryUrl = reader.GetStringSafe(10); categoryList.Add(category); } reader.Close(); } catch (Exception ex) { DIYError sError = new DIYError(ex); } } return(categoryList); }
public bool InsertArticle(Article article) { string queryString = "INSERT INTO [MLB].[dbo].[Article] " + "([CategoryId],[SecondLevCategoryId],[ThirdLevCategoryId],[Name],[Title],[Description],[Author],[Keywords],[MainContent],[SideContent],[ListItemContent],[ViewRequests],[URLLink],[NameId]) " + "VALUES " + "(@CategoryId,@SecondLevCategoryId,@ThirdLevCategoryId,@Name,@Title,@Description,@Author,@Keywords,@MainContent,@SideContent,@ListItemContent,@ViewRequests,@URLLink,@NameId)"; using (SqlConnection connection = new SqlConnection(Base.conn)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(queryString, connection); command.Parameters.AddWithValue("@CategoryId", article.CategoryId); command.Parameters.AddWithValue("@SecondLevCategoryId", article.SecondLevCategoryId); command.Parameters.AddWithValue("@ThirdLevCategoryId", article.ThirdLevCategoryId); command.Parameters.AddWithValue("@Name", article.Name); command.Parameters.AddWithValue("@Title", article.Title); command.Parameters.AddWithValue("@Description", article.Description); command.Parameters.AddWithValue("@Author", article.Author); command.Parameters.AddWithValue("@Keywords", article.Keywords); command.Parameters.AddWithValue("@MainContent", article.MainContent); command.Parameters.AddWithValue("@SideContent", article.SideContent); command.Parameters.AddWithValue("@ListItemContent", article.ListItemContent); command.Parameters.AddWithValue("@ViewRequests", article.ViewRequests); command.Parameters.AddWithValue("@URLLink", article.URLLink); command.Parameters.AddWithValue("@NameId", article.NameId); try { connection.Open(); command.ExecuteNonQuery(); } catch (Exception ex) { DIYError sError = new DIYError(ex); } } return(true); }
//public int TrackingId; //public string Session, IP, URL; //public DateTime CreatedDate; //public Tracking(string session, string ip, string url){ // this.Session = session; // this.IP = ip; // this.URL = url; //} //public Tracking(string session, string ip, string url, bool insert) //{ // this.Session = session; // this.IP = ip; // this.URL = url; // if (insert) // { // this.InsertTracking(); // } //} public static void InsertTracking(string session, string ip, string url) { string queryCheckGame = "INSERT INTO [dbo].[Tracking] " + "([Session] ,[IP] ,[URL]) " + "VALUES (@Session ,@IP ,@URL)"; using (SqlConnection connection = new SqlConnection(Base.conn)) { SqlCommand command = new SqlCommand(queryCheckGame, connection); command.Parameters.AddWithValue("@Session", session); command.Parameters.AddWithValue("@IP", ip); command.Parameters.AddWithValue("@URL", url); try { connection.Open(); command.ExecuteNonQuery(); } catch (Exception ex) { DIYError sError = new DIYError(ex); } } }
public List <ArticleComment> ArticleComments(int articleId) { List <ArticleComment> comments = new List <ArticleComment>(); string queryString = "SELECT [CommentId],[ArticleId],[CommentText],[CreatedDate],[RepyToCommentId] FROM [MLB].[dbo].[ArticleComment] WHERE ArticleId = " + articleId + " ORDER BY CreatedDate DESC"; using (SqlConnection connection = new SqlConnection(Base.conn)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(queryString, connection); //command.Parameters.AddWithValue("leagueId", leagueId); try { connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { comments.Add(new ArticleComment { CommentId = reader.GetInt32(0), ArticleId = reader.GetInt32(1), Text = reader.GetStringSafe(2), CreatedDate = reader.GetDateTime(3), RepyToCommentId = reader.GetInt32Safe(4) }); } reader.Close(); } catch (Exception ex) { DIYError sError = new DIYError(ex); } } return(comments); }
public List<ArticleComment> ArticleComments(int articleId) { List<ArticleComment> comments = new List<ArticleComment>(); string queryString = "SELECT [CommentId],[ArticleId],[CommentText],[CreatedDate],[RepyToCommentId] FROM [MLB].[dbo].[ArticleComment] WHERE ArticleId = " + articleId + " ORDER BY CreatedDate DESC"; using (SqlConnection connection = new SqlConnection(Base.conn)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(queryString, connection); //command.Parameters.AddWithValue("leagueId", leagueId); try { connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { comments.Add(new ArticleComment { CommentId = reader.GetInt32(0), ArticleId = reader.GetInt32(1), Text = reader.GetStringSafe(2), CreatedDate = reader.GetDateTime(3), RepyToCommentId = reader.GetInt32Safe(4) }); } reader.Close(); } catch (Exception ex) { DIYError sError = new DIYError(ex); } } return comments; }
public ArticleList PostList(int categoryRowId, int currentPage, int numberPerPage, int categoryLev) { ArticleList al = new ArticleList(); al.ListItems = new List<Article>(); al.CurrentPage = currentPage; string queryString = "sp_LoadPostList"; using (SqlConnection connection = new SqlConnection(Base.conn)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(queryString, connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add(new SqlParameter("@CategoryRowId", categoryRowId)); try { connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Article a = new Article(); a.ArticleId = reader.GetInt32(0); a.Name = reader.GetString(1); a.Title = reader.GetStringSafe(2); a.Description = reader.GetStringSafe(3); a.ListItemContent = reader.GetStringSafe(4); a.ViewRequests = reader.GetInt32(5); a.URLLink = reader.GetString(6); a.NameId = reader.GetString(7); a.CategoryRowId = reader.GetInt32(8); a.CategoryId = reader.GetInt32Safe(9); a.SecondLevCategoryId = reader.GetInt32Safe(10); a.ThirdLevCategoryId = reader.GetInt32Safe(11); a.CategoryName = reader.GetString(12); a.CategoryUrl = reader.GetString(13); a.SecondLevCategoryName = reader.GetStringSafe(14); a.SecondLevCategoryUrl = reader.GetStringSafe(15); a.ThirdLevCategoryName = reader.GetStringSafe(16); a.ThirdLevCategoryUrl = reader.GetStringSafe(17); a.CreatedDate = reader.GetDateTime(18); al.ListItems.Add(a); } reader.Close(); } catch (Exception ex) { DIYError sError = new DIYError(ex); } } al.TotalItems = 25; return al; }
public List<CustomHtmlLink> RelatedArticleLinks(Category cat, string linkPrefix, int categoryLevel) { List<CustomHtmlLink> linkList = new List<CustomHtmlLink>(); //List<Article> articles = new List<Article>(); string queryString = "SELECT [Name],[Title],[URLLink] FROM [MLB].[dbo].[Article]"; string whereSql = ""; switch (categoryLevel) { case 1: whereSql = "WHERE CategoryId = " + cat.CategoryId; break; case 2: whereSql = "WHERE SecondLevCategoryId = " + cat.SecondLevCategoryId + " AND CategoryId= " + cat.CategoryId + " AND ThirdLevCategoryId = 0"; break; case 3: whereSql = "WHERE ThirdLevCategoryId = " + cat.ThirdLevCategoryId; break; case 4: whereSql = "WHERE CategoryId = " + cat.CategoryId; break; default: break; } queryString += whereSql; using (SqlConnection connection = new SqlConnection(Base.conn)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(queryString, connection); //command.Parameters.AddWithValue("leagueId", leagueId); try { connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { CustomHtmlLink link = new CustomHtmlLink { LinkText = reader.GetString(0), Title = reader.GetString(1), Href = linkPrefix + reader.GetString(2) }; linkList.Add(link); } reader.Close(); } catch (Exception ex) { DIYError sError = new DIYError(ex); } } return linkList; //if (!String.IsNullOrEmpty(cat.ThirdLevCategoryUrl)) //{ // CustomHtmlLink link = new CustomHtmlLink(); // link.LinkText = cat.ThirdLevCategoryName; // link.Href = "/" + linkPrefix + "/" + cat.CategoryUrl + "/" + cat.SecondLevCategoryUrl + "/" + cat.ThirdLevCategoryUrl; // link.Title = cat.ThirdLevCategoryName; // linkList.Add(link); // foreach (Article art in CategoryArticles(cat.ThirdLevCategoryId, 3)) // { // } // //List<Article> articles = // return linkList; //} //if (!String.IsNullOrEmpty(cat.SecondLevCategoryUrl)) //{ // //List<Article> articles = // CustomHtmlLink link = new CustomHtmlLink(); // link.LinkText = cat.SecondLevCategoryName; // link.Href = "/" + linkPrefix + "/" + cat.CategoryUrl + "/" + cat.SecondLevCategoryUrl; // link.Title = cat.SecondLevCategoryName; // linkList.Add(link); // return linkList; //} //if (!String.IsNullOrEmpty(cat.CategoryUrl)) //{ // //List<Article> articles = // CustomHtmlLink link = new CustomHtmlLink(); // link.LinkText = cat.CategoryName; // link.Href = "/" + linkPrefix + "/" + cat.CategoryUrl; // link.Title = cat.CategoryName; // linkList.Add(link); // return linkList; //} //if (!String.IsNullOrEmpty(cat.CategoryUrl)) //{ // CustomHtmlLink link = new CustomHtmlLink(); // link.LinkText = cat.CategoryName; // link.Href = "/" + linkPrefix + "/" + cat.CategoryUrl; // link.Title = cat.CategoryName; // linkList.Add(link); //} //if (String.IsNullOrEmpty(cat.SecondLevCategoryUrl)) //{ // CustomHtmlLink link = new CustomHtmlLink(); // link.LinkText = cat.SecondLevCategoryName; // link.Href = "/" + linkPrefix + "/" + cat.CategoryUrl + "/" + cat.SecondLevCategoryUrl; // link.Title = cat.SecondLevCategoryName; // linkList.Add(link); //} //return linkList; }
public bool InsertCategory(Category category) { if (category.CategoryId == 0) { string maxQuery = "SELECT MAX(CategoryId) FROM Category"; using (SqlConnection connection = new SqlConnection(Base.conn)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(maxQuery, connection); //command.Parameters.AddWithValue("leagueId", leagueId); try { connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { category.CategoryId = reader.GetInt32(0) + 1; } } catch (Exception ex) { DIYError sError = new DIYError(ex); } } } string queryString = "INSERT INTO [MLB].[dbo].[Category] " + "([CategoryId],[SecondLevCategoryId],[ThirdLevCategoryId],[CategoryName],[CategoryUrl],[SecondLevCategoryName],[SecondLevCategoryUrl],[ThirdLevCategoryName],[ThirdLevCategoryUrl]) " + "VALUES " + "(@CategoryId,@SecondLevCategoryId,@ThirdLevCategoryId,@CategoryName,@CategoryUrl,@SecondLevCategoryName,@SecondLevCategoryUrl,@ThirdLevCategoryName,@ThirdLevCategoryUrl)"; if (!string.IsNullOrEmpty(category.SecondLevCategoryName) && !string.IsNullOrEmpty(category.SecondLevCategoryUrl)) { if (category.SecondLevCategoryId == 0) { string maxQuery = "SELECT MAX(SecondLevCategoryId) FROM Category"; using (SqlConnection connection = new SqlConnection(Base.conn)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(maxQuery, connection); //command.Parameters.AddWithValue("leagueId", leagueId); try { connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { category.SecondLevCategoryId = reader.GetInt32(0) + 1; } } catch (Exception ex) { DIYError sError = new DIYError(ex); } } } } else { category.SecondLevCategoryName = ""; category.SecondLevCategoryUrl = ""; } if (!string.IsNullOrEmpty(category.ThirdLevCategoryName) && !string.IsNullOrEmpty(category.ThirdLevCategoryUrl)) { if (category.ThirdLevCategoryId == 0) { string maxQuery = "SELECT MAX(ThirdLevCategoryId) FROM Category"; using (SqlConnection connection = new SqlConnection(Base.conn)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(maxQuery, connection); //command.Parameters.AddWithValue("leagueId", leagueId); try { connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { category.ThirdLevCategoryId = reader.GetInt32(0) + 1; } } catch (Exception ex) { DIYError sError = new DIYError(ex); } } } } else { category.ThirdLevCategoryName = ""; category.ThirdLevCategoryUrl = ""; } //if (string.IsNullOrEmpty(category.SecondLevCategoryUrl)) //{ // category.SecondLevCategoryUrl = ""; //} using (SqlConnection connection = new SqlConnection(Base.conn)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(queryString, connection); command.Parameters.AddWithValue("@CategoryId", category.CategoryId); command.Parameters.AddWithValue("@SecondLevCategoryId", category.SecondLevCategoryId); command.Parameters.AddWithValue("@ThirdLevCategoryId", category.ThirdLevCategoryId); command.Parameters.AddWithValue("@CategoryName", category.CategoryName); command.Parameters.AddWithValue("@CategoryUrl", category.CategoryUrl); command.Parameters.AddWithValue("@SecondLevCategoryName", category.SecondLevCategoryName); command.Parameters.AddWithValue("@SecondLevCategoryUrl", category.SecondLevCategoryUrl); command.Parameters.AddWithValue("@ThirdLevCategoryName", category.ThirdLevCategoryName); command.Parameters.AddWithValue("@ThirdLevCategoryUrl", category.ThirdLevCategoryUrl); try { connection.Open(); command.ExecuteNonQuery(); } catch (Exception ex) { DIYError sError = new DIYError(ex); } } return(true); }
public bool InsertCategory(Category category) { if (category.CategoryId == 0) { string maxQuery = "SELECT MAX(CategoryId) FROM Category"; using (SqlConnection connection = new SqlConnection(Base.conn)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(maxQuery, connection); //command.Parameters.AddWithValue("leagueId", leagueId); try { connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { category.CategoryId = reader.GetInt32(0) + 1; } } catch (Exception ex) { DIYError sError = new DIYError(ex); } } } string queryString = "INSERT INTO [MLB].[dbo].[Category] " + "([CategoryId],[SecondLevCategoryId],[ThirdLevCategoryId],[CategoryName],[CategoryUrl],[SecondLevCategoryName],[SecondLevCategoryUrl],[ThirdLevCategoryName],[ThirdLevCategoryUrl]) " + "VALUES " + "(@CategoryId,@SecondLevCategoryId,@ThirdLevCategoryId,@CategoryName,@CategoryUrl,@SecondLevCategoryName,@SecondLevCategoryUrl,@ThirdLevCategoryName,@ThirdLevCategoryUrl)"; if (!string.IsNullOrEmpty(category.SecondLevCategoryName) && !string.IsNullOrEmpty(category.SecondLevCategoryUrl)) { if (category.SecondLevCategoryId == 0) { string maxQuery = "SELECT MAX(SecondLevCategoryId) FROM Category"; using (SqlConnection connection = new SqlConnection(Base.conn)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(maxQuery, connection); //command.Parameters.AddWithValue("leagueId", leagueId); try { connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { category.SecondLevCategoryId = reader.GetInt32(0) + 1; } } catch (Exception ex) { DIYError sError = new DIYError(ex); } } } } else { category.SecondLevCategoryName = ""; category.SecondLevCategoryUrl = ""; } if (!string.IsNullOrEmpty(category.ThirdLevCategoryName) && !string.IsNullOrEmpty(category.ThirdLevCategoryUrl)) { if (category.ThirdLevCategoryId == 0) { string maxQuery = "SELECT MAX(ThirdLevCategoryId) FROM Category"; using (SqlConnection connection = new SqlConnection(Base.conn)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(maxQuery, connection); //command.Parameters.AddWithValue("leagueId", leagueId); try { connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { category.ThirdLevCategoryId = reader.GetInt32(0) + 1; } } catch (Exception ex) { DIYError sError = new DIYError(ex); } } } } else { category.ThirdLevCategoryName = ""; category.ThirdLevCategoryUrl = ""; } //if (string.IsNullOrEmpty(category.SecondLevCategoryUrl)) //{ // category.SecondLevCategoryUrl = ""; //} using (SqlConnection connection = new SqlConnection(Base.conn)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(queryString, connection); command.Parameters.AddWithValue("@CategoryId", category.CategoryId); command.Parameters.AddWithValue("@SecondLevCategoryId", category.SecondLevCategoryId); command.Parameters.AddWithValue("@ThirdLevCategoryId", category.ThirdLevCategoryId); command.Parameters.AddWithValue("@CategoryName", category.CategoryName); command.Parameters.AddWithValue("@CategoryUrl", category.CategoryUrl); command.Parameters.AddWithValue("@SecondLevCategoryName", category.SecondLevCategoryName); command.Parameters.AddWithValue("@SecondLevCategoryUrl", category.SecondLevCategoryUrl); command.Parameters.AddWithValue("@ThirdLevCategoryName", category.ThirdLevCategoryName); command.Parameters.AddWithValue("@ThirdLevCategoryUrl", category.ThirdLevCategoryUrl); try { connection.Open(); command.ExecuteNonQuery(); } catch (Exception ex) { DIYError sError = new DIYError(ex); } } return true; }
public List <CustomHtmlLink> RelatedArticleLinks(Category cat, string linkPrefix, int categoryLevel) { List <CustomHtmlLink> linkList = new List <CustomHtmlLink>(); //List<Article> articles = new List<Article>(); string queryString = "SELECT [Name],[Title],[URLLink] FROM [MLB].[dbo].[Article]"; string whereSql = ""; switch (categoryLevel) { case 1: whereSql = "WHERE CategoryId = " + cat.CategoryId; break; case 2: whereSql = "WHERE SecondLevCategoryId = " + cat.SecondLevCategoryId + " AND CategoryId= " + cat.CategoryId + " AND ThirdLevCategoryId = 0"; break; case 3: whereSql = "WHERE ThirdLevCategoryId = " + cat.ThirdLevCategoryId; break; case 4: whereSql = "WHERE CategoryId = " + cat.CategoryId; break; default: break; } queryString += whereSql; using (SqlConnection connection = new SqlConnection(Base.conn)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(queryString, connection); //command.Parameters.AddWithValue("leagueId", leagueId); try { connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { CustomHtmlLink link = new CustomHtmlLink { LinkText = reader.GetString(0), Title = reader.GetString(1), Href = linkPrefix + reader.GetString(2) }; linkList.Add(link); } reader.Close(); } catch (Exception ex) { DIYError sError = new DIYError(ex); } } return(linkList); //if (!String.IsNullOrEmpty(cat.ThirdLevCategoryUrl)) //{ // CustomHtmlLink link = new CustomHtmlLink(); // link.LinkText = cat.ThirdLevCategoryName; // link.Href = "/" + linkPrefix + "/" + cat.CategoryUrl + "/" + cat.SecondLevCategoryUrl + "/" + cat.ThirdLevCategoryUrl; // link.Title = cat.ThirdLevCategoryName; // linkList.Add(link); // foreach (Article art in CategoryArticles(cat.ThirdLevCategoryId, 3)) // { // } // //List<Article> articles = // return linkList; //} //if (!String.IsNullOrEmpty(cat.SecondLevCategoryUrl)) //{ // //List<Article> articles = // CustomHtmlLink link = new CustomHtmlLink(); // link.LinkText = cat.SecondLevCategoryName; // link.Href = "/" + linkPrefix + "/" + cat.CategoryUrl + "/" + cat.SecondLevCategoryUrl; // link.Title = cat.SecondLevCategoryName; // linkList.Add(link); // return linkList; //} //if (!String.IsNullOrEmpty(cat.CategoryUrl)) //{ // //List<Article> articles = // CustomHtmlLink link = new CustomHtmlLink(); // link.LinkText = cat.CategoryName; // link.Href = "/" + linkPrefix + "/" + cat.CategoryUrl; // link.Title = cat.CategoryName; // linkList.Add(link); // return linkList; //} //if (!String.IsNullOrEmpty(cat.CategoryUrl)) //{ // CustomHtmlLink link = new CustomHtmlLink(); // link.LinkText = cat.CategoryName; // link.Href = "/" + linkPrefix + "/" + cat.CategoryUrl; // link.Title = cat.CategoryName; // linkList.Add(link); //} //if (String.IsNullOrEmpty(cat.SecondLevCategoryUrl)) //{ // CustomHtmlLink link = new CustomHtmlLink(); // link.LinkText = cat.SecondLevCategoryName; // link.Href = "/" + linkPrefix + "/" + cat.CategoryUrl + "/" + cat.SecondLevCategoryUrl; // link.Title = cat.SecondLevCategoryName; // linkList.Add(link); //} //return linkList; }
public List<Article> CategoryArticles(int categoryId, int categoryLevel) { List<Article> articles = new List<Article>(); string queryString = "SELECT * FROM [Article]"; string whereSql = ""; switch (categoryLevel) { case 1: whereSql = "WHERE CategoryId = " + categoryId; break; case 2: whereSql = "WHERE SecondLevCategoryId = " + categoryId; break; case 3: whereSql = "WHERE ThirdLevCategoryId = " + categoryId; break; case 4: whereSql = "WHERE CategoryId = " + categoryId; break; default: break; } queryString += whereSql; using (SqlConnection connection = new SqlConnection(Base.conn)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(queryString, connection); //command.Parameters.AddWithValue("leagueId", leagueId); try { connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Article article = new Article(); articles.Add(article); } reader.Close(); } catch (Exception ex) { DIYError sError = new DIYError(ex); } } return articles; }
public bool InsertArticle(Article article) { string queryString = "INSERT INTO [MLB].[dbo].[Article] " + "([CategoryId],[SecondLevCategoryId],[ThirdLevCategoryId],[Name],[Title],[Description],[Author],[Keywords],[MainContent],[SideContent],[ListItemContent],[ViewRequests],[URLLink],[NameId]) " + "VALUES " + "(@CategoryId,@SecondLevCategoryId,@ThirdLevCategoryId,@Name,@Title,@Description,@Author,@Keywords,@MainContent,@SideContent,@ListItemContent,@ViewRequests,@URLLink,@NameId)"; using (SqlConnection connection = new SqlConnection(Base.conn)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(queryString, connection); command.Parameters.AddWithValue("@CategoryId", article.CategoryId); command.Parameters.AddWithValue("@SecondLevCategoryId", article.SecondLevCategoryId); command.Parameters.AddWithValue("@ThirdLevCategoryId", article.ThirdLevCategoryId); command.Parameters.AddWithValue("@Name", article.Name); command.Parameters.AddWithValue("@Title", article.Title); command.Parameters.AddWithValue("@Description", article.Description); command.Parameters.AddWithValue("@Author", article.Author); command.Parameters.AddWithValue("@Keywords", article.Keywords); command.Parameters.AddWithValue("@MainContent", article.MainContent); command.Parameters.AddWithValue("@SideContent", article.SideContent); command.Parameters.AddWithValue("@ListItemContent", article.ListItemContent); command.Parameters.AddWithValue("@ViewRequests", article.ViewRequests); command.Parameters.AddWithValue("@URLLink", article.URLLink); command.Parameters.AddWithValue("@NameId", article.NameId); try { connection.Open(); command.ExecuteNonQuery(); } catch (Exception ex) { DIYError sError = new DIYError(ex); } } return true; }
public List<CustomHtmlLink> MostViewed(int categoryId, int returnAmount) { List<CustomHtmlLink> viewList = new List<CustomHtmlLink>(); string queryString = "MostViewed"; using (SqlConnection connection = new SqlConnection(Base.conn)) { // Create the Command and Parameter objects. SqlCommand command = new SqlCommand(queryString, connection); command.CommandType = System.Data.CommandType.StoredProcedure; command.Parameters.AddWithValue("@categoryId", categoryId); command.Parameters.AddWithValue("@returnAmount", returnAmount); try { connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { CustomHtmlLink link = new CustomHtmlLink(); link.Href = "/" + reader.GetStringSafe(0); if (!reader.IsDBNull(1)) { link.Href += reader.GetStringSafe(1); } if (!reader.IsDBNull(2)) { link.Href += reader.GetStringSafe(2); } link.LinkText = reader.GetStringSafe(3); link.Title = reader.GetStringSafe(4); viewList.Add(link); } reader.Close(); } catch (Exception ex) { DIYError sError = new DIYError(ex); } } return viewList; }