I'am fighting my way through the implementation of DAL 2 in the DNN FAQs module. After rererereading the Blog from Charles Nurse and watching his Session from DNNWorld on Youtube, I decided to start with the Categories. So this is my new CategoryInfo (stripped out the comments here):
using DotNetNuke.ComponentModel.DataAnnotations;
using System.Web.Caching;
namespace DotNetNuke.Modules.FAQs
{
[TableName("FAQsCategory")]
[PrimaryKey("FaqCategoryId",AutoIncrement = true)]
[Scope("ModuleId")]
[Cacheable("FaqCategory",CacheItemPriority.Normal,20)]
public class CategoryInfo
{
public CategoryInfo(int faqCategoryId, int moduleId, int faqCategoryParentId, string faqCategoryName, string faqCategoryDescription)
{
this.FaqCategoryId = faqCategoryId;
this.ModuleId = moduleId;
this.FaqCategoryParentId = faqCategoryParentId;
this.FaqCategoryName = faqCategoryName;
this.FaqCategoryDescription = faqCategoryDescription;
this.Level = 0;
this.ViewOrder = 999;
}
public int FaqCategoryParentId { get; set; }
public int FaqCategoryId { get; set; }
public int ModuleId { get; set; }
public string FaqCategoryName { get; set; }
public string FaqCategoryDescription { get; set; }
[IgnoreColumn]
public int Level { get; set; }
public int ViewOrder { get; set; }
}
}
In the FAQsController, the first method I changed is GetCategory:
public CategoryInfo GetCategory(int faqCategoryId, int moduleId)
{
CategoryInfo category;
using (IDataContext ctx = DataContext.Instance())
{
var rep = ctx.GetRepository<CategoryInfo>();
category = rep.GetById(faqCategoryId, moduleId);
}
return category;
}
All the stuff in DataProvider (GetCategory), SqlDataProvider (GetCategory) and the SP FAQCategoryGet can be deleted. Thats fine ! I like that ! Tested and runs perfect!
But now I have my first problem. ListCategories has a subselect to another table in the Stored Procedure SQL Statement:
SELECT
[FaqCategoryId],
[ModuleId],
CASE WHEN [FaqCategoryParentId] IS NULL THEN 0 ELSE [FaqCategoryParentId] END AS [FaqCategoryParentId],
[FaqCategoryName],
[FaqCategoryDescription],
0 As [Level],
[ViewOrder]
FROM dbo.[dnn_FAQsCategory]
WHERE [ModuleId] = @ModuleId
AND ([FaqCategoryId] IN (SELECT CategoryId FROM dbo.[dnn_FAQs]) OR @OnlyUsedCategories=0)
First idea was to simply use the Find-Syntax:
public IEnumerable<CategoryInfo> ListCategories(int moduleId, bool onlyUsedCategories)
{
IEnumerable<CategoryInfo> categories;
using (IDataContext ctx = DataContext.Instance())
{
var rep = ctx.GetRepository<CategoryInfo>();
categories = rep.Find("WHERE [ModuleId] = @0 AND ([FaqCategoryId] IN (SELECT CategoryId FROM {databaseOwner}[{objectQualifier}FAQs]) OR @1=0)", moduleId, onlyUsedCategories);
}
return categories;
}
But that does not work because {databaseOwner} and {objectQualifier} are not replaced at runtime.:-(
My next try was to use the ExecuteQuery<T> method:
public IEnumerable<CategoryInfo> ListCategories(int moduleId, bool onlyUsedCategories)
{
IEnumerable<CategoryInfo> categories;
using (IDataContext ctx = DataContext.Instance())
{
categories = ctx.ExecuteQuery<CategoryInfo>(CommandType.StoredProcedure, "FAQCategoryList", moduleId,
onlyUsedCategories);
}
return categories;
}
...but that also does not work, because the Stored procedures name is not FAQCategoryList but dnn_FAQCategoryList (objectqualifier...)
What I really need are some real world samples. I took a look at the announcement module but in there there are only simple samples ... When I think of converting some of my projects with really complex SQL statements to DAL2 my hairs get grey faster than I can look X-D
Anyone could help ?