While converting the data access layer for the DNN FAQ module, I came across some pitfalls. There is some documentation on the internet (*), but this remains more on the surface. And moreover, the datalayer is still missing an important part in the implementation (more on that later). So this document should help you people not making the same mistakes as I did when going my first steps with DAL 2.
Basics on DAL 2
The main target of the new data layer is to free the developer of the massive burdon to write these ever repeating tasks while developing the data access layer. Up til now we had to write a method in XYZController, an abstract method in DataProvider and a concrete method in SqlDataprovider. In most cases you have to write an additional stored procedure including installation and deinstallation script.
Another goal is the opportunity to work in a more modern way with typed data objects (POCOs) and with IEnumerables of them instead of untyped datareaders and datatables. To realize this and some other features (e.g. automatic caching), DNN integrated the PetaPoco Class Library since version 7 in the core.
The DataInfo objects /null values
My first step when converting the FAQs module to DAL 2 leaded me to the two datainfo classes for the faqs (FAQInfo.cs) and the categories (CategoryInfo.cs). Initially the classes implemented the IHydratable and also (for token replace) the IPropertyAccess interface. The code for IHydratable is now obsolete; hydrating is part of PetaPoco. The IPropertyAccess interface remains unchanged because I want to work with token replace also in the future. For better clarity and readability I change all the properties to automatic properties ({get;set;}). As a last step I add data annotations needed by PetaPoco (e.g. [PrimaryKey…], see other documentations for these topics). I end up with 183 lines of code instead of 443 at the beginning (a lot of comment lines are stripped out here):
using DotNetNuke.ComponentModel.DataAnnotations;
using System.Web.Caching;
namespace DotNetNuke.Modules.FAQs
{
[TableName("FAQsCategory")]
[PrimaryKey("FaqCategoryId",AutoIncrement = true)]
[Scope("ModuleId")]
[Cacheable("FAQsCategory",CacheItemPriority.Normal,20)]
public class CategoryInfo
{
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; }
}
}
As an example I list here the CategoryInfo class. For your information: The categories are ordered hierarchical for display in a treeview (so we have an ID and a ParentId).
Two things are notable:
- By using PetaPoco, the handling of null values is much easier . Fields which could hold null values in the database can by defined as nullable datatypes in the info class (e.g.
int?, datetime?, string). Assigning a null value to such a property and persisting this with PetaPoco leads directly to a null value in the database and vice versa. No more need to conversion of values with Null.GetNull oder usage of if (xy == DBNull.value) is needed !!!
- The property “Level” does not exist in the corresponding database table. This property is filled by calculating the hierarchy while selecting the category data. To avoid errors when inserting or updateing with PetaPoco (PetaPoco generates a full SQL statement with every defined property of the info class), this property should have the attribute
[IgnoreColumn] !
The Controller Class
Simple Data Access with PetaPoco GetById
All the data access logic should now be defined In the controller class. Like in the example above I ‘ll begin with the categories, because their access methods are simpler than the faq ones. Lets start with the “get” method:
public CategoryInfo GetCategory(int? faqCategoryId)
{
CategoryInfo category;
using (IDataContext ctx = DataContext.Instance())
{
var rep = ctx.GetRepository<CategoryInfo>();
category = rep.GetById(faqCategoryId);
}
return category;
}
A fast test shows a wonderful successful result! So we could kick out a lot of obsolete code:
DataProvider:
public abstract IDataReader GetFAQ(int faqId, int moduleId);</pre>
SqlDataProvider:
public override IDataReader GetCategory(int faqCategoryId, int moduleId)
{
return ((IDataReader) (SqlHelper.ExecuteReader(ConnectionString, DatabaseOwner + ObjectQualifier + "FAQCategoryGet", faqCategoryId, moduleId)));
}
and the code for the stored procedure:
/* -------------------------------------------------------------------------------------
/ FaqCategoryGet
/ ------------------------------------------------------------------------------------- */
IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}FAQCategoryGet') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}[{objectQualifier}FAQCategoryGet]
go
CREATE PROCEDURE {databaseOwner}[{objectQualifier}FAQCategoryGet]
(
@FaqCategoryId int,
@moduleId int
)
AS
SELECT
[FaqCategoryId],
[ModuleId],
CASE WHEN [FaqCategoryParentId] IS NULL THEN 0 ELSE [FaqCategoryParentId] END AS [FaqCategoryParentId],
[FaqCategoryName],
[FaqCategoryDescription],
0 As [Level],
[ViewOrder]
FROM {databaseOwner}[{objectQualifier}FAQsCategory]
WHERE
[FaqCategoryId] = @FaqCategoryId
AND [moduleid]=@moduleId
GO
So far everything went fine!
Data Access with SQL Select
Next goal is the method “ListCategories” which should result in a list of categories from the database (wow! never had expected this !). This method has a parameter “onlyUsedCategories”. If true, this method returns only categories which have a minimum of one corresponding record in the faqs table.
The original stored procedure looked like this:
/* -------------------------------------------------------------------------------------
/ FaqCategoryList
/ ------------------------------------------------------------------------------------- */
if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}{objectQualifier}FAQCategoryList') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE {databaseOwner}[{objectQualifier}FAQCategoryList]
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}FAQCategoryList]
(
@ModuleID int,
@OnlyUsedCategories bit
)
AS
SELECT
[FaqCategoryId],
[ModuleId],
CASE WHEN [FaqCategoryParentId] IS NULL THEN 0 ELSE [FaqCategoryParentId] END AS [FaqCategoryParentId],
[FaqCategoryName],
[FaqCategoryDescription],
0 As [Level],
[ViewOrder]
FROM {databaseOwner}[{objectQualifier}FAQsCategory]
WHERE [ModuleId] = @ModuleId
AND ([FaqCategoryId] IN (SELECT CategoryId FROM {databaseOwner}[{objectQualifier}FAQs]) OR @OnlyUsedCategories=0)
GO
because of the subselect in the where clause we could not use the PetaPoco standard syntax for querying. This would look similar to this:
public static IEnumerable<CategoryInfo> ListCategories(int moduleId)
{
IEnumerable<CategoryInfo> cats;
using (IDataContext context = DataContext.Instance())
{
var repository = context.GetRepository<CategoryInfo>();
cats = repository.Find("WHERE ModuleID = @0", moduleId);
}
return cats;
}
First idea was to expand the where clause by using the subselect:
cats = repository.Find("WHERE ModuleID = @0 AND FaqCategoryId IN (SELECT CategoryId FROM {databaseOwner}[{objectQualifier}FAQs]) OR @1=0)", moduleId);</pre>
Indeed there is a problem with this! The variables {databaseOwner} and {objectQualifier} are not replaced by the DAL! I’ve created a workaround for this (see next paragraph for more details **!)[Edit: see annotations at the end of this blog!]** For now, I use the PetaPoco ExecuteQuery syntax to get my data:
<pre class="brush: csharp; auto-links: true; collapse: false; first-line: 1; gutter: true; html-script: false; light: false; ruler: false; smart-tabs: true; tab-size: 4; toolbar: true;">public IEnumerable<CategoryInfo> ListCategories(int moduleId, bool onlyUsedCategories)
{
IEnumerable<CategoryInfo> categories;
string sql = "SELECT [FaqCategoryId], [ModuleId]," +
" CASE WHEN [FaqCategoryParentId] IS NULL THEN 0 ELSE [FaqCategoryParentId] END AS [FaqCategoryParentId]," +
" [FaqCategoryName], [FaqCategoryDescription],0 As [Level],[ViewOrder]" +
" FROM {databaseOwner}[{objectQualifier}FAQsCategory] " +
" WHERE [ModuleId] = @0" +
" AND ([FaqCategoryId] IN (SELECT CategoryId FROM {databaseOwner}[{objectQualifier}FAQs]) OR @1=0)";
sql = ReplaceSqlPlaceholders(sql);
using (IDataContext ctx = DataContext.Instance())
{
categories = ctx.ExecuteQuery<CategoryInfo>(CommandType.Text, sql, moduleId, onlyUsedCategories);
}
return categories;
}
Replace SQL Tokens
This topic was the hardest nut to crack because nobody could help with this. The controller class does not know the variables objectQualifier and databaseOwner. These are defined in web.config dependent on the dataprovider type:
<data defaultProvider="SqlDataProvider">
<providers>
<clear />
<add name="SqlDataProvider"
type="DotNetNuke.Data.SqlDataProvider, DotNetNuke" connectionStringName="SiteSqlServer"
upgradeConnectionString=""
providerPath="~\Providers\DataProviders\SqlDataProvider\"
objectQualifier="dnn"
databaseOwner="dbo." />
</providers>
</data>
To retrieve this information in a proper way, we need to pass it from the SqlDataProvider to our controller. By default only the SqlDataProvider knows these properties. To retrieve them in a regular way we need to add them as virtual properties in the abstract DataProvider class…
public virtual string ObjectQualifier { get; set; }
public virtual string DatabaseOwner { get; set; }
… and in SqlDataProvider mark these as "override":
public override string ObjectQualifier
{
get
{
return _objectQualifier;
}
}
Now we are able to access these two properties in our controller class and can replace the placeholders at runtime with the appropriate values:
private string ReplaceSqlPlaceholders(string sql)
{
return sql
.Replace("{objectQualifier}", DataProvider.Instance().ObjectQualifier)
.Replace("{databaseOwner}",DataProvider.Instance().DatabaseOwner);
}
But (as mentioned before) this is only a workaround and should be fixed as far as possible in the core (e.g. in PetaPocoDataContext.cs) !
The core team has implemented the objectQualifier and databaseOwner replacement into the core since 7.0.1, so this chapter is obsolete now!
CRUD Methods
After cracking this big hunk the next steps were running easy:
public int AddCategory(CategoryInfo category)
{
using (IDataContext ctx = DataContext.Instance())
{
var rep = ctx.GetRepository<CategoryInfo>();
if (category.FaqCategoryParentId == 0)
category.FaqCategoryParentId = null;
rep.Insert(category);
return category.FaqCategoryId;
}
}
public void UpdateCategory(CategoryInfo category)
{
using (IDataContext ctx = DataContext.Instance())
{
var rep = ctx.GetRepository<CategoryInfo>();
if (category.FaqCategoryParentId == 0)
category.FaqCategoryParentId = null;
rep.Update(category);
}
}
public void DeleteCategory(int faqCategoryId)
{
using (IDataContext ctx = DataContext.Instance())
{
var rep = ctx.GetRepository<CategoryInfo>();
rep.Delete("WHERE FaqCategoryId = @0",faqCategoryId);
}
}
Stored Procedures
In the FAQ module exists a stored procedure which contains a relative complex sql statement for retrieving categories sorted by hierarchical structure:
WITH cte ([FaqCategoryParentId], [FaqCategoryId], [ModuleId], [FaqCategoryName], [FaqcategoryDescription], [Level], [ViewOrder], [Sort] )
AS
(
-- Anchor member definition
SELECT e.FaqCategoryParentId, e.FaqCategoryId, e.ModuleId, e.FaqCategoryName, e. FaqcategoryDescription, 0 AS [Level], e.ViewOrder,
RIGHT(REPLICATE('0', 8) + CAST(e.ViewOrder AS VARCHAR(MAX)),8) as [Sort]
FROM {databaseOwner}[{objectQualifier}FAQsCategory] AS e
WHERE e.FaqCategoryParentId IS NULL
UNION ALL
-- Recursive member definition
SELECT e.FaqCategoryParentId, e.FaqCategoryId, e.ModuleId, e.FaqCategoryName, e. FaqcategoryDescription, [Level] + 1 AS [Level], e.ViewOrder,
CAST(d.Sort + '/' +
RIGHT(REPLICATE('0', 8) + CAST(e.FaqCategoryparentId AS VARCHAR(MAX)),8) + '-' +
RIGHT(REPLICATE('0', 3) + CAST(e.ViewOrder AS VARCHAR(MAX)) ,3)
AS VARCHAR(MAX)) AS [Sort]
FROM {databaseOwner}[{objectQualifier}FAQsCategory] AS e
INNER JOIN cte AS d
ON e.FaqCategoryParentId = d.FaqCategoryId
)
-- Statement that executes the CTE
SELECT CASE WHEN cte.FaqCategoryParentId IS NULL THEN 0 ELSE cte.FaqCategoryParentId END AS [FaqCategoryParentId], [FaqCategoryId], [ModuleId], [FaqCategoryName], [FaqcategoryDescription], [Level], [ViewOrder]
FROM cte
WHERE cte.ModuleId = @ModuleId
AND (cte.FaqCategoryId IN (SELECT CategoryId FROM {databaseOwner}[{objectQualifier}FAQs]) OR @OnlyUsedCategories = 0)
order by [Sort]
I tried to do this in sql syntax too (like “Data Access with Sql Select”) but failed. The reason was that PetaPoco automatically appends a generated sql snippet (“SELECT field1, field2, field3…”) before our sql string (ExecuteQuery<CategoryInfo> arranges this). So in this case I did not convert the SP into controller code but leave it as SP and access this SP from my controller class directly:
public IEnumerable<CategoryInfo> ListCategoriesHierarchical(int moduleId, bool onlyUsedCategories)
{
IEnumerable<CategoryInfo> categories;
string sql = "{objectQualifier}FAQCategoryListHierarchical";
sql = ReplaceSqlPlaceholders(sql);
using (IDataContext ctx = DataContext.Instance())
{
categories = ctx.ExecuteQuery<CategoryInfo>(CommandType.StoredProcedure, sql, moduleId, onlyUsedCategories);
}
return categories;
}
The problem with non working Common Table Expressions (cte syntax) is also solved in DNN 7.0.1.
This counts only for the SELECT case. There is another complex SP in the project which changes the vieworder of two FAQs. Here I could use the sql syntax in code with no problems:
public void ReorderFAQ(int faqId1, int faqId2, int moduleId)
{
string sql = "WITH tmpSwappedOrder(ItemId,ViewOrder) AS" +
" (" +
" SELECT @1 AS ItemId,ViewOrder FROM {databaseOwner}[{objectQualifier}FAQs] WHERE ItemId = @0" +
" UNION" +
" SELECT @0 AS ItemId,ViewOrder FROM {databaseOwner}[{objectQualifier}FAQs] WHERE ItemId = @1" +
" )" +
" UPDATE {databaseOwner}[{objectQualifier}FAQs] SET ViewOrder = (SELECT ViewOrder FROM tmpSwappedOrder s WHERE s.ItemId = {databaseOwner}[{objectQualifier}FAQs].ItemId)" +
" WHERE {databaseOwner}[{objectQualifier}FAQs].ItemId IN (SELECT ItemId FROM tmpSwappedOrder);" +
" " +
" WITH tmpReorder(ViewOrder,ItemId) AS" +
" (" +
" SELECT TOP 1000 row_number() OVER (ORDER BY f.ViewOrder) as rank, f.ItemId" +
" FROM {databaseOwner}[{objectQualifier}FAQs] f" +
" WHERE f.ModuleId = @2" +
" ORDER BY rank " +
" )" +
" UPDATE {databaseOwner}[{objectQualifier}FAQs] " +
" SET ViewOrder = (SELECT ViewOrder FROM tmpReorder r WHERE r.ItemId = {databaseOwner}[{objectQualifier}FAQs].ItemId)" +
" WHERE ModuleId = @2";
sql = ReplaceSqlPlaceholders(sql);
using (IDataContext ctx = DataContext.Instance())
{
ctx.Execute(CommandType.Text, sql,faqId1,faqId2,moduleId);
}
}
Tables with Joins
In the FAQ project the data for the faqs had been assembled by a join to the category table. Name and description of the category were collected from the category table and the username of the last editor was collected from the user table. When working with PetaPoco this is not very practical. We need an info class including the joined columns, but for updateing and inserting they must be decorated as [IgnoreColumn]. The downside is, that they are not populated with data then …
So to get around this problem we could choose between two solutions. First we can create two info classes, one containing all the fields including the joined ones (for selections) and another one containing only with properties for the columns of the table (for CRUD operations).
Alternatively we have the opportunity to work only with the simple info class and collect the needed category information as an extra call when needed. Which solution is the best depends on the context – in the FAQ module I chosed the second solution. But if I need to loop with foreach through 300 FAQ records and have to retrieve for every single record the name and description of the category with another sql access, it could be better to choose the first solution…
Example in Token Replace:
before :
#region Implementation of IPropertyAccess
public string GetProperty(string strPropertyName, string strFormat, CultureInfo formatProvider, UserInfo AccessingUser, Scope AccessLevel, ref bool PropertyNotFound)
{
PropertyNotFound = false;
switch (strPropertyName.ToLower())
{
case "question":
return PropertyAccess.FormatString(_question, strFormat);
case "answer":
return PropertyAccess.FormatString(_answer, strFormat);
case "user":
return PropertyAccess.FormatString(_createdByUserName, strFormat);
case "viewcount":
return _viewCount.ToString(String.IsNullOrEmpty(strFormat) ? "g" : strFormat, formatProvider);
case "vieworder":
return _viewOrder.ToString(String.IsNullOrEmpty(strFormat) ? "g" : strFormat, formatProvider);
case "categoryname":
return PropertyAccess.FormatString(_faqCategoryName, strFormat);
case "categorydesc":
return PropertyAccess.FormatString(_faqCategoryDescription, strFormat);
case "datecreated":
return _createdDate.ToString(String.IsNullOrEmpty(strFormat) ? "d" : strFormat, formatProvider);
case "datemodified":
return _dateModified.ToString(String.IsNullOrEmpty(strFormat) ? "d" : strFormat, formatProvider);
case "index":
return _index.ToString(String.IsNullOrEmpty(strFormat) ? "g" : strFormat, formatProvider);
default:
PropertyNotFound = true;
return String.Empty;
}
}
after:
#region Implementation of IPropertyAccess
public string GetProperty(string strPropertyName, string strFormat, CultureInfo formatProvider, UserInfo AccessingUser, Scope AccessLevel, ref bool PropertyNotFound)
{
PropertyNotFound = false;
FAQsController faqController;
switch (strPropertyName.ToLower())
{
case "question":
return PropertyAccess.FormatString(Question, strFormat);
case "answer":
return PropertyAccess.FormatString(Answer, strFormat);
case "user":
UserInfo user = UserController.GetUserById(PortalSettings.Current.PortalId, Convert.ToInt32(CreatedByUser));
return PropertyAccess.FormatString(user.DisplayName, strFormat);
case "viewcount":
return ViewCount.ToString(String.IsNullOrEmpty(strFormat) ? "g" : strFormat, formatProvider);
case "vieworder":
return ViewOrder.ToString(String.IsNullOrEmpty(strFormat) ? "g" : strFormat, formatProvider);
case "categoryname":
faqController = new FAQsController();
return PropertyAccess.FormatString(faqController.GetCategory(CategoryId).FaqCategoryName, strFormat);
case "categorydesc":
faqController = new FAQsController();
return PropertyAccess.FormatString(faqController.GetCategory(CategoryId).FaqCategoryDescription, strFormat);
case "datecreated":
return CreatedDate.ToString(String.IsNullOrEmpty(strFormat) ? "d" : strFormat, formatProvider);
case "datemodified":
return DateModified.ToString(String.IsNullOrEmpty(strFormat) ? "d" : strFormat, formatProvider);
case "index":
return Index.ToString(String.IsNullOrEmpty(strFormat) ? "g" : strFormat, formatProvider);
default:
PropertyNotFound = true;
return String.Empty;
}
}
LINQ and Friends
One of the big pros of the new DAL 2 is the possibility to work with Linq on the result sets. In faq module I haven’t used this, but for completeness I want to link to the DNN announcement module, which is ported by EricVB before:
public static IEnumerable<AnnouncementInfo> GetAnnouncements(int moduleId, DateTime startDate, DateTime endDate)
{
IEnumerable<AnnouncementInfo> announcements;
using (IDataContext context = DataContext.Instance())
{
var repository = context.GetRepository<AnnouncementInfo>();
announcements = repository.Find("WHERE ModuleID = @0 AND ( ((PublishDate >= @1) OR @1 IS NULL) AND ((PublishDate <= @2) OR @2 IS NULL) )",
moduleId, Null.GetNull(startDate, DBNull.Value), Null.GetNull(endDate, DBNull.Value))
.OrderBy(a => a.ViewOrder)
.ThenByDescending(a => a.PublishDate);
}
return announcements;
}
}
Note: Eric has not used the null value handling of PetaPoco and works instead with Null.GetNull() converted values.
Summary
With the DAL 2 data layer we developers finally got a long-overdue modernization of data access. Except for the above-mentioned drawback with the wildcard replacement we now can work wonderfully simple with this, and above all, we don’t have to get our fingers bloody while typing tons of unneccessary code. If you know the difficult parts (and some of them I mentioned above), module development is a lot faster and cleaner now. And with the “Cacheable” –data annotation we get the extra benefit of automatic caching without the need to write a single line of code. The performance of DAL 2 should be nearly the same as before (have not checked this). So ladys and gentleman: Start with DAL 2 today, you have my unconditional recommendation!
One last thing: If you see an error in this blog or have some annotations and/or corrections please feel free to add a comment! I’m happy about every… ok nearly every feedback ;-)
Important links