mardi 17 mai 2016

VB.NET Entity Framework access multiple database server with several DbContext classes

I have a test database and a live database located on different servers. My current design manages to work with both but I'd like to improve the access. I am out of ideas by now and want your opinion.


Question:

How can I solve the following design problem?

I created two EF6 classes to access the different databases. (Highlighted ones) EF6-classes

On program start I define a default connection depending on the build mode.

#If DEBUG Then
        myDbType = AbstractDBAccess.DatabaseType.Test
#Else
        myDbType = AbstractDBAccess.DatabaseType.Live
#End If

I then use myDBType to create DBAccess objects to interact with my DB. From now on it automatically takes care to connect to either test or live DB.

Dim userAccess = new UserDBAccess(myDBtype)
userAccess.GetUser()
userAccess.Dispose()

Dim projectAccess = new ProjectDBAccess(myDBType)
projectAccess.DoWork()
projectAccess.Dispose()

I got this idea after watching a SW-Architecture video on youtube https://www.youtube.com/watch?v=sA-Hp4aBWb4 which I modified to my needs.

So until now this looks like a very clean way but I run into trouble.

My problem is that for each database access I have to copy/paste 99% of my code depending on which server I want to access. E.G.

For the live DB: ctxLive < @see code below

For the testDB: ctxTest < @see code below

I have a base class from which all DBAccess classes derive.

Imports System.Data.Entity
Public MustInherit Class AbstractDBAccess
    Implements IDisposable
#Region "Fields"
    ' Access live db via EF 6
    Protected ctxLive As DBLiveEntities

    ' Access test db via EF 6
    Protected ctxTest As DBTestEntities

    ' Remember DB to access
    Protected myDBType As DatabaseType
#End Region
#Region "Enum"
    ''' <summary>
    ''' Add more data bases here.
    ''' </summary>
    ''' <remarks>Matthias Köhler</remarks>
    Public Enum DatabaseType
        Live = 0
        Test = 1
    End Enum
#End Region
#Region "Constructor"
    Public Sub New(ByVal dbType As AbstractDBAccess.DatabaseType)

        myDBType = dbType ' 

        ' Depending on what type we get from startup we grant access to test or live DB
        Select Case dbType
            Case DatabaseType.Live
                Me.ctxLive = New DBLiveEntities

            Case DatabaseType.Test
                Me.ctxTest = New DBTestEntities
        End Select
    End Sub
#End Region
#Region "Methods"
    Public Function GetDBAccess() As DbContext
        ' My Problem is i need to return two different types in this method.
        ' After creating an instance I save which access this object was intended for with "myDBType"
        ' Both classes derive from DbContext but if I implement it this way I can't see my tables. See screenshot below.
        Select Case myDBType
            Case DatabaseType.Live
                Return Me.ctxLive
            Case DatabaseType.Test
                Return Me.ctxTest
        End Select

        Throw New Exception("No matching case for " & myDBType.ToString)
    End Function
#End Region


The Problem:

You see the Select-Case is 99% the same. Imagine this for complicated code and 15 classes. I just don't like that copy pasting. I just need to change the "ctxLive" or "ctxTest".

Imagine that someone has to add another DB in some years. He has to go through the whole code and add a case to each method.

Isn't there a better way?

Can't-see-tables

Here the matching code for this screenshot.

Public Class UserDBAccess
    Inherits AbstractDBAccess
    Implements IDisposable

    Public Sub New(ByVal dbType As AbstractDBAccess.DatabaseType)
        MyBase.New(dbType)
    End Sub

    Public Sub GetUser()
        ' Currently I have to add a lot of select cases to seperate my live DB and test DB.
        ' They have different connection strings and are on different servers
        Select Case Me.myDBType
            Case DatabaseType.Live
                Me.ctxLive.CCTUsers.Where(Function(u) u.UserName.Contains("StackOverflow"))
            Case DatabaseType.Test
                Me.ctxTest.CCTUsers.Where(Function(u) u.UserName.Contains("StackOverflow"))
        End Select

        ' I have a lot of Copy Pasting which in my opinion is ugly.
        ' I want sth like this to save me all that select cases
        ' The difference here is the "GetDBAccess" 
        Me.GetDBAccess.CCTUsers.Where(Function(u) u.UserName.Contains("StackOverflow"))
    End Sub
End Class

Aucun commentaire:

Enregistrer un commentaire