Database Class

[CAVESTER]

Hi all,

 

I'm starting a new project and want to try and use OOP as much as possible (I'm not that familiar with it yet but get the general idea)...anyway, I want to connect to a MySQL database and do various database things such as read data, edit data, insert and delete etc.

 

My whole app will use the database methods so therefore I am wanting to create a Database class so that I can encapsulate all the database methods I want to use within the one class. My question is how do I go about doing this?...Here is what I have thus far...

 

Public Class CDatabase


   Const cConString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
                       "SERVER=localhost;" & _
                       "DATABASE=eQA;" & _
                       "UID=root;" & _
                       "PASSWORD=;" & _
                       "OPTION=3"

   Dim oConn As OdbcConnection

   Public Sub New()
       oConn = New OdbcConnection(cConString)
       Me.Connect()
   End Sub

   Private Sub Connect()
       oConn.Open()
   End Sub

   Private Sub Disconnect()
       oConn.Close()
   End Sub

   Public Function ReadData(ByVal strSQL As String) As OdbcDataReader
       Dim oCommand As New OdbcCommand(strSQL, oConn)
       'Dim oReader As OdbcDataReader
       '
       oReader = oCommand.ExecuteReader()
       '
       Return oReader
       '
       oReader.Close()  // Buggered because of this!
   End Function
End Class

 

My problem is that I have to close the DataReader (best practice) but I cant as it stands because the .Close method gets ignored because of the "Return oReader" statement and If I close it first before returning it to the calling class then the DataReader in the calling class is closed and I can no longer use it. Do I have to create separate methods used to close the DataReader when I am finished with it in the calling class? HELP IM CONFUSED :confused:

 

Cheers,

 

Lee.

[Roey]

Have you thought about using a DataAdapter instead of a DataReader as if you are doing reads and updates it can involve a lot less code, as you can use the CommandBuilder (not very efficient) but good for RAD.

[CAVESTER]

Yes, the class as I see it would also incapsulate functions which would allow the use of the DataAdapter, however my question is really to do with how I should be doing it in an OOP manor i.e as in my example how do I return the DataReader object or DataAdapter object for that matter yet still be able to close it hence keeping everything "clean" without jeperdising the returned object.

 

Or...do I need to create another method in my class which just closes the DataAdapter and hence need to call it after I have used the DataAdapter method and finished with it?

 

i.e

 

Dim oDB as New CDatabase //class CDatabase is the class in my above code.

Dim oDA ad odbcDataAdapter

'

oDA = oDB.ReadData(SELECT * FROM Customer)

 

// Do stuff with returned oDA DataAdapter object

 

oDB.CloseReader

 

 

 

Cheers,

 

Lee.

[Roey]

This is an example of how I've done it, not too sure on how good it is but it works !!!

 

 

Public Function All_Customers() As DataSet

 

selectSQL = "SELECT * FROM tblCustomerMaster ORDER BY CustomerCompanyName ASC"

 

Dim mySelectCommand As OleDbCommand = New OleDbCommand(selectSQL, oConnection)

Dim myOleDbDataAdapter As OleDbDataAdapter = New OleDbDataAdapter(mySelectCommand)

 

Try

myOleDbDataAdapter.Fill(dsReturn, "Customer")

All_Customers = dsReturn

Catch exc As Exception

Throw exc

Finally

mySelectCommand.Dispose()

myOleDbDataAdapter.Dispose()

oConnection.Dispose()

End Try

 

End Function

 

Public Function Update_Customers(ByVal dsCustomer As DataSet) As Boolean

 

selectSQL = "SELECT * FROM tblCustomerMaster ORDER BY CustomerCompanyName ASC"

 

Dim myOleDbDataAdapter As OleDbDataAdapter = New OleDbDataAdapter()

myOleDbDataAdapter.SelectCommand = New OleDbCommand(selectSQL, oConnection)

Dim myCommandBuilder As New OleDbCommandBuilder(myOleDbDataAdapter)

 

Try

myOleDbDataAdapter.Update(dsCustomer, "Customer")

Update_Customers = True

Catch exc As OleDb.OleDbException

Update_Customers = False

Throw exc

Catch exc As Exception

Update_Customers = False

Throw exc

Finally

myCommandBuilder.Dispose()

myOleDbDataAdapter.Dispose()

oConnection.Dispose()

End Try

 

End Function

[angula]

What about leaving it open and then calling oConn.Dispose for clean-up?

 

Also, here is some interesting text on OOP from:

 

OOP with Microsoft Visual Basic .NET and Microsoft Visual C# Step by Step

by Robin A. Reynolds-Haertle

 

Not a great book overall but it does have its moments.

 

 

 

Considerations in Designing Reusable Classes

When you design reusable classes, certain concepts are important to keep in mind. Some of these are object-oriented concepts, while some are general programming concepts.

 

