Solved

VB .NET DataGrid, DataView or DataSet for Column selection

Posted on 2004-04-05
13
75,840 Views
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?
0
Comment
Question by:MarkNethercott
  • 4
  • 4
  • 3
  • +2
13 Comments
 
LVL 28

Expert Comment

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

sSQL = "select colA, ColC from Table"
......
da.Fill(ds)

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
        Next
0
 

Author Comment

by:MarkNethercott
ID: 10763767
Thanks.  

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
Datagrid.TableStyle.Add
Declare a GridColumnStyleCollection
Set GridCSCObject = DataGrid.TableStyles(index of your created
tablestyleobject).GridColumnStyle
Then you can add and remove columns by using
MyGridColumnStyleCollectionObject.
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?
0
 
LVL 28

Expert Comment

by:iboutchkine
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
0
 
LVL 7

Expert Comment

by:ScrptMasta
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
0
 
LVL 28

Expert Comment

by:iboutchkine
ID: 10766350
I Agree with ScrptMasta
0
 

Expert Comment

by:skwash
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
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 28

Expert Comment

by:iboutchkine
ID: 10767877
It is true only for WEB datagrid. There is no such thing for Win forms DG
0
 
LVL 7

Expert Comment

by:ScrptMasta
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.
 
0
 

Author Comment

by:MarkNethercott
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.

0
 
LVL 7

Accepted Solution

by:
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
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbconDataGridColumns.asp

Creating Custom Columns for the ASP.NET Datagrid
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspp/html/creatingcustomcolumns.asp

DataGrid Control (Windows Forms)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskcreatingcustomcolumntypesindatagrid.asp
0
 
LVL 7

Expert Comment

by:ScrptMasta
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
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlDataReaderClassTopic.asp
0
 

Author Comment

by:MarkNethercott
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
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspp/html/creatingcustomcolumns.asp

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.
0
 

Expert Comment

by:Duc_de_Belfort
ID: 12063432
Already tried that?

(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatacolumncollectionclassremovetopic1.asp)

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
       cols.Remove(colToRemove)
    End If
 End Sub

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

Regards,
Duc

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now