Help with displaying partial fields in DataGridView

Hi,

I'm using the code below to display data from 1 table to multiple DataGrid, how do I mofiy the code in part A to choose which fields I want to display in each table, the code in part B to display the fields take too long.

Part A:

Dim FilteredDT As DataTable
        Dim DV As New DataView(dtsetlinkBEL.Tables(0), SearchCriteria, Nothing, DataViewRowState.CurrentRows)
        FilteredDT = DV.ToTable

        If FilteredDT.Rows.Count > 0 Then

            C1Screen1.DataSource = FilteredDT
            C1Screen2.DataSource = FilteredDT
            C1Screen3.DataSource = FilteredDT
            C1Screen4.DataSource = FilteredDT

Part B:

  Dim i As Integer
        i = 0
        For i = 14 To 86
            Me.C1Screen1.Columns(i).Visible = False
        Next
       
        For i = 1 To 13
            Me.C1Screen2.Columns(i).Visible = False
        Next
        For i = 23 To 86
            Me.C1Screen2.Columns(i).Visible = False
        Next

        For i = 1 To 22
            Me.C1Screen3.Columns(i).Visible = False
        Next
        For i = 41 To 86
            Me.C1Screen3.Columns(i).Visible = False
        Next

        For i = 1 To 40
            Me.C1Screen4.Columns(i).Visible = False
        Next
vcharlesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vcharlesAuthor Commented:
Hi,

The code below is an approach but I need to list the fields to be displayed, is there a way to include fields 1 to field 9 without listing them in the code?

  C1Screen1.DataSource = FilteredDT.DefaultView.ToTable(True, New String() {"Field1", "Field2", "Field3"..........})
Jacques Bourgeois (James Burger)PresidentCommented:
Since none of the columns is displayed in 2 tables, why don't you create 4 datatables, one for each column?

And by the way, you seem to be making a common error, by putting a single table in a DataSet (When to use a DataSet / DataTable / DataReader / Command)
vcharlesAuthor Commented:
Hi,

When working with xml, i find it easy to use a dataset, but will look at your lonk to try to improve the code.

If pssible would prefer to use one table to display different fields in multiple  Grids, but current  spproach is too slow, was hoping for a better approach  to acheive the same.

Thanks,

V.
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Jacques Bourgeois (James Burger)PresidentCommented:
The problem with using one big DataTable in 4 grids is probably that it takes up too much memory.

When you use DataBinding with a Control, the values contained in the DataTable need to be converted to strings in order to be displayed. You thus end up with a copy of most of the data, in text form.

When you fill 4 grids from the same DataTable, you are doing 4 of these copies. That is a lot of redundancy. And this is done even if you hide the columns as you are doing. So you are taking up a lot of space in memory for nothing.
vcharlesAuthor Commented:
The data is from an xml file, how would yoou create multiple tables with different  fields from an xml file? Since I am loading the xml to a dataset  and putting my results in a datatable, will still need to run my query fron one table.
Thanks.
vcharlesAuthor Commented:
Hi,

Below is the code I am using with a query string (Searchcriteria),  not sure how to create multiple table following this approach.

 Dim FilteredDT As DataTable
        Dim DV As New DataView(dtsetlinkBEL.Tables(0), SearchCriteria, Nothing, DataViewRowState.CurrentRows)
        FilteredDT = DV.ToTable

Open in new window

Jacques Bourgeois (James Burger)PresidentCommented:
For the XML, I am sorry, but I do not have an answer. In the environments where I work, we deal directly with the databases and use XML only for small stuff. So, my knowledge of XML manipulations is limited to reading and writing a file as a whole.

There a probably ways to query a XML file, but this is not something I a familiar with. That might be another question to post to XML experts.

I do not know how much power you have over the generation of that XML, but if I had to do something like what you are trying to do, I would generate 4 XML files out of my database instead of getting out a big one that needs to be manipulated later. I would then simply create one DataTable from each file.

-----

As for the DataView, it let's you filter records and sort them, but it returns all the fields. There is no way to select the fields. As a way to prevent columns generation so that you do not need to hide them and end up with a lot of useless data in memory, it's not a solution.

But your reposting of the code let me see something that did not catch my attention the first time. You are creating a second DataTable from your DataView. This is overkill, because you end up with the original table + the table generated from the DataView. Another good way of filling up the memory with data that you do not use.

There are 2 ways you could help your code, depending on your needs. I do not know if it would completely solve your problem, but it could surely help.

