I'm trying to determine which is the best practice for using the adapter. Currently I use manager classes for my bl. The following is a snippet from one of those classes:

    Private Shared _Adapter As New DataAccessAdapter

    Public Shared Function GetAllSchools() As EntityCollection
        Dim Sorter As ISortExpression = New SortExpression
        Sorter.Add(SortClauseFactory.Create(Elementary.GradeBook.LLBL.TermFieldIndex.StartDate, SortOperator.Ascending))

        Dim Sorter2 As ISortExpression = New SortExpression
        Sorter2.Add(SortClauseFactory.Create(Elementary.GradeBook.LLBL.SpecialScoreFieldIndex.SpecialScoreId, SortOperator.Ascending))

        Dim PrefetchPath As IPrefetchPath2 = New PrefetchPath2(CType(EntityType.SchoolEntity, Integer))
        PrefetchPath.Add(SchoolEntity.PrefetchPathCalendar).SubPath.Add(CalendarEntity.PrefetchPathTerm, Nothing, Nothing, Nothing, Sorter)
        PrefetchPath.Add(SchoolEntity.PrefetchPathSpecialScore, Nothing, Nothing, Nothing, Sorter2)

        GetAllSchools = New EntityCollection(New SchoolEntityFactory)
        _Adapter.FetchEntityCollection(GetAllSchools, Nothing, Nothing, Nothing, PrefetchPath)

        Return GetAllSchools
    End Function

Should I be doing it this way? Should I create a new adapter in each routine? Should I create one adapter for the entire bl and not for each manager class?

Thanks,

Fishy

btw, Frans, I think you may have misspelled 'Practises' in your documentation

I think this is a matter of taste, I personally do not create a single adapter, especially a shared/static one for my business layers.

In my business layers I use 'using' statements for all my adapter access, with connection pooling etc. recreating the connection is non existent and it keeps the state of the connection to a known state with a minimal amount of resources.

My code roughly looks like this, fetch is self contained with a using, and saves have two overloads one with and one without an adapter being passed in so that I can span a transaction over multiple business objects. The client code never uses the one with the adapter, it is only used within a manger to manager transaction.

But like I said its really a matter of taste and goals.

public bool FetchAddressBusinessObject(int addressId, out object dataSource)
{
    using (DataAccessAdapter Adapter = new DataAccessAdapter(DBConnection.ConnectionString))
    {
    // Fetch the data
    }
}

public bool SaveAddress(out int addressID)
{
    using (DataAccessAdapter Adapter = new DataAccessAdapter(DBConnection.ConnectionString))
    {
        return SaveAddress(Adapter, out addressID);
    }
}

public bool SaveAddress(DataAccessAdapter adapter, out int addressID)
{
    // Check for transactions, start checkpoint or transaction etc.
    // do work
}

jtgooding wrote:

In my business layers I use 'using' statements for all my adapter access, with connection pooling etc. recreating the connection is non existent and it keeps the state of the connection to a known state with a minimal amount of resources.

Is there an eqivalent to 'using' in vb?

<edit> I converted it over to vb using a tool:

 Public Function SaveAddress(ByRef addressID As Integer) As Boolean 
 ' Using 
 Dim Adapter As DataAccessAdapter = New DataAccessAdapter(DBConnection.ConnectionString) 
 Try 
   Return SaveAddress(Adapter, addressID) 
 Finally 
   CType(Adapter, IDisposable).Dispose() 
 End Try 
End Function

Thanks,

Fishy

btw, anymore opinions would be appreciated

jtgooding wrote:

In my business layers I use 'using' statements for all my adapter access, with connection pooling etc. recreating the connection is non existent and it keeps the state of the connection to a known state with a minimal amount of resources.

Doesn't the connection get opened/closed everytime you do a database call with the exception of transactions and unitofwork?

Fishy wrote:

I'm trying to determine which is the best practice for using the adapter. Currently I use manager classes for my bl. The following is a snippet from one of those classes:

    Private Shared _Adapter As New DataAccessAdapter

    Public Shared Function GetAllSchools() As EntityCollection
        Dim Sorter As ISortExpression = New SortExpression
        Sorter.Add(SortClauseFactory.Create(Elementary.GradeBook.LLBL.TermFieldIndex.StartDate, SortOperator.Ascending))

        Dim Sorter2 As ISortExpression = New SortExpression
        Sorter2.Add(SortClauseFactory.Create(Elementary.GradeBook.LLBL.SpecialScoreFieldIndex.SpecialScoreId, SortOperator.Ascending))

        Dim PrefetchPath As IPrefetchPath2 = New PrefetchPath2(CType(EntityType.SchoolEntity, Integer))
        PrefetchPath.Add(SchoolEntity.PrefetchPathCalendar).SubPath.Add(CalendarEntity.PrefetchPathTerm, Nothing, Nothing, Nothing, Sorter)
        PrefetchPath.Add(SchoolEntity.PrefetchPathSpecialScore, Nothing, Nothing, Nothing, Sorter2)

        GetAllSchools = New EntityCollection(New SchoolEntityFactory)
        _Adapter.FetchEntityCollection(GetAllSchools, Nothing, Nothing, Nothing, PrefetchPath)

        Return GetAllSchools
    End Function

