none
Create application using two database (SqlServer & MS Access) RRS feed

  • Question

  • Hello

    I have create a accounting application using vb.net + sqlserver express and my application is working properly.

    I have used typed dataset mostly and sometimes Linq2Sql

    Now, I want to make my application work with MS ACCESS database

    I can do it by duplicating the forms and change the connection string to match the db engine. Is this is the way to go?

    What is the standard for creating application that works in multi database engines?

    TIA


    .Net Blog VFP Blog

    Tuesday, August 13, 2019 10:35 AM

Answers

  • I have solved this by creating a class that works on OleDb Connection

    OleDb can work with MSACCESS and MSSQL

    I only had to change the connection string

    I am using CommandBuilder so it will handle the correct Update/Delete statement depends on my select

    something like this

    Dim OleOv4 as new OleOv4(my_connection_string)
    OleOv4.GetDataByTable("ITEM")
    Me.Datagridview1.Datasource = OleOv4.BindingSource

    Thanks everyone who helped


    .Net Blog VFP Blog

    • Marked as answer by Samir Ibrahim Wednesday, November 25, 2020 11:23 AM
    Wednesday, November 25, 2020 11:22 AM

All replies

  • Hello,

    Using typed data via the data wizards is the wrong path, the proper path to allow sharing is to have a class project for each database that have the same methods which are called from your form project. Using typed data classes are hard coded to a data provider and also data types may be the same yet the underlying provider OleDb and SqlClient have different types for underlying data.

    With that although it may be hard at this point is to look at using managed data providers rather than typed data classes.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Proposed as answer by KHURRAM RAHIM Wednesday, November 25, 2020 9:23 PM
    • Unproposed as answer by KHURRAM RAHIM Wednesday, November 25, 2020 9:23 PM
    Tuesday, August 13, 2019 10:43 AM
    Moderator
  • I got your point

    If I created a class project for each data provider. How to switch between them in my application? using IF End If before each data processing ?


    .Net Blog VFP Blog

    Tuesday, August 13, 2019 12:14 PM
  • Let's say that the application starts up and checks to see if the MS-Access database exists, if so this is your decision point, use the MS-Access classes while if the MS-Access database does not exists use SQL-Server classes. Does this make sense?

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Proposed as answer by KHURRAM RAHIM Wednesday, November 25, 2020 9:24 PM
    Tuesday, August 13, 2019 3:29 PM
    Moderator
  • You can use a DAL.

    http://geekswithblogs.net/joriente/archive/2008/03/31/120886.aspx

    Tuesday, August 13, 2019 8:30 PM
  • Hello Karen

    <<see if the MS-Access database exists, if so this is your decision point>>

    This is not what i am asking for.

    Let say client_A chose to work with access database, and client_B with Sql Server

    I have my start form frm_start where in the load event i check if "as you suggested" if MS-Access database exists" to decide my application database engine it will use

    now, I have a form called frm_item_list which shows the list of items in grid

    I have also 2 classes or 2 datasets ot 2 DAL (whatever) for my 2 database

    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

    in the frm_item_list load event, what/how should i code?

        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
    I am asking, if in each form, in each data processing I should create if statement? is this the way to go?




    .Net Blog VFP Blog


    Wednesday, August 14, 2019 7:12 AM
  • Hello

    Yes, I can use 2 DAL for each DB. My question is how to code the use of 2 DAL in application? 

    Please check my response to Karen.

    Ty


    .Net Blog VFP Blog

    Wednesday, August 14, 2019 7:15 AM
  • I have solved this by creating a class that works on OleDb Connection

    OleDb can work with MSACCESS and MSSQL

    I only had to change the connection string

    I am using CommandBuilder so it will handle the correct Update/Delete statement depends on my select

    something like this

    Dim OleOv4 as new OleOv4(my_connection_string)
    OleOv4.GetDataByTable("ITEM")
    Me.Datagridview1.Datasource = OleOv4.BindingSource

    Thanks everyone who helped


    .Net Blog VFP Blog

    • Marked as answer by Samir Ibrahim Wednesday, November 25, 2020 11:23 AM
    Wednesday, November 25, 2020 11:22 AM