Object-Oriented Programming and databases

Question

I am learning about object-oriented programming. My application is based on two MySQL databases. So I get some records, insert some data to my databases very often.

Is it correct to have a class called MySQL? This is a very simple class, there are only two methods - Connect() and Disconnect(). This class is shown below:

Imports MySql.Data.MySqlClient

Public Class MySQL 
    Private csJablotron As String = My.Settings.csJablotron
    Private csBCS As String = My.Settings.csBCS
    Private _connection As MySqlConnection

    Public ReadOnly Property Connection() As MySqlConnection
        Get
            Return _connection 
        End Get
    End Property


    Public Sub Connect(shop As String)

        Select Case shop
            Case "jablotron"
                _connection = New MySqlConnection(csJablotron)
            Case "bcs"
                _connection = New MySqlConnection(csBCS)
            Case Else
                MessageBox.Show("There is no shop with this name.")
                Exit Sub
        End Select

        Try
            _connection .Open()
        Catch ex As MySqlException
            MessageBox.Show(ex.Message)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub


    Public Sub Disconnect()
        Try
            _connection .Dispose()
        Catch ex As MySqlException
            MessageBox.Show(ex.Message)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
End Class

And then in my main program when I need to use my database I just simply do that:

Try
    mySql.Connect("bcs")    'mySql is an object of MySQL class
    ... 
    'here I select some records and do something with them...
Catch
    MessageBox.Show("Error")
Finally
    mySql.Disconnect()
End Try

Is it correct in object-oriented programming? Or better is just always use a using statement when I need a database connection, and create a connection string there and don't even use this class? I know that this is a theoretical question but I am very curious what is better.

Check zhe singelton design pattern for that stackoverflow.com/questions/2902347/… — ruedi
– ruedi, Commented Nov 22, 2014 at 11:27

BradleyDotNET · Accepted Answer · 2014-11-19 20:41:29Z

1

Abstracting your persistence layer (database) is always a good idea. So, keep your class around!

I would recommend a few things:

Using a using statement only makes sense if you implement IDisposable. Otherwise; your method is fine.
Don't do direct SELECT statements and the like in your code-behind. Those should be hidden behind methods in the persistence class.

Otherwise; you are on the right track.

answered Nov 19, 2014 at 20:41

BradleyDotNET

61.5k10 gold badges106 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

XardasLord Over a year ago

Thanks for express reply! So I should add a method like ExecuteQuery() which gets a string query parameter? Then I could execute this method from other classes and do SELECT, UPDATE or DELETE statements. Yea, it makes sense because MySQL class should connect, disconnect and execute queries.

BradleyDotNET Over a year ago

@XardasLord No. You're other classes should know nothing about SQL. You should add methods to MySQL like "GetCars" that then does the appropriate SELECT. This becomes especially useful when you need to do the same query over again.

XardasLord Over a year ago

Oh okay. It is good when I need to SELECT a whole table or something basic. But what when I need a specific query? Something detailed? Should I add all methods with all SELECT statements which I use to MySQL class then?

BradleyDotNET Over a year ago

@XardasLord Yes. Remember that you can pass parameters to the methods if you want to do WHERE or similar queries. In a perfect world, this class inherits from an interface, and you could replace it with some other persistence layer without changing any client code.

BradleyDotNET Over a year ago

@XardasLord Fully static classes are difficult to Dependency Inject (and thus unit test their dependencies). I've done this as a singleton that inherits from an IPersistence interface, then used a Service Locator to retrieve the singleton instance. If you don't care about testing; by all means, make it static (just make sure its thread safe!)

|

Collectives™ on Stack Overflow

Object-Oriented Programming and databases

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related