[2008] Fill array with database records [RESOLVED]

[katuki]

Hi, I have been told that the best way to do a search in VB is to fill an array with ALL the records from a table in a database and then filter the records from the array, instead of using the SQL statement WHERE.


Can you help me out with this please? I need this quite urgently.

[gep13]

Hey,

How told you that?

To me, this would seem like an invalid approach. This would mean taking back records from the database that essentially, you are not interested in.

To me, it would make much more sense only pulling back the data you want, using a where clause on the SQL query.

Gary

[katuki]

It was a cousin of mine who told me (he is a computing engineer).

In first place, selecting info from an array is much quicker than retrieving data from the database.

In second place, I am doing this for a project where I have to prove a searching technique, and using the WHERE statement doesnt award me any points.

Do you think you can help me?

[gep13]

Hey,

But in order to get the information from the database, you first have to select it. It will take longer to retrieve all the records than it would to select a subset of the records based on a where clause.

Can you explain exactly what your requirements are?

Gary

[katuki]

It is true that it would take more time to retrieve the records into the array and then filter the data from the array, than to select the data directly from the database, but it is quicker in the way I said if you want to do many queries, because then you just need to query the array (from the second query onwards).

[katuki]

Does anyone know how i can do this? My deadline is coming up soon :s

[gep13]

Hey,

That is a fair point, assuming that the information in the database does not change. ADO.Net uses a disconnected architecture in that once you have made your query, the connection to the database closes, so any changes made to the database will not be retrieved, until you re-query.

Your best bet would probably be to execute the query and place the information into a DataSet, and then query it directly.

Are you using .Net 3.5? If so, you could do this using LINQ.

Here is an article that might help you:

http://blogs.msdn.com/adonet/archive...o-dataset.aspx

Gary

[jmcilhinney]

It is true that it would take more time to retrieve the records into the array and then filter the data from the array, than to select the data directly from the database, but it is quicker in the way I said if you want to do many queries, because then you just need to query the array (from the second query onwards).

That still depends on the total number of records and the number of records each query will return. You need to take into account that databases are optimised for running queries where VB is not. If the database is local then network traffic is not an issue either. Either might be better overall depending on various criteria.

For instance, if you had 100 records total and you wanted to perform 100 queries that returned 90 records each, it would definitely be better to query the database once and filter locally. If you had 1,000,000 records and you wanted to perform 100 queries that returned 10 records each though, 100 database queries would be the better option.

[katuki]

Hi, thi is for small project so i dnt think speed will bean issue in either ways.

The problem is that this is part of a coursework and my teacer said that i will not earn any points if i use the WHERE clause, becase that doesnt prove much of searching techniques.


That cousin of mine discussed wth me all the steps I should do, the problem is when it comes do coding...

The steps should be:

Select all the records in the database table and fill an array with it;
With a FOR loop filter the array line by line to see which records meet the searcing criteria and copy all these records to a new array;
Fill a DataTable with the data from the second array

If anyone could help me write the code I would really appreciate it.

[gep13]

Hey,

I think I am right in saying that there will be very few people here who are willing to just write the code for you, since, as you have said, as you have said this is course work, and you will learn nothing if someone just gives you the code.

What have you tried so far? Can you paste some of the code you are using?

Did you read the link that I posted to you?

Gary

[katuki]

Hi, I have been trying to do some coding, but a problem has arrived, as well as a doubt.