If you do not need to keep the original table (dtsetlinkBEL.Tables(0)) after creating FilteredDT through the DataView, get rid of the first table and its dataset before hiding your columns. That might free a lot of memory if you have many records in the datatable.

            dtsetlinkBEL.Tables(0).Dispose()
            dtsetlinkBEL.Dispose()

If you need to keep the table in memory to enable the user to make a new selection form the same data, then drop FilteredDT, you do not need it.

A DataView is a limited window on a full DataTable. You can use it almost anywhere you use a DataTable. But it is still the original table that you are working with, not a copy. Make changes in the DataView, and they go in the DataTable. You could then simply use your DataView as the DataSource.

           C1Screen1.DataSource = DV
            C1Screen2.DataSource = DV
            C1Screen3.DataSource = DV
            C1Screen4.DataSource = DV

You would still end up with useless hidden fields in your columns, so the first part of this comment still holds. But it is a quick fix that might help you to get a better performance.
vcharlesAuthor Commented:
Hi,

Is it possible to add the results of my query to multiple tables by choosing which columns I want to add in each table? Noticed a columns property but unable to add the columns.


 Dim FilteredDT As DataTable
 Dim FilteredDT1 As DataTable

        Dim DV As New DataView(dtsetlinkBEL.Tables(0), SearchCriteria, Nothing, DataViewRowState.CurrentRows)
          FilteredDT = DV.ToTable.Columns.?
          FilteredDT1 = DV.ToTable.Columns.?
vcharlesAuthor Commented:
Hi,

Thank you for the suggestions, will try them and get back to you, I had another suggestion as posted above but it may not be possible.
vcharlesAuthor Commented:
Hi,

I'm using the code below to create and close the dataset.

Unfortunately the performance didn't seem to  improve using DV as datasource. Part B of the code n my initial post still causes the delay.

Dim curfile As String = (Application.StartupPath + "\linkSearch.xml")
            fslinkBEL = New System.IO.FileStream(Application.StartupPath + "\linkSearch.xml", IO.FileMode.Open)
            dtsetlinkBEL.Clear()
            dtsetlinkBEL.ReadXml(fslinkBEL)
            fslinkBEL.Close()
vcharlesAuthor Commented:
Hi,

Is there a way to save the data in DV to an xml file? would than create 4 xml files save them to 4 data tables and display them in 4 grids.

V.
Jacques Bourgeois (James Burger)PresidentCommented:
Going the wrong way here.

Something like DV.ToTable.WriteXML would probably do the trick. But that means that you create 4 tables, save them to the disk and then reload them. This might take as long if not longer than hiding the columns. And you would still have to hide them.

Dealing with the tables might help, but your main problem are these columns, so this is where you should concentrate.

There are a few things you could try tha deal directly with the columns.

Your best bet is to start with data that is ready to be used as is. Go to the source. Where does that data comes from? Is it possible to ask them to generate 4 XML files with only the fields you need for each grid? That would be the best solution.

If this is not possible, as I suggested before, you should put out another question for XML experts to ask them if there is a way to query the XML in a way similar to a SQL query, which would enable you to both set the filter on the rows you need, and limit the return to the columns needed for each grid.
in your Part B code, make sure that the grids do no refresh after you remove each column. This forces the system to reorganize the display and redraw the grid and this can take time.

A third thing you might also try is to completely remove the columns instead of just hiding them. Calling Me.C1Screen1.Columns.RemoveAt(i) would do it. Not sure it would be faster, but this is something that is easy to try. You might try instead to call Dispose on each column if Remove does not help. If you had one table for each grid, you could remove the columns from the DataTable before filling in the grid, which would probably a lot faster. But because you have only one table, this is not possible.

If this does not work, then you might simply make sure that the DataGridView is not refreshed after dealing with each column. This forces the system to recalculate the position of everything and to redraw the grid, which can take time. This can be done by calling the SuspendLayout method on your grid at the beginning of your Part B code, and ResumeLayout when you have finished.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vcharlesAuthor Commented:
Thank you for all the information. Will try suspendlayout to see if it works a nit faster, the the system runs slowly only for the first query, sebsequent searches displays much faster, if going through the same process each time why is it much faster after the first search?
Jacques Bourgeois (James Burger)PresidentCommented:
I could not answer that last one. I do not have all the code and all the properties you have set on the different components that you use. And the DataGridView is a complex control. Who knows how Microsoft implemented things in the background to optimize it.

One would need to use the debugger and analysis tools in order to have the whole picture. If you are in Visual Studio 2015, the Debug menu now has options for Diagnostic Tools that can be used to help you see the roadblocks in your application. It can help to understand stuff like that.
vcharlesAuthor Commented:
Thank You.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.