Saturday, May 3, 2014

A .NET WinForm DataGridView with advanced capabilities

updated to version 1.0.19617.11

This components is a .NET WinForm DataGridView with advanced capabilities, it is an extention of the .NET WinForm DataGridView.


The project is a refactoring of "Advanced DataGridView with Excel-like auto filter" you can find here: https://adgv.codeplex.com.
The author, also post this as a patch in the project page.

This Enhanced DataGridView components features:
  • Ascending or Descending Sort order for any column, by code or run-time
  • Filter any column using a Custom Filter, or selecting values from using an Excel-like Filter List view, using also an advanced feature for DateTime types
  • Saving and Restoring preset for Filter and Sort
  • Enabling or Disabling Filter and/or Sort features for any column,by code or run-time
  • Search ToolBar to find values in columns
A Sample project is attached in code to test this DataGridView.

It is written using Microsoft Visual Studio 2012, using the .NET 4.0 framework.

Copyright (c), 2014 Davide Gironi <davide.gironi@gmail.com>
Original work Copyright (c), 2013 Zuby <zuby@me.com>
This project is licensed under the Microsoft Public License (Ms-PL), see attached LICENSE file for further information.


ChangeLog

  • 1.0.x.11: fixed DateTime filter problem
  • 1.0.x.10: fixed AdvancedDataGridViewSearchToolBar problem with not Visible column search
  • 1.0.x.9: first release


Code

Notes
  • read risk disclaimer
  • excuse my bad english

