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