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