99 comments:

  1. Hello
    Displaying the project I would like to know if there in VB. I have translated with code translators (Instant for VB from Tangible Solutions) but does not work in especially in the case of filter dates.
    I think the problem is in the translation BuildNodes(ByVal vals As IEnumerable(Of DataGridViewCell)) for data type for dates

    Sorry for my bad English
    Regards from Spain and thanks
    Eduardo my mail is e.gomez.lopez@hotmail.com

    ReplyDelete
    Replies
    1. This project is written in C#, no VB, but you could use it in any VB.NET WinForm project, adding the compiled dll as a reference. I've never try auto-transaltor. You should modify the c# code, instead of translatin it.

      Delete
  2. Hello,
    I have a problem. When I filter a column checking "Blanks" value, then I try to reset Filter, but all the data get lost and therse no way to getting back even if i try to repopulate databinding.

    ReplyDelete
    Replies
    1. Hello,
      unluckly i don't get this issue. Try to tell me step by step how do you get this error. Let's take AdvancedDataGridViewSample as example, if I open this sample program, the i click on the "string" column filter/order icon, then i select only the "(Blanks)" value. I get the blank filtered. Then if i check "(Select All)" or i click "Clean Filter And Sort" button, the filter resets, and the column are all repopulate.

      Delete
  3. Thanks for answer Davide. Im gonna try with example and see wath happens. comming back with coments!

    ReplyDelete
  4. How can I implement this in a vb project on an already created and bound DataGridView? I can't get it to work. An example of the code would be nice. Thanks

    ReplyDelete
    Replies
    1. Hello, there are at least two ways. One is to remove your old datagridview components form you form, and then add this new grid you have added to your toolbox. Another is just to rewrite the code of the designer to initialize the AdvancedDataGridView. Before starting up to replace the DataGridView of you project, my suggestion is to recreate the sample project "AdvancedDataGridViewSample" in VB, the porting will be easy.

      Delete
  5. David, this is the best example of what Microsoft SHOULD do, but do not. However, like Colton, I need a better understanding of how to implement this into a project using VB. I have import the dll but no extra datagridview appears in my toolbox.

    Can you please provide some support code to use your product?

    ReplyDelete
    Replies
    1. Thank you. Before using this components. You should add this item to the toolbox. Right-click on toolbox - Select "Choose Items" - Browse to your DLL - Add the item.
      Then use this as a normal grid, You just have to attach two handler in order to make it works, the FilterStringChanged and the SortStringChanged, set the BindingSource Sort and Filter property here, take a look at the Sample project. Then there are other thing you can do with this grid, but that's the basic.

      Delete
    2. hi Davide very good job
      in the filteringstringchanged he didnt recognise the zuby from the zuby.adgv.advanceddatagridview
      thanks

      Delete
    3. Hello, tahnk you for this feedback. Please be more specific, I can not understaind where's the missing attach point here.

      Delete
    4. thanks davide for the reply
      i put reference to your dll file and it worked
      thanks a lot and again good job

      Delete
    5. Happy to hear that. Thank you and good work.

      Delete
  6. Davide,
    I've downloaded the source code and tried to load your solution for some testing. Unfortunately, the source doe zip file you provided seems to have a problem. When I load it in VS 2012 or 2013 the code runs fine but trying to open the AdvancedDataGridViewSearchToolBar and AdvancedDataGridView components just come up blank on the design surface. What am I missing?

    Thanks,
    Stephen

    ReplyDelete
    Replies
    1. Hello, I've imported it to VS2013, and rebuild the project. No problem at all here. It happens even if you drag and drop a new AdvancedDataGridView from the toolbox to the FormMain of the sample project?

      Delete
  7. No, I can use the ADGV control with out problems but what doesn't seem to be working for me is opening the Advanced DataGridView CS project and trying to view the AdvancedDataGridView.cs control on the designer surface.

    ReplyDelete
    Replies
    1. The AdvancedDataGridView class in an extention to the DataGridView, then, it will not be painted by the visual studio designer as an editable component. Sorry about that.

      Delete
  8. @Davide Thanks for example actually i am working on similar approach i am pass the the data table for grid view



    DataTable dataobj = new DataTable();

    dataobj.Load(rdr); // some query and it have some data in it

    advancedDataGridView_main.DataSource = dataobj; // pass to your grid view component my data table


    and filter function of excel ( kind of throwing some exception )

    can you help me out


    my showing some example

    passing the datatable for gridview ?

    ReplyDelete
    Replies
    1. Hello and thank you for your feedback. If you look at the FormMain constructor in the FormMain class of the AdvancedDataGridViewSample
      project, you could see how i load the AdvancedDataGridView DataSource.

      Delete
  9. Davide, I really need some help. I am populating the ADGV with database query. No can you give the population of ADGV with some database example.
    After I load the ADGV with SQL Query, the ADGV load the data properly and shows the filters as well. But I am not able to populate the datasource back while filtering. Can you explain completely with one example.

    My Source code is like below

    public void loadGrid()
    {

    sql = "Select * from tbl_IdDetails";
    sql1 = @" SELECT [Branch Code]
    ,[Server]
    ,[UserID]
    ,[NAME]
    ,[NNFCTCL]
    ,[NNF CASH]
    ,[FOCTCL] from tbl_IdDetails";

    objFunc.populateGrid(sql1,ADVANCEDDATGRID);



    }


    private void multiEditDataGridViewPanel1_FilterStringChanged(object sender, EventArgs e)
    {

    bindingSource_main.Filter = ADVANCEDDATGRID.FilterString;
    }

    I am not able to pass the data source back while filtering. Please help with an complete example. Thanks for your help.

    ReplyDelete
    Replies
    1. You can set you datagrid DataSource to your BindingSource, then you could load you BindingSource.DataSource with a DataTable loaded with the database rows.

      Delete
    2. Davide, Thanks for your reply. I tried but not able to figure it out. I am very new to dot net and Not able to manipulate much. Can you please give a complete example with full code??? A complete code with database connectivity?

      Thanks very much for your help in advance.

      Delete
    3. I'm sorry but I usually does not provide complete examples on request, because if I do this for you, I have to do this for any asking this, and I have not time to do this for any request. Really sorry.
      I can just poin you to the solution. First step: have you done this with a standard DataGridView?

      Delete
    4. Yes, I did this with a standard DataGridView. After that the filter is applied on them. The data with filter is coming properly. But when I use the filter, datagrid does not refresh with the filter.

      Please let me know how to set the filter with filter string. just that line of code.

      Delete
    5. Ok, let's do it at steps. Use an AdvancedDataGridView, with the attached FilterStringChanged method. Add a debug point on you set filter function (i mean here in your example above: bindingSource_main.Filter = ADVANCEDDATGRID.FilterString;) the when you trigger a filter event (as exemple by setting a custom filter), take a look to the "FilterString". Does it contains the proper filter string for the BindingSource?

      Delete
    6. Using the AdvanceDataGridView Control. This is the below complete control on load of the page and then on filter string. It is giving error that filter string is null

      public IdDetails()
      {
      InitializeComponent();
      _dataTable = new DataTable();
      _dataSet = new DataSet();

      //initialize bindingsource
      bindingSource_main.DataSource = _dataSet;

      //initialize datagridview
      multiEditDataGridViewPanel1.DataSource = bindingSource_main;

      //set bindingsource


      }



      public void loadGrid()
      {

      sql = "Select * from tbl_IdDetails";
      sql1 = @" SELECT [Branch Code]
      ,[Server]
      ,[UserID]
      ,[NAME]
      ,[NNFCTCL]
      ,[NNF CASH]
      ,[FOCTCL] from tbl_IdDetails";
      objFunc.populateGrid(sql, multiEditDataGridViewPanel2);
      objFunc.populateGrid(sql1, multiEditDataGridViewPanel1);
      objFunc.DatatableData(sql1, _dataTable);

      multiEditDataGridViewPanel2.Columns[3].Frozen = true;


      }






      private void multiEditDataGridViewPanel1_FilterStringChanged(object sender, EventArgs e)
      {

      bindingSource_main.Filter = multiEditDataGridViewPanel1.FilterString;
      }

      private void IdDetails_Load(object sender, EventArgs e)
      {
      loadGrid();
      objFunc.InitializeEditableDataGridView(multiEditDataGridViewPanel2);


      }

      Delete
    7. Hello, please do not flow with comments of code here. Instead build a little project sample. Do not load data with a dabatase but just create sample data. Then, when you've got this, share the solution link one can download to test. From what i see it could be the populateGrid function. If the FilterString is null, something goes wrong in the filter creation procedure. You can see it debugging the code from the advanceddatagrid.

      Delete
  10. Hello, how can I make this sln file work with my VS 2008? It is a requirement of me to use VS 2008 at the moment, so can't change to newer editions.

    ReplyDelete
    Replies
    1. You can create a new solution, new project and import files. Or you can try to edit by hand the csproj/vbproj files setting "Microsoft Visual Studio Solution File, Format Version" to 10.00 (which is Visual Studio 2008), but I'm not shure it will works.

      Delete
    2. This comment has been removed by the author.

      Delete
    3. Thank you for the input. It already is set to 10.00, unfortunately. If theoretically I build this from a newer edition of VS 2008 and get the dll and include it to my references, how would I use it in my project? Is it a completely new control type which I must substitute in place of the normal datagridview?

      Delete
    4. It's an extention of the DataGridView, you can build with other VisualStudio as long as you are using .NET 4.

      Delete
  11. I'm trying to use it, but I can't see how to set BindinSource DataSource with dataSet, like the sample. There's no step by step to use this component, so it's not very helpful. :(

    Also, if I don't populate the grid with DataTable (I'm currently using AddRows), is it possible to still make ADGV work?

    Thanks!

    ReplyDelete
    Replies
    1. Hello, you just have to use attach the Bindisource like you usually do on DataGridView. Looking at the sample provided, the line "advancedDataGridView_main.DataSource = bindingSource_main;".

      Delete
    2. Right. I've got it. The only problem now is that I don't know how to pass a list of DataGridViewRow to the BindingSource DataSource, because by doing this I see all DataGridViewRow class members instead of Cells content only. This code doesn't seem to work:

      bindingSource.DataSource = dgvRowsList;
      bindingSource.DataMember = "Cells";
      datagrid.DataSource = bindingSource;

      Any idea?

      Thanks once again :)

      Delete
    3. You could load a typed IEnumerable (let's say for example you have a class Person, you will load a IEnumerable enumberablePerson = ...), then convert it to a DataTable and load it as your BindingSource.DataSource by using something like this: yourBindingSource.DataSource = DGDataTableUtils.ToDataTable(enumberablePerson);
      You can find the ToDataTable here: https://gist.github.com/davidegironi/9348c74c01d979942901#file-dgdatatableutils-cs
      Also remember to set the DateGridView.DataSource to the BindingSource, like you are doing: yourDataGridView.DataSource = yourBindingSource

      Delete
    4. Hmmm... For architecture reasons I'm trying to avoid DataTables. :(

      Delete
  12. This comment has been removed by the author.

    ReplyDelete
  13. Hi David - new to your site - have downloaded the dll and added to both references and to the toolbox in VS2013. Bound datasources for 2 applications - one VB net and the other is in C# -- both access and load the data, but alas the autofiltering function and sorting both don't respond. The complete dataset remains displayed as initially loaded. Any ideas? =Vytas A

    ReplyDelete
    Replies
    1. Hello. Have you FilterStringChanged and SortStringChanged EventHandler to you AdvancedDataGridView instance? Take a look to the FormMain from the provided sample as reference.

      Delete
  14. I am unable to add the DLL in tool box in VS2015. Please let me know if I need to do anything specific to make it compatible with VS2015

    ReplyDelete
    Replies
    1. Hello, Does the sample project provided with code get imported and runned?

      Delete
  15. This comment has been removed by the author.

    ReplyDelete
  16. your project is really good, thank u, one problem that i have is, i want to enable only one column sort like excel. if i sort a second column the old sort filter in the other column should be cleared like that of excel. is there any way to do this?? Thank you..

    ReplyDelete
    Replies
    1. You have to modifiy the code. You could change the MenuStrip.SortChanged event in order to CleanSort just for the not selected columns.

      Delete
  17. hi,thank u for replying,i added an else{cleansort} for cell_sortchanged its giving the desired result,but the ascending and descending image don't change..any ideas

    ReplyDelete
    Replies
    1. Images changes on sortASCMenuItem_Click and sortDESCMenuItem_Click methods, add a debug point there, see what's happenen...

      Delete
    2. ok, i will,is there anyway to disable custom sort and enable the default datagridview sort??
      Thank You

      Delete
    3. Not by default. MenuStrip is the key again for this.

      Delete
  18. Hi Davide,

    During a small project I am working on I found your remake of the ADGV Dataview.
    I am having a small issue and was hoping you can help me a bit.

    I’ve setup a DataBinding to an SQL server and the information is showing correct. However the filter and sort isn’t working.
    I found you posted this on your blog:

    just have to attach two handler in order to make it works, the FilterStringChanged and the SortStringChanged, set the BindingSource Sort and Filter property here, take a look at the Sample project. Then there are other thing you can do with this grid, but that's the basic.

    I can’t open the Sample project in 2015 and I am pretty new to programming so I was hoping you can help me a bit. Can you explain a bit what I need to do to get it working?

    Thank you!
    Mike

    ReplyDelete
    Replies
    1. Hello,
      This sample project was built in VS2012.
      VS2015 should be able to import the VS2012 project. I'm using VS2013 and VS2015 for other projects, and never encounter problems on opening VS2012 solutions with VS2013 or VS2015.
      What's the problem VS give you on opening the VS2012 solution?
      Anyway that's right, you just have to attach the Filter and Sort events.

      Delete
  19. Hello Davide. Good stuff here :)

    However the current date filtering wasn't working for me with a en-US culture.

    Long story short, here's a fix that should work across all cultures when it comes to the date formatting used in the controls current date filtering.

    The 2 files to modify are FormCustomFilter.cs and MenuStrip.cs.

    In MenuStrip on line 67-68 change const to readonly;

    private const string ConvertYearMonthDayFormat = "dd/MM/yyyy";
    private const string ConvertYearMonthDayHourMinuteSecondFormat = "dd/MM/yyyy HH:mm:ss";

    to

    private readonly string ConvertYearMonthDayFormat = dd/MM/yyyy";
    private readonly string onvertYearMonthDayHourMinuteSecondFormat = "dd/MM/yyyy HH:mm:ss";

    Then go to line 88 in the constructor and insert the following;

    ConvertYearMonthDayFormat = CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern;
    ConvertYearMonthDayHourMinuteSecondFormat = ConvertYearMonthDayFormat + " " + CultureInfo.CurrentCulture.DateTimeFormat.LongTimePattern;


    Just do the same thing to FormCustomFilter.cs.

    There may be a reason not to use these changes, but it's working for me in both my test cases.


    But anyways, good stuff :)

    ReplyDelete
    Replies
    1. Hello and thank you for your feedback!
      Can you try this version? http://wikisend.com/download/436394/AdvancedDataGridView-src_1.0.19617.11.zip
      I think I've fixed it for multi-cultures. I've checked it overriding the program culture by the code you find in sample Program.cs and it seems to me to work. Before posting it i just need your checking feedback. Thank you.

      Delete
    2. Note: link expires in 14 days.

      Delete
    3. Ok, I have it. I'll be able to check it in a couple hours.

      Delete
    4. Ok, that seems to have fixed it Davide. Good job.
      Yup, I'd release that version. It's pretty important :)

      On a side note. I have a bug fix/check (dealing with Image columns) and a new feature (dealing with the filtering) that I'm working on. I was thinking of publishing your source to BitBucket. If you don't have any objections that is. I can make you lead on it of course lol. This way others can issue Pull requests when they make good changes to thier own version.

      Up to you man. I'm gonna publish it anyways because I like source control hehe. But, I'll make it a private repo without your blessing.

      Delete
    5. Hello, thank you for your feedback. Yes, it's time to move this project to a CVS. I'm using bitbucket for my private repository, and GitHub for public. You can find the code on the GitHub link above. Hope it's not a problem for you GitHub instead of BitBucket.

      Delete
    6. Absolutely not man. I use GitHub too :)
      Cool.

      Delete
    7. David, could You upload egain your fixed Advenced DGV? I will be very greatfull for that :)

      Delete
    8. Hello, you can find the last version of this project (the fixed 1.0.*.11 one) in the gitub page, there you can also find the compiled release. I've to take time to test and merge the Earl pull, I hope to have time next week.

      Delete
    9. Great job man, very very helpfull!

      Delete
    10. Unfotunetly Your ADGV doesn't support a column type of 'Time'. I work on hh:mm:ss format and this is the same problem like before with dates. David, could You help me with that or give me some advice how to manage this problem?

      Delete
    11. Hi David,

      Thanks for providing this useful stuff.

      I have one problem with date time calendar control with Advance datagridview.

      After binding it make that calendar control column readonly(not editable).

      Can you please help in that?

      Delete
    12. Hello Bartosz, please check the new release, it should even work with TimeSpan.

      Delete
    13. Hello Brinda, I've checked it, but to me it seems to works. Can you packup a sample?

      Delete
  20. Hi there David, I was searching for something exactly like this for a project of mine and this seems like this will do the trick.
    I have a question for you though, the data gridview, lets say I added some data into the table which is displayed in the gridview from another form and when I open the form where the filter functionality is located, will the gridview show the updated/newly added values or not?

    I am asking this because currently i am not able to access my pc
    that is used for development.

    ReplyDelete
    Replies
    1. Hello, the databinding of this component works exactly like the Microsoft winform datagridview, so if you set the Datasource of this grid to a BindingSource DataSource, if you take a look to the sample provided code, you will notice that the DataSource of this component it is set to a BindingSource before the binding source is loaded, then when it is loaded by the SetTestData method, the table is populated.

      Delete
    2. Ok so I found out that the data/table is generated from here I guess.

      //initialize bindingsource
      bindingSource_main.DataSource = _dataSet;

      //initialize datagridview
      advancedDataGridView_main.DataSource = bindingSource_main;

      //set bindingsource
      SetTestData();

      so what should I add here if I want to add my own data table instead of the one used in the sample

      Delete
    3. You just have to set DataSource to your _dataSet, then load this with SetTestData

      Delete
  21. Could you tell me what changes I need to main in the FormMain so that My table is displayed rather than the one that is already being used

    ReplyDelete
    Replies
    1. Hello. To load the Main table would not be so difficult, but i think you have to understand why does it works that way. The best thing you can do is to follow some tutorial on .NET bindingsource tutorial.

      Delete
  22. Hi David,

    Thanks for providing this useful stuff.

    I have one problem with date time calendar control with Advance datagridview.

    After binding it make that calendar control column readonly(not editable).

    Can you please help in that?

    ReplyDelete
    Replies
    1. Hello, do you mean that all the cells of a datetime column are readonly, and column of other type, like string, has cells that be edited?

      Delete
    2. Yes actually I am using calendar control data grid view column in it.
      But figure out the solution I have to manually set that column property read only true and than its working fine.

      Want to take your guidance on another thing as well. If I use this grid for 8-10 columns its loading is pretty much fast 1-5 sec but when working with 100 columns its taking 40 secs which is bit more.

      Can you focus on that part of optimization?

      Delete
    3. For that amount of column you can disable the sorting andor filtering for all the column that you do not use. Or, if you want, you can try the optimization on the code on github, then do a push.

      Delete
  23. Is there an option of numeric sort? TIA.

    ReplyDelete
    Replies
    1. Hello, yes, it's the sort icon on each column header. So if the underline column has a numeric datatype it will be sorted by numeric order.

      Delete
  24. Hi Davide Gironi,

    I am using your datagridview and it is fantastic!!! I get data from database and bind to your advancedgridview and the filters are brilliant.

    There is a functionality in the app to get the latest data on pressing F5. When i remove the datasource and rebind with the new data, the filters are lost.

    Is there a way to retain the filters with refreshing of data. Please let me know asap. Thanks in advance.

    ReplyDelete
    Replies
    1. Hello, thank you for your feedback. Filter and sorting does not change even if you reload the attached BindingSource. And there's a save and restore filter and sort function.

      Delete
    2. Thanks for your response. Can u pls send me a sample code for that? Thanks.

      Delete
    3. This comment has been removed by the author.

      Delete
    4. This is how it works to me, without any change.

      Delete
    5. I did change the code as below:
      bdSharepointSource.DataSource = dtSharepoint
      bdSharepointSource.ResetBindings(False)

      I am updating datatable dtSharepoint and reassigning to binding source and calling resetbindings.

      It is working as expected. I could see the changes coming in. But when i click on clear filter of any one column, all filters are getting cleared. Can you please check whether this issue is there in your code and let me know? Thanks again for your help.

      Delete
    6. Staring from the project sample. Add a Reload button that perform
      _dataTable.Rows.Clear();
      AddTestData();
      In the sample, that way filters and sort does not change.

      Delete
    7. It worked. Thanks a lot for your help and timely help.

      Delete
  25. This comment has been removed by the author.

    ReplyDelete
  26. This comment has been removed by the author.

    ReplyDelete
  27. For an instance, I opened the filter menustrip by clicking the filter button. The filter menu is now open. If I want to close the filter menu, either I have to click on 'Cancel' button or I have to click somewhere else on the datagridview. But we I re-click on the same filter button (of the column which already menustrip is open), the filter menustrip closes and opens again. On this second click, it should only close and should not open again.

    ReplyDelete
    Replies
    1. Hello, and thank you for suggestion. I've reply you on GitHub.

      Delete
  28. Where to download more components like this ADGV? I would like to download an advanced listbox, which accepts multiple columns with multiple fields

    ReplyDelete
    Replies
    1. Hello, the only other "enhanced" components I've publish can be found here http://davidegironi.blogspot.it/2016/02/enhancedboxhelper-net-winform-component.html it is a TextBox, a ComboBox and a DateTimePicker. Hope this helps.

      Delete
  29. Hi Davide,

    Great component. Awesome Work. Seems my filter is hanging when I change data source. How to I call the clear filter?

    ReplyDelete
    Replies
    1. Never mind I found it. Silly me. Thanks
      bindingSource1.RemoveFilter();

      Delete
    2. Hello, thank you. Also thank you for posting your solution here.

      Delete
  30. private void fee_create1_Load(object sender, EventArgs e)
    {
    //return to datatabl
    this.advancedDataGridView1.DataSource = pardeep.Show_Data("SELECT * from student");
    }

    private void advancedDataGridView1_SortStringChanged(object sender, EventArgs e)
    {

    //what code type here to sort advancedDataGridView column
    }
    private void advancedDataGridView1_FilterStringChanged(object sender, EventArgs e)
    {
    //what code type here to filter advancedDataGridView column
    }

    ReplyDelete
    Replies
    1. Hello, it depends on what your "Show_Data" method returns. The best things is to attache de DataSource of a BindingSource to a dataset, the the DataSource of the datagridview to the BindingSource generated. You can take a look at the sample file provided in source code, it may help.

      Delete
  31. Is it possible grouping rows ?

    ReplyDelete
    Replies
    1. Hello, no unluckly this is not implemented.

      Delete