Should I be doing it this way? Should I create a new adapter in each routine? Should I create one adapter for the entire bl and not for each manager class?

Thanks,

Fishy

btw, Frans, I think you may have misspelled 'Practises' in your documentation

I haven't tested this, but I think you'll run into problems having a shared/static adapter. The main problem, I think, is the fact that, under Adapter, transactions are nested in the Adapter object itself. So, if you have two calls coming in for data, and one calls Adapter.StartTransaction(), both will be running under that transaction (and getting rolled back, in the event the commit fails). Badness.

I have a simple method called "GetAdapter()" made available in my base manager class that returns a fresh Adapter initialized with the current connection string. I use this adapter for each method in my BL, then dispose of it in a Finally block. This is VB, of course, whereas I would just use a Using block in C#.

Jeff...

jeffreygg wrote:

I have a simple method called "GetAdapter()" made available in my base manager class that returns a fresh Adapter initialized with the current connection string. I use this adapter for each method in my BL, then dispose of it in a Finally block. This is VB, of course, whereas I would just use a Using block in C#.

Jeff...

Would you mind posting both the 'GetAdapter' class and a sample of how you use it?

Thanks Jeff,

Fishy

Equivilant VB.NET code is:

         Dim Adapter As New DataAccessAdapter()
        Try
            ' do your stuff
        Finally
            If Adapter <> Nothing Then
                Adapter.Dispose()
            End If
        End Try

The using statement will be supported in VB.Net 2.0 / 2005

jtgooding wrote:

Equivilant VB.NET code is:

         Dim Adapter As New DataAccessAdapter()
        Try
            ' do your stuff
        Finally
            If Adapter <> Nothing Then
                Adapter.Dispose()
            End If
        End Try

The using statement will be supported in VB.Net 2.0 / 2005

Thanks,

Fishy

Fishy wrote:

jeffreygg wrote:

I have a simple method called "GetAdapter()" made available in my base manager class that returns a fresh Adapter initialized with the current connection string. I use this adapter for each method in my BL, then dispose of it in a Finally block. This is VB, of course, whereas I would just use a Using block in C#.

Jeff...

Would you mind posting both the 'GetAdapter' class and a sample of how you use it?

Thanks Jeff,

Fishy

Sure

This function is located in BaseManager, the class from which all other manager classes inherit. If you need to, you can create an overloaded method which accepts an arbitrary connection string. I didn't need that, in this case.

    Protected Function GetAdapter() As DatabaseSpecific.DataAccessAdapter
        Return New DatabaseSpecific.DataAccessAdapter(Globals.CurrentConnectionString)
    End Function

This is an example of a function located in one of the descendant managers which calls GetAdapter as part of its data operation.

Public Function GetPurchaseOrder(poID as Guid) as PurchaseOrderEntity

Dim adapter as DataAccessAdapterBase  = GetAdapter()
Dim po as New PurchaseOrderEntity(poID)
Dim path as New PrefetchPath2(EntityType.PurchaseOrderEntity)

'buncha processing including prefetch paths, etc, etc

Try
  adapter.FetchEntity(po)
Finally
  adapter.Dispose()
End Try

Return po

End Function

Jeff...

Thanks Jeff

jtgooding wrote:

I think this is a matter of taste, I personally do not create a single adapter, especially a shared/static one for my business layers.

public bool FetchAddressBusinessObject(int addressId, out object dataSource)
{
    using (DataAccessAdapter Adapter = new DataAccessAdapter(DBConnection.ConnectionString))
    {
    // Fetch the data
    }
}

public bool SaveAddress(out int addressID)
{
    using (DataAccessAdapter Adapter = new DataAccessAdapter(DBConnection.ConnectionString))
    {
        return SaveAddress(Adapter, out addressID);
    }
}

public bool SaveAddress(DataAccessAdapter adapter, out int addressID)
{
    // Check for transactions, start checkpoint or transaction etc.
    // do work
}

I dont use static methods either, just in case I ever need to play in the COM+ arena. My code typically looks like that code above, however, I make methods that have DataAccessAdapters in the argument list protected so that client tiers cannot access them.