在DAL层使用PetaPoco的正确方法(ASP.NET Web Forms VB.NET)

Question

我正在尝试使用PetaPoco为ASP.NET web窗体项目生成DAL层。

Namespace Eva.Dal.Polls  
Public Partial Class EVAMOD_PL_CategoryDb
    Private db As Eva.Dal.Core.EvaDb

    Public Function Insert(a As EVAMOD_PL_Category) As Object
        Return db.Insert(a)
    End Function

    Public Sub New()
        db = New Eva.Dal.Core.EvaDb
    End Sub
End Class

Public Partial Class EVAMOD_PL_GL_CategoryDb
    Private db As Eva.Dal.Core.EvaDb

    Public Function Insert(a As EVAMOD_PL_GL_Category) As Object
        Return db.Insert(a)
    End Function

    Public Sub New()
        db = New Eva.Dal.Core.EvaDb
    End Sub
End Class
End Namespace

我特别感兴趣的是如何打开数据库。在PetaPoco站点中有这样的示例

// Create a PetaPoco database 
objectvar db=new PetaPoco.Database("connectionStringName");
// Show all articles    
foreach (var a in db.Query<article>("SELECT * FROM articles")){
    Console.WriteLine("{0} - {1}", a.article_id, a.title);
}

但是在PetaPoco附带的T4生成器中有一个很好的部分，它与所有DTO一起生成如下所示

Namespace Eva.Dal.Core
Public Partial Class EvaDb
    Inherits Database

    Public Sub New() 
        MyBase.New("ConnectionString")
        CommonConstruct()
    End Sub

    Public Sub New(connectionStringName As String)
        MyBase.New(connectionStringName)
        CommonConstruct()
    End Sub
    
    Private Partial Sub CommonConstruct()
    End Sub

    Public Interface IFactory
        Function GetInstance() As EvaDb
    End Interface

    Public Shared Property Factory() As IFactory
        Get
            Return mFactory
        End Get
        Set
            mFactory = Value
        End Set
    End Property

    Private Shared mFactory As IFactory

    Public Shared Function GetInstance() As EvaDb
        If istance IsNot Nothing Then
            Return istance
        End If

        If Factory IsNot Nothing Then
            Return Factory.GetInstance()
        Else
            Return New EvaDb
        End If
    End Function

    <ThreadStatic> _
    Shared istance As EvaDb

    Public Overrides Sub OnBeginTransaction()
        If istance Is Nothing Then
            istance = Me
        End If
    End Sub
    
    Public Overrides Sub OnEndTransaction()
        If istance Is Me Then
            istance = Nothing
        End If
    End Sub
    
    Public Class Record(Of T As New)
        Public Shared ReadOnly Property Repo() As EvaDb
            Get
                Return EvaDb.GetInstance()
            End Get
        End Property
        Public Function IsNew() As Boolean
            Return Repo.IsNew(Me)
        End Function
        
        .......

    End Class
End Class
End Namespace 

那么，在PetaPoco中创建DB对象并在DAL层中使用它的正确方法是什么呢？

另外，我读到有一种使用PetaPoco的方法来保持连接打开并重用它，我猜这在BLL/DAL架构中是不可行的，例如，当你从BLL访问数据库有2-3个操作时？或者，如果是，那么应该如何正确处理呢？创建打开连接并执行所有2-3操作的DAL方法？因为在BLL中打开连接不应该是这种情况。

Answer 1

更新：这是使用共享连接的方法：

public class SharedConnection : IDisposable
{
    private Database _db;

    public SharedConnection(Database db)
    {
        _db = db;
        _db.OpenSharedConnection();
    }

    public void Dispose()
    {
        _db.CloseSharedConnection();
    }
}

public class FooBarDao
{
    private Database _db = new Database("conn_str_name");

    public SharedConnection GetSharedConnection()
    {
        return new SharedConnection(_db);
    }

    public Foo GetFoo(string id)
    {
        return db.SingleOrDefault<Foo>(
            "SELECT FooVal FROM FooTbl WHERE Id = @0", id);
    }

    public Bar GetBar(string id)
    {
        return db.SingleOrDefault<Bar>(
            "SELECT BarVal FROM BarTbl WHERE Id = @0", id);
    }
}

public class FooBarManager
{
    private FooBarDao _dao = new FooBarDao;

    public void GetFooAndBar(string fooId, string barId)
    {
        using (_dao.GetSharedConnection())
        {
            _dao.GetFoo(fooId);
            _dao.GetBar(barId);
        }
    }
}

Answer 2

我总是创建一个包装器来实例化它：

public class DatabaseCreator
   public shared function GetContext() As EvaDb
       return new EvaDb(ConfigurationManager.ConnectionStrings("X").ConnectionString)
   end sub
end class

我不会只为您正在执行的即时操作保持数据库连接的打开时间，但也不会让它处于打开状态以便重用。有了多个层，您可以将应用程序构建为跨业务组件共享连接(类似于存储库模式)。我已经养成了在每个存储库方法中打开连接的习惯，或者将引用传递给存储库的构造函数。

Answer 3

按照Brian的建议并在Stackoverflow上阅读了另一个用户建议，我创建了一个静态助手函数，该函数使用当前的httpcontext为每个请求创建(或返回现有的)单个数据库实例。此方式(vb.net)：

    Public NotInheritable Class DbHelper
    Public Shared Function GetEvaDbIstance() As Eva.Dal.Core.EvaDb
        If HttpContext.Current.Items("EvaDb") Is Nothing Then
            Dim retval = New Eva.Dal.Core.EvaDb
            HttpContext.Current.Items("EvaDb") = retval
            Return retval
        End If
        Return DirectCast(HttpContext.Current.Items("EvaDb"), Eva.Dal.Core.EvaDb)
    End Function

    Private Sub New()

    End Sub
    End Class

然后在我的Dal对象中，我只是

    Private db As New Eva.Dal.Core.EvaDb = DbHelper.GetEvaDbIstance()