public List<Category> GetActiveCatagoryList()
{
var cat = new Category();
const string commandString =
@"select C.CategoryId,C.CategoryName,C.Ordr,SC.SubCategoryId,SC.SubCategoryName,SC.Ordr
FROM Categories C,SubCategories SC
WHERE C.Active = 1 AND SC.Active = 1 AND C.CategoryId = SC.CategoryId
ORDER BY C.Ordr,SC.Ordr";
int Categoryid = 0;
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
var command = GetCommand(commandString, connection, CommandType.Text);
var Catlist = new List<Category>();
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
if (Categoryid != (int)reader["CategoryID"])
{
cat = new Category();
cat.CategoryId = (int)reader["CategoryID"];
cat.CategoryName = (string)reader["CategoryName"];
cat.Ordr = Convert.ToDecimal(reader["Ordr"]);
Categoryid = (int)reader["CategoryID"];
if (Categoryid != 0)
{
Catlist.Add(cat);
}
}
var subcat = new SubCategory();
subcat.CategoryId = (int)reader["CategoryID"];
subcat.SubCategoryId = (int)reader["SubCategoryID"];
subcat.CategoryName = (string)reader["CategoryName"];
subcat.SubCategoryName = (string)reader["SubCategoryName"];
subcat.Ordr = Convert.ToDecimal(reader["Ordr"]);
cat.subcategoylist.Add(subcat);
}
}
}
return Catlist;
}
}
public ActionResult ChangeCategoryStatus(int CategoryId, bool Value)
{
var Category = new Category();
Category = _adminRepository.GetCategorybyId(CategoryId);
if (Category != null)
{
Category.Active = Value;
}
_adminRepository.UpdateCategory(Category);
return Json(new { Message = " Category Status Changed Successfully", Success = true });
}
public void AddCategory(Category category)
{
const string commandString = @"INSERT INTO Categories
(CategoryName,Active,Ordr) VALUES (@CategoryName,@Active,@Ordr)";
try
{
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
var command = new SqlCommand(commandString, connection);
command.Parameters.Add("@CategoryName", category.CategoryName );
command.Parameters.Add("@Active", true );
command.Parameters.Add("@Ordr", category.Ordr );
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
}
public ActionResult SaveCategory(Category Category)
{
if (Request.Form["CategoryId"] == "0")
{
_adminRepository.AddCategory(Category);
}
else
{
_adminRepository.UpdateCategory_1 (Category);
}
CategoryModel vm = new CategoryModel();
vm.Categories = _adminRepository.GetAllCategory ();
return View("ManageCategories", vm);
}
public void UpdateCategory_1(Category category)
{
const string commandString = @"Update Categories SET Active = @Active,CategoryName = @CategoryName ,Ordr=@Ordr WHERE CategoryId=@CategoryId";
// const string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\kommanap\Gustin\Gustin\App_Data\Gustin.mdf;Integrated Security=True;User Instance=True";
try
{
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
var command = new SqlCommand(commandString, connection);
command.Parameters.Add("@CategoryId", category.CategoryId);
command.Parameters.Add("@Active", category.Active );
command.Parameters.Add("@CategoryName", category.CategoryName );
command.Parameters.Add("@Ordr", category.Ordr );
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
}
public Category GetCategorybyId(int CategoryId)
{
const string commandString =
@"SELECT CategoryId, CategoryName, Active,Ordr FROM Categories WHERE CategoryID = @CategoryId order by Ordr";
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
var command = GetCommand(commandString, connection, CommandType.Text);
var category = new Category();
{
command.Parameters.Add("@CategoryId", CategoryId);
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
category.CategoryId = (int)reader["CategoryId"];
category.CategoryName = (string)reader["CategoryName"];
category.Active = Convert.ToBoolean(reader["Active"]);
category.Ordr = Convert.ToDecimal(reader["Ordr"]);
}
}
}
}
return category;
}
}
public List<Category> GetAllCategory()
{
const string commandString =
@"SELECT CategoryId, CategoryName,Active ,ordr FROM Categories order by ordr";
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
var command = GetCommand(commandString, connection, CommandType.Text);
var categories = new List<Category>();
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var category = new Category();
category.CategoryId = (int)reader["CategoryId"];
category.CategoryName = (string)reader["CategoryName"];
category.Active = Convert.ToBoolean(reader["Active"]);
category.Ordr = Convert.ToDecimal(reader["Ordr"]);
categories.Add(category);
}
}
}
return categories;
}
}
public List<Category> GetAllCategory()
{
const string commandString =
@"SELECT CategoryId, CategoryName FROM Categories";
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
var command = GetCommand(commandString, connection, CommandType.Text);
var categories = new List<Category>();
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var category = new Category();
category.CategoryId = (int)reader["CategoryId"];
category.CategoryName = (string)reader["CategoryName"];
categories.Add(category);
}
}
}
return categories;
}
}
