public static DataTable GetFullCategoryById(Category category)
{
SqlCommand sqlCmd;
var sqlAdapter = new SqlDataAdapter();
SqlConnection sqlConnect = Blog.Common.SqlProvider.SelectGetConnection();
DataTable dt = new DataTable { Locale = CultureInfo.CurrentCulture };
try
{
sqlConnect.Open();
sqlCmd = new SqlCommand("sp_getFullCategory", sqlConnect);
sqlCmd.Parameters.Add("id", SqlDbType.Int).Value = category.CategoryId;
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlAdapter.SelectCommand = sqlCmd;
sqlAdapter.Fill(dt);
return dt;
}
catch (SqlException)
{
sqlConnect.Close();
return null;
}
finally
{
sqlConnect.Close();
}
}
public static bool DeleteCategory(Category category)
{
SqlCommand sqlcmd;
SqlConnection sqlconnecnt = Common.SqlProvider.SelectGetConnection();
try
{
sqlconnecnt.Open();
sqlcmd = new SqlCommand("sp_DeleteCategory", sqlconnecnt);
sqlcmd.Parameters.Add("id", SqlDbType.Int).Value = category.CategoryId;
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.ExecuteNonQuery();
return true;
}
catch (SqlException)
{
sqlconnecnt.Close();
return false;
}
finally
{
sqlconnecnt.Close();
}
}
public static List<Category> GetCategories()
{
List<Category> result = new List<Category>();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "select * from dbo.Category order by OrderNr";
DataTable categories = DBContext.DataAccess.Instance.GetSQLTable(cmd);
foreach (DataRow row in categories.Rows)
{
Category category = new Category((int)row["CategoryId"], (string)row["Name"], (string)row["ImagePath"], (string)row["Color"], (int)row["OrderNr"]);
result.Add(category);
}
return result;
}
public static Category GetCategory(int aCategoryId)
{
Category category = null;
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "select * from dbo.Category where CategoryId=@CategoryId";
cmd.Parameters.AddWithValue("@CategoryId", aCategoryId);
DataTable dtCategory = DBContext.DataAccess.Instance.GetSQLTable(cmd);
if (dtCategory.Rows.Count > 0)
{
DataRow row = dtCategory.Rows[0];
category = new Category((int)row["CategoryId"], (string)row["Name"], (string)row["ImagePath"], (string)row["Color"], (int)row["OrderNr"]);
}
return category;
}
public static Category FillDataCategoryById(int categoryId)
{
SqlCommand sqlcmd;
SqlConnection sqlconnecnt = Common.SqlProvider.SelectGetConnection();
Category category = new Category();
try
{
sqlconnecnt.Open();
sqlcmd = new SqlCommand("sp_GetFullCategoryByID", sqlconnecnt);
sqlcmd.Parameters.Add("id", SqlDbType.Int).Value = categoryId;
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.ExecuteNonQuery();
SqlDataReader dr = sqlcmd.ExecuteReader();
if (dr != null)
if (dr.Read())
{
category.CategoryName = (string)dr["CategoryName"];
category.Description = (string)dr["Description"];
}
return category;
}
catch (SqlException)
{
sqlconnecnt.Close();
return category;
}
finally
{
sqlconnecnt.Close();
}
}
public static List<Article> GetArticles(Category aCategory = null, int? aPageNr = null, bool aIsAuthenticated = false)
{
if (aCategory != null)
return GetArticles(aCategory.CategoryId, aPageNr, aIsAuthenticated);
else
return GetArticles(aPageNr, aIsAuthenticated);
}
public static bool InsertCategory(Category category)
{
SqlCommand sqlcmd;
SqlConnection sqlconnecnt = Common.SqlProvider.SelectGetConnection();
try
{
sqlconnecnt.Open();
sqlcmd = new SqlCommand("sp_InsertCategory", sqlconnecnt);
sqlcmd.Parameters.Add("categoryname", SqlDbType.NVarChar).Value = category.CategoryName;
sqlcmd.Parameters.Add("description", SqlDbType.NVarChar).Value = category.Description;
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.ExecuteNonQuery();
return true;
}
catch (SqlException)
{
sqlconnecnt.Close();
return false;
}
finally
{
sqlconnecnt.Close();
}
}
public static bool UpdateCategory(Category category)
{
return CategoryDao.UpdateCategory(category);
}
public static DataTable SelectFullCategoryById(Category category)
{
return CategoryDao.GetFullCategoryById(category);
}
public static bool InsertCategory(Category category)
{
return CategoryDao.InsertCategory(category);
}
public static bool DeleteCategory(Category category)
{
return CategoryDao.DeleteCategory(category);
}