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 http://stackoverflow.com/questions/2902347/what-is-the-best-way-to-implement-singleton-design-pattern-in-c-sharp-with-perfo — ruedi, Nov 22 '14 at 11:27

score 1 · Accepted Answer · answered Nov 19 '14 at 20:41

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 '14 at 20:41

BradleyDotNET

60,462
10
96
117

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. – XardasLord Nov 19 '14 at 21:03
@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. – BradleyDotNET Nov 19 '14 at 21:06
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? – XardasLord Nov 19 '14 at 21:13
1

@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 Nov 19 '14 at 21:14
I have another question. Should I make this class static or not? – XardasLord Nov 22 '14 at 11:12
1

@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!) – BradleyDotNET Nov 22 '14 at 17:40

Object-Oriented Programming and databases

1 Answers1