2
\$\begingroup\$

EDIT BELOW

I am trying to create resusable classes for MSSQL and MySQL queries (later also Update and Delete queries). I tried it doing the OOP-way. Here is my result:

Einstellungen.vb:

Public Class Einstellung

Private _Server As String
Public Property Server As String
    Get
        Return _Server
    End Get
    Set(value As String)
        _Server = value
    End Set
End Property

Private _Benutzer As String
Public Property Benutzer As String
    Get
        Return _Benutzer
    End Get
    Set(value As String)
        _Benutzer = value
    End Set
End Property

Private _Passwort As String
Public Property Passwort As String
    Get
        Return _Passwort
    End Get
    Set(value As String)
        _Passwort = value
    End Set
End Property

Private _Datenbank As String
Public Property Datenbank As String
    Get
        Return _Datenbank
    End Get
    Set(value As String)
        _Datenbank = value
    End Set
End Property

Private _Art As String
Public Property Art As String
    Get
        Return _Art
    End Get
    Set(value As String)
        _Art = value
    End Set
End Property

Private _Port As UShort = 3306
Public Property Port As UShort
    Get
        Return _Port
    End Get
    Set(value As UShort)
        _Port = value
    End Set
End Property

Private _IntegratedSecurity As Boolean
Public Property IntegratedSecurity As Boolean
    Get
        Return _IntegratedSecurity
    End Get
    Set(value As Boolean)
        _IntegratedSecurity = value
    End Set
End Property

Public Sub New()
End Sub

Private _EinstellungenSchnittstelle As EinstellungenSchnittstelle
Public Property EinstellungSchnittstelle As EinstellungenSchnittstelle
    Get
        Return _EinstellungenSchnittstelle
    End Get
    Set(value As EinstellungenSchnittstelle)
        _EinstellungenSchnittstelle = value
    End Set
End Property

Public Function BekommeVerbindung() As System.Data.Common.DbConnection
    If Art = "MySQL" Then
        Return New MySqlConnection() With {
            .Host = Server,
            .UserId = Benutzer,
            .Password = Passwort,
            .Database = Datenbank,
            .Port = Port,
            .Charset = "UTF8"}
    ElseIf Art = "MSSQL" Then
        Dim con As New SqlConnection
        If IntegratedSecurity = True Then
            con.ConnectionString = New SqlConnectionStringBuilder With {
                .DataSource = Server,
                .InitialCatalog = Datenbank,
                .IntegratedSecurity = True}.ConnectionString
        End If
        con.ConnectionString = New SqlConnectionStringBuilder With {
                .DataSource = Server,
                .UserID = Benutzer,
                .Password = Passwort,
                .InitialCatalog = Datenbank}.ConnectionString
        Return con
    Else
        Return Nothing
    End If
End Function

End Class

SqlMethoden.vb:

Public Class SqlMethoden

Enum SqlDataType
    VarChar
    Text
    Int
End Enum

Private AbfragenMY As New Dictionary(Of String, String) From
    {
        {"Datenbanken", "SHOW DATABASES"}
    }

Private AbfragenMS As New Dictionary(Of String, String) From
    {
        {"Datenbanken", "SELECT name FROM master.dbo.sysdatabases"}
    }

Private _Verbindung As System.Data.Common.DbConnection
Public Property Verbindung As System.Data.Common.DbConnection
    Get
        Return _Verbindung
    End Get
    Set(value As System.Data.Common.DbConnection)
        _Verbindung = value
    End Set
End Property

Sub New(ByVal Con As System.Data.Common.DbConnection)
    Me.Verbindung = Con
End Sub

Public Function SqlSelect(ByVal Name As String, Optional ByVal Parameter As DbParameter() = Nothing) As DataTable
    If Me.Verbindung.GetType Is GetType(MySqlConnection) Then
        Using con = Verbindung
            Using cmd As New MySqlCommand(AbfragenMY(Name), con)
                If Parameter IsNot Nothing Then
                    cmd.Parameters.AddRange(Parameter)
                End If
                Using ada As New MySqlDataAdapter(cmd)
                    Using dt As New DataTable
                        ada.Fill(dt)
                        Return dt
                    End Using
                End Using
            End Using
        End Using
    Else
        Using con = Verbindung
            Using cmd As New SqlCommand(AbfragenMS(Name), con)
                If Parameter IsNot Nothing Then
                    cmd.Parameters.AddRange(Parameter)
                End If
                Using ada As New SqlDataAdapter(cmd)
                    Using dt As New DataTable
                        ada.Fill(dt)
                        Return dt
                    End Using
                End Using
            End Using
        End Using
    End If
End Function