Containment - You won�t know what kind of object is hosting the class. Your Deck (of cards) class could be a field in a Game class, a Microsoft Windows Form class, or a custom control. Therefore, you want to be careful not to make any assumptions about the context of the Deck class. Although you�ll develop the Deck class in the context of a Windows application, the Deck class won�t contain any references to a form.

 

You might want to add behavior that�s dependent on the client code. For example, you might want to add code that draws the card. You could add a method that takes an argument to specify a form to draw on. However, you could also add a method that takes a .NET Framework Graphics object. This would allow you to write code to draw the card on any object that can create a Graphics object, and your object would be usable across a wider client base.

 

Abstraction - Be clear about your abstraction; a class should do one thing well. It�s tempting to add a lot of support functions when you�re designing a reusable class. Yet every method, property, and event that you add to the interface limits your choices in implementation, making it more difficult for the class to do one job well. A clear abstraction is easier for developers to use correctly because the properties and methods make sense and the behavior is predictable.

 

Interface - Provide a complete interface but don�t go overboard. Implement the interface well enough so that the next developer can extend it. For example, the Hand class you implement in this chapter has a method to remove pairs. You could also add a method to remove runs of cards�for example, the Jack, Queen, and King of Hearts. However, you could instead create a new class, based on the Hand class. Because the Hand class exposes all the cards through an indexer and provides a Remove method, you can implement the method to remove the run of cards in the new extended class.

 

Client code - Well-designed classes streamline the client code. Much of the looping and decision structures are contained in the class methods rather than in the client code. The method calls are marked by an absence of arguments because the class encapsulates the information needed to execute the method.

 

Hope this helps.

[Derek Stone]

Arguably you shouldn't need to return the DataReader object at all. It's intended use is fast forward-only open-late close-early data access. Instead encapsulate the data it contains in an application-defined class. If the reader is returning data about a customer define a class named Customer and loop through the returned results, filling a new instance of the Customer class and returning it in place of a reader or DataSet. Doing so will yield a "truly OOP" design which, while not always practical to implement, is what you designated in your request.

[CAVESTER]

Hi, I tried what u have suggested but I have had no joy.

 

here is what I have, I get no error messages or anything just no updated database!!!:mad:

 

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

       Dim MyDS As New DataSet

       MyDS = Me.GetProjects

       Dim Str As String = MyDS.Tables(0).Rows(0).Item(0)

       ' Create a new row in the dataset...
       Dim MyRow As DataRow
       MyRow = MyDS.Tables.Item(0).NewRow()

       ' Set the field values of the new row...
       MyRow("project_number_id") = 1
       MyRow("project_title") = "test"
       MyRow("email_path") = "test path"
       MyRow("live") = True


       ' Add the new row to the collection...
       MyDS.Tables.Item(0).Rows.Add(MyRow)

       Me.UpdateProjects(MyDS)

   End Sub

   Public Function GetProjects() As DataSet

       Dim MyConn As New OdbcConnection(cConString)
       Dim selectSQL As String = "SELECT * FROM projects"
       Dim mySelectCommand As OdbcCommand = New OdbcCommand(selectSQL, MyConn)
       Dim myDataAdapter As OdbcDataAdapter = New OdbcDataAdapter(mySelectCommand)
       Dim dsReturn As New DataSet

       Try
           myDataAdapter.Fill(dsReturn, "Projects")
           Return (dsReturn)
       Catch exc As Exception
           Throw exc
       Finally
           mySelectCommand.Dispose()
           myDataAdapter.Dispose()
           MyConn.Dispose()
       End Try

   End Function


   Public Function UpdateProjects(ByVal dsProjects As DataSet) As Boolean

       Dim MyConn As New OdbcConnection(cConString)
       Dim selectSQL As String = "SELECT * FROM projects"
       Dim myDataAdapter As OdbcDataAdapter = New OdbcDataAdapter
       myDataAdapter.SelectCommand = New OdbcCommand(selectSQL, MyConn)
       Dim myCommandBuilder As New OdbcCommandBuilder(myDataAdapter)

       Try
           myDataAdapter.Update(dsProjects, "Projects")
           Return True
       Catch exc As OleDb.OleDbException
           Return False
           Throw exc
       Catch exc As Exception
           Return False
           Throw exc
       Finally
           myCommandBuilder.Dispose()
           myDataAdapter.Dispose()
           MyConn.Dispose()
       End Try

   End Function

 

Can anyone spot whats wrong?

 

Cheers,

 

Lee.

[XyBoy]

the syntax

{

return false

throw exc

}

looks weird to me (the return statement ends the execution of the function, so the exception is not rethrown)

 

So that might be why you don't get any error. I wouldn't be surprised if there actually was an exception when updating.

 

Second, I don't know if you can write the select statement without ";" at the end.

("select * from projects;")