I have coded the following:

        Dim connect_string As String = _
           "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=""" & Application.StartupPath & "\db_extras_test2003.mdb" & """;" & _
           "Persist Security Info=False"

        ' Open a database connection.
        Dim conn_db_extras_test2003 As New OleDb.OleDbConnection(connect_string)
        conn_db_extras_test2003.Open()

        Dim data_adapter As New OleDbDataAdapter("SELECT Name, DOB, Height FROM tbl_contacts", conn_db_extras_test2003)
        Dim arrDbRecords() As String
        data_adapter.Fill(arrDbRecords)

But as I expected, an error occured:

Error	1	Overload resolution failed because no accessible 'Fill' can be called with these arguments:
    'Public Function Fill(dataTable As System.Data.DataTable) As Integer': Value of type '1-dimensional array of String' cannot be converted to 'System.Data.DataTable'.
    'Public Overrides Function Fill(dataSet As System.Data.DataSet) As Integer': Value of type '1-dimensional array of String' cannot be converted to 'System.Data.DataSet'.	C:\Users\Luis\Documents\Visual Studio 2008\Projects\ExCoAs 0.7 05.10.2009 beta\Extras_Program_Test5\Form2.vb	72	9	Extras_Program_Test5

My database table has the following columns: ID, Name, DOB, Height, Imglocation1, Imglocation2, Imglocation3 and as i was trying to put all of that into a 1-dimmensional array it crashed. But my doubt is what kind of array do I have to declare, and how do I do it?

Also, do you think I am going in the right way?

[gep13]

Hey,

Have you looked at the documentation for the Fill method of the DataAdapter?

http://msdn.microsoft.com/en-us/libr...pter.fill.aspx

You should see that none of them take a string array.

What you are after is a DataSet.

Gary

[katuki]

Hi,

but my objetive there was to fill the array with the query I did to the database, retrieving all the records of the database table to the array.

How can I do it then?

[BackWoodsCoder]

I wont give you the code, but i can give you the path to the answer.

1.) Create a new dataset
2.) Use the dataadpater to fill this data set (you pretty much did that except you used an array instead of a DS)
3.) Use a for each loop on the dataset
4.) With each record that meets your criteria, add that row/item/whatever to your array by using the items.add method of the array class

That's one way to do it anyways.

[BackWoodsCoder]

also, here's an example of using the for loop to pull out pertinent records.

Lets say you wanted to pull back names of any males in your data set and are using a List (Of String) called arraylist and a dataset you created called dataset

For Each Row As DataRow in Dataset.tables(0).rows
                  if Row("Sex") = "Male" Then ArrayList.Add(row("Name"))
             Next

[Shaggy Hiker]

Does your application have to use an array? You will have to fill a datatable, whether or not it is part of a dataset. To then move the data into the array would be possible, but it is hardly ideal. The datatable has a DataView accessible through the DefaultView property. Filtering that using the RowFilter property would probably be faster than any search you could devise. However, if that is also not going to "earn you any points", then perhaps you don't want to go this way. However, consider that a datatable wraps a collection of DataRows accessible via the Rows member, and you should realize that a datatable is practically an array, anyhow.

[BackWoodsCoder]

Yah, definitely a couple ways to skin this cat. IMO it's best to let the SQL server do your data processing for you, guess I always just liked pulling back only the data i needed to use instead of grabbing everything and wading through the crap to find what i need.

[Shaggy Hiker]

I'd have to say that there are times (many similar queries against the same table) where it makes more sense to pull it locally, but even in those cases, I would prefer to use the DataView for searching. I have wondered whether this is actually more efficient than some kind of custom search, but I have never bothered to test.

[katuki]

Thanx everone for the help you have provided me with.

At the moment I have got the following code:

Dim connect_string As String = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=""" & Application.StartupPath & "\db_extras_test2003.mdb" & """;" & _
         "Persist Security Info=False"

        ' Open a database connection.
        Dim conn_db_extras_test2003 As New OleDb.OleDbConnection(connect_string)
        conn_db_extras_test2003.Open()


        'Make a Command to select data.
        ' WHERE     (Name LIKE '%' + ? + '%') AND (DOB LIKE '%' + ? + '%') AND (Height LIKE '%' + ? + '%')
        Dim data_adapter As New OleDbDataAdapter( _
        "SELECT ID, Name, DOB, Height, Imglocation1, Imglocation2, Imglocation3 FROM tbl_contacts", conn_db_extras_test2003)
        Dim ds As New DataSet()
        'Dim ArrayFilteredRecords() As String
        data_adapter.Fill(ds)
        For Each Row As DataRow In ds.Tables(0).Rows
            If Row("Name") = txt_search.Text Then ListBox1.Items.Add(Row("Name"))
        Next

        data_adapter.Dispose()

This displays in a listbox the corresponding name that i've searched with the one in the column "Name"; I just need to add a bit of code so that it diplays the rest of the data in the rows filtered.

[rjv_rnjn]

If the objective is to learn filtering data through VB.Net; I'd give the maximum credit to whoever does it through DataView rowfilter (as SH suggested). A couple of observations:

a.) You are fetching multiple columns from the database table. If you store each row in an array then you have to play around with boxing & unboxing, a big performance hit.
b.) Iterating through an array to find a particular value is not "filtering", in my opinion.
c.) And as you've already observed, a 1-D array will not work. And a 5 dimensional array to store data would be unacceptable in real world solutions.

[rjv_rnjn]

I just need to add a bit of code so that it diplays the rest of the data in the rows filtered.

Where do you want to display other columns of the data (in different controls like label, textbox)?

[katuki]

Ive done it:

        For Each Row As DataRow In ds.Tables(0).Rows
            If Row("Name") = txt_search.Text Then ListBox1.Items.Add( _
                Row.Item("Name") & " " & _
                Row.Item("DOB") & " " & _
                Row.Item("Height"))
        Next

Now if I type for example in txt_search "luis" i will have the following output:

luis 03-07-1992 185
luis 05-01-1991 174
luis 30-01-2000 159

Thnx everyone for the help

Although I did not use the array, I haven't aso used the WHERE clause so I believe thisway will award me full points

[BackWoodsCoder]

I have wondered whether this is actually more efficient than some kind of custom search, but I have never bothered to test.

I would say that would be dependent on the dataset you were working with and the specs of your machine. It has been my experience, at least in the reporting world that i once inhabited, that it was always faster to let the SQL server do as much of the processing as possible before handing off stuff to the client side for presentation.

However, I really think the answer to this is just "it depends". I also agree with you that multiple database hits are generally less efficient (especially on a highly trafficked network/server) then processing within your code.

Katuki: Nice job, like many others have suggested if there is no specific need for you to use an array you don't really need one. A lot of these programming assignments force you to use controls or objects that normally wouldn't be used, but since you're learning about them in class you're forced to use them to reflect this knowledge

[katuki]

Let me just add that I am trying to do this for about 11 months!

[rjv_rnjn]

Let me just add that I am trying to do this for about 11 months!

What, converting to array?

Good that you got it working.
Mark the thread as resolved, if there's nothing else left to ask for this question!

[Shaggy Hiker]

Here's an alternative that would probably be slightly faster:

ds.Tables(0).DefaultView.RowFilter = "Name = '" & txt_search.Text & "'"
For x As Integer = 0 to ds.Tables(0).DefaultView.Count - 1
 Row.Item("Name").ToString & " " & _
 Row.Item("DOB").ToString & " " & _
 Row.Item("Height")
Next

This uses a DataView to filter the table down to just the items that match, then writes them out. This gets rid of all the iterations where the items don't match.