Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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
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 @ 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.


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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

618 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