Public Function ErstelleParameter(ByVal Name As String, ByVal Value As Object, ByVal Typ As SqlDataType) As DbParameter
    If Me.Verbindung.GetType Is GetType(MySqlConnection) Then
        Dim parameter As New MySqlParameter()
        With parameter
            .ParameterName = Name
            .Value = Value
            Select Case Typ
                Case SqlDataType.Int
                    .MySqlType = MySqlType.Int
                Case SqlDataType.Text
                    .MySqlType = MySqlType.Text
                Case SqlDataType.VarChar
                    .MySqlType = MySqlType.VarChar
            End Select
        End With
        Return parameter
    Else
        Dim parameter As New SqlParameter
        With parameter
            .ParameterName = Name
            .Value = Value
            Select Case Typ
                Case SqlDataType.Int
                    .SqlDbType = SqlDbType.Int
                Case SqlDataType.Text
                    .SqlDbType = SqlDbType.Text
                Case SqlDataType.VarChar
                    .SqlDbType = SqlDbType.VarChar
            End Select
        End With
        Return parameter
    End If
End Function

End Class

This is how i call the SqlSelect-Method:

Dim Einstell As New Einstellung 'Properties will be filled with DataBinding
    Dim Sql As New SqlMethoden(Einstell.BekommeVerbindung)
    Dim result = Sql.SqlSelect("Datenbanken")
    Dim resultParameter = Sql.SqlSelect("Datenbanken", {
                                        Sql.ErstelleParameter("@Test", "Test", SqlMethoden.SqlDataType.Text),
                                        Sql.ErstelleParameter("@Test2", "Test2", SqlMethoden.SqlDataType.Text)})

It works fine for me, but I think its not very good programmed (as i am still learning). Is there even something good about this code? How to do it better?

EDIT: So I created four new classes: SqlVerbindung (Base class), MsSqlVerbindung and MySqlVerbindung (implements the Base class) and SqlFactory (creates my My or Ms class)

SqlVerbindung.vb:

Imports System.Data.Common

<Serializable>

Public MustInherit Class SqlVerbindung

Enum SqlDataType
    VarChar
    Text
    Int
End Enum

Protected AbfragenMY As New Dictionary(Of String, String) From
    {
        {"Datenbanken", "SHOW DATABASES"}
    }

Protected AbfragenMS As New Dictionary(Of String, String) From
    {
        {"Datenbanken", "SELECT name FROM master.dbo.sysdatabases"}
    }

Private _Server As String
Public Property Server As String
    Get
        Return _Server
    End Get
    Set(value As String)
        _Server = value
    End Set
End Property

Private _Benutzer As String
Public Property Benutzer As String
    Get
        Return _Benutzer
    End Get
    Set(value As String)
        _Benutzer = value
    End Set
End Property

Private _Passwort As String
Public Property Passwort As String
    Get
        Return _Passwort
    End Get
    Set(value As String)
        _Passwort = value
    End Set
End Property

Private _Datenbank As String
Public Property Datenbank As String
    Get
        Return _Datenbank
    End Get
    Set(value As String)
        _Datenbank = value
    End Set
End Property

Private _Art As String
Public Property Art As String
    Get
        Return _Art
    End Get
    Set(value As String)
        _Art = value
    End Set
End Property

Private _Port As UShort = 3306
Public Property Port As UShort
    Get
        Return _Port
    End Get
    Set(value As UShort)
        _Port = value
    End Set
End Property

Private _IntegratedSecurity As Boolean
Public Property IntegratedSecurity As Boolean
    Get
        Return _IntegratedSecurity
    End Get
    Set(value As Boolean)
        _IntegratedSecurity = value
    End Set
End Property

Protected MustOverride Function BekommeVerbindung() As DbConnection

Protected MustOverride Function SqlSelect(ByVal Name As String, Optional ByVal Parameter As DbParameter() = Nothing) As DataTable

Protected MustOverride Function ErstelleParameter(ByVal Name As String, ByVal Value As Object, ByVal Typ As SqlDataType) As DbParameter

Protected MustOverride Sub SqlUpdate()

End Class

MySqlVerbindung.vb:

Imports System.Data.Common
Imports Devart.Data.MySql

<Serializable>
Public Class MySqlVerbindung
Inherits SqlVerbindung

Protected Overrides Sub SqlUpdate()
    Throw New NotImplementedException()
End Sub

Protected Overrides Function SqlSelect(Name As String, Optional Parameter() As DbParameter = Nothing) As DataTable
    Using con = BekommeVerbindung()
        Using cmd As New MySqlCommand(AbfragenMY(Name), con)
            If Parameter IsNot Nothing Then
                cmd.Parameters.AddRange(Parameter)
            End If
            Using ada As New MySqlDataAdapter(cmd)
                Using dt As New DataTable
                    ada.Fill(dt)
                    Return dt
                End Using
            End Using
        End Using
    End Using
End Function

Protected Overrides Function ErstelleParameter(Name As String, Value As Object, Typ As SqlDataType) As DbParameter
    Dim parameter As New MySqlParameter()
    With parameter
        .ParameterName = Name
        .Value = Value
        Select Case Typ
            Case SqlDataType.Int
                .MySqlType = MySqlType.Int
            Case SqlDataType.Text
                .MySqlType = MySqlType.Text
            Case SqlDataType.VarChar
                .MySqlType = MySqlType.VarChar
        End Select
    End With
    Return parameter
