CODEWITHSUNDEEP
sqlvb.netvisual-studio-2010oledb
AdamWest
AdamWest

Reputation: 29

Multiple Database connections within 1 application - VB .NET

This may have been answered but my search hasn't found what I was looking for.

Basically, I am developing an application which allows the user to build a query at design time, i.e. for users with no prerequisite knowledge of SQL

The application thus far allows the user to select which table(s) from the database they wish to start querying (I won't go into the details of the rest for now)

My confusion is this; I already have the connection to the database in a subroutine which obtains the schema information and filters it to display only the available tables within the database, which then compiles the data into a listbox, here is that sub:

        Public Sub getSchemaInfo()
    Dim ds As New DataSet
    Dim dt As New DataTable
    Dim con As New OleDbConnection
    Dim strDatabaseLocation As String = Application.StartupPath
    Dim da As New OleDbDataAdapter
    Dim i As Integer

    'ds.Tables.Add(dt)


    con.ConnectionString = "Provider=microsoft.jet.oledb.4.0; data source = " & strDatabaseLocation & _
                                                     "\EmployeeDepartment.mdb"
    'clear listbox of any data first
    frmAddTable.lbTables.Items.Clear()

    'Try catch block used to handle connection errors gracefully
    Try
        con.Open()
        'Accessing methods to obtain schema information
        dt = con.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() _
                                     {Nothing, Nothing, Nothing, "TABLE"})

        'loop datatable to store schema information within it
        For i = 0 To dt.Rows.Count - 1

            'compile lbtables with a list of available tables from the database
            frmAddTable.lbTables.Items.Add(dt.Rows(i)!TABLE_NAME.ToString())


        Next


    Catch ex As Exception
        MessageBox.Show(ex.Message.ToString(), "Data Load Error", MessageBoxButtons.OK,
                        MessageBoxIcon.Exclamation)
    End Try

    con.Close()

As you can see, at the very top is all the information regarding the connection to the database and loading of the information into the dataset.

My question is this; Whenever I need to gain access to the database and any information within it, will I have to perform all the connection process (oledbconnection, etc..) or is there I way I can create a class for the connection functions and simply reference them whenever I need to connect?

For example, I now am in the process of creating another sub which gathers the columns, based on the tables chosen in the listbox, and displays it back onto the main form in the relevant checklistbox, again, connecting to the database, therefore would I need to perform all of the connection processes?

Any information would be very useful, thank you!

Upvotes: 1

Views: 6325

Answers (1)

ewahner
ewahner

Reputation: 1189

It is a standard approach to separate your DAL ( Data Access Logic ) from your Business Logic. I would definitely create a separate class for connecting to the database and executing the queries that would bring back the results that you can then either Bind to a control or iterate over inside a loop.

You might even want to look into using EF ( Entity Framework ) or my favorite LINQ to SQL to help in following a standard Design Pattern. By using a framework like EF or L2S you can leverage their ability to cache objects and return back strongly typed objects versus loosely typed. Strongly typed objects give you intelisense and are less prone to common mistakes like misspelling a field from a DataTable.

Upvotes: 2

Related Questions