Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


VB .NET DataGrid, DataView or DataSet for Column selection

Posted on 2004-04-05
Medium Priority
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
  • 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 @ http://www.dotnet247.com/247reference/msgs/10/52769.aspx

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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.


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 2000 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 www.datagridgirl.com (which also references http://www.syncfusion.com/FAQ/WinForms/FAQ_c44c.asp#q708q) 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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses
Course of the Month13 days, 17 hours left to enroll

581 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