End Function

Protected Overrides Function BekommeVerbindung() As DbConnection
    Return New MySqlConnection() With {
        .Host = Me.Server,
        .UserId = Me.Benutzer,
        .Password = Me.Passwort,
        .Database = Me.Datenbank,
        .Port = Me.Port,
        .Charset = "UTF8"}
End Function

End Class

MsSqlVerbindung.vb:

Imports System.Data.Common
Imports System.Data.SqlClient

<Serializable>
Public Class MsSqlVerbindung
Inherits SqlVerbindung

Protected Overrides Sub SqlUpdate()
    Throw New NotImplementedException()
End Sub

Protected Overrides Function SqlSelect(Name As String, Optional Parameter() As DbParameter = Nothing) As DataTable
    Using con = BekommeVerbindung()
        Using cmd As New SqlCommand(AbfragenMS(Name), con)
            If Parameter IsNot Nothing Then
                cmd.Parameters.AddRange(Parameter)
            End If
            Using ada As New SqlDataAdapter(cmd)
                Using dt As New DataTable
                    ada.Fill(dt)
                    Return dt
                End Using
            End Using
        End Using
    End Using
End Function

Protected Overrides Function ErstelleParameter(Name As String, Value As Object, Typ As SqlDataType) As DbParameter
    Dim parameter As New SqlParameter
    With parameter
        .ParameterName = Name
        .Value = Value
        Select Case Typ
            Case SqlDataType.Int
                .SqlDbType = SqlDbType.Int
            Case SqlDataType.Text
                .SqlDbType = SqlDbType.Text
            Case SqlDataType.VarChar
                .SqlDbType = SqlDbType.VarChar
        End Select
    End With
    Return parameter
End Function

Protected Overrides Function BekommeVerbindung() As DbConnection
    Dim con As New SqlConnection
    If Me.IntegratedSecurity = True Then
        con.ConnectionString = New SqlConnectionStringBuilder With {
            .DataSource = Me.Server,
            .IntegratedSecurity = True,
            .InitialCatalog = Me.Datenbank}.ConnectionString
    Else
        con.ConnectionString = New SqlConnectionStringBuilder With {
            .DataSource = Me.Server,
            .UserID = Me.Benutzer,
            .Password = Me.Passwort,
            .InitialCatalog = Me.Datenbank}.ConnectionString
    End If
    Return con
End Function
End Class

SqlFactory.vb:

Public Class SqlFactory

Public Shared Function Erstellen(ByVal Art As String) As SqlVerbindung
    Return If(Art = "MySQL",
        New MySqlVerbindung,
        New MsSqlVerbindung)
End Function

End Class

Now I have a problem: Using databinding with an BindingSource (Datasource = SqlVerbindung) and I want to call the function SqlSelect. Therefore I have to check if SqlVerbindung.Art = "MySQL" or "MSSQL" and then cast it to "MsSqlVerbindung" or "MySqlVerbindung" to call the method. I think thats not how it should work. Any tips or suggestions are welcome :)

\$\endgroup\$
  • \$\begingroup\$ Everywhere you have an if statement, is code which applies to a specific type. Therefore, do this: class Connection {} class MsSqlConnection : Connection {} class MySqlConnection : Connection {}. Then put all the code which has no if statements in Connection class. The rest of code put it into the class it applies to. Now you have no if conditions. Lastly, for creation either use a switch or if like this: var connection = null; if (art == "MSSQL") { connection = new MsSqlConnection(); } else .... You get the idea. \$\endgroup\$ – CodingYoshi Aug 31 '18 at 15:16
  • \$\begingroup\$ If you want to be fancy, create a class ConnectionFactory { public Connection Create(string art) { if (art == "MSSQL" { return new MsSqlConnection(); }}}. The trick is that the code should not use the concrete implementation but use the abstract Connection class. That way, the using code is totally decoupled from the concretes. \$\endgroup\$ – CodingYoshi Aug 31 '18 at 15:42
  • \$\begingroup\$ Will convert this into an answer later, but off-hand: Don't make the class rely on building its own connection; get rid of all the connection string variables and make the already established connection object a parameter (not an instance variable). Use an interface and have two implementations, one for each database, rather than the if condition. \$\endgroup\$ – jpmc26 Aug 31 '18 at 15:52
  • \$\begingroup\$ @CodingYoshi Comments are for seeking clarification to the question, and may be deleted. Please put all suggestions for improvements in answers. \$\endgroup\$ – 200_success Sep 1 '18 at 3:48
  • \$\begingroup\$ I edited my post, maybe someone can check it up? \$\endgroup\$ – KingLM97 Sep 4 '18 at 22:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.