1

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

2 Answers 2

1

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..

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

5 Comments

Thanks Louis, can you show me an example including the class which holds all the database stuff as well? Many thanks!
P.s. I did not try to compile... don't copy the code necessarily, learn from it and treat as pseudo code.
I added the right property types on the customParam class. For better understanding.
Cheers, very useful - what I'm also trying to do though is be able to create an instance of a class which has all of database stuff already setup as well, ie of the "Dim con As OleDbConnection = New OleDbConnection(FileLocations.connectionStringNewDb) Dim cmd As New OleDbCommand etc etc..." stuff as well
To create the whole thing for you, I think is out of the scope of this question, and what SO is meant to be. Try your hand at creating your own class, and we can help correct you where you are going wrong? This question was asked to show ow multiple params would be passed, as an object, and that is shown above? :-) Tag me in on a new question if needs be.. Ciao
0

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.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.