What is the way to use 2 database (SQL server & access) in one application?

Question

0.00/5 (No votes)

See more:

Hello

I have created an accounting application using vb.net + Sql server using typed Dataset and Linq2Sql.

Now I want to create a second version for my application using MS-Access

What is the best way to go for that? creating a second copy of my application?

Can I have one form (item list) using Sql Server or Access depending on client choice?

What I have tried:

- I tried to cast sqlserver dataset to access dataset (failed)

- I created one dataset and tried to change the connection string (did not worked)

- I created 2 class for each database, but i could not figure out how to use one variable to handle both class (this below worked, but before each data processing I had to create IF statement to check what to use) Is this right?

VB

Public Class Access_Class
    Function GetTable_ITEM As DataTable
        '''' some code to return data
    End Function
End Class

Public Class SqlServer_Class
    Function GetTable_ITEM As DataTable
        '''' some code to return data
    End Function
End Class

Private Sub frm_itemlist_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        If My.Computer.FileSystem.FileExists("myaccess.accdb") Then
            Dim o As New Access_Class
            Dim dt = o.GetTable_ITEM
            Me.DataGridView1.DataSource = dt
        Else
            Dim o As New SqlServer_Class
            Dim dt = o.GetTable_ITEM
            Me.DataGridView1.DataSource = dt
        End If
    End Sub

Posted 16-Aug-19 21:21pm

Samir Ibrahim

Updated 5-Jan-21 22:55pm

Add a Solution

4 solutions

Solution 4

I ended up creating my own ORM using OleDbCommandBuilder

In my case MS ACCESS and MSSQL Server both worked fine using OleDb.OleDbConnection

Thank you everyone who put lights IN front of me.

Posted 5-Jan-21 22:55pm

Samir Ibrahim

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2019-08-16T21:46:00

Solution 1

That's a bit complicated, depending on how you wrote the original application.
If you wrote it in layers, with a Presentation Layer (PL) to handle user interaction, a Business Layer (BL) to handle the business rules for your data, and a separate Data Layer (DL) to handle the actual storage then it becomes pretty simple (ish) - particularly if each Layer is in a separate Assembly.
All you'd have to do is create a new DL (based on eth original, but targeted to Access rather than MS SQL) and reference that in your app instead of the SQL layer.

But ... if you've written a monolithic app which uses the DB from all over the place then you have a major job of work on your hands, and it will be extremely difficult to test and maintain the software.

You can't cast SQL objects to Access, and even if you could, it wouldn't work: the Acccess syntax is different to MS SQL in some places - notably parameterised queries which I hope you are using throughout!

Posted 16-Aug-19 21:46pm

OriginalGriff

Comments

Samir Ibrahim 17-Aug-19 3:56am

When I created my application, using 2 database was not in consideration. So yes, its "complicated"

Let Say I have one PL one BL two DL

How to tell the form from where to fetch the data?

OriginalGriff 17-Aug-19 4:19am

The easiest way is to create an abstract DL class, which presents "agnostic" methods to the outside world.
Your two (or three, or four) different concrete DB classes derive from that and implement the methods for each specific DB engine.
The rest of the app uses the abstract class and it's methods, having set an instance of the appropriate concrete class at startup (or config, whichever).

But unless your app was designed for that, it's going to be a fair amount of work.
And looking at the code sample below ... you've thrown the existing code together without planning much in advance at all. It's going to be "complicated" for you do change!

Samir Ibrahim 17-Aug-19 4:41am

Thank you for guiding.

OriginalGriff 17-Aug-19 4:53am

You're welcome!

Samir Ibrahim 17-Aug-19 4:07am

I can create 3rd DL?

Public Class dal3_class
Private _DbEngine As String
Sub New(DbEngine As String)
_DbEngine = DbEngine
End Sub

Function GetTable_ITEM As DataTable
Select Case _DbEngine
Case "A"
Dim o As New Access_Class
Dim dt = o.GetTable_ITEM
Return o

Case "S"
Dim o As New SqlServer_Class
Dim dt = o.GetTable_ITEM
Return o
End Select
End Function
End Class

Is this a good concept?

RickZeeland · Accepted Answer · 2019-08-16T23:10:00

Solution 2

When you use an ORM it's easy to switch between databases (except for some complicated ones like NHibernate).
Here's an overview: https://www.slant.co/topics/16667/~orms-for-c[^]

Also see: XAF_how-to-change-connection-to-the-database-at-runtime[^]
And: A.Bekiaris's .Net / XAF Blog: Connecting to any database at runtime[^]

Posted 16-Aug-19 23:10pm

RickZeeland

Updated 18-Aug-19 22:53pm

v3

Comments

Samir Ibrahim 19-Aug-19 3:32am

I downloaded the XPO ORM and checking how it work.

If I use it. I will end up creating class to handle the usage of 2 Data Model?

RickZeeland 19-Aug-19 4:44am

Haven't used XPO myself, but it seems to me that you need only 1 data model.

Samir Ibrahim 19-Aug-19 7:24am

Interesting...

The example given is for two database of the same engine..

I will check if it can be tweaked for two database of different engine.

Thanks.

Member 14552976 · Accepted Answer · 2019-08-19T01:37:00

it possible to connect multiple database with one application." Yes. Very straightforward - two connection strings, two different connections. If they're actually two different platforms like MySQL and SQL-Server, then you may need separate code libraries to access them, unless you use an ORM or other abstraction layer which can handle both without changes. Basically it's no different to using one database, except that you have 2. Same again if you have 20.