petapoco查询Sql Server视图 [英] petapoco query a Sql Server View

问题描述

我已经开始将DAL2与dotnetnuke 7一起使用.我有一些复杂的查询，这些查询已经在实例所使用的SQL Server数据库中为其创建了视图.从这些视图访问简单选择的最佳实践是什么.

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.

如果我使用以下命令，那么是否绕过了dbOwner和ObjectQualifier:

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

问题: 还是应该为每个sql-server-view定义一个类和属性(就像我为每个表所做的那样)，并且表名注释是如下所示的视图名?

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

本文似乎表明我应该不使用视图，而是使用iEnumerable的可忽略列扩展我的产品类，这些列表示我需要的联接"数据记录.准确吗?

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.

如果您不需要更新或插入视图的表中，我认为使用视图是一种很好的方法.我已经成功地将视图用作DAL2表，但是即使它是架构绑定的，也只能使用GET.如果您只是在读取数据，那是最好的方法.

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.

我也通过将respitory方法中的子数据加入来做到了. 在本文(完整的源代码在相关文件"部分中)，我有一个基于带有ignorecolumn属性的表的DAL2对象.

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

}

该属性将填充到DAL2存储库方法中.

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

这篇关于petapoco查询Sql Server视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！