Database Connection Class

[combs]

Hi...
This is my [newbie] first attempt at creating a class to handle all my database connections, creating datasets and - eventully - inserts, updates and deletes.

I created this to avoid code duplication and needless call to and from the database, but I'm not sure that I've done anything better than what I started with (without the class). I still have a couple of subs to workout and complete, but I was hoping that those of you with the knowledge of this would take a look and let me know if I'm started down the right path here... I'd like to be as efficient as possible, but don't know if I'm accomplishing anything towards that goal...

What I have now (code below) is a form with a few tabs. Whe the form loads, a query is sent to the database for the different divisions and that is used as the titles on the respective tabs. It works now...

I'd really appreaciate any help or suggestions anyone could offer.

Public Class Form1
    Inherits System.Windows.Forms.Form


    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim ds As New DataSet
        Dim sql As String
        Dim db As New DBConn

        sql = "SELECT DISTINCT Employees1.[Division] FROM Employees1 ORDER BY Employees1.[Division] DESC"

        ds = db.get_data(sql)

        Me.TabPage1.Text = ds.Tables("my_dataset").Rows(0).Item(0)
        Me.TabPage2.Text = ds.Tables("my_dataset").Rows(1).Item(0)

    End Sub

    Private Sub Form1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
        Dim con As OleDb.OleDbConnection
        Dim db As DBConn
        db.CloseDB(con)
    End Sub
End Class

Public Class DBConn
    Public Function get_data(ByVal sql As String) As DataSet

        Dim con As New OleDb.OleDbConnection
        con = OpenDB()

        Dim da As OleDb.OleDbDataAdapter

        If con.State = ConnectionState.Closed Then con.Open()

        da = New OleDb.OleDbDataAdapter(sql, con)

        Dim my_ds As New DataSet

        da.Fill(my_ds, "my_dataset")

        Return my_ds

    End Function

    Public Function CloseDB(ByRef db_con As OleDb.OleDbConnection)
        db_con.Close()
        [b][green]'*** not working -- need the current database connection to pass in???[/green][/b]
    End Function

    Public Function OpenDB() As OleDb.OleDbConnection
        Dim con As New OleDb.OleDbConnection
        Dim ds As New DataSet
        Dim da As OleDb.OleDbDataAdapter
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = \\4fl-w2k01\public\OutOfOffice\OutOfOffice_Depot.mdb"
        Return con
    End Function

    Public Function GetConn() As OleDb.OleDbConnection
        Dim con As New OleDb.OleDbConnection
        [b][green]'*** somehow get the current database connection and pass it back[/green][/b]
    End Function

End Class

 

[mansii]

I think that you're on the right track. But if I may suggest:

First, put some error handlings there.
For instance, in your Form1_Load event, how if the db.get_data(sql) function failed to return any dataset. The rest of your code will raise some errors, right?

Second, you don't need the CloseDB function if you close any opened conection right after you use it. Please note that DataAdapter doesn't need the connection epened.
This will affect your Form1_Closing event.

Regards,
mansii

 

[woogoo]

Hi,

The reason your Close() method isn't working is that your Connection Object has gone out of scope (died) as it's instanciated locally in your get_data(...) method so dies when this method returns.

You may want to use some private member variables as well to allow you to persist the connection so you don't need to re-connect to the DB on every call.

Also consider putting in an Init() method and Enumereration or similar to allow you to create a connection to the DB once the class has been newed.

Dim m_Conn as DBConn = New DBConn()
m_Conn.Init(DBConn.Oracle)

Create an instance and Initialise for Oracle Database.

You really should extend your error handling to include try..catch...finally...endtry within your database methods.


woogoo

 

[combs]

Thanks for your replies!

mansii: I have never used the error handling in VB.Net before, but am planning on putting that in when I get to it.
If I close a db connection right after opening it, am I saving anything (efficiency-wise) by putting this in a class? Do opening and closing operations take a toll on performance?

I plan to use this in a multi-user environment where lots (20 to 30) people will be viewing/updating all the time. However all database operations are relatively simply - mostly updates with no insertions or deletions.

woogoo: Thanks for your comments as well. I will try to institute your suggestions. Is there a FAQ or a particular website that you could point me to that demonstrates some of the subjects you brought up?

Thanks again!

 

[mansii]

In my opinion, yes. But, since 20-30 people can access (update) the database at the same time, you need to consider record locking too. But, as I mentioned, filling out a dataset using DataAdapter (at least in VS 2005) does not require an opened connection. I mean not hard codedly .Open.

Regards.

 

[pmegan]

Hi,

Here are a few things I would do differently. I'm sure some will disagree, but getting a bunch of feedback from our peers helps us get better.

First I'd go through each procedure and get rid of variables that aren't necessary anymore. The OpenDB function has a dataAdapter and DataSet that are declared and never used. Also, the Form.Closing event creates a database connection and closed it. This may have been needed in an earlier build, but it doesn't really accomplish anything anymore.

Make the DBConn instance in your form and the database connection instance in DBConn private global variables. If they aren't going to change, there's no need to recreate them every time you open a connection.

Make a constructor for the DBConn class and define your database connection there. Since there's no security sensitive info in the connection string, I'd move it to the config file instead of hard coding it. It's easier to point the app to a new location if the path changes.

This is probably the trickiest part. If you can create stored procedures in the database, write one each for the select, insert, update and delete commands for your data adapter and get rid of the ad hoc query. Create a collection of commands referencing these procedures when each form loads and pass it into the get_data procedure. The coding will be harder, but if you have to change the query later you won't have to recode and redistribute your app. You can also pass the collection into one update_data procedure instead of writing separate routines to insert, update and delete data.

Here's a devcity article on using stored procedures in access with .Net.

http://www.devcity.net/Articles/18/msaccess_sp.aspx

Make the OpenDB and CloseDB functions return boolean values, this way you can interupt processing gracefully if your connection failes to open or close. Also, check the state of your connection before opening/closing so you don't throw an exception. Something like this:

 Private Function OpenDB() As Boolean
	  Try
			If Me.m_dbConn.State <> ConnectionState.Open Then
				 Me.m_dbConn.Open()
			End If
			Return True
	  Catch ex As SqlClient.SqlException
			RaiseEvent Err(ex)
			Return False
	  Catch ex As Exception
			RaiseEvent Err(ex)
			Return False
	  End Try
 End Function

 Private Function CloseDB() As Boolean
	  Try
			If Not m_dbConn Is Nothing AndAlso m_dbConn.State = ConnectionState.Open Then
				 Me.m_dbConn.Close()
			End If
			Return True
	  Catch ex As SqlClient.SqlException
			RaiseEvent Err(ex)
			Return False
	  Catch ex As Exception
			RaiseEvent Err(ex)
			Return False
	  End Try
 End Function

So in the get_data function, you don't have to create the dbconnection. Just open it. If it fails let the user know about it, otherwise load up your data:

Try
	If OpenDB() then
		' load your data
	End If
Catch ex As Exception
	'TODO: handle exceptions
Finally
	CloseDB()
End Try