public ArticleInfo Get(Int32 id) { ArticleInfo article = new ArticleInfo(); String sqlExpression = "sp_GetArticleById"; CCountryGateway countryGateway = new CCountryGateway(); CTagGateway tagGateway = new CTagGateway(); using (SqlConnection conn = CDbConnection.GetConnection()) { SqlCommand command = new SqlCommand(sqlExpression, conn); command.CommandType = CommandType.StoredProcedure; SqlParameter idParam = new SqlParameter { ParameterName = "@articleId", Value = id }; command.Parameters.Add(idParam); using (var reader = command.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { Int32 countryId = (Int32)reader["CountryID"]; article.ID = (Int32)reader["ID"]; article.Url = (String)reader["URL"]; article.Title = (String)reader["Title"]; article.Content = (String)reader["Content"]; article.Html = (byte[])reader["HTML"]; article.Date = (DateTime)reader[5]; article.Country = countryGateway.Get(countryId); article.Tags = tagGateway.GetAllRelatedToArticle(article.ID).Select(x => tagGateway.Get(x)).ToArray(); } } } } return(article); }
public List <ArticleInfo> GetByTag(CTag tag) { List <ArticleInfo> articles = new List <ArticleInfo>(); CCountryGateway countryGateway = new CCountryGateway(); String sqlExpression = "sp_GetArticleByTag"; using (SqlConnection conn = CDbConnection.GetConnection()) { SqlCommand cmd = new SqlCommand(sqlExpression, conn); cmd.CommandType = CommandType.StoredProcedure; SqlParameter tagParam = new SqlParameter { ParameterName = "@tag", Value = tag.ID }; cmd.Parameters.Add(tagParam); using (var reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { ArticleInfo article = new ArticleInfo(); article.Country = countryGateway.Get((Int32)reader["CountryID"]); article.Date = (DateTime)reader["Date"]; article.Content = (String)reader["Content"]; article.Html = (byte[])reader["Html"]; article.Url = (String)reader["Url"]; article.Title = (String)reader["Title"]; article.ID = (Int32)reader["ID"]; articles.Add(article); } } } } return(articles); }