Click here to Skip to main content
15,867,568 members
Articles / Desktop Programming / Windows Forms
Article

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

Rate me:
Please Sign up or sign in to vote.
4.81/5 (19 votes)
23 Oct 2007CPOL3 min read 113.2K   9.2K   143   16
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 :

VB
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)


Written By
Web Developer
Belgium Belgium
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionSort and filter datagridview with *.txt file instead of other database. Pin
Raven Saint15-Jun-15 7:11
Raven Saint15-Jun-15 7:11 
GeneralMy vote of 5 Pin
aalhanane29-May-12 9:36
aalhanane29-May-12 9:36 
QuestionHow do you see the filter/comboxlist Pin
GrahamEd7-Feb-12 7:01
GrahamEd7-Feb-12 7:01 
GeneralExcelent !!! Pin
jstecker1-Feb-12 6:20
jstecker1-Feb-12 6:20 
GeneralMy vote of 5 Pin
jstecker1-Feb-12 6:18
jstecker1-Feb-12 6:18 
QuestionDatatype that Can be used Pin
Vinay Indoria10-Dec-11 2:28
professionalVinay Indoria10-Dec-11 2:28 
AnswerRe: Datatype that Can be used Pin
Andy3210-Dec-11 3:32
Andy3210-Dec-11 3:32 
QuestionRe: Datatype that Can be used Pin
Vinay Indoria10-Dec-11 20:56
professionalVinay Indoria10-Dec-11 20:56 
You dont need to be sorry Andy,you did great work by developing such a grid.

So can u please list the datatypes that can be used.
You have covered varchar thats for sure and remaining datatypes i have not tried so can you please list them.
Its urgent.
Thank you.

-Vinay
GeneralThanks Pin
Vinay Indoria8-Dec-11 18:58
professionalVinay Indoria8-Dec-11 18:58 
GeneralMy vote of 5 Pin
Vinay Indoria8-Dec-11 18:57
professionalVinay Indoria8-Dec-11 18:57 
GeneralThanks Pin
YZK29-Mar-11 22:50
YZK29-Mar-11 22:50 
GeneralMy vote of 5 Pin
Global Analyser3-Nov-10 11:32
Global Analyser3-Nov-10 11:32 
QuestionRefresh & delete button is possible? Pin
la_morte19-May-09 6:23
la_morte19-May-09 6:23 
GeneralFiltering Data according to combo box selected item Pin
ayeshika14-Jun-08 10:47
ayeshika14-Jun-08 10:47 
GeneralConnection string Pin
paulsoren14-Mar-08 1:55
paulsoren14-Mar-08 1:55 
GeneralGood Work Pin
Oceanic67927-Feb-08 10:30
Oceanic67927-Feb-08 10:30 

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

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