VB.NET - create an object from this example code (learning object orientated programming techniques)

Question

I'm going back through some code in a vb.net I wrote and re factoring, I'm trying to make it more object orientated as I'm trying to teach myself better coding practice. I have one particular section of code that gets repeated a lot throughout the app, the basic structure doesn't really change other than sometimes is may have more or less parameters (the code inside the function):

Public Function EditTest(ByVal test_id As Integer) As DataTable

    Dim con As OleDbConnection = New OleDbConnection(FileLocations.connectionStringNewDb)
    Dim cmd As New OleDbCommand
    cmd.CommandType = CommandType.StoredProcedure
    cmd.CommandText = "Q_EDIT_TEST"
    cmd.Parameters.Add("@Parameter1", OleDbType.Integer).Value = test_id
    cmd.Connection = con
    con.Open()
    Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)
    Dim ds As DataSet = New DataSet()
    da.Fill(ds, "editTest")

    Dim rtable_ As DataTable = ds.Tables("editTest")

    con.Close()

    Return rtable_

End Function

I'd like to be introduce object orientation into this by just creating a new object, based on a class? "DatabaseData" lets call the class, with it's creation parameters as (in this example) the name of the query I'm going to run (Q_EDIT_TEST) and the parameters I'm going to add to it (this is where I'm finding it tricky). The example above I have 1 parameter, what if I wanted to create an object with 2 parameters? 3 parameters? etc. Can anyone show me an example of how this could be achieved? Cheers!

Ok bear with me here :-) still learning here:

Public Class DatabaseData

    Property queryName As String
    Dim con As OleDbConnection = New OleDbConnection(FileLocations.connectionStringNewDb)
    Dim cmd As OleDbCommand
    cmd.CommandType = CommandType.StoredProcedure
    cmd.CommandText = queryName

End Class

cmd.CommandType throws an error:

"Error 1 Declaration expected. "

Ok this is what I have so far, it doesn't work I know but am I on the right track here? or am I being insane and is there a much better way of doing this...

Imports System.Data.OleDb
Imports System.Data.SqlClient

Module PureClassClasses

Public Class Params

    Property myParams As New List(Of customParam)

    Class customParam
        Property Name As String
        Property Value As OleDbType
    End Class


End Class

Public Class DataGetter

    Private queryName As String
    Private listOfParams As List
    Private rtable As DataTable

    Public Sub New(ByVal Qn As String, ByVal paramList As List)

        queryName = Qn
        listOfParams = paramList

    End Sub

    ReadOnly Property ReturnedData(ByVal queryName As String, ByVal listOfParams) As DataTable
        Get
            Dim conn As OleDbConnection = New OleDbConnection(FileLocations.connectionStringNewDb)
            Dim cmd As New OleDbCommand
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = queryName
            'cmd.Parameters.Add("@Parameter1", OleDbType.Integer).Value = test_id <-- HELP?
            'cmd.Parameters.Add("@Parameter2", OleDbType.Integer).Value = test_id <-- HELP?
            cmd.Connection = conn
            conn.Open()
            Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)
            Dim ds As DataSet = New DataSet()
            da.Fill(ds, "returnedData")
            Dim rtable_ As DataTable = ds.Tables("returnedData")
            conn.Close()
            Return rtable
        End Get
    End Property

End Class

End Module

I want to be able to call it somthing along the lines of...

Dim p As New Params
Dim cp As New Params.customParam
cp = new customParam
cp.name = "My First Param"
cp.type = OleDbType.Integer
cp.value = 5
p.myparams.add(cp)

Dim myDataTable As New DataTable
myData = new DataGetter
myDataTable = myData.ReturnData("Q_A_QUERY", listofparams)

Is this making any sense... :S

Answer 1

for database access, we have a full scale data access layer that suits all of our needs and it's fully object oriented. it's too large for me to post here, but if you would like a copy send me a message.

Answer 2

This not really looking at the proper properties of db params, but to give you an idea about the structure.

Class Params

property myParams as new list(of customParam)

class customParam

property name as string
property value as OleDbType

end class

end class

Usage:

dim p as new params
dim cp as new params.customParam

cp = new customParam
cp.name = "My First Param"
cp.value = OleDbType.Integer

p.myparams.add(cp)

cp = new customParam
cp.name = "My 2ndParam"
cp.value = OleDbType.Integer

p.myparams.add(cp)

Then, pass p as an object into your routine, and go and get all the custom obects (params), it contains.

Your Code, simplified:

Public Function EditTest(ByVal test_id As Integer, p as params) As DataTable

for each cp in p
cmd.Parameters.Add(cp.name, cp.value)
next

end function

You will need co construct your classes with the right properties. This code is only to show you how OO would be used here..