VB .NET DataGrid, DataView or DataSet for Column selection

Posted on 2004-04-05
Last Modified: 2011-08-18
Apologies if this appears as a duplicate question.  I thought I'd submitted it already, but my orginal question seems to have vanished.

I'd like to know how I should control which columns are displayed in my datagrid.  Is it at the DataGrid, DataView or DataSet level.

I believe it is at the DataGrid level, but am not sure how to achieve it.

I have a DataGrid1 with its datasource set to DataView1 which is in turn a view of DataSet1.  If DataView1 is the default view for DataSet1 that has ColA, ColB and ColC, how do I set up the DataGrid to just show ColA and ColC?
Question by:MarkNethercott
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +2
LVL 28

Expert Comment

ID: 10762095
The easiest way is to do it in SQL when you ar ecreating a dataset

sSQL = "select colA, ColC from Table"

dg.DataSource = ds

Now you will have colA and ColC in your dataagrid

Another way of doing it is to hide columns

'we have cols 0-3 (4 columns). Show only the 1st col
      For i = 1 To 3
            dtUserProject.Columns(i).ColumnMapping = MappingType.Hidden

Author Comment

ID: 10763767

I thought the idea of DataViews was to give different views of the same DataTable so that you kept the load on the database server to a minimum.  

I'm comfortable with modifying the query, so that I can restrict the columns that I'm calling in the first place, but thanks for changing the DataTable properties, but would really like to achieve this at the DataView/DataGrid level.

In the meantime, I've found the following @

You must first declare an new DataGridTableStyle object.
Then set the .Mappingname = your Datasets tablename.
Add the datagridtablestyle object to the grid by using
Declare a GridColumnStyleCollection
Set GridCSCObject = DataGrid.TableStyles(index of your created
Then you can add and remove columns by using
Ex: MyGridColumnStyleCollectionObject.RemoveAt(ColumnIndex) hides the column
at position ColumnIndex.

Hope u got the idea..

Fredrik Pradeep.k

I wonder if this is the best way to control it?
LVL 28

Expert Comment

ID: 10764963
I think the best way is to create different dataviews using different SQL. If you don't have any other requirements you don't need to apply table styles. But that is a matter of taste. You can do it either way
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.


Expert Comment

ID: 10766320
As mentioned above its more a matter of style. I like to change the Command.Text properties of the Adapter object to send another SQL command that creates the exact view I need on the fly. This way only the data thats needed is held into memory and not the big load of data that you would normally hold in a dataset for viewing purposes. It also makes it easier when updating the dataset as well.

Just my $.02
LVL 28

Expert Comment

ID: 10766350
I Agree with ScrptMasta

Expert Comment

ID: 10767738
are you using visual studio?

If yes, if you do not want to do it at the SQL Query level, you can change the columns in the data grid.

just right click the datagrid -> property builder -> columns

uncheck "create columns automatically at run time"

then just add columns as needed, I am guessing that you will mostly just be using the bound column, make the "datafield" the column that you want the data to display.  You can also modify how the data looks from here.

And if you are adding a Hyperlink column you can specify one column as the text, and another as the data.

Try that
LVL 28

Expert Comment

ID: 10767877
It is true only for WEB datagrid. There is no such thing for Win forms DG

Expert Comment

ID: 10769359
iboutchkine is correct on a win form you have a couple of choices as to similar controls, personally I use a ListView or a TreeView.

Also I would like to add to the comment about SQL load performance. You have to understand that when using a DataSet w/ADO.NET you are working with "disconnected" data. The SQL connection is open just long enough to run the command, fill the set and then close. It doesn't hold an active connection open while you work on the data. Unless you’re loading tons of info into your DataSet I wouldn't worry too much about performance. If it is of most importance then consider using a stored view within SQL and calling that instead of sending a SQL command via the Adapter. You will need to use the Command.Type method instead of Command.Text if you go this route. However it’s what you should be doing if performance is your major concern.

Are you sure that you even need the use of more then the default view from the DataSet ? Meaning you have a DataSet and you need to create multiple DataTables or Views from the initial DataSet ? If not then you shouldn't be using a DataView as your DataSource.

You are aware that you can hold more then one table from your database within a DataSet correct? You will just need multiple adapter objects to create multiple tables in the DataSet and you will have to fill and update each one separately.

Author Comment

ID: 10769636
Thank you all for your comments.  

At the moment, the database is quite small - there are only about 30 tables with limited data that will grow to about 40 tables.  However, it's possible that some of the tables will get quite large  - 500,000 to 4,000,000 rows and I wanted to build the prototype using best practices and grapple with some of the more involved techniques that are required.  I can always simplify things once I've got the perspective of doing it the 'hard' way.  From what I'd read and tested, it seems that the most complicated way of working with data (in a disconnected manner) is;

SQL Data <-DataAdapter-> DataSet(s) <-DataView Manager-> DataView <-TableStyles-> DataGrid  (I know this is not 'technically' accurate but the sense is true)

And I was trying to work out how to get the last filter in the chain under control, which was why I wanted to understand 'how' to control the view at the DataGrid end of the chain.  Now that I've read about Table Styles and built some prototypes all is much clearer.  As always with .NET, it's the knowledge of the keywords that helps you navigate - and the 'hidden' suggestion was great.

I was aware of the approaches that have been suggested, but have changed my weighting of them based on the comments received.

I don't feel that my orginal question was answered, but the comments from iboutchkine & ScrptMasta have been really useful, so unless I hear otherwise, I was going to split the points evenly.


Accepted Solution

ScrptMasta earned 500 total points
ID: 10775123
How you control you DataGrid column design will depend on how its setup from the begining. Are you binding at design time or run time ? Seeing as how there are also multiple ways of controlling the data to and from the grid, this might boil dowm to a matter of style as well.

What I do is consider things class by class or form by form. If you have a form where the databound controls are always going to be the same and will always show the same exact data then there is no reason not to bind your controls at design time. However if you know that the form contains controls which might show data one way ay times and then another way at others obviously you will need to bind them at run-time for flexability.

As far as how to control your columns see if any of these pages help.

DataGrid Columns

Creating Custom Columns for the ASP.NET Datagrid

DataGrid Control (Windows Forms)

Expert Comment

ID: 10775183
Also here is the link to the complete System.Data namespace. If you read from the top of the class it may help to put all into perspective a little better.

System.Data Namespace

Author Comment

ID: 10785869
Thanks for the additonal comments ScrptMasta,

I found the following link the most useful of the ones you suggested;

Creating Custom Columns for the ASP.NET Datagrid

Which explains a lot of the background & logic as a basis for creating custom columns.  This is useful for using the columns generally.

The additional links that are referenced (which also references provide good additonal resources.

Expert Comment

ID: 12063432
Already tried that?


Private Sub TestAndRemove(ByVal colToRemove As DataColumn)
    Dim cols As DataColumnCollection
    ' Get the DataColumnCollection from a DataTable in a DataSet.
    cols = DataSet1.Tables("Orders").Columns
    If cols.Contains(colToRemove.ColumnName) Then
    End If
 End Sub

This solved my problem, after I had found your question on my way to look for possible solutions.



Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question