Click here to Skip to main content
Click here to Skip to main content
Go to top

DataGridView with integrated filtering, sorting, and updating changes to SQL

, 23 Oct 2007
Rate this:
Please Sign up or sign in to vote.
Extended DataGridView with filtering, sorting, automatic saving, and a multicolumn combobox.

Screenshot - SQLDatagridview.png

Introduction

This little contribution is a DataGridView which auto-saves inserts and updates to an SQL database, extended with filtering and sorting on multiple columns, and as a bonus, it contains the code for a multicolumn combo box.

Background

As I work a lot with the DataGridView, I found it time consuming to write the code over and over for handling update and insert events. Furthermore, I would like to have the DataGridView extended with filtering and sorting on multiple columns. Therefore, I needed a DataGridView, based on SQL-data, which can be easily put on a form, and handles the data updates without the need for extra coding. I wrote down the tasks I frequently repeated, and started extending the DataGridView to my needs.

So, for starters, it would be nice if all the header text were caps, by default. Then, it would be nice if all double values are right outlined by default, with the correct format. Same for dates and integers. Also, nice, and added to the functionality, all columns have a minimum column width so that the header text is always visible.

I was also annoyed by the fact that the DataGridView, by default, adds text on the column names, so I got rid of that by renaming all DataGridView columns by default to the data property name. This can easily be done, although one must consider, when creating queries, the column names contain no underscores or non-standard characters.

Then, when column names were equal the data property names, I wrote some code which gets information on the data types for the columns, so the update and insert SQL statements could be generated and added to the functionality of the extended DataGridView. However, one must consider that the base table column names may not be altered to custom names in the query, otherwise the generated statements won't work. I myself always make sure that the columns for the table which needs to be updated have the exact column names in the query I put together for displaying the data. If someone rather uses different column names in the query, the class can be extended by adding a dictionary containing column mapping, for which the info can be passed through the form, and with a little adjustment of the generation of the SQL statements, this should work.

Then, I included filtering and sort capabilities in the header columns, which both support multiple columns.

I recently put together a DataGridView multiple column combo box, which I added to the project because I also wrote some code to handle possible data errors.

Using the code

We need a single line of code for loading the data, and a sub for handling the data updates and insert events, as shown below :

Private Sub Form1_Load(ByVal sender As Object, _
            ByVal e As System.EventArgs) Handles Me.Load
    RemoveHandler Me.NorthwindDataSet.Orders_Qry.RowChanged, _
                  New DataRowChangeEventHandler(AddressOf Rijgewijzigd)
    Me.Orders_QryTableAdapter.Fill(Me.NorthwindDataSet.Orders_Qry)
    Dim dv As New DataView(Me.NorthwindDataSet.Orders_Qry, "", _
                           "", DataViewRowState.OriginalRows)
    Me.filteredSource.DataSource = dv
    Me.SqlDatagridview1.Bind_to_Bindingsource(Me.filteredSource)
    AddHandler Me.NorthwindDataSet.Orders_Qry.RowChanged, _
               New DataRowChangeEventHandler(AddressOf Rijgewijzigd)
End Sub
Private Sub Row_Changed(ByVal sender As Object, _
            ByVal e As System.Data.DataRowChangeEventArgs)
    SqlDatagridview1.SaveDatatoDatabase("Server=.\SQLExpress;AttachDbFilename=" & _
                     Application.StartupPath & "\northwnd.mdf;Database=Northwind;" & _ 
                     "Trusted_Connection=Yes;", e, "orders")
End Sub

By using this minimal code, you have a DataGridView control supporting multiple column filtering and sorting, which automatically updates changes and new records to the SQL database. If you want to use this DataGridView with another data source, such as OLEDB for example, a little re-coding should do the trick.

I added the Northwind database as an example; however, the auto-generation of SQL inserts and updates works with any SQL-database; the only thing to do is to make sure the columns of the table which needs to be updated have the same column names in the query as they do in the actual SQL table.

Hope this can be of use for someone.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Andy32
Web Developer
Belgium Belgium
No Biography provided

Comments and Discussions

 
GeneralMy vote of 5 Pinmemberaalhanane29-May-12 9:36 
QuestionHow do you see the filter/comboxlist PinmemberGrahamEd7-Feb-12 7:01 
GeneralExcelent !!! PinmemberJonathan Steckeer1-Feb-12 6:20 
GeneralMy vote of 5 PinmemberJonathan Steckeer1-Feb-12 6:18 
QuestionDatatype that Can be used PinmemberVinay Indoria10-Dec-11 2:28 
AnswerRe: Datatype that Can be used PinmemberAndy3210-Dec-11 3:32 
QuestionRe: Datatype that Can be used PinmemberVinay Indoria10-Dec-11 20:56 
GeneralThanks PinmemberVinay Indoria8-Dec-11 18:58 
GeneralMy vote of 5 PinmemberVinay Indoria8-Dec-11 18:57 
GeneralThanks PingroupYZK29-Mar-11 22:50 
GeneralMy vote of 5 PinmemberHeaven20203-Nov-10 11:32 
QuestionRefresh & delete button is possible? Pinmemberla_morte19-May-09 6:23 
GeneralFiltering Data according to combo box selected item Pinmemberayeshika14-Jun-08 10:47 
GeneralConnection string Pinmemberpaulsoren14-Mar-08 1:55 
GeneralGood Work PinmemberOceanic67927-Feb-08 10:30 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 23 Oct 2007
Article Copyright 2007 by Andy32
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid