public static List<CategoriesDL> GetAll() { List<CategoriesDL> categoriesList = new List<CategoriesDL>(); SqlConnection conn = new SqlConnection(BWConfig.ConnectionString); string queryString = @"SELECT DISTINCT CI.CategoryID, CI.blogCategoryID, CI.DisplayName, CI.UrlName, COUNT(DISTINCT CACR.ContractorID) as ContractorsAmount FROM CategoryInfo CI FULL OUTER JOIN (SELECT DISTINCT CategoryID, ContractorID, AreaID FROM ContractorAreaCategoryRel ) CACR ON CACR.CategoryID = CI.CategoryID WHERE CI.blogCategoryID IS NOT NULL GROUP BY CI.CategoryID, CI.blogCategoryID, CI.DisplayName, CI.UrlName"; SqlCommand command = new SqlCommand(queryString, conn); command.CommandType = CommandType.Text; SqlDataReader rdr = null; try { conn.Open(); rdr = command.ExecuteReader(); if (rdr.HasRows) { while (rdr.Read()) { int catID = Convert.ToInt32(rdr["CategoryID"]); int blogCatId = Convert.ToInt32(rdr["blogCategoryID"]); string catName = rdr["DisplayName"].ToString(); string catUrlName = rdr["UrlName"].ToString(); int contractorsAmount = Convert.ToInt32(rdr["ContractorsAmount"]); CategoriesDL categorie = new CategoriesDL(catID, blogCatId, catName, catUrlName, contractorsAmount); categoriesList.Add(categorie); } } } finally { if (rdr != null) { if (!rdr.IsClosed) rdr.Close(); rdr.Dispose(); } if (conn != null) { if (conn.State == ConnectionState.Open) conn.Close(); conn.Dispose(); } } return categoriesList; }
public static List<CategoriesDL> GetTopCategoriesByZipCode(string zipCode) { List<CategoriesDL> topCategoriesList = new List<CategoriesDL>(); SqlConnection conn = new SqlConnection(BWConfig.ConnectionString); string queryString = @"SELECT DISTINCT CI.CategoryID, CI.blogCategoryID, CI.DisplayName, CI.UrlName, COUNT(DISTINCT CACR.ContractorID) as ContractorsAmount FROM Zip Z JOIN Area A ON (Z.AreaID = A.AreaID) JOIN City C ON (A.CityID = C.CityID) JOIN BlogPostsToCities BPCity ON (C.CityID = BPCity.CityID) JOIN BlogPosts BP ON (BPCity.PostID = BP.PostID) JOIN BlogPostsToCategories BPCat ON (BP.PostID = BPCat.PostID) JOIN CategoryInfo CI ON (BPCat.BlogCatID = CI.blogCategoryID) FULL OUTER JOIN (SELECT DISTINCT CategoryID, ContractorID, AreaID FROM ContractorAreaCategoryRel ) CACR ON CACR.CategoryID = CI.CategoryID AND CACR.AreaID = Z.AreaId WHERE Z.ZipCode = @zipCode AND BP.isTopBlog = 1 GROUP BY CI.CategoryID, CI.blogCategoryID, CI.DisplayName, CI.UrlName"; SqlCommand command = new SqlCommand(queryString, conn); command.CommandType = CommandType.Text; command.Parameters.Add("zipCode", SqlDbType.Int).Value = zipCode; SqlDataReader rdr = null; try { conn.Open(); rdr = command.ExecuteReader(); if (rdr.HasRows) { while (rdr.Read()) { int catID = Convert.ToInt32(rdr["CategoryID"]); int blogCatId = Convert.ToInt32(rdr["blogCategoryID"]); string catName = rdr["DisplayName"].ToString(); string catUrlName = rdr["UrlName"].ToString(); int contractorsAmount = Convert.ToInt32(rdr["ContractorsAmount"]); CategoriesDL category = new CategoriesDL(catID, blogCatId, catName, catUrlName, contractorsAmount); topCategoriesList.Add(category); } } } finally { if (rdr != null) { if (!rdr.IsClosed) rdr.Close(); rdr.Dispose(); } if (conn != null) { if (conn.State == ConnectionState.Open) conn.Close(); conn.Dispose(); } } return topCategoriesList; }
public static CategoriesDL GetCategoryInfo(int areaId, int cityId, int categoryId) { SqlConnection conn = new SqlConnection(BWConfig.ConnectionString); SqlDataReader rdr = null; //Get breadcrumb info var queryString = "SELECT CategoryInfo.DisplayName AS CatName, CategoryInfo.UrlName AS CatUrlName, AreaInfo.UrlName AS AreaUrlName, AreaInfo.DisplayName AS AreaDisplayName, CityInfo.UrlName AS CityUrlName, CityInfo.DisplayName AS CityDisplayName " + "FROM CategoryArea INNER JOIN CategoryInfo ON CategoryArea.CategoryID = CategoryInfo.CategoryID INNER JOIN " + "AreaInfo ON CategoryArea.AreaID = AreaInfo.AreaID INNER JOIN CityInfo ON AreaInfo.CityID = CityInfo.CityID WHERE (AreaInfo.AreaID = @AREAID) AND " + "(CityInfo.CityID = @CITYID) AND (CategoryInfo.CategoryID = @CATID)"; SqlCommand command = new SqlCommand(queryString, conn); command.CommandType = CommandType.Text; command.Parameters.Add("CATID", SqlDbType.VarChar).Value = categoryId; command.Parameters.Add("CITYID", SqlDbType.VarChar).Value = cityId; command.Parameters.Add("AREAID", SqlDbType.VarChar).Value = areaId; CategoriesDL category = new CategoriesDL(-1, -1, String.Empty, String.Empty, 0); try { conn.Open(); rdr = command.ExecuteReader(); if (rdr.HasRows) { if (rdr.Read()) { category.CatUrlName = String.Concat("/", rdr["CatUrlName"], "/", rdr["CityURLName"], "/", rdr["AreaURLName"]); category.Name = rdr["CatName"].ToString(); } } } finally { if (rdr != null) { if (!rdr.IsClosed) rdr.Close(); rdr.Dispose(); } if (conn != null) { if (conn.State != ConnectionState.Closed) conn.Close(); conn.Dispose(); } } return category; }
public static List<CategoriesDL> GetTopCategoriesByAreaId(int areaId) { List<CategoriesDL> topCategoriesList = new List<CategoriesDL>(); SqlConnection conn = new SqlConnection(BWConfig.ConnectionString); string queryString = @"SELECT CI.CategoryID, CI.blogCategoryID, CI.DisplayName, CI.UrlName, COUNT(DISTINCT CC.ContractorID) as ContractorsAmount FROM CategoryArea CA JOIN Area A ON (CA.AreaID = A.AreaID) JOIN CategoryInfo CI ON (CA.CategoryID = CI.CategoryID) JOIN ContractorCategory CC ON (CC.CategoryID = CI.CategoryID) WHERE A.AreaID = @areaId AND isTopCategory = 1 GROUP BY CI.CategoryID, CI.blogCategoryID, CI.DisplayName, CI.UrlName ORDER BY CI.DisplayName"; SqlCommand command = new SqlCommand(queryString, conn); command.CommandType = CommandType.Text; command.Parameters.Add("areaId", SqlDbType.Int).Value = areaId; SqlDataReader rdr = null; try { conn.Open(); rdr = command.ExecuteReader(); if (rdr.HasRows) { while (rdr.Read()) { int catID = Convert.ToInt32(rdr["CategoryID"]); int blogCatId = Convert.ToInt32(rdr["blogCategoryID"]); string catName = rdr["DisplayName"].ToString(); string catUrlName = rdr["UrlName"].ToString(); int contractorsAmount = Convert.ToInt32(rdr["ContractorsAmount"]); CategoriesDL category = new CategoriesDL(catID, blogCatId, catName, catUrlName, contractorsAmount); topCategoriesList.Add(category); } } } finally { if (rdr != null) { if (!rdr.IsClosed) rdr.Close(); rdr.Dispose(); } if (conn != null) { if (conn.State == ConnectionState.Open) conn.Close(); conn.Dispose(); } } return topCategoriesList; }
public static CategoriesDL GetCategoryInfo(string catId, string catUrl, bool isImageUrl = false) { SqlConnection conn = new SqlConnection(BWConfig.ConnectionString); SqlDataReader rdr = null; string queryString = @"SELECT CategoryID, blogCategoryID, DisplayName, UrlName, Website FROM CategoryInfo WHERE CategoryID = @id OR UrlName = @catUrl"; SqlCommand command = new SqlCommand(queryString, conn); command.CommandType = CommandType.Text; command.Parameters.Add("id", SqlDbType.VarChar).Value = catId; command.Parameters.Add("catUrl", SqlDbType.VarChar).Value = catUrl; CategoriesDL category = new CategoriesDL(-1, -1, String.Empty, String.Empty, 0); try { conn.Open(); rdr = command.ExecuteReader(); if (rdr.HasRows) { if (rdr.Read()) { category.Id = Convert.ToInt32(rdr["CategoryID"]); category.BlogCatId = Convert.ToInt32(rdr["blogCategoryID"]); category.Name = rdr["DisplayName"].ToString(); if (isImageUrl) { category.CatUrlName = rdr["Website"].ToString(); } else { category.CatUrlName = rdr["UrlName"].ToString(); } } } } finally { if (rdr != null) { if (!rdr.IsClosed) rdr.Close(); rdr.Dispose(); } if (conn != null) { if (conn.State != ConnectionState.Closed) conn.Close(); conn.Dispose(); } } return category; }