DataTable and DataGridView relationship

Ask questions about creating Graphical User Interfaces (GUI) in PowerShell and using WinForms controls.
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
User avatar
Lembasts
Posts: 293
Meble kuchenne na zamówienie - na wymiar - Wrocław
Joined: Thu Mar 15, 2012 9:47 am

Re: DataTable and DataGridView relationship

Post by Lembasts »

Thanks. I worked out the cellpainting event bit - that is awesome.
I understand at my brief look into the datagridview object the power but also the complexity.
The association between the datatable and the datagridview is also interesting. There appear to be some properties of the datatable (e./g. column type) that exist in the DGV. I think its probably best to just load up the datatable with raw data and use the DGV properties to control how the data appears. And changing data in the datatable automatically triggers a change in the DGV which I found interesting. In the past I have always completely reloaded other controls when the original data changed.
This whole binding thing got me thinking - Im doing alot of reading SQL tables into PS variables and then using loops to add rows to things like listviews and checkedlistboxes. Would data binding allow me to associate the SQL data directly with the control?
The DGV also would also have some quirks - e.g. I tried the CellValidating event and found that when the datatable is first loaded as the datasource for the DGV, this event is triggered just once with rowindex and columnindex = 0. Cant explain that one!
So im looking forward to experimenting with events and properties. My many google searches yesterday gave me some stuff but not so much at the real practical level that beginners would like. Maybe I should write a blog post when Ive worked it all out? Or maybe its time for a Spotlight on DGV?

jvierra
Posts: 14444
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: DataTable and DataGridView relationship

Post by jvierra »

Interesting. The issue you are facing is one of lack of understanding of how Forms and Form's controls are designed to work. You need to start by learning what a "Control" is and how all controls are inherited from "Control". You also need to learn how the form and control interact by design.

Until you understand forms and controls data binding will be a challenge, There are whole books written on the subject. They might be useful as a start.

You post that the DGV has the properties of the Data Table, No - the DataTable is attached to the DGV and the DGV manages all objects attached as a data source. The Cell is the wrapper of the field and applies all formatting and style. This is not part of the table or the database.

The linked examples I posted demonstrate this fairly explicitly. They also show how to manage a table in a form with multiple bound controls.

I suggest that deep learning of each control is required. All controls are complex so learning controls from the ground up is the only way to understand teh complexity. All complex controls are collections of other controls all based on "Control".

Start with: https://docs.microsoft.com/en-us/dotnet ... etcore-3.1

The big issue will be understanding how PowerShell works with WinForms. PowerShell is adapted to work with Net classes in specific ways. With WinForms it interacts through events. PS can only support one thread with Forms unless you use Runspaces. The examples are all for C# and specify threads. Until you understand all of this ignore references to threads,

User avatar
Lembasts
Posts: 293
Joined: Thu Mar 15, 2012 9:47 am

Re: DataTable and DataGridView relationship

Post by Lembasts »

Thanks - Ive written thousands of lines of code using powershell studio with awesome apps that the users love using a whole bunch of controls which work fine.
A deeper understanding would probably let me shrink my code Im guessing.

User avatar
Lembasts
Posts: 293
Joined: Thu Mar 15, 2012 9:47 am

Re: DataTable and DataGridView relationship

Post by Lembasts »

btw - interested why your last link refers to dotnet core rather than dotnet framework?

jvierra
Posts: 14444
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: DataTable and DataGridView relationship

Post by jvierra »

Lembasts wrote:
Wed Jun 03, 2020 4:08 pm
btw - interested why your last link refers to dotnet core rather than dotnet framework?
This is one thing I try to get PS users to understand - how to read and use technical documentation.

The answer is that, if you look at the bottom of the page, you will see that it applies to all versions of WinForms.

All vendors do docs in subtly different ways. The first thing to do is to understand how they display documentation. This has always been fundamental to using technology and has been that way for decades. Somehow current users have not learned that which makes things much harder for them.

I would assess that nearly 90% of all questions asked in forums can be answered by looking at the documentation.

Don't feel bad. I had the same issues trying to get my programming teams to learn to use docs. When they finally took the time to understand how they found they had more freedom and more time.

jvierra
Posts: 14444
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: DataTable and DataGridView relationship

Post by jvierra »

Lembasts wrote:
Wed Jun 03, 2020 3:24 pm
Thanks - Ive written thousands of lines of code using powershell studio with awesome apps that the users love using a whole bunch of controls which work fine.
A deeper understanding would probably let me shrink my code Im guessing.
Shrinking code is good. Better yet you would have less trouble figuring out how to get what you want. I have worked with many programmers that have written hundreds of thousands of lines but they still didn't know how to write a program. It is referred to as brute force programming or keep coding until it works.

The solution is always to learn the fundamentals correctly and not by asking for examples for simple things that you have never seen before.

Programming is like being in college every day. Everyday we need to do research on various things. We spend more time learning as it allows us to write better, safer and more reliable code much faster.

User avatar
Lembasts
Posts: 293
Joined: Thu Mar 15, 2012 9:47 am

Re: DataTable and DataGridView relationship

Post by Lembasts »

One (maybe) last thing. What is the best way to hide data from a DGV?
Example - I have a DataTable from Active Directory with a name and guid. I only want to display the name in the DGV but if they select that, I need to reference the guid to access the AD object.
Using my old method with a checkedlistbox I would:
$checkedlistbox.displaymember = 'name'
$checkedlistbox.items.addrange($adobjs)
...where $adobjs had both a name and guid property but only the name would be displayed but I could easily access the guid property if required.
For a DGV all I can think of is to make the guid column hidden?

oh and can I add constraints to a datatable in powershell to avoid duplicates?

User avatar
Lembasts
Posts: 293
Joined: Thu Mar 15, 2012 9:47 am

Re: DataTable and DataGridView relationship

Post by Lembasts »

OK - I found the constraint bit - easy to add duplicate keys on a datatable to avoid duplicates.

jvierra
Posts: 14444
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: DataTable and DataGridView relationship

Post by jvierra »

There is no way to answer your questions correctly ithout knowing what you are doing and why.

A DataTable object can enforce data constraints if it is attached to a database correctly. This allows you to enforce integrity checks and to easily update changes.

You can add a key to a DataTable object with the methods on the object.

This is another case where you need to check the documentation to see what is available and to see examples of how to use the object. You can keep guessing but that will only cause you to create bad code and it will take you forever to build anything.

See: https://docs.microsoft.com/en-us/dotnet ... etcore-3.1

$datatable.PrimaryKey

For a dataset you can use "Constraints" to specify "Foreign Key" columns. This is how we establish table relations in data.

We can also use a view to restrict the rows displayed.

Almost all elements of a relational database can be synthesized with the "System.Data" namespace objects. The key is to understand how this data technology is implemented and how it is designed to support programmers. It is the same issue as with Forms and controls. THe key is to learn this so you can correctly understand how to use it and how to design solutions with this.

User avatar
Lembasts
Posts: 293
Joined: Thu Mar 15, 2012 9:47 am

Re: DataTable and DataGridView relationship

Post by Lembasts »

I figured out the constraints bit. I was after info on how to hide stuff thats in a DataTable from the DGV.
I know I can hide columns but is that the best way?

Locked