Using My.Settings to save a DataGridView's Column Size and Column order

Article
2017-09-02

Question

_{Saturday, September 2, 2017 1:10 AM}

Hello Community,

So to setup a My.Settings system to save a users preferred column order and column sizes for any given DataGridView seems a little more problematic than I first expected.

There doesn't seem to be a binding property for column size or column (collection) order.

Short of applying the current column order (by index) and then each column size to My.Settings as the DataGrid closes, and then re-applying it to the same datagridview next session...

Is there an easier way to get this done I am missing please. As I said I cant seem to find a binding property for either event.

I am expecting the answer is no, and it has to be done programmatically.

Is just that we have a ton of datagrids to handle in this way, to find an easier method would really make things, well easier.

thank you for reply's in advance, all help is appreciated

All replies (17)

_{Wednesday, September 6, 2017 11:18 PM ✅Answered}

Here is the solution on Microsoft OneDrive.

https://1drv.ms/u/s!AtGAgKKpqdWjiGTqiL7QEU7ppVEJ

What the Visual Studio solution looks like, one vb.net and one C# project where the C# project has the DataGridView.

Download, build then run followed by moving columns, resizing columns. Close the app, re-open to see the magic.

How I did it (also see comments in the code).

I built the C# project which in turn places the DataGridView in the IDE toolbox.

Then add columns, set the data property name for each column. Setup the data via a class dedicated to data operations (well not much there but enough to roll with for this sample)

Imports System.Data.OleDb

Public Class Operations
    ''' <summary>
    ''' Crate a proper connection to our database
    ''' </summary>
    Private Builder As New OleDbConnectionStringBuilder With
    {
        .Provider = "Microsoft.ACE.OLEDB.12.0",
        .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
    }
    ''' <summary>
    ''' Container to load data in from LoadCustomerData method below
    ''' </summary>
    ''' <returns></returns>
    Public Property CustomersDataTable As DataTable
    Private mException As Exception
    ''' <summary>
    ''' Allow read access to exception
    ''' </summary>
    ''' <returns></returns>
    Public ReadOnly Property Exception As Exception
        Get
            Return mException
        End Get
    End Property
    ''' <summary>
    ''' Get customer data into our class level DataTable
    ''' </summary>
    ''' <returns></returns>
    Public Function LoadCustomerData() As Boolean
        CustomersDataTable = New DataTable
        Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
            Using cmd As New OleDbCommand With {.Connection = cn}
                cmd.CommandText =
                    <SQL>
                        SELECT 
                            Identifier, 
                            CompanyName, 
                            ContactName, 
                            ContactTitle 
                        FROM Customers
                    </SQL>.Value

                Try
                    cn.Open()
                    CustomersDataTable.Load(cmd.ExecuteReader)
                    Return True
                Catch ex As Exception
                    mException = ex
                    Return False
                End Try
            End Using
        End Using
    End Function

End Class

Wrote code in the form (with a good deal of comments)

Public Class Form1
    Private ops As New Operations
    ''' <summary>
    ''' GfDataGridView1 is the custom DataGridView done in C# included in this solution.
    ''' I load data from MS-Access, could had been MySQL or SQL-Server, all that matters
    ''' for this demo is we are loading the database table data into a DataTable. You could
    ''' also load the data into other containers e.g. a DataSet but what is a DataSet, a collection
    ''' of DataTable objects so this should be clear. If you are using TableAdapters it will work also
    ''' and highly advise to stay away from TableAdapters.
    ''' 
    ''' I could had thrown in a BindingSoource and even a BindingNavigator, it still works :-)
    ''' 
    ''' IMPORTANT: I created columns in the form designer for the DataGridView and set the DataPropertyName
    ''' to the field from the DataTable to show data for each column. I would normally hide the primary key
    ''' Identifier but kept it in sight for allowing more columns to be resized and moved so the 
    ''' custom DataGridView can show itself off.
    ''' 
    ''' Note AllowUserToOrderColumns = True must be set to true else you can't reorder the columns.
    ''' Feel free to rename the DataGridView as you see fit.
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        GfDataGridView1.AllowUserToOrderColumns = True
        If ops.LoadCustomerData() Then
            ' yipie we have load data so set the DataSource to the data just loaded
            GfDataGridView1.DataSource = ops.CustomersDataTable
        Else
            ' if something went wrong, report it.
            MessageBox.Show($"Failed to load data.{Environment.NewLine}{ops.Exception.Message}")
        End If

    End Sub
