Return a copy of an array instead of a pointer for it's elements

[giovaniluigi]

I'm implementing in a class, methods to manage a simple database.
This class will perform all the tasks related with database and will return results in simple structures to the other classes.
Remark: My Class was designed to deal with a single Table at once, so it is using internal variables to store a single table.

There is one method to retrieve results from a table and it is:

Public Function GetItems(Optional ByVal condition As String = "", Optional ByVal sort As String = "") As DataRow()

I pass to this method the SQL condition or filter and the sort direction if I want the result sorted.
The result is an Array of DataRow type.

I could return a DataTable but it would be the same for my purpose.

Now when I use this code I do something like this:

Dim db as New ClassDataBase

Private Table1 As New List(Of Data.DataRow)
Private Table2 As New List(Of Data.DataRow)

db.OpenTable("Table1_Name")
Table1 = db.GetItems()
db.CloseTable()

db.OpenTable("Table2_Name")
Table2 = db.GetItems()
db.CloseTable()

After opening the tables and storing the results now I can use the results that are in memory like this:

MessageBox(Table1(0).Item("ColummName").ToString)

With the code above I'm retrieving the value on line 0 columm "ColummName" from a DataRow object.

PROBLEM:

My Class was designed to deal with a single Table at once.
So to retrieve results I create a local array and then GetItems() from the table.

Like this: LocalArray = GetItems()

BUT

It doesn't work, because when I assign the result of the GetItems() to the local array I'm just dealing with references.

So when I open the Table2 the class overrides the arrays inside of it and store the data from Table2 now.
This way, the local array (Table1) where Table1 was supposed to be... is not valid anymore because even being a local array, it is just pointers to the content inside the class.

I need a solution to extract the results and store into a local Array.
The solution would be the method GetItems() return an object and not just references.
But everytime I think in create an object, I fall into the problem in how to pass the individual values to it and not the references.
For example, I've tried with lists, but I fell into the same problem.

Someone knows how can I return an Array "By Value" instead of returning just pointers ?

 

[jmcilhinney]

You're trying to solve the wrong problem. Either your class is badly designed or you're using it incorrectly. If your class is designed to deal with a single table at once then that's how it should be used. If you want to deal with two tables then you should be either using two instances of your class or else you should be using it for one table at a time, not doing anything with the second until you have completely finished with the first.

As for your question about arrays, arrays are reference types so there's no way to access them other than by reference, just like all class instances. If what you want is a copy of an array then that's exactly what you have to create: a copy of the array. You should note that, if the type of the array elements is a reference type, creating a copy of the array will not create copies of the elements. For instance, DataRow is a class so creating a copy of a DataRow array will create new array containing all the same DataRows. If you change a filed of a DataRow through one array then you'll see the change in the other. What won't affect both arrays is replacing one of the elements, but you're unlikely to be doing that anyway. That's the way it works and that's the way it should work.

 

[giovaniluigi]

Well what you said is actually correct.
I should use more than one instance of it then.
There is only one thing, I'm connecting to the database in each instance so as more instances as lower the performance will be.
Also, more than one instance managing the same table may cause some problems that I'll need to treat...

I only need to access more than one table each time because I'm going to query for example:
Return all the items in Table2 where 'name' is the same as 'name' of Item 0 from Table1

So to perform this I'll need to have Table1 in memory to search what I need in table 2.

You're trying to solve the wrong problem. Either your class is badly designed or you're using it incorrectly. If your class is designed to deal with a single table at once then that's how it should be used. If you want to deal with two tables then you should be either using two instances of your class or else you should be using it for one table at a time, not doing anything with the second until you have completely finished with the first.

As for your question about arrays, arrays are reference types so there's no way to access them other than by reference, just like all class instances. If what you want is a copy of an array then that's exactly what you have to create: a copy of the array. You should note that, if the type of the array elements is a reference type, creating a copy of the array will not create copies of the elements. For instance, DataRow is a class so creating a copy of a DataRow array will create new array containing all the same DataRows. If you change a filed of a DataRow through one array then you'll see the change in the other. What won't affect both arrays is replacing one of the elements, but you're unlikely to be doing that anyway. That's the way it works and that's the way it should work.

 

[jmcilhinney]

The issue is simple: your class is poorly designed. If you design it properly then it will work properly. Many, many people have designed data access layers before and I don't recall all of them having issues with the fact that arrays are reference types so the issue is specific to your implementation.

 

[Rob Sherratt]

Just as a matter of interest, would this work for you?
I am not suggesting whether this is "good" or "bad" practice, but it seems to answer your question.
My "point" is that passing an Object Array by reference provides a way to return a variable number of parameters of indeterminate type.
I encountered a similar situation time and again trying to read values from an Excel Spreadsheet using Odbc and OleDb

Regards,
Rob

Private Sub MyDataBaseHandler(ByRef ObjectArray() As Object)

    ' and then somewhere

    ReDim ObjectArray(number_of_values_to_return)

    ObjectArray(0) = MyDataHandler1() ' return value / type 1
    ObjectArray(1) = MyDataHandler2() ' return value / type 2

    ' etc

    Exit Sub
End Sub


' The calling procedure tests ObjectArray.Getlength(0) for the number of values returned.
' The calling procedure tests ObjectArray(0).GetType() if unsure of the data type returned, e.g. the data type from a "foreign" Database may be uncertain.
' The calling procedure tests String.IsNullorEmpty(ObjectArray(0)) if a null String data type needs to be tested.
' The calling procedure tests (ObjectArray(0) = Nothing) if a pointer to a data structure needs to be tested for validity.