Пример #1
0
        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);
        }
Пример #2
0
        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);
                }
            }
        }
Пример #3
0
        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);
                }
            }
        }
Пример #4
0
        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;
        }
Пример #5
0
        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);
        }
Пример #6
0
        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);
        }
Пример #7
0
        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);
        }
Пример #8
0
        //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);
                }
            }
        }
Пример #9
0
        //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);
                }

            }
        }
Пример #10
0
        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;
        }
Пример #11
0
        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);
        }
Пример #12
0
        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);
        }
Пример #13
0
        //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);
                }
            }
        }
Пример #14
0
        //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);
                }

            }
        }
Пример #15
0
        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);
        }
Пример #16
0
        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;
        }
Пример #17
0
        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;
        }
Пример #18
0
        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);
                }
            }
        }
Пример #19
0
        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;
        }
Пример #20
0
        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);
        }
Пример #21
0
        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;
        }
Пример #22
0
        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);
                }
            }
        }
Пример #23
0
        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;
        }
Пример #24
0
        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;
        }
Пример #25
0
        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;
        }
Пример #26
0
        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;
        }