I have started using the DAL2 with dotnetnuke 7. I have some complicated queries that I have created views for in the SQL server database that my instance uses. What is the best practice for accessing a simple select from these views.
If I use the following then does this bypass the dbOwner and ObjectQualifier:
Public Function GetProducts_Short_Active() As IEnumerable(Of Object)
Using ctx As IDataContext = DataContext.Instance
Return ctx.ExecuteQuery(Of Object)(CommandType.Text, "SELECT * FROM dbo.vw_ProductList_Short_Active", Nothing)
End Using
End Function
QUESTION:
Or should I define a class and properties for each sql-server-view (like I did for each table) with the table name annotation being the name of the view like below?
<TableName("vw_ProductList_Short_Active")> _
<PrimaryKey("ProductId")> _
<Cacheable("ProductList_Short_Active", CacheItemPriority.Default, 20)> _
<Scope("PortalId")>
Public Class ProductList_Short_Active
''view properties go here
End Class
EDIT 1:
This article seems to indicate that I should not use a view, but rather extend my product class with ignorable columns of iEnumerable that represent the "joined" data records I need. Is that accurate?
I have done this a few ways.
If you don't need to update or insert into the view's tables, I would think using the view is a good way to do it. I have successfully used a view as a DAL2 table, but even if it is schema-bound, only GETs will work. If you are just reading data, that is the best way to do it.
I have also done it is with joining child data from the respoitory method.
In this article (full source code is in the Related files section), I have a DAL2 object based on a table with an ignorecolumn attribute.
[TableName("DNNuclear_DataVisualizer_Chart")]
[PrimaryKey("ChartId", AutoIncrement = true)]
[Cacheable("Charts", CacheItemPriority.Default, 20)]
[Scope("ModuleId")]
public class Chart
{
///<summary>
///</summary>
public int ChartId { get; set; }
...
[IgnoreColumn]
public IList<SeriesData> SeriesData { get; set; }
...
}
That attribute gets filled in the DAL2 repository method.
public Chart GetItem(int itemId)
{
Chart t;
using (IDataContext ctx = DataContext.Instance())
{
var rep = ctx.GetRepository<Chart>();
t = rep.GetById(itemId);
var repD = ctx.GetRepository<ChartData>();
var data = repD.Get(itemId);
if (data != null && data.Count() > 0)
{
// Get unique categories
var uniqueCategories = data.OrderBy(x => x.Category).Select(x => x.Category).Distinct();
t.Categories = uniqueCategories.ToList();
// Get series data
t.SeriesData = getSeriesData(data, t.Categories.ToArray<string>());
}
}
return t;
}