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