End Class

That is it. Make sure to read the comments in the form above.

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

_{Thursday, September 7, 2017 8:24 AM ✅Answered}

Grats Frank, will do. Ty for you help as always

The problem is the when, not so much the what:

I was thinking along the same lines that Acamar has suggested; I'd just do it differently with binary serialization, but the *when* is the critical part.

Maybe there aren't yet columns there because it's unbound and your program hasn't yet gotten around to setting it up or maybe it's databound and your program hasn't gotten to the binding (or it's not yet complete).

I have no idea about that because I'm only looking at a part of it not the whole thing. If you look at the link that Karen originally posted, about halfway down you'll see when he's saving (in the .Dispose method) and when he's reloading (when it's being created), that makes perfect sense.

The *what* wouldn't be that involved, but the when surely is! ;-)

"A problem well stated is a problem half solved.” - Charles F. Kettering

_{Saturday, September 2, 2017 2:10 AM}

Is just that we have a ton of datagrids to handle in this way, to find an easier method would really make things, well easier.

You will have to do it programmatically, but do not use separate values for each item. Create a specialised string collection where each string represents the position and width of one column. The array therefore defines all columns. Put the code in a method that saves/restores the DGV layout using a settings property that is based on the DGV Name property, so the same method works for all DGVs in the solution.

_{Saturday, September 2, 2017 11:31 AM}

If you don't mind using a custom DataGridView there is the following on Code Project

https://www.codeproject.com/Articles/37087/DataGridView-that-Saves-Column-Order-Width-and-Vis

Download the source (first link), extract the project gfoidDataGridView, add it to your solution and build. Now when adding the control to your solution Visual Studio will do a upgrade unless you are using an older version of Visual Studio.

Use it by selecting from the toolbox

Since this custom DataGridView inherits from DataGridView you use it no differently. Column reorder, column width and visibility are stored in user.config under the user profile e.g. C:\Users\Karen\AppData\Local...

Anyways I tried this in a vb.net project and works as advertised.

_{Wednesday, September 6, 2017 9:06 PM}

Download the source (first link), extract the project gfoidDataGridView, add it to your solution and build. Now when adding the control to your solution Visual Studio will do a upgrade unless you are using an older version of Visual Studio.

Thank you again for help, but sadly I have no Idea how to add this to my solution. If at all possible please, a link to explain this would be greatly appreciated...

I have download and extracted the gfoidDataGridView project as instructed. But I have no idea how to add it to my own project. Or how to use, initiate or activate it once its there.

No doubt it is relatively straight forward
I apologize for my ignorance and appreciate any further help you can give.

Rob
{o_o}

EDIT: I have temporarily unmarked your response as the valid answer to facilitate your reply (if you can ofc). I will replace it asap.
Again thank you for your assistance

_{Wednesday, September 6, 2017 9:32 PM}

ah,

I wish I'd not missed this when you posted it, but I don't think it would be that hard to do (famous last words), would it?

Not with Application Settings, but set up a class which can be persisted then iterate the **DataGridViewColumn**s and get the properties?

You'd want to persist the .Width property and the .DisplayIndex property. Maybe I'm missing it?

"A problem well stated is a problem half solved.” - Charles F. Kettering

_{Wednesday, September 6, 2017 9:53 PM}

Thank you again for help, but sadly I have no Idea how to add this to my solution. If at all possible please, a link to explain this would be greatly appreciated..

You haven't indicated which design decisions you have decided on, so I will make some assumptions.

-Each DGV will get its own settings variable.
-The settings variable name will decided at edit time
-The settings variable will be created manually. It is type String.
-The settings variable name will be in the tag property of the DGV

Alternatives include using a single settings variable for each DGV (that would require a specialised string array for the settings variable, rather than a single string for each DGV), adding more information to the saved data, and creating the settings variable automatically from the control name.

    Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
        For Each C As Control In Controls
            If TypeOf (C) Is DataGridView Then
                Dim DGV As DataGridView = CType(C, DataGridView)
                If My.Settings(DGV.Tag) <> "" Then
                    Dim Columns() As String = My.Settings(DGV.Tag).split("~")
                    For I As Integer = 0 To Columns.Count - 2
                        Dim S As String = Columns(I)
                        Dim Details() As String = S.Split("|")
                        DGV.ColumnCount = Columns.Count
                        DGV.Columns(I).HeaderText = Details(0)
                        DGV.Columns(I).DisplayIndex = CInt(Details(1))
                        DGV.Columns(I).Width = CInt(Details(2))
                    Next
                End If
            End If
        Next
    End Sub

    Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
        For Each C As Control In Controls
            If TypeOf (C) Is DataGridView Then
                Dim DGV As DataGridView = CType(C, DataGridView)
                Dim S As String = ""
                For Each Column As datagridviewcolumn In DGV.columns
                    S &= Column.HeaderText & "|" & Column.DisplayIndex.ToString & "|" & Column.Width.ToString & "~"
                Next
                S.Trim("~"c)
                My.Settings(DGV.Tag) = S
            End If
        Next
    End Sub

_{Wednesday, September 6, 2017 10:26 PM}

Im a bit confused now...

There are around 2 dozen datagridviews throughout our app to track everything from client attendance, stock depletion and stock reporting (such as reconcile stock and daily, weekly, monthly stock usage)

So I had setup a textfile to save each datagrids column order, column visible status and column width between sessions.

Each user that is added to the app has its own text file to separate each user preferences.
Having so many datagrids to manage, I was hoping for an easier system than a text file for each user.

I am sorry I have not volunteered the correct information you need to help me...
I will work through what you both have added and reply from there.

Thank you for you replies, I appreciate you patience and ongoing help (would be utterly lost without MSDN). And again my sincerest apologies for my ignorance.

_{Wednesday, September 6, 2017 10:41 PM}

Probably I miss also something. What is easier than creating a method (sub) to save your setting and use that all the time.

You can even create a separate (helper) class for that and put that in a library.

The only thing you pass is the DataGridView from which you want the datagridviews to be saved in the setting. You can use the properties of that passed Control to do all the work.

In that way you can have thousand datagridviews and only code it one time.

Success
Cor

_{Wednesday, September 6, 2017 10:42 PM}

Download the source (first link), extract the project gfoidDataGridView, add it to your solution and build. Now when adding the control to your solution Visual Studio will do a upgrade unless you are using an older version of Visual Studio.

Thank you again for help, but sadly I have no Idea how to add this to my solution. If at all possible please, a link to explain this would be greatly appreciated...

I have download and extracted the gfoidDataGridView project as instructed. But I have no idea how to add it to my own project. Or how to use, initiate or activate it once its there.

No doubt it is relatively straight forward
I apologize for my ignorance and appreciate any further help you can give.

Rob
{o_o}

EDIT: I have temporarily unmarked your response as the valid answer to facilitate your reply (if you can ofc). I will replace it asap.
Again thank you for your assistance

Hang in there, I'm putting something together for you.

_{Wednesday, September 6, 2017 10:45 PM}

Each user that is added to the app has its own text file to separate each user preferences.
Having so many datagrids to manage, I was hoping for an easier system than a text file for each user.

What is a 'user' if it's a Windows user then all you need to do is adjust the code that saves the text to the text file so that it saves the text to a specialised string collection instead, create a settings variable of the same type, and save to that settings variable instead of writing to a file. Reverse the change for loading the settings.

If a 'user' is application-defined then you can do the same thing, except that you will need a settings variable for each 'user'. As the application must be managing 'users', you can manage the settings variable at the same time.

_{Wednesday, September 6, 2017 10:48 PM}

Im a bit confused now...

There are around 2 dozen datagridviews throughout our app to track everything from client attendance, stock depletion and stock reporting (such as reconcile stock and daily, weekly, monthly stock usage)

So I had setup a textfile to save each datagrids column order, column visible status and column width between sessions.

Each user that is added to the app has its own text file to separate each user preferences.
Having so many datagrids to manage, I was hoping for an easier system than a text file for each user.

I am sorry I have not volunteered the correct information you need to help me...
I will work through what you both have added and reply from there.

Thank you for you replies, I appreciate you patience and ongoing help (would be utterly lost without MSDN). And again my sincerest apologies for my ignorance.

ah,

I don't know who you're addressing but if you're interested, I'll put something together tomorrow. On your end, it'll be pretty easy to manage (I'm thinking about just two public methods that you'd work with).

Are the DataGridViews on multiple forms? That can be managed but I do need to know this in advance.

"A problem well stated is a problem half solved.” - Charles F. Kettering

_{Wednesday, September 6, 2017 11:01 PM}

Have the coding done, just writing comments to assist and will upload and provide link.

_{Wednesday, September 6, 2017 11:05 PM}

Not with Application Settings, but set up a class which can be persisted then iterate the **DataGridViewColumn**s and get the properties?

You'd want to persist the .Width property and the .DisplayIndex property. Maybe I'm missing it?

Thankyou for your help yet again Frank...

I was hoping to attach the datagrid preferences to the same system that will handle other user preferences such as form and toolstrip locations and sizes.

At first glance using the "User" scope property within My.Settings seemed less problematic to setup.

As far as the datagrids go I would like to save users Column index order, column width, and Column visible status.

A class instance (sourced from a seperate saved textfile for each user) on user login via the app (not windows login) is how I have it setup atm.

_{Wednesday, September 6, 2017 11:11 PM}

Not with Application Settings, but set up a class which can be persisted then iterate the **DataGridViewColumn**s and get the properties?

You'd want to persist the .Width property and the .DisplayIndex property. Maybe I'm missing it?

Thankyou for your help yet again Frank...

I was hoping to attach the datagrid preferences to the same system that will handle other user preferences such as form and toolstrip locations and sizes.

At first glance using the "User" scope property within My.Settings seemed less problematic to setup.

As far as the datagrids go I would like to save users Column index order, column width, and Column visible status.

A class instance (sourced from a seperate saved textfile for each user) on user login via the app (not windows login) is how I have it setup atm.

Hey Rob,

Good to see you again.

What I have in mind will be easier than that; at least to the point of transparency to what's going on behind the scenes, but let's do this: Wait until Karen and others post what they will and if you're still interested, tomorrow I'll set it up.

Are there other properties that you're letting your users change? We could - if you want - allow them to be selective about what they want to save/recall.

It's all up to how you want it but be patient and let's see how it plays out. :)

"A problem well stated is a problem half solved.” - Charles F. Kettering

_{Wednesday, September 6, 2017 11:18 PM}

Grats Frank, will do. Ty for you help as always

_{Thursday, September 7, 2017 12:56 AM}

Thank you immensely for the time to compile all that for my query. I Cant express my gratitude enough (really appreciate the extra REM's in the code too).

Am working through your example project now...

Share via

Using My.Settings to save a DataGridView's Column Size and Column order

Question

All replies (17)

Additional resources