Can data connections, data adapters, data sets, etc be placed in a global location?

emaduddeen

Well-known member
Joined
May 5, 2010
Messages
171
Location
Lowell, MA & Occasionally Indonesia
Programming Experience
Beginner
Hi Everyone,

Can data connections, data adapters, data sets, etc be placed in a global location?

Many of my forms have code like this:

VB.NET:
    ' Declare a DataAdapter for the DataTable.
    '-----------------------------------------
    Dim objDataAdapter As OleDbDataAdapter

    ' Declare a DataTable object to hold data from the SQL query.
    '------------------------------------------------------------
    Dim objDataTable As DataTable

    ' Declare a DataSet.
    '-------------------
    Dim objDataSet As New DataSet

    ' Declare a DataReader.
    '----------------------
    Dim objDataReader As OleDbDataReader

    ' Declare a connection object.
    '-----------------------------
    Dim objConnection As OleDbConnection = Nothing

    ' Command object to be used in select, insert, delete and update statements.
    '---------------------------------------------------------------------------
    Dim objCommand As OleDbCommand

    Public Sub New()
        InitializeComponent()

            ' Create the connection object to use an SQL query and open it.
            '--------------------------------------------------------------
            objConnection = New OleDbConnection(My.Settings.ISGLConnectionString)
            objConnection.Open()

            ' Create a DataTable object to hold data from the SQL query.
            '-----------------------------------------------------------
            objDataTable = New DataTable()

            ' Create a DataAdapter object for the DataTable.
            '-----------------------------------------------
            objDataAdapter = New OleDbDataAdapter(strSqlStatement, objConnection)

    End Sub

Is there any way to get all of this code in a central location so this stuff can be access from other forms?

If the answer is yes could you show some sample code and instruction on how to do it?

Thanks.

Truly,
Emad
 
You should do some reading on n-tier design, which means separating your application into logical layers. The forms are part of the presentation layer and the ADO.NET objects are part of the data access layer. There is usually a business logic layer in between those two. That is a standard three-tier design but the term "n-tier" is generally used because the number of tiers can vary.
 
Hi Everyone,

Can data connections, data adapters, data sets, etc be placed in a global location?

Follow the DW2 link in my signature (section Creating a Simple Data App), you'll finish with a custom dataset, with tableadapters and other things all in their own namespace - you can then use this from anywhere in your app

i.e. Visual Studio helps you achieve the sensible "n-tier" Application that JMC was talking about..
 
i just create a module, then declare them there, use Public instead of Dim when declearing.

I don't recommend this approach, for a variety of reasons from bad multithreading practice, to it being a shoddy way to write an OO program
 
I used to think creating a single global connection once was more efficient than creating hundreds/thousands of connections as needed (even with opening & closing as needed). MS recommends the opposite though and creating/destroying as needed. This didnt make much sense (even with CJ trying to explain it) to me but after some testing it does seem to be more efficient.
 
Hi Tom,

Is it still more efficient to still move everything into a class module and do the new connections, etc from that location such as in this code?

VB.NET:
Imports System.Data.OleDb

Public Class ClassDatabaseObjects
    ' Declare a DataTable object to hold data from the SQL query.
    '------------------------------------------------------------
    Dim objDataTable As DataTable

    ' Declare a DataAdapter for the DataTable.
    '-----------------------------------------
    Dim objDataAdapter As OleDbDataAdapter

    ' Declare a connection object.
    '-----------------------------
    Dim objConnection As OleDbConnection = Nothing

    ' Command object to be used in select, insert, delete and update statements.
    '---------------------------------------------------------------------------
    Dim objCommand As OleDbCommand

    Public Function GetUsdaDataConnection() As OleDbConnection
        ' Create the connection object.
        '------------------------------
        objConnection = New OleDbConnection(My.Settings.USDADatabaseConnection)

        Return objConnection
    End Function

    Public Function GetNutritionDataConnection() As OleDbConnection
        ' Create the connection object.
        '------------------------------
        objConnection = New OleDbConnection(My.Settings.NutritionDatabaseConnection)

        Return objConnection
    End Function

    Public Function GetDataTable(ByVal pSqlQuery As String, _
                                 ByVal pTableName As String, _
                                 ByVal pPrimaryKeyColumn As String) As DataTable

        ' A DataColumn must be an array.
        ' I need to find out if more then 1 column can be in this array so it can be used as the
        ' primary key for DataTable searches.
        '---------------------------------------------------------------------------------------
        Dim objKeyValueDataColumn(0) As DataColumn

        Try
            ' Create the connection object to use an SQL query and open it.
            '--------------------------------------------------------------
            objConnection = GetNutritionDataConnection()
            objConnection.Open()

            ' Create a DataTable object to hold data from the SQL query.
            '-----------------------------------------------------------
            objDataTable = New DataTable(pTableName)

            ' Create a DataAdapter object for the DataTable.
            '-----------------------------------------------
            objDataAdapter = New OleDbDataAdapter(pSqlQuery, objConnection)

            ' Load the DataAdapter with the data into the DataTable.
            '-------------------------------------------------------
            objDataAdapter.Fill(objDataTable)

            ' Set up the primary key for the data table.
            '-------------------------------------------
            objKeyValueDataColumn(0) = objDataTable.Columns(pPrimaryKeyColumn)
            objDataTable.PrimaryKey = objKeyValueDataColumn

        Catch ex As Exception
            MessageBox.Show(ex.Message)

        Finally
            ' Close the connection if it's currently open.
            '---------------------------------------------
            If objConnection IsNot Nothing Then
                objConnection.Close()
            End If
        End Try

        Return objDataTable
    End Function
End Class

Just want to make sure I'm doing everything the most efficient way.

Truly,
Emad
 
Using your above functions as an example, my personal preference would be to store your above connection strings into local or global variables (at project load and when connection settings change), no need to keep calling additional functions and retrieving from my.settings for a string variables more than once. Then local to your sub, create a USING block for your connection object (ensures your object is destroyed as soon as it hits the End block).

Most of my database functionality calls look like the following:

VB.NET:
        Dim ds As New dsTypedDatasetName

        Using con As New SqlConnection(g_strDbConnection)
            Dim cmd As New SqlCommand
            Dim da As New SqlDataAdapter

            cmd.Connection = con
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = strSql

            da.SelectCommand = cmd
            da.Fill(ds)

            da.Dispose()
            cmd.Dispose()
        End Using

The class object is fine & will save on some coding but you then need to create a new object each time you use it & need to institute a method to be able to properly dispose of your class object as soon as your finished with it.
 
This didnt make much sense (even with CJ trying to explain it) to me but after some testing it does seem to be more efficient.

Because elsewhere in the system is a device created by Microsoft themselves, that recycles the connections for you, and it operates at a lower level (closer to the bottleneck) and with more artificial intelligence than most coders can reasonably put into their programs..
 
Most of my database functionality calls look like the following:

VB.NET:
        Dim ds As New dsTypedDatasetName

        Using con As New SqlConnection(g_strDbConnection)
            Dim cmd As New SqlCommand
            Dim da As New SqlDataAdapter

            cmd.Connection = con
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = strSql

            da.SelectCommand = cmd
            da.Fill(ds)

            da.Dispose()
            cmd.Dispose()
        End Using

Which is odd, given that you have a typed dataset, I'd have expected to see:

VB.NET:
Dim ds as New TypedDataSet
Dim ta as New TypedDataSetTableAdapters.SomeTableAdapter
ta.Fill(ds.SomeDataTable)
 
Which is odd, given that you have a typed dataset, I'd have expected to see:

VB.NET:
Dim ds as New TypedDataSet
Dim ta as New TypedDataSetTableAdapters.SomeTableAdapter
ta.Fill(ds.SomeDataTable)

Just personal preference on my part that I like to keep everything seperate from the actual typed datasets and just use it for the table(s) structures. With different database connections per office location, constantly making changes and not wanting to search thru every typed dataset to see where a particular SP is used and I prefer the organazation and read ability of storing all my queries in a central module location.

Also many of my SP's return multiple resultsets with the single call and use dataadapters tablemappings to assign each of the results to there proper tables rather then seperate hits to the database for each table call.
 
Last edited:
Back
Top