petapoco query a Sql Server View

Question

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?

Answer 1